# Monday, January 23, 2012
Entity Framework Code First Spatial Data

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.

Manufacturer.cs
  1. public class Manufacturer {
  2.  
  3.     public long ManufacturerId { get; set; }
  4.  
  5.     [Required]
  6.     [StringLength(40)]
  7.     public string Name { get; set; }
  8.  
  9.     [Required]
  10.     [StringLength(40)]
  11.     public string Country { get; set; }
  12.  
  13.     [Required]
  14.     public DbGeography Location { get; set; }
  15.  
  16.     #region << Navigation Properties >>
  17.  
  18.     public virtual IList<Model> Models { get; set; }
  19.  
  20.     #endregion
  21.  
  22. }

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.

image

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.

  1. private void SeedManufacturer(DataContext context) {
  2.     _ferrari = new Manufacturer {Name = "Ferrari", Country = "Italy"};
  3.     _lamborghini = new Manufacturer {Name = "Lamborghini", Country = "Italy"};
  4.     _astonMartin = new Manufacturer {Name = "Aston Martin", Country = "United Kingdom"};
  5.     _porsche = new Manufacturer {Name = "Porsche", Country = "Germany"};
  6.     _ferrari.Location = DbGeography.Parse("POINT(44.53244    10.86412)");
  7.     _lamborghini.Location = DbGeography.Parse("POINT (44.65934    11.12693)");
  8.     _astonMartin.Location = DbGeography.Parse("POINT (52.18708    -1.48351)");
  9.     _porsche.Location = DbGeography.Parse("POINT (48.83498    9.15231)");
  10.     new List<Manufacturer> {_ferrari, _lamborghini, _astonMartin, _porsche}.ForEach(x => context.Manufacturers.Add(x));
  11. }

Now if you look at the data in SQL Server you an see that the data is now represented as a nasty looking hex.

image

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.

  1. Manufacturer ferrari = dc.Manufacturers.First(x => x.Name == "Ferrari");
  2. Manufacturer lamborghini = dc.Manufacturers.First(x => x.Name == "Lamborghini");
  3. double distanceInKilometers = ferrari.Location.Distance(lamborghini.Location);
  4. double distanceInMiles = distanceInKilometers/1609.344;
  5. string outputText = string.Format("{0} is {1} miles from {2}", ferrari.Name, distanceInMiles, lamborghini.Name);

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.

  1. DbGeography myHouse = DbGeography.Parse("POINT (4.65934    1.12693)");
  2. IList<Manufacturer> closeToMe = manufacturers.Where(x => (x.Location.Distance(myHouse)*.00062) <= 100).ToList();
Code | Code First | EF | Entity Framework
posted on Monday, January 23, 2012 8:30:40 AM (Central Standard Time, UTC-06:00)  #    Comments [3]
Related posts:
Asynchronous Operations Come To Entity Framework 6
Kendo UI for ASP.NET MVC: Part 3: Creating a Bundle for the Kendo UI Scripts and Content
Kendo UI for ASP.NET MVC: Part 2–AutoComplete
Kendo UI in ASP.NET MVC: Part 3–Introducing Kendo UI for ASP.NET MVC
Getting Started with Kendo UI in ASP.NET MVC
ASP.NET MVC: Putting Your Controllers In A Separate Assembly
Tuesday, January 31, 2012 4:26:33 AM (Central Standard Time, UTC-06:00)
I don't have any words to appreciate this post.....I am really impressed ....the person who created this post surely knew the subject well. Thanks for sharing this with us.

Unitech New Residential Project
Monday, February 13, 2012 10:56:08 AM (Central Standard Time, UTC-06:00)
Great post.
I can't find System.Data.Spatial in EF 4.3 :-(
Is it going to be in vNext (4.5?)
ivo
Saturday, February 25, 2012 3:10:16 PM (Central Standard Time, UTC-06:00)
ivo,
Glad you enjoyed the post.

System.Data.Spatial is part of the functionality that is waiting to be released as part of the next major release of EF and is currently only available as part of the June CTP.
Keith Burnell
Name
E-mail
(will show your gravatar icon)
Home page

Comment (Some html is allowed: a@href@title, strike) where the @ means "attribute." For example, you can use <a href="" title=""> or <blockquote cite="Scott">.  

Enter the code shown (prevents robots):
Live Comment Preview