Spatial Entities

Recent work has given me my first real exposure to the spatial data types of SQL Server. Even though I’m not using these directly, as they are nicely abstracted behind already existing stored procedures and functions, I do find getting to know them very interesting. One problem with the reporting project I’m busy with though, is that the current Entity Framework (EF) RTM does not support the spatial data types, and I’ve had to use good ol’ ADO.NET and typed data sets instead.
I thought I’d do a little research into spatial data in EF over the weekend, as was delighted to find that the Entity Framework June 2011 CTP caters for spatial data. I have Jason Follas to thank for this, for his Entity Framework Spatial: First Look blog post. In the work I’m doing right now, a common task is finding, e.g., all the suburbs in a given radius from one particular suburb, and Jason’s very first example query is a very similar distance query. When I saw that, I knew my research was going to be fun.

To illustrate how to find closest suburbs to a given suburb, I have created a simple EF data model, with just a Suburb table, which has one spatial field of SQL Server data type geometry:

Here the Geometry property contains a polygon that describes the borders of the suburb, with vertices defined in terms of degrees latitude and longitude. For this example, I will use a simple Pythagorean calculation for distance – we are not using this data for navigation or engineering – between the centres of two suburbs:

And now for the part you’ve all been waiting for, a LINQ to Entities query using a spatial column:

In the above code, I first retrieve my home suburb, Morninghill, and then query for the twenty closest suburbs, by ordering by distance from Morninghill, ascending. Distance, as used in this example, is the distance between the ‘centre’ of Morninghill and another suburb, hence my using the Centroid property, which returns a Point that is effectively the ‘centre’ of a Polygon. Properties and functions like Centroid are available out of the box with the new Spatial data types. I then use a second query to return the twenty closest suburbs as SuburbModel instances, with a distance from Morninghill calculated using our home-rolled Distance function. I need a second query because LINQ to Entities doesn’t allow using custom functions like Distance in a query executed against the data store, in this case SQL Server, as it cannot translate these to native SQL Server functions. It is this second query I would use if I wanted to find all suburbs within a certain distance of Morninghill.
CodeProject