Importing shape files into SQL Server 2012

In a project we needed a set of boundaries in order to answer domain questions such as:
  Which services that we know about can provide activity a at point p?

The services we were interested in happened to coincide with the administrative boundaries of a country. For example, states and towns. I might blog about how we linked the services in our database to their boundaries later but the first challenge was getting some spatial data into a database to start messing around with.

The first tool I discovered was http://www.sharpgis.net/page/SQL-Server-2008-Spatial-Tools.aspx. It looked like it would do everything I needed... However Australia and the UK provide their data in different coordinate systems. Data for the UK is available through the Ordinance Survey and uses the British Grid System funnily enough, Australia doesn't use the British grid! I decided to use the excellent dotspatial to spin up my own quick and dirty importer that would also project the data into the WGS84 system which, from my reading, seems to pretty standard. It's SRID is 4326 - you'll need that to query in SQL. It's also the default coordinate system of EF's DbGeography type.

In order to open the shape files and project them to WGS84 we need some NuGet packages:
    DotSpatial.Data (rest are dependencies)
      DotSpatial.Mono
      DotSpatial.Projections
      DotSpatial.Serialization
      DotSpatial.Topology

As previously mentioned, I used entity framework for my data access but that's not really important.

Example code of using dot spatial:
var filePath = "some path to a shape file.shp"
            
// Load the shape file (need to have .dbf, .prj, .qpj, .shx files as well as .shp)
var featureSet = FeatureSet.OpenFile(filePath);
 
// This happens in memory, you may get an OOM exception :(
featureSet.Reproject(KnownCoordinateSystems.Geographic.World.WGS1984)
 
// Have a peek at the kinds of delicious metadata we have
featureSet.DataTable.Columns;
 
// Access individual features
foreach (var feature in featureSet.Features)
{
    // inspect meta data for the feature
    feature.DataRow.ItemArray["some column name"];
    // Populate a domain object and save to the database
}

It's all very simple and credit has to go to dotspatial for a great API.

The only thing you may find, and why I specifically said SQL 2012 is:
  SQL is sensitive to ring order and shape file authors aren't!

What that means is that SQL may think some of your boundaries are over half a hemisphere. On SQL Server 2008/r2 that results in an error when you try to insert it, SQL 2012 allows it. If you're sure there aren't supposed to be any this large you can run some SQL to reorient them. Another problem you may have, is that the data isn't valid as far as SQL is concerned. I wrote a simple class that executes some SQL to fix up the data available which is available at https://gist.github.com/Rob89/2215acdafd742e4144bb. Obviously, the code uses EF but you can modify it.

You'll notice that my table is called boundaies and has an Id column, a BoundaryName column and a Data column.

Final tip: QGIS is an invaluable tool when working with shape files, I had to use it to open all the data I downloaded from the Australian Bureau of Statistics and re-save it before dotspatial could open it.

Comments

Popular posts from this blog

Trimming strings in action parameters in ASP.Net Web API

Full text search in Entity Framework 6 using command interception

Composing Expressions in C#