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:
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:
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:
Alastair Aitchison 
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?
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)
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.
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.
sorry for the bother, i found the solution to my problem. it’s the -nln parameter. cheers
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.
Hi Sam, Thanks! To load direct into the geography datatype you need to add -lco “GEOM_TYPE=geography”
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?
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.
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,
Pingback: Importera postnummerpolygoner i SQL Server 2012, del 1. « Kentor BI
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).
Great post. It’s very slow to upload large datasets to SQL server though especially since downloading from SQL->SHP file is extremely fast. Is there a way to bulk import?
It works great but it’s painfully slow. I wonder if OGR2OGR will ever support bulk importing to MSSQLSpatial…