Support for spatial data was introduced in SQL Server 2008. What this allows you to do is capture data related to 2D geometric or geographical images in SQL Server. It actually allows you to do much more than just storing a bunch of numbers representing this data. In the initial releases of Entity Framework the spatial data type was not available for use, but with the Entity Framework June 2011 CTP release this functionality is now available.
I am once again working with the code from my Entity Framework Code First Demo post. This simple application is dream car tracker that tracks manufacturers and models of cars that someday I would love to own. As part of the manufacturer data I currently store the Country but would like to be able to map out the corporate headquarters for the day when I get to take a trip and take delivery. I am able to retrieve the latitude and longitude from Google Maps by knowing the address.
The Ferrari factory located at: Via Abetone Inferiore n. 4, I-41053 Maranello Italy has a latitude and longitude of 44.53244, 10.86412. At this point if you are new to the spatial data type, like I was, you are probably thinking “that is just a decimal and you could always store decimals in SQL Server” and you are correct. I could certainly just add two decimal properties to my model that represent latitude and longitude and go on my merry way but using spatial data functionality introduced in the June CTP I get so much more.
Because I am dealing with geographical data and not geometric data I am going to use the DbGeography type, that can be found in System.Data.Spatial, and add a Location property to my Manufacturer model.
Once I have Entity Framework Code First regenerate my database I can take a look and see that the .NET Framework type DbGeography translates to the SQL Server type Geography.
Now that I have my property I can populate the Location data by converting my latitude and longitude values to a DbGeography using the static parse method. I can add this to my SeedManufacturer method.
Now if you look at the data in SQL Server you an see that the data is now represented as a nasty looking hex.
I know you are still wondering what the point is, pun intended! Well, now I can get to the cool part. I can use the methods that come along with DbGeography to do things such as getting distance between two manufacturers as illustrated below in line 3.
You can also use the methods in LINQ. The query below finds all the manufacturers that are within a 100 mile radius of my house.
Keith is a Senior Software Engineer with Falafel Software. He has been developing software since 1999 specializing in web-based solutions primarily using the Microsoft stack. He has been a Microsoft MVP in ASP.NET since 2012.
The opinions expressed herein are my own personal opinions and do not represent
my employer's view in any way.