Click here to Skip to main content
Click here to Skip to main content

Spatial Entities

, 15 Oct 2011
Rate this:
Please Sign up or sign in to vote.
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 int

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:

Entity Model

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:

Source code for distance function

Distance Calculation

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

Source code for querying the Suburb entities

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 effectivly 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.

License

This article, along with any associated source code and files, is licensed under A Public Domain dedication

About the Author

Brady Kelly
Founder Erisia Web Development
South Africa South Africa
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 fifteen years, and am currently trying to learn WPF and MVVM from the bottom up, and somehow find a way to strengthen my creative faculties.
- Follow me on Twitter at @bradykelly
Follow on   Twitter

Comments and Discussions

 
-- There are no messages in this forum --
| Advertise | Privacy | Mobile
Web01 | 2.8.140721.1 | Last Updated 16 Oct 2011
Article Copyright 2011 by Brady Kelly
Everything else Copyright © CodeProject, 1999-2014
Terms of Service
Layout: fixed | fluid