Using Spatial Data Types with Entity Framework 5.0

One of the latest features added in Entity framework 5.0 is its support for Spatial data type. Entity Framework supports working with spatial data through the DbGeography or DbGeometry classes. Spatial Data type is used to store the location data. Location can be points on earth or any geometric shapes like polygon, curve, etc. Now the various kind of operation supported by these types can make calculations easy in real life applications. Calculating shortest distance, area, elevation  are  few examples. Importance of spatial data has increased with the increasing demand for location aware applications. Realizing this, SQL server started support for spatial types from version 2008.

What is Spatial data Types

Spatial Data types are categorized into the geometry and geography data types. It  support sixteen spatial data objects, or instance types. Not only points the object can be many other shapes. We can perform various kind of operations on these types. For example, finding shortest distance, finding area, finding the elevation, etc.

The figure taken from http://technet.microsoft.com below depicts the geometry hierarchy upon which the geometry and geography data types are based. The instantiable type of geometry and geography are indicated in blue.

Geometry and geography are almost same with slight difference that geography represents the points on ellipsoidal earth. example, in terms of lattitude and lattitude while geometry is based on flat coordinate system (More details can be found at http://technet.microsoft.com/en-us/library/bb964711.aspx)


Let’s start with a simple application of spatial data, which could be useful for most applications. Spatial data is useful in almost every case because every database has person as entity and every person has an address. We usually think of addresses as street, city, state, country, and ZIP code, but an address is a point on the earth and can also be represented as a latitude/longitude and elevation above mean sea level. Once we represent address in this format, we can now represent it on maps, we can use geolocation API of HTML5 compatible browsers, We can map it to Geographic Information system (GIS)

Measurements in spatial data types

In the planar, or flat-earth, system, measurements of distances and areas are given in the same unit of measurement as coordinates. Using the geometry data type, the distance between (1, 1) and (4, 5) is 5 units, regardless of the units used.

In the ellipsoidal or earth coordinate system, coordinates are given in degrees of latitude and longitude. However, lengths and areas are usually measured in meters and square meters. The most common unit of measurement for the Geography data type is meters.

An example script in SQL server 2012 to find the shortest distance between curves is shown below. Similarly, distance between point and points ,points and a region can be found.

DECLARE @Shape1 geometry = ‘POLYGON ((-20 -30, -3 -26, 20 -40, -20 -30))’
DECLARE @Shape2 geometry = ‘POLYGON ((-18 -20, 0 -10, 4 -12, 10 -20, -18 -20))’
SELECT @Shape1
SELECT @Shape2
SELECT @Shape1.ShortestLineTo(@Shape2).STBuffer(.5)


To use Spatial data types with Entity Framework, The application should be targeted to .NET 4.5 version. Visual Studio 2012 targets .NET 4.5 by default.

Example Application:

We follow Code first Approach of entity framework to create an application.  DbGeography and DbGeometry types are part of system.data.spatial assembly

The created  model is as follows: We create Bank entity to demonstrate the DbGeography type.

Code Snippet
  1. using System.Data.Entity;
  2. using System.Data.Spatial;
  3. namespace SpatialBlog.Models
  4. {
  5.     public class BankContext : DbContext
  6.     {
  7.         public BankContext()
  8.             : base(“BlogConnection”)
  9.         {
  10.         }
  11.         public DbSet<Bank> Banks { get; set; }
  12.      }
  13.      public class Bank {
  14.         [Key]
  15.         [DatabaseGeneratedAttribute(DatabaseGeneratedOption.Identity)]
  16.         public int Id { get; set; }
  17.         public string BankName { get; set; }
  18.         public DbGeography location { get; set; }
  19.     }


We can see that Apart from Bank name, Bank entity has location which is of the DbGeography type. After following necessary steps to generate database from code first approach, we create some sample data in the database as follows:

Code Snippet
  1. using (var context = new Bank Context())
  2.  {
  3. Bank BankSBI = newBank(){ BankName = “State Bank of India”, location = DbGeography.FromText(“POINT(-122.336106 47.605049)”) };
  4. Bank BankPNB = new Bank(){ BankName = “Punjab National Bank”,location = DbGeography.FromText(“POINT(-122.335197 47.646711)”)};
  5. context.Banks.Add(BankPNB);
  6. context.Banks.Add(BankSBI);
  7. context.SaveChanges();
  8.  }


Now the generated Database can be seen:
Notice the location field which is represented as hexadecimal value and value is not readable like Int or string. Still value can be seen visually by clicking on the spatial result tab shown under the pink curve

generated Database

Now the two generates points can be seen in the grid with lattitude and longitude values. If we enter more spatial data, For example, every points on border of Asia, we can see the real shape of Asia.

We can see that Apart from Bank name, Bank entity has location which is of the DbGeography type. After following necessary steps to generate database from code first approach, we create some sample data in the database as follows:
Now we can use LINQ queries to perform various manipulation over these data types, like finding the all the banks within 99999 meters., Where -122.296623 is longitude and 47.640405 is lattitude.

Code Snippet
  1. var myLocation = DbGeography.FromText(“POINT(-122.296623 47.640405)”);
  2. var bank = context.Banks.FirstOrDefault(u=>u.location.Distance(myLocation) < 99999.0);

Example above shows the use of Distance method of DbGeography class, which takes another DbGeography object as parameter and returns the shortest distance in Meters. We can also find the other values like area, dimensions etc., if the location of type is collection of points or enclosed curve by accessing its property.

Once we understand EF support for Spatial data, we can create mobile application or web application and further display the results on various Map service providers like google earth. Benefit of Support for spatial types are that the position given by GPS, mobile or networks are in compatible format (latitude and longitude) and hence can be directly used.

We create a simple application for robber to demonstrate this. Suppose a robber using this application wants to rob a bank. As he is in urgent and quick need of money. He wants to rob a nearest bank. First of all he finds his location and clicks on find nearest bank. Figure below shows a web application with two simple buttons Find your location and find nearest bank.


On clicking the check your location button, A confirmation appears for sharing the location. HTML 5 Browser supports Geolocation API which can calculate the approx position by IP address and Wi-fi network. If it is mobile application, it also checks for GPS position solution to find at better approximation. Hence it can’t be very accurate for client using web application as much for mobile application


navigator.geolocation.getCurrentPosition(showPosition) is the method to find current position as depicted in script below.

Code Snippet
  1. <script>
  2.     var lat;
  3.     var lng;
  4.     var x = document.getElementById(“demo”);
  5.     function getLocation() {
  6.         if (navigator.geolocation) {
  7.             navigator.geolocation.getCurrentPosition(showPosition);
  8.         }
  9.         else { x.innerHTML = “Geolocation is not supported by this browser.”; }
  10.     }
  11.     function showPosition(position) {
  12.         x.innerHTML = “Latitude: “ + position.coords.latitude +
  13.         “<br>Longitude: “ + position.coords.longitude;
  14.         lat = position.coords.latitude;
  15.         lng = position.coords.longitude;
  16.     }
  17.     function PlotonMap(lat, long) {
  18.         var latlon = lat + “,” + long;
  19.         var img_url = “http://maps.googleapis.com/maps/api/staticmap?center=”
  20.     + latlon + “&zoom=14&size=400×300&sensor=false”;
  21.         document.getElementById(“mapholder”).innerHTML = “<img src='” + img_url + “‘>”;
  22.     }
  23. </script>


Now we can pass this position to web server to find the nearest bank from database.

Code Snippet
  1. <script>
  2.     function findNearest() {
  3.         $.ajax({
  4.             url: @Url.Action(“FindNearest”, “Home”),
  5.         data: { lattitude: lat, longitude: lng },
  6.         type: ‘POST’,
  7.         success: function (dataResult) {
  8.             $(“#lat”).after(dataResult.lattitude);
  9.             $(“#lng”).after(dataResult.longitude);
  10.             $(“#bankName”).after(dataResult.name);
  11.             $(“#dist”).after(dataResult.distance);
  12.             PlotonMap(dataResult.lattitude, dataResult.longitude);
  13.         },
  14.         error: function (data) {
  15.             alert(this.error);
  16.             alert(“error”);
  17.         }
  18.     });
  19. }
  20. </script>


Code below is part of controller class and it uses Entity framework with LINQ query to find nearest Bank.

Code Snippet
  1. public ActionResult FindNearest(string lattitude, string longitude)
  2. {
  3. var context = new Bank Context();
  4. var Location = DbGeography.PointFromText(
  5. string.Format(“POINT({0} {1})”, lattitude, longitude),4326);
  6. ICollection<Bank> bankList = context.Banks.OrderBy(u=>     u.location.Distance(Location)).ToList();
  7. Bank nearbank = bankList.FirstOrDefault();           
  8. var name =   nearbank.BankName;
  9. var lat = nearbank.location.Latitude.Value;
  10. var longit =  nearbank.location.Longitude.Value;
  11. var dist = nearbank.location.Distance(Location);                   
  12. return Json(new {lattitude=lat, longitude=longit,name= nearbank.BankName,distance=dist/1000});
  13. }


The result is displayed below:

We can see from figure above the calculated distance with the coordinate of the Bank. This coordinate is also plotted on static google map. Thus robber using my application finds the nearest bank as Punjab National Bank (PNB) and the approx distance from his current location as 6002 Km. Of course, PNB is not that much far away from any place in India, but I entered coordinate of bank incorrectly while inserting into database through EF. Thus robber got discouraged to rob the bank and my heroic effort saved the bank from robbery. Actually, The random Coordinate I have put for PNB corresponds to some place in Seattle, US as evident from MAP.

You might also like