Notes on importing spatial data into SQL Server 2008

There are a variety of ways to do this, but often you will hit issues with data validity.

  • If you have our ArcGIS Maplink product (or ArcSquirrel) then loading data in using this into a temporary SQL table is probably best as it will do a reasonable amount of checking.
  • MapInfo has its own SQL loader tool ("Easy Loader" - find it in the Tool Manager)
  • If you are just using open source you can use OGR2OGR

ogr2ogr -overwrite -f "MSSQLSpatial" "MSSQL:server=MyServer;database=MyDatabase;Trusted_Connection=yes;" "C:\Temp\MyShapeFile.shp" -a_srs "EPSG:27700"

Once the data is in SQL there can still be issues:

Incorrect SRID

  • Make sure you specified the correct SRID when the data was loaded (if you didn’t specify it – it may well be 0). You can check this with the SQL: SELECT geom.STSrid from SitesGeom Where geom.STSrid <> 27700

Invalid ring

UPDATE foo SET geom = geom.STUnion(geom.STStartPoint());

Invalid geometries

UPDATE foo SET geom = geom.MakeValid()

SRID not imported

Import geometry data into temp SQL table (e.g. foo) – with tempGeom column

Then try to populate a geometry column from the WKB of the validated column:

ALTER TABLE foo ADD geomFinal geometry;

SET geomFinal = geometry::STGeomFromWKB(geomTemp.STAsBinary(), 27700)

Comments

James

re: Notes on importing spatial data into SQL Server 2008

25 September 2014

You can get some very odd failures if you have renamed a table in SQL server and then try and import a table to the original (now non existent) name.

 

e.g. If You uploaded a shape file called Towns and so had a SQL table called Towns which you the renamed to BigTowns in SQl server you may find that you can't import another shape file called Towns.

 

I can only assume that its checking some metadata somewhere.  The workaround is simply to rename your shape file before importing.

Find out more