Garmin eTrex 20 handheld GPS (released in late September 2011)
SketchUp: A better view of small spaces
When you’re modeling a small room, it can be a pain to see what’s inside. The problem is that the walls and ceiling get in the way. One solution is to lop off the ceiling and work in a top view, dollhouse-style. Other folks set up scenes from the interior corners and adjust their Field of View to something super-wide like 90 degrees.
Looking at a small interior space from the outside isn’t very rewarding.
Deleting the ceiling and switching to a top view is useful, but fiddly.
Standing in the corner and making your Field of View really wide is just weird. What are you—a housefly?
Both of the above techniques work—to a point. Personally, I think it’s like trying to read a book through a keyhole. By far my favorite method for working on small interiors is to make use of SketchUp’s ability to have faces with different materials on each side:
The face separating Susan and Sang is yellow on one side and green on the other.
Creating a completely transparent material and painting the green side makes it see-through.
The Entity Info dialog box shows that the selected face is yellow on the front and see-through on the back.
By painting the outward-facing surfaces with a see-through material—one whose opacity is set to 0%—I can see in from the outside. Super useful, super simple.
Here, I painted all of the outward-facing surfaces with a transparent material. Notice that the interior surfaces still look opaque?
Orbiting around my model, I can see through all of the walls. I can even see through the floor.
Convert, Reproject, and Load Spatial Data to SQL Server with OGR2OGR
I used OGR2OGR to join a set of shapefiles together prior to loading them into SQL Server using Shape2SQL. But OGR2OGR can do much more than simply appending shapefiles – it can convert data into different formats, transform coordinates into different spatial references systems, read and write a whole variety of spatial datasources, and (with a bit of fiddling) load them into SQL Server 2008.
To demonstrate, I thought I’d repeat the objective of my previous post, but instead of simply appending the Ordnance Survey open data shapefiles together, I would reproject them into a different SRID, and import them into SQL Server too using OGR2OGR alone, without using Shape2SQL or any other tools.
Use OGR2OGR to create a CSV file containing WKT
OGR2OGR can’t insert spatial data directly into SQL Server 2008, but it can create CSV files that can be read into SQL Server. To create an output file in CSV format, set the –f CSV output option. You can also manually set layer creation options to dictate the field delimiter and line return character of the CSV file, using the LINEFORMAT and SEPARATOR layer creation options.
By default, the CSV file created only contains columns containing the attributes associated with each shape (i.e the data in the .dbf file associated with a shapefile), but it doesn’t include the shape itself. To include the shape of the geometry in Well-Known Text format, you also need to state the –lco “GEOMETRY=AS_WKT” layer creation option.
But that’s not all – OGR2OGR can also reproject data between coordinate systems as it converts it. To convert to another spatial reference system, include the -t_srs parameter, together with either the full WKT definition (hard to escape properly,i.e. GEOGCS(ELLIPSOID(“WGS 84”, ……) or the EPSG code (much easier, i.e. EPSG:4326 for WGS84).
Putting this all together, you can use the following command to load a shapefile (in this case, the Ordnance Survey OS Vector Map settlement_area polygon shapefile), reproject the data into SRID 4326, and then save the result as a CSV file containing a column with the WKT of each resulting polygon:
[php]ogr2ogr -f "CSV" "Settlement_Area" "Settlement_Area.shp" -t_srs "EPSG:4326" -lco "GEOMETRY=AS_WKT" -lco "LINEFORMAT=CRLF" -lco "SEPARATOR=SEMICOLON" [/php]
NOTE – I’ve noticed some problems with the quote/speechmark characters in this blog being changed to “smartquotes”, even when I use the code-formatting option. OGR2OGR isn’t happy about this, so I recommend that you don’t try and copy and paste the line above into a CMD window – if you get an error about “Couldn’t fetch request layer uT” or something similar then be sure to retype using normal speech marks.
More information on CSV options for OGR2OGR can be found here: http://www.gdal.org/ogr/drv_csv.html
Create a Format File
To import the CSV into SQL Server, we have a range of options – you could use SSIS, or you could use the Tasks –> Import Data wizard. Or you could use BCP or BULK INSERT. However, I’m going to use OPENROWSET. This will allow me to write a single query to access the CSV file directly from T-SQL as if it were an existing table.
In order to do this, we first need to define a format file which specifies the datatypes of each of the columns in the input csv file, and how they should be mapped to datatypes of columns in a sql table. More information on format files can be found here.
The following shows the XML format file required for the OS VectorMaps Settlement Area shapefile just converted:
[php]<?xml version="1.0"?>
<BCPFORMAT xmlns="http://schemas.microsoft.com/sqlserver/2004/bulkload/format" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
<RECORD>
<FIELD ID="0" xsi:type="CharTerm" TERMINATOR="""/>
<FIELD ID="1" xsi:type="CharTerm" MAX_LENGTH="2147483647" TERMINATOR="";"/>
<FIELD ID="2" xsi:type="CharTerm" TERMINATOR=";"/>
<FIELD ID="3" xsi:type="CharTerm" TERMINATOR="\r\n"/>
</RECORD>
<ROW>
<COLUMN SOURCE="1" NAME="WKT" xsi:type="SQLVARYCHAR"/>
<COLUMN SOURCE="2" NAME="FEATCODE" xsi:type="SQLDECIMAL"/>
<COLUMN SOURCE="3" NAME="FEATDESC" xsi:type="SQLVARYCHAR"/>
</ROW>
</BCPFORMAT>
[/php]
Query the CSV from SQL Server using the Format File
Finally, you can write a SELECT query in SQL Server to query the CSV file using OPENROWSET in conjunction with the format file above. Since the CSV contains column headers, I’ve included the FIRSTROW=2 parameter to skip the header row. I’ve also used the geometry::Parse() method in the SELECT to dynamically create the geometry data from the Well-Known Text representation contained in the WKT column of the supplied CSV file.
[php]SELECT
FEATCODE,
FEATDESC,
geography::Parse(WKT)
FROM OPENROWSET(
BULK ‘C:Settlement_Area.csv’,
FORMATFILE=’C:Settlement_Area_format_file.xml’,
FIRSTROW = 2
) AS CSV;[/php]
And here’s the results showing my Ordnance Survey data, originally provided as a shapefile using projected coordinates in SRID 27700, now loaded as latitude/longitude coordinates (EPSG:4326) using the geography datatype:
