OGR2OGR Patterns for SQL Server

gdalThe 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:

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"

34 Responses to OGR2OGR Patterns for SQL Server

  1. Hi Alastair,
    Thanks for the great tutorial!

    We’re trying to import some 3rd party shapefile data. The data is geometry data set to EPSG:3005. We have successfully imported the data using your tutorial for geometry. However, the data being stored our SQL Server database is being stored with a elevation field set to ‘0’

    POLYGON ((1366898.121 1248253.487 0, 1367184.317 1244815.378 0, 1363746.208 1244529.182 0, 1363460.012 1247967.291 0, 1366898.121 1248253.487 0))

    Obviously this is causing the polygon to draw incorrectly. Any ideas how the elevation field would come to end up in a geometry table? Any ideas how to have OGR2OGR ignore the elevation field on import?

    Any help would be greatly appreciated!
    Thanks in advance,
    Carlos

    • alastaira says:

      Hi Carlos,

      I’m a bit unclear whether your source shapefile has Z values that you’re trying to ignore, it has Z values that you’re trying to import, or you don’t think it should have Z values at all but SQL Server is inserting 0’s. Is your shapefile publicly available?

  2. Hi Alastair,
    Sorry I see where I wasn’t clear…multitasking is not always good :).
    Our system stores geometry data in the common format Polygon((0 0, 0 0)). When we imported the data set (see below) we found what was being stored was Polygon((0 0 0, 0 0 0))…XY and Z. When we try to draw the polygon it obviously didn’t work. So we’ve run a basic cleanup script to strip the elevation value once the data is in SQL Server and the data now displays correctly.

    We don’t have a way of seeing details of the .shp file so I can only assume that the data in the shp file does not have elevations given that when I ordered it I requested NAD 83/BC Albers which, in my understanding, would correspond to geometry polygons without an elevation as geometry polygon definition does not support an elevation value. Therefore I can only assume that ogr2ogr is inserting the elevation value during import.

    The data is available for free download but I have it on FTP which will be much simpler and quicker for you to obtain. Please email me at c_daponte@yahoo.ca and I will send you the login info.

    Thanks for ur help!
    C

  3. Shaun says:

    Thank you for the ogr2ogr ‘patterns’! I had polygon geometry stored in SQL Server, but when I tried to convert back to Shapefiles (with my sql being “Select * from Table”) I was getting back points–but the points were all in a vertical line with and way off in some odd coordinates. After changing the Sql to ogr_geometry.STAsBinary()–I got the polygons. Thanks

  4. Chris says:

    It looks like you have a problem in your tigerline import script; instead of “EPSG:4269″ you have “ESPG:4269″ where the ‘P’ and ‘S’ have been transposed. In any event, even after correcting this, although the mechanics of the import succeeds, the spatial data is somehow not translated correctly. When the spatial data are queried from SQL Server, say, for the top 100 records, I get only a blank green grid with no shape outlines displayed. I’m not sure what the problem is; I’m using the latest oversion of the OGR2OGR tool.

  5. Pingback: ESRI Shapefiles and Bing Maps « Ricky's Bing Maps Blog

  6. Jon Smith says:

    Hi Alastaira,

    I have just swapped over to VS2012 and I am using localdb only, i.e. no installed SQL Server. I wondered if you could help with how I might access SQL from OGR2OGR. I tried “MSSQL:(localdb)\v11.0;database=MyDatabaseName;trusted_connection=yes”, but that didn’t work. I did try using (localdb)\v11.0 in the Shapefile uploaded utility Shape2Sql which accessed the database (but of course failed because the data was beyond the valid bounds) so this proves that the database is accessable (I do have SSMS installed and that could see the database). However Shape2Sql does use a standard database configuration form to set things up so you would expect that to work.

    Just to make it clear what I am trying to do is upload some OS shapefiles into SQL server and convert from Easting/Northings to SRID:4326 in the process. The actual batch file is:

    ogr2ogr -f “MSSQLSpatial” “MSSQL:(localdb)\v11.0;database=MyDatabaseName;trusted_connection=yes” “an os shape file.shp” -s_srs “EPSG:27700″ -t_srs “EPSG:4326″ -overwrite -lco “GEOM_TYPE=geography” -lco “GEOM_NAME=geog4326″

    Clearly at the moment I can simply convert the files and then upload via Shape2Sql, but it would be useful to me, and maybe others, to know how to access localdb within OGR2OGR.

    Thank you for your fine blog. I have found it invaluable. I also bought your book some years ago and it to had helped and is well used.

    • Update to my last post. I got the command line wrong (I left out the word server and MSSQLSpatial should not be in quotes), although correcting it still doesn’t get it to work. The corrected command line is:

      ogr2ogr -f MSSQLSpatial “MSSQL:server=(localdb)\v11.0;database=MyDataBaseName;trusted_connection=yes” “an os shape file.shp” -s_srs “EPSG:27700″ -t_srs “EPSG:4326″ -overwrite -lco “GEOM_TYPE=geography” -lco “GEOM_NAME=geog4326″

      The OGR2ORG error I get is:

      MSSQLSpatial driver failed to create MSSQL:server=(localdb)\v11.0;database=MyDataBaseName;trusted_connection=yes

      Be helpful to get your advice on this.

      • Alex Mumme says:

        Jon,
        What you might try is changing (localdb) to localhost (without the parenthesis). (localdb) is a driver level alias for localhost 127.0.0.1. If Ogr2ogr isn’t using the most recent version of SQL Server Native Client then a lookup for (localdb) will come back as nonexistent. If localhost still doesn’t work, try 127.0.0.1.

  7. John Sanborn says:

    I downloaded the OSGeo4W within the last week, so I assume I have the latest version of ogr2ogr, version 1.8 that includes the MSSQL driver. However, when I attempt to download a simple polygon from SQL Server (I’ve tried both 2008 R2 Express and 2012 Standard) I get this failure:

    H:\>ogr2ogr -f “ESRI Shapefile” “C:\TestShapeExport\Brad.shp” “MSSQL:server=\\SDNHMWEB\SQL_2008EX;database=Botany_Test2008;trusted_connection=yes;
    Uid=web_herbuser;Pwd=xxxxx” -sql “SELECT * FROM GeoShapesPublic WHERE
    ShapeName = ‘BradShape'” -overwrite

    ERROR 1: Unable to initialize connection to the server for MSSQL:server=\\SDNHMW
    EB\SQL_2008EX;database=Botany_Test2008;trusted_connection=yes;Uid=web_herbuser;
    Pwd=xxxxx,
    [Microsoft][ODBC SQL Server Driver][DBNETLIB]SQL Server does not exist or access
    denied.

    There is then presented a long list of drivers in which MSSQLSpatial is there, but MSSQL is not.
    I can’t seem to find any thing that indicates what version of ogr2ogr I have installed. Does that error message indicate that I do not have the MSSQL driver installed. If so, how would I get it?
    Thanks for any help

    • alastaira says:

      Hi John – no, that error indicates that you’ve provided the wrong connection information for your SQL Server. The likely cause is that you’re specifying a uid and password, but you’re also setting trusted_connection=yes. Trusted connection basically means “Use the credentials for the *currently logged in user*”. If you want OGR2OGR to use the application user credentials for web_herbuser then you should remove the “trusted_connection=yes” (or set it to false). The next thing is to obviously check that the server, database, username, and password supplied are all correct.

      • John Sanborn says:

        Thanks, but I’ve still got the problem. I know the credentials are good, they work fine on my web pages. After running it again with the changes you suggested, I did note that the on the opening of the ogr2ogr I got a line:
        GDAL 1.7.3 released 2010/11/10
        In your article, Exporting spatial Data From SQL to ESRI Shapefile, you noted that users should make sure they have version 1.8 which has the required MSSQL driver. Does that line mean I have an older version? As I mentioned, I just downloaded this a few days ago.

        Just to verify, here’s the latest attempt:

        H:\>ogr2ogr -f “ESRI Shapefile” “C:\TempShapes\BradShape.shp” “MSSQL:server=\\SDNHMWEB\SQL_2008EX;database=Botany_Test2008;uid=web_herbuser;pwd=xxxx” -sql “SELECT * FROM GeoShapesPublic WHERE ShapeName=’BradShape'” -overwrite
        ERROR 1: Unable to initialize connection to the server for MSSQL:server=\\SDNHMWEB\SQL_2008EX;database=Botany_Test2008;uid=web_herbuser;pwd=xxxx,
        [Microsoft][ODBC SQL Server Driver][DBNETLIB]SQL Server does not exist or access denied.
        FAILURE:
        Unable to open datasource `MSSQL:server=\\SDNHMWEB\SQL_2008EX;database=Botany_Test2008;uid=web_herbuser;pwd=ezra#43′ with the following drivers.

  8. Alastair,

    Maybe you can help me figure out what is going wrong in my attempt to export data from SQL Server 2008 R2 to an ESRI Shapefile using OGR2OGR. I have numerous database tables in SQL Server that contain a single GEOGRAPHY data column and I can query those tables without any problem including calling geom.STAsBinary() without error. However when I run the exact command you set forth, which makes all the sense in the world, I get one error per database row returned by OGR2OGR that reads ERROR 1: GetNextRawFeature(): Corrupt Data.

    The original data was imported from ESRI Shapefiles that has varying SRID’s so I transformed them all to 4326 using OGR2OGR and the import worked fine, and the data within SQL Server is equally as solid as I can use any MSSQL method against the data without problem. Thus my frustration and confusion since it would seem logical that this should be an easy process without issue but alas it’s not so any ideas would be greatly appreciated.

    Thanks!

    • alastaira says:

      I have seen that error before, although I had thought that it was when I was trying to do connections to SQL Server 2012 and use the native binary format (which changed relative to SQL Server 2008/R2) rather than using STAsBinary(), which should be unchanged. Have you tried using STAsText() and tell OGR2OGR to expect WKT instead?

      • Tom Dougherty says:

        No I haven’t tried that but I have tried using the native binary and EPSG 4326 and will create the Shapefile but it’s inaccurate since it plots boundaries in Virginia at Antarctica and it rotates the coords 90 degrees so the state appears to be sideways. I can’t locate the syntax for the a_srs spec if I want to specify WKT as the output. Do you have an example? Thanks!

      • Tom Dougherty says:

        I found that syntax but exporting to a CSV will not solve either of my issues. First I need ESRI Shapefiles I can distribute to clients and partners, and second I need to upload them to ArcGIS Online. In both cases CSV files don’t accomplish the goal. So I’m back to contemplating what could be causing the error I mentioned.

      • alastaira says:

        I’m not suggesting you export to CSV – still export to Shapefile, but change the STAsBinary() in the SELECT statement to STAsText() so that OGR2OGR sees WKT rather than WKB.

  9. Christopher Daniel says:

    Hi Alistair,

    I got the ogr2ogr commands from your ProSpatial book to successfully execute, but for whatever reason many of the files I try to import appear in SQL Server Spatial Viewer as whole earth polygons. Any idea what I might be doing wrong?

    • Christopher Daniel says:

      P.S. this only happens when I use the -lco options to specify the geography datatype. If I import the files with the default geometry datatype, then the polygons appear correctly but are likely incorrect because the data is in SRID 4269

    • alastaira says:

      Hi Christopher. Yes, I have a good idea – it’s to do with ring orientation. Try reorienting the data to correct those polygons that are “inside out” as follows:
      UPDDATE YourTable
      SET YourWonkyGeographyColumn = YourWonkyGeographyColumn.ReorientObject()
      WHERE YourWonkyGeographyColumn.EnvelopeAngle() > 90;

      • Christopher Daniel says:

        I thought that might be the problem, but since I’m working with Statistics Canada data I thought they would have properly oriented polygons. I also didn’t know there was a way to fix the polygons as you’ve suggested above. Thanks for the super fast response :)

        Chris

  10. Christopher Daniel says:

    Just in case others come to this page with the same problem, I ended up needing to run a slightly modified version of the update query suggested by Alistair:

    UPDATE yourTable SET = YourWonkyGeographyColumn.MakeValid().ReorientObject() WHERE YourWonkyGeographyColumn.MakeValid().EnvelopeAngle() > 90;

    Thanks again to Alistair for all the great SQL Server Spatial tips and advice.

    Chris

  11. Ryan W. says:

    Hello

    When I run the following code:

    ogr2ogr -f “MSQLSpatial” “MSSLQ:server=SQL\SENEXSQL1;database=ESRIShapefilesDB;trusted_connection=yes;” “tl_2010_us_zcta510.shp” a_srs “ESPG:4269″ -lco “GEOM_TYPE=geography” -lco “GEOM_NAME=geog4269″ -nln “tblUSAZCTA” -progress

    I get an error saying ‘ogr2ogr’ is not recognized as an internal or external command, operable program or batch file. I know this is a simple problem but I have little experience with command line prompts. IT installed the software on our server a couple of days ago so I don’t understand the error. Any ideas of what is causing this problem? Also currently the “tblUSAZCTA” table does not exists in the database. Will this script automatically create the table?

    I recently brought your book. Thanks in advance!

    Regards,

    Ryan

    • alastaira says:

      Hi, that error simply means that the command prompt can’t find the OGR2OGR program. You either need to get your I.T. department to add the location of the ogr2ogr executable to the command path, or you need to use the “cd” command to change into whatever directory OGR2OGR was installed into (e.g. cd c:\Program Files\OGR2OGR) but if it was installed on a server for you, you’ll likely not know where that is.
      Yes, OGR2OGR will create tables for you.

  12. Teri M. says:

    Hello,
    Is there a way to use ogr2ogr to load geographic shapefile data to the geography data type in SQL 2008r2? Polygons in the shapefile use right-hand rule for vertex order and SQL geography requires left-hand rule and we don’t have access to the ReorientObject method since we’re running 2008r2 and not SQL 2012. Thanks.

  13. Martin Newman says:

    If I use a command based on one of yoru patterns like this to convert OS opendata shape files into SRID 4326 I appear to get “inside out” polygons. What have I done wrong? ANd thaks for your help in advance

    ogr2ogr -f MSSQLSpatial “MSSQL:Server=MAINW7\MARTIN_SQL2012;Database=SpatialTest;Trusted_Connection=true;” “C:\somefolder\district_borough_unitary_region.shp” -s_srs “EPSG:27700″ -t_srs “EPSG:4326″ -overwrite -lco “GEOM_TYPE=geography” –
    lco “GEOM_NAME=ogr_geog” -nln “district_borough_unitary_region”

    • Zberteoc says:

      Martin, it happened to me too. The problem is that some polygons are not ordered properly to be accepted by the SQL server. You can try 2 things:

      1. Use the -skipfailure parameter see if that works. It will probably skip the polygons that cause errors. If it doesn’t the only way is to import data to SQL as geometry, in which case the polygon order doesn’t matter, and after that you will try to convert the polygons to geography inside SQL like this:

      GEOGRAPHY::STGeomFromText([ogr_geometry].MakeValid().STUnion([ogr_geometry].STStartPoint()).ToString(),4326) as ogr_geography

      where ogr_geometry is the default name of the column if you don’t change the type and name.

  14. Zberteoc says:

    I am trying to import a shp file to SQL and it fails wit:

    ERROR 1: INSERT command for new feature failed. [Microsoft][ODBC SQL Server Driver][SQL Server]Arithmetic overflow error converting numeric to data type numeric.

    I used to import bigger files but this one is a pain.

  15. Ingo says:

    Hi alastaira, thanks for your article! Do you know how to import not only the spacial data but also the attributes from a shape file to SQL Server?

    Many thanks!

  16. Alberto says:

    Hi,
    I have to append a shape file to a geometry column in SQL Server 2008. With ogr2ogr -f “MSSQLSpatial” “WorkingMSSQLConnectioString” “pathtoShapefile” -append -a_srs “EPSG:4326″ it creates tree new tables in the database. Is there a way to add the record to my existing table based on the same shapefile pattern?

  17. Pingback: ESRI Shapefiles and Bing Maps - Ricky's Bing Maps Blog - Site Home - MSDN Blogs

  18. tstorli says:

    I recently faced the same issue that Tom set forth on February 7, 2013.
    When I tried to to use the STAsText() method, ogr2ogr complained and truncated the Polygon text strings.

    I also had the Polygons, that was based on US Geography, end up in a vertical position in Antarctica.

    However, I was able to resolve this as follow:

    ogr2ogr -f “ESRI Shapefile” “C:\temp\YOURSHPFILE.shp” “MSSQL:server=YOURSQLSERVER;database=YOURDB;trusted_connection=yes;” -sql “SELECT [ID],[GeoType],[GeoName],GEOMETRY::STGeomFromWKB([GeoShapes].STAsBinary(),4326) As [GeoShape] FROM [dbo].[GEO_TABLE] WHERE [GeoShapes].STGeometryType() = ‘POLYGON'” -overwrite -a_srs “EPSG:4326″

    Hope this helps anyone.

  19. William R Blair says:

    Alastair,
    With support from your excellent OGR2OGR articles we have been successful outputting Shape files from SQL Server 2012 geometry files. We recently upgraded to SQL 2014 and now can no longer connect to the database. Do you know of any work a rounds, or when and if OGR2OGR may have an updated driver that can connect to SQL Server 2014.

    Best Regards
    William Blair

  20. Pingback: ESRI Shapefiles and Bing Maps | Maps Search

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s