OGR2OGR Patterns for SQL Server

The Geospatial Data Abstraction Library (GDAL) is an open source library and set of tools for converting and manipulating spatial data. While GDAL itself deals with raster data – GeoTIFFs, ECW, DEMs and the like – its sister project, OGR, deals with vector data formats such as ESRI Shapefiles, MapInfo, KML, or SQL Server geography/geometry data. The OGR2OGR utility is the specific GDAL/OGR component for transforming data between these different vector formats.

GDAL/OGR is pretty powerful, offering many of the same features as commercial GIS software costing £’000s. However, the tools are not that easy to use, exposing only command-line interfaces which are pretty intolerant to any syntax errors. What’s more, many of the OGR2OGR code examples you’ll find on the internet focus on PostGIS as a backend database (for which support is more mature) rather than SQL Server, as I typically use. So, here’s my own notes on some common patterns for using OGR2OGR to import and export spatial data to SQL Server.

Installation

• You can build GDAL/OGR from source, but an easy way to install and configure GDAL/OGR windows binaries is by downloading the OSGeo4W package.
• OGR2OGR runs from the command line, so you’ll either need to execute the application from the installation directory, or alternatively set a PATH to that directory so the ogr2ogr.exe executable can be found.
• Make sure you also set a system environment variable, GDAL_DATA, which points to the data directory in which GDAL support files gcs.csv and pcs.csv are saved. These files allow you to specify spatial references by their EPSG code.

General Notes

• For ease of reading, in the following code listings I’ve separated out each parameter value onto a separate line. However, you should enter them all on a single line, with no extra carriage returns.
• You don’t need to enclose every parameter value in “double quotes”, but you might find it easier if you do (especially if you’re going to be referencing directory paths containing spaces, for example)
• If you do use double quotes, you should use “straight quotes”, and not “smart quotes”.
• After importing any data, OGR2OGR will create two additional tables in your SQL Server database – spatial_ref_sys and geometry_columns, which store metadata about the spatial columns in the tables. These tables can be safely ignored (or deleted) – OGR2OGR will recreate them after each conversion anyway.
• The general pattern for any OGR2OGR conversion is:

  ogr2ogr
    -f {destination format}
    {destination}
    {source}
    {options}
  
  

• For more information:
  • From the command-line: ogr2ogr –help
  • OGR2OGR general syntax: http://www.gdal.org/ogr2ogr.html
  • SQL Server driver syntax: http://www.gdal.org/ogr/drv_mssqlspatial.html
  • ESRI Shapefile driver syntax: http://www.gdal.org/ogr/drv_shapefile.html

Importing Data to SQL Server

Import geography data from ESRI Shapefile to SQL Server

• The following code listing imports data from the tl_2010_06_zcta510.shp shapefile to the SQL Server instance running on localhost\SQL2012Express.
• The data is inserted into a geography column called geog4269 in the CaliforniaZCTA table of the ProSpatial database.
• The inserted geography data is assigned the EPSG:4269 SRID, which is the spatial reference system in which the data of the shapefile is already defined.
• A progress bar is displayed as the data is imported.

ogr2ogr
 -f "MSSQLSpatial"
 "MSSQL:server=localhost\SQL2012Express;database=ProSpatial;trusted_connection=yes;"
 "tl_2010_06_zcta510.shp"
 -a_srs "ESPG:4269"
 -lco "GEOM_TYPE=geography"
 -lco "GEOM_NAME=geog4269"
 -nln "CaliforniaZCTA"
 -progress

Import geometry data from ESRI Shapefile to SQL Server

• The following code listing takes data from the precincts.shp ESRI shapefile and loads it into the sql2012 SQL Server instance running on localhost.
• Since not explicitly specified otherwise, data is inserted into a default geometry column called ogr_geometry.
• The geometry data is assigned the projected SRID EPSG:2249 (assumed to be the SRID in which the source data is supplied, but this is not checked by OGR2OGR).

ogr2ogr
  -f "MSSQLSpatial" 
  "MSSQL:server=localhost\denali;database=tempdb;trusted_connection=yes" 
  "precincts.shp" 
  -a_srs "EPSG:2249"

Transform data from ESRI Shapefile to a Different SRID and Load to SQL Server

• The following code listing takes data from the precincts.shp ESRI shapefile and loads it into the sql2012 SQL Server instance running on localhost.
• Data is inserted into a geography column, geog4326, in a table called precincts_reprojected.
• Any existing table is overwritten.
• The source shapefile is defined using EPSG:2249 (Massachussetts Mainland – a projected coordinate system), but the data is transformed during load into EPSG:4326 (WGS84 – a geographic coordinate system)

ogr2ogr
-f "MSSQLSpatial" 
"MSSQL:server=localhost\sql2012;database=tempdb;trusted_connection=yes"
"precincts.shp"
-s_srs "EPSG:2249"
-t_srs "EPSG:4326"
-overwrite
-lco "GEOM_TYPE=geography"
-lco "GEOM_NAME=geog4326" 
-nln "precincts_reprojected"

Exporting from SQL Server

Exporting from SQL Server to KML

• The following code listing creates a KML file suitable for viewing in Google Earth, containing the geometries in the geog4326 column of the precincts_reprojected table.
• The file is saved as C:\temp\precincts.kml. Any existing file by that name is overwritten.

ogr2ogr
-f "KML"
"C:\temp\precincts.kml"
"MSSQL:server=localhost\denali;database=tempdb;trusted_connection=yes;"
-sql "SELECT prcnts_id, geog4326.STAsBinary() FROM precincts_reprojected"
-overwrite

Exporting from SQL Server to ESRI Shapefile

• The following code exports data from the OGRExportTable in the localhost\sqlexpress SQL Server instance to an ESRI Shapefile called c:\temp\sqlexport_linestring.shp.
• ESRI Shapefiles can only contain features of a single geometry type. In this example, only LineString features are selected:
  • The SQL Statement has the condition WHERE shapegeom.STGeometryType() = 'LINESTRING'
  • The layer creation option –lco "SHPT=ARC" tells the Shapefile driver to create a destination shapefile that contains arc (i.e. one-dimensional, Curve, LineString geometries).

ogr2ogr
-f "ESRI Shapefile"
"C:\temp\sqlexport_linestring.shp" 
"MSSQL:server=localhost\sqlexpress;database=tempdb;trusted_connection=yes;" 
-sql "SELECT shapeid, shapename, shapegeom.STAsBinary(), bufferedshape.ToString(), bufferedshapearea AS area  FROM OGRExportTestTable WHERE shapegeom.STGeometryType() = 'LINESTRING'" 
-overwrite 
-lco "SHPT=ARC" 
-a_srs "EPSG:2199"