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
:
The Suburb Entity
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:
Distance Calculation
And now for the part you’ve all been waiting for, a LINQ to Entities query using a spatial column:
A Query for Nearby Suburbs
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
I am a software developer in Johannesburg, South Africa. I specialise in C# and ASP.NET MVC, with SQL Server, with special fondness for MVC and jQuery. I have been in this business for about eighteen years, and am currently trying to master Angular 4 and .NET Core, and somehow find a way to strengthen my creative faculties.
- Follow me on Twitter at @bradykelly