5

I am trying to import some shapefiles into SQL Server 2008 R2. I found this useful tool called Shape2SQL however I am not sure how I can import shapefile directly using only SQL scripts as this is important for build automation and deployment. So far I haven't found anything useful just yet.

Would anyone be able to point some directions?

Improve this question
2
  • I found shp2sqlserver which I am giving it a try. code.google.com/p/cumberland/wiki/shp2sqlserver
    – Jeff
    Commented Jul 19, 2012 at 1:24
  • shp2sqlserver is only in alpha and has great limitations so far which isn't suitable for me. From the website quote: This is alpha level software. I have loaded some complex shapefiles up to 20MB with it, but it could bomb on yours. The Shapefile loader only supports Point, Polyline, Polygon, and there associated *M types (measurement value is ignored)
    – Jeff
    Commented Jul 19, 2012 at 1:29

1 Answer 1

Reset to default
16

I would equate this to asking for a SQL script that imported an excel file to SQL Server. Even if it were possible, you would be better off if you did not use only SQL scripts to automate the deployment of information.

Its like asking for a screwdriver to hammer in a nail. SQL Server doesn't understand things like shapefile format and excel spreadsheets because that's not what a DBMS is for: it's what DTS and/or SSIS is for.

To answer your question, however, GDAL has been the DTS of spatial information for quite some time now. Have a look at the following command to import spatial information to SQL Server using ogr2ogr:

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

GDAL Binaries (Assuming Windows) are available from this list (via). You can install according to this process, but you should ignore the steps about python.

After installing GDAL, just modify the connection string and data source in the above command to suit your needs. Note that ogr2ogr also supports multiple other formats.

Improve this answer
6
  • By GDAL you mean this link? trac.osgeo.org/gdal/wiki/DownloadingGdalBinaries
    – Jeff
    Commented Jul 19, 2012 at 3:56
  • I found this blog post useful after learning the keyword GDAL from @Geoist's answer. woostuff.wordpress.com/2011/06/07/…
    – Jeff
    Commented Jul 19, 2012 at 4:20
  • And I am using ogr2org from OSGeo4W Windows installer. trac.osgeo.org/osgeo4w
    – Jeff
    Commented Jul 19, 2012 at 4:21
  • OSGeo4W is highly recommended, although a bit more complicated to manage IMO.
    – nagytech
    Commented Jul 19, 2012 at 4:22
  • Tried a few standlone gdal windows binaries and found OSGeo4W Windows installer works the best (for my situation now at least). Thanks.
    – Jeff
    Commented Jul 19, 2012 at 4:35

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.