Two Free Ways to Push Geographic Data in SQL Server

If you own SQL Server 2008 or higher (even the free Express version) you have the essentials of a GIS. 

But how to put GIS data into SQL Server?  I cover 2 simple ways to get data into SQL Server:

1. SharpGIS’s SQL Server Spatial Tools http://www.sharpgis.net/page/sql-server-2008-spatial-tools.aspx
2. OGR2OGR as part of the GDAL Suite at http://trac.osgeo.org/gdal/wiki/DownloadingGdalBinaries

If you downloads SharpGIS’s tool, you can load a ShapeFile (actually it is series of files such as Road.shp, Road.dbf, Road.idx..) into SQL Server.

 

In the Configure Dialog dialog box, you need to log into SQL Server, enter the SQL Server name and choose the database.

If you shapefile has a coordinate system that is projected, such as State Plane or UTM, you should use the Geometry, otherwise choose Geography (Latitude and Longitude).

Also, I recommend not to create the Spatial Index right away as I find this often causes errors during load with this tool.

Leave the default Table Name, Geometry Name and Key Name and click Upload to Database.

To view the data, open the Microsoft SQL Server Management Studio and expand the Databases to your current database.

Right-click On your Table and choose Select Top 1000 Rows to see the SHP you just loaded.

Now, to load the same SHP File, you will have to use the Command Prompt with the OGR2OGR Tool.

To create a BAT or batch file, simply create a text file and save it with a .bat extension.

In the *.bat file, first add the path of the OGR2OGR.exe:

“C:\Program Files\GDAL\ogr2ogr.exe”

Then add the -f MSSQLSpatial switch to import into SQL Server.

Next, add the connection to SQL Server:
“MSSQL:server=HOGWARTS\SQLEXPRESS;database=WORLD;trusted_connection=yes”

Finally, add the path of where the Shapefile is:
 D:\Projects\DMTI\SHP\RAIL75.shp

Full entry:
“C:\Program Files\GDAL\ogr2ogr.exe” -f MSSQLSpatial  “MSSQL:server=HOGWARTS\SQLEXPRESS;database=WORLD;trusted_connection=yes”  D:\Projects\DMTI\SHP\RAIL75.shp

Finally, double-click on the ogr.bat file or whatever you named it, and the SHP file will be loaded into SQL Server.

To view the data, open the Microsoft SQL Server Management Studio and expand the Databases to your current database.

The additional dbo.geometry_columns and dbo.spatial_ref_sys are used by Open Source GISs such as QGIS to determine the spatial data columns and their coordinate system.  QGIS can render the data without these support tables however, so they are not required.

Advertisement