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

Post a Comment

Popular posts from this blog

Trimming strings in action parameters in ASP.Net Web API

In standard MVC it's very easy to create a model binder to trim all of the strings passed into action methods. The approach I used for that was from here:  http://blogs.taiga.nl/martijn/2011/09/29/custom-model-binders-and-request-validation/ . It works well and it trims the properties of models too. The problem came when I tried to do the same thing for Web API. I added a similar StringTrimmingBinder and registered it as a service using the very non-discoverable API: var provider = new SimpleModelBinderProvider( typeof ( string ),  new StringTrimmingModelBinder()); GlobalConfiguration.Configuration.Services.Insert( typeof (ModelBinderProvider),  0,  provider); As a side note, I have no idea how you're supposed to find out how to register a model binder without opening up Bing and Googling it. Anyway, this worked great when the parameters to the action were actually strings but doesn't work with complex types as parameters. Now, I'v...
Read more

Full text search in Entity Framework 6 using command interception

Image
Background One of the main problems we encountered using Entity Framework was its lack of support for full text search. Our search function is based around a series of processors that attempt to match the incoming phrase against a set of patterns that we're expecting (10+). For example, there's one for dates and times, recent financial quarters as well as more domain specific ones. Everything is cached and so is really fast, except when it isn't: Here's the monitoring chart for our SQL DB in Azure That's a spike to 100% resource utilisation because we fall through to a phrase search as a last resort (which cannot be cached) - it's exceedingly rare but it still hurts. Before EF, the app used full text search and it was one of the casualties when the app was migrated to EF4 several years ago. The lack of full text search has been getting more and more painful as the amount of data has grown. We really needed full text search back... Command intercept...
Read more

Composing Expressions in C#

This post follows on from  Reusing predicates in Entity Framework . In my previous post I introduced the "and" extension method which allowed me to create a new entity framework safe expression from two existing expressions. Here's how we make that happen: The code is from a blogpost from Colin Meek from 2008:  InvocationExpression and LINQ to Entities . I'll just dump my version of the code here. The idea is exactly the same (even using the visitor as an immutable stack): public class InvocationExpander : ExpressionVisitor { private readonly ParameterExpression _parameter; private readonly Expression _expansion; private readonly InvocationExpander _previous; public static T Expand<T>(T expr) where T : LambdaExpression { return (T) new InvocationExpander().Visit(expr); } public InvocationExpander() { } private InvocationExpander(ParameterExpression parameter, Expression expansion, Invocati...
Read more