Importing Spatial Data to SQL Server with OGR2OGR–now even easier!

A few months back, I posted an article explaining how to import spatial data into SQL Server 2008 from any format supported by the OGR library (including ESRI shapefiles, GML, and TIGER data), using OGR2OGR. That article was written using OGR2OGR from v1.7 of the GDAL 1.7 library, which doesn’t support SQL Server 2008 directly, so I instead used OGR2OGR to create a CSV file containing spatial data in Well-Known Text format and then parsed that data in SQL Server using the STGeomFromText() method.

The good news is that things have become a bit easier since then, and version 1.8 of the GDAL library now has a MSSQLSpatial driver that can interface directly with geometry and geography data in SQL Server 2008.

The bad news is that most of the places that offer pre-compiled GDAL binaries for Windows have yet to update to the new version. FWTools, for example, still comes packaged only with v1.7. Likewise, the osgeo download site at http://download.osgeo.org/gdal/win32/ also lists GDAL versions up to v1.7.

So, if you want to get hold of the latest GDAL to import directly into SQL Server you’ll have to build it yourself from source, which can be downloaded from http://download.osgeo.org/gdal/gdal180.zip

Fortunately, the source has been very considerately packaged, and includes solution files that will build GDAL out-of-the-box in VS2005, 2008, and 2010. Simply load the .sln file, click build, and wait a few minutes:

Building GDAL 1.8 in VS2010

Then, if you look in the output directory (\warmerda\bld\bin, by default) you should see a lovely collection of utilities for working with spatial data – GDAL (for working with raster data), and OGR (for its vector sibling).

Here’s the output of calling ogr2ogr –formats, which retrieves the list of supported vector spatial formats – note the MSSQLSpatial format supported for both read/write:

image 

Example usage to load a shapefile to SQL Server as follows:

ogr2ogr -overwrite -f MSSQLSpatial "MSSQL:server=.\MSSQLSERVER2008;database=spatial;trusted_connection=yes" "TG20.shp"

And here’s some lovely OS vector shape data in SQL Server:

image

About these ads
This entry was posted in Spatial, SQL Server and tagged , , . Bookmark the permalink.

12 Responses to Importing Spatial Data to SQL Server with OGR2OGR–now even easier!

  1. Dustin says:

    Thanks for the blog post Alastair. I have gotten ogr2ogr working and I have successfully imported some data using the geometry datatype, but when I change to geography as in the below command, I am unable to get much data to load.

    C:\Program Files\GDAL>ogr2ogr -lco GEOM_TYPE=geography -lco SRID=4326 -overwrite
    -f MSSQLSpatial “MSSQL:Server=SERVERNAME;Database=DBNAME;Tr
    usted_Connection=true;” “C:\Documents and Settings\username\Desktop\GeoData\gadm
    _v1_lev0_shp\gadm1_lev0.shp”

    I am trying a number of free data sources (gadm.org and naturalearthdata.com) and nothing much is working out. Most of the errors are “System.ArgumentException: 24200: The specified input does not represent a valid geography instance.”

    I believe my SRID is correct (but not sure). Can anyone recommend sources of global data that will actually import as a geography as opposed to a geometry or a way to clean up this data so it imports at a geometry?

    • alastaira says:

      Several suggestions for you:
      - I normally assign the SRID using -a_srs “EPSG:4326″ rather than -lco SRID=4326, which seems a bit flaky sometimes.
      - SQL Server Denali is much more tolerant of “invalid” geography instances than SQL Server 2008/R2, so might be worth giving that a go
      - Take out the -lco GEOM_TYPE=geography for now and try loading into the geometry type to start with. If that succeeds, clean up the geometry by UPDATE gadm1_lev0 SET ogr_geometry = ogr_geometry.MakeValid(); Then, try to populate a geography column from the WKB of the validated column: ALTER TABLE gadm1_lev0 ADD ogr_geography geography; SET ogr_geography = geography::STGeomFromWKB(ogr_geometry.STAsBinary(), 4326)

      • Dustin says:

        Thanks for the help!
        Using a combination of the above methods I have all but norway imported. The problem there is the data set has an outlying island the exceeds the 1 hemisphere rule. I am currently trying to do subset of the norway collection with a bounding box which will remove the island via STIntersection. If there is a better to do this, please let me know.

        Thanks again for the help, this site and your book have been very helpful.

  2. papa.stiff says:

    Can I force ogr2ogr to create a sql spatial table with a name not necessarily the same as the input file (say a mapinfo table)? Because it seems it creates a spatial table of same name by default and I don’t know the syntax / keyword combi to override that.

  3. Sam Loud says:

    Alistair, as you suggesting that you load the data into a geometry datatype and then convert it to geography with SQL Spatial functionality. This implies that OGR2OGR can’t load directly into a geograophy datatype. Is that correct?
    Super articles, BTW; very helpful indeed.

  4. Omar says:

    Looks good, need to do the same thing myself, bring OSGB data into a SQL server, would anyone have a latest version compiled for windows?

  5. darkniobe says:

    Also worth noting, or at least as a trouble I stumbled upon, if you’re using -lco “GEOM_TYPE=geography” you may also need to specify your spatial reference system. You can ask ogr2ogr to perform translation for you by using something like -a_srs EPSG:4326

    The driver documentation recommends using -lco “SRID=4326″ (or some other spatial reference system), but I couldn’t get this to work in my attempts.

  6. Alisson says:

    Hi friend,

    I would like to transform a shape with multilinestring in a shape with polygons using ogr2ogr.

    Polygon to multilinestring, I use:
    ogr2ogr -f “ESRI Shapefile” -overwrite “C:/Users/Alisson/Basins3.shp” “C:/Users/Alisson/Basins2.shp” -sql “SELECT * FROM Basins2″ -skipfailures -nlt MULTILINESTRING

    This works.

    Multilinestring to multipolygon, I use:
    ogr2ogr -f “ESRI Shapefile” -overwrite “C:/Users/Alisson/polygon.shp” “C:/Users/AlissonContorno30.shp” -sql “SELECT * FROM Contorno30″ -skipfailures -nlt MULTIPOLYGON

    But, this error appears:
    ERROR 1: Attempt to write non-polygon (LINESTRING) geometry to POLYGON type shapefile.

    Can you help me?

    Best regards,

  7. Pingback: Importera postnummerpolygoner i SQL Server 2012, del 1. « Kentor BI

  8. Thomas says:

    Worked like a charm. Many thanks for posting!

    Interestingly, it has been a couple of years since your post, but I still had to compile the code to get the MSSQLSpatial format (i.e., still not in the FWTools distribution).

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