.NET - Spatial Data in Entity Framework 5.0

Since version 2008 of Microsoft SQL Server we are able to store geographic coordinates in spatial datatypes. Because the ADO.NET Entity Framework did not support this, you had to write TSQL stored procedures to query nearby locations or calculate distances. But now Entity Framework 5.0 has been released and it supports spatial datatypes in .NET.

The DbGeography and DbGeometry types are defined in the 4.5 version of System.Data.Entity assembly. So first of all you have to create a .NET 4.5 project and include the NuGet package Entity Framework 5.0. The DbGeography datatype can be found in namespace System.Data.Spatial. It is immutable so after creating you can't modify any properties. And it has no constructor so you have to use a factory to create an instance. It represents different kinds of geographic data like a coordinate with latitude and longitude.

• Setup of demo project
• Inserting and updating locations
• Calculating distances to a given location
• Calculating distances to a given route
• Locations that intersect with an area

Setup of demo project

I created a small demo application to show some cool features with spatial data in EF 5.0. I started with a new table Castle in SQL Server which stores data of old castles and impressive châteaux in my country Belgium. The table has a nvarchar field Description to store the name and place and a Location field of datatype Geography to store the latitude and longitude. Then I imported this table in my Entity Data Model. The Location property was imported as a DbGeography .NET type.

Be aware thaty EF 5.0 creates a DbContext and not a ObjectContext like in previous version. The DbContext class is a wrapper class of ObjectContext. It exposes the most commonly used features of ObjectContext and it can be used in all development models: Code First, Model First and Database First. I still prefer Database first so I can include a SQL Database project in my solution to keep the database model up to date.

Inserting and updating locations

For inserting a location you can use 2 factory methods FromText and PointFromText and specify a string in the Well-known Text (WKT) format. WKT is a text markup language for representing 18 kinds of geometry/spatial objects. The most common one is a Point with a longitude (-180 180) and latitude (-90 90). Be aware that in the WKT format the longitude is the first parameter and that the coordinates are separated by a space. Most GPS and mapping software shows latitude and then longitude as decimals degrees (DD) or in DDM or DMS format.

Coordinates of Brussels, Belgium
Longitude: 4.352491666666666
Latitude 50.84670833333334

Well-known Text (WKT)			POINT(4.216122140 51.102824254)
Decimal Degrees (DD) 			50.846708 		4.352492
Degrees Decimal Minutes (DDM) 		50° 50.802500' N 	4° 21.149500' E
Degrees Minutes Seconds (DMS) 		50° 50' 48.1500" N 	4° 21' 8.9700" E

Both factory methods FromText and PointFromText have a second parameter coordinateSystemId and it is required in the PointFromText method. The most common SRID/coordinateSystemId in GPS and mapping software is 4326.

var context = new ScipBeDemoEntities();
 
var wellKnownText = "POINT(4.216122140 51.102824254)";
context.Castles.Add(new Castle()
{
    Id = Guid.NewGuid(),
    Description = "De Marnix de Sainte-Aldegonde Castle, Bornem",
    Location = DbGeography.FromText(wellKnownText),
});

var pointWellKnownText = "POINT(4.311275282 50.876433399)";
var coordinateSystemId = 4326;
context.Castles.Add(new Castle()
{
    Id = Guid.NewGuid(),
    Description = "Rivieren Castle, Ganshoren",
    Location = DbGeography.PointFromText(pointWellKnownText, coordinateSystemId)
});

To improve this I create a small static helper class GeographyHelper with a CreatePoint method. I used Code Contracts to validate the input.

/// <summary>
/// Create DbGeography object with given latitude and longitude in decimal degrees.
/// </summary>
/// <param name="longitude">Longitude as double.</param>
/// <param name="latitude">Latitude as double.</param>
/// <returns>DbGeography object.</returns>
public static DbGeography CreatePoint(double latitude, double longitude)
{
    Contract.Requires<ArgumentOutOfRangeException>(latitude >= -90 && latitude <= 90);
    Contract.Requires<ArgumentOutOfRangeException>(longitude >= -180 && longitude <= 180);
 
    string wellKnownText = String.Format(CultureInfo.InvariantCulture.NumberFormat,
       "POINT({0} {1})", longitude, latitude);
    return DbGeography.FromText(wellKnownText);
}

Now you improve the code for adding locations:

context.Castles.Add(new Castle()
{
    Id = Guid.NewGuid(),
    Description = "Gravensteen Castle, Ghent",
    Location = GeographyHelper.CreatePoint(51.057189625, 3.720707743)
});

Once the data is inserted in the database you can query it. Because it is an immutable type, you cann't change the Latitude or Longitude property after loading an entity. So if you want to update a location, then you have to create a new DbGeography object:

private static void UpdateCastle(ScipBeDemoEntities context, Guid castleId, 
  double newLatitude, double newLongitude)
{
    var castle = context.Castles.SingleOrDefault(c => c.Id == castleId);
    if (castle != null)
    {
        castle.Location = GeographyHelper.CreatePoint(newLatitude, newLongitude);
        context.SaveChanges();
    }
}

Calculating distances to a given location

Now all castles are available in the database we can start querying them and calculating distances.

In my first query want to retrieve all castles nearby a given location. So I pass the latitude and longitude of my location, the radius in kilometres and in the query I calculate the distance. The DbGeography type has a Distance function which returns the results in meters.

private static void QueryCastlesOnDistance(ScipBeDemoEntities context, 
  double latitude, double longitude, double radiusInKm)
{
    var location = GeographyHelper.CreatePoint(latitude, longitude);
 
    var radiusInMeters = radiusInKm * 1000;
 
    var castles =
        from c in context.Castles
        let distance = c.Location.Distance(location)
        where distance <= radiusInMeters
        select new
        {
            c.Description,
            c.Location,
            Distance = distance
        };
 
    foreach (var castle in castles.OrderBy(c => c.Distance))
    {
        Console.WriteLine(string.Format("{0}: {1} {2} - {3}km", 
            castle.Description, 
            castle.Location.Latitude, 
            castle.Location.Longitude, 
            castle.Distance / 1000));
    }
}

My new function can be called to check which castles in Belgium are nearby the cities of Antwerp and Ghent. These are the results:

Console.WriteLine("Castles 30km from Antwerp");
QueryCastlesOnDistance(context, 51.22116744201541, 4.399556375162323, 30);

Het Steen Castle, Antwerp: 51.222708023 4.397398218 - 0.23km
Cortewalle Castle, Beveren: 51.213818551 4.265149774 - 9.43km
Cleydael Castle, Aartselaar: 51.138134445 4.363969892 - 9.57km
Wissekerke Castle, Bazel: 51.145421326 4.301663414 - 10.86km
De Marnix de Sainte-Aldegonde Castle, Bornem: 51.102824254 4.21612214 - 18.38km
Borrekens Castle, Vorselaar: 51.213167616266 4.77747750082014 - 26.42km

Console.WriteLine("Castles 10km from Ghent");
QueryCastlesOnDistance(context, 51.05391501651696, 3.724127989266182, 10);

Gravensteen Castle, Ghent: 51.057189625 3.720707743 - 0.44km

Console.WriteLine("Castles 50km from Ghent");
QueryCastlesOnDistance(context, 51.05391501651696, 3.724127989266182, 50); 

Gravensteen Castle, Ghent: 51.057189625 3.720707743 - 0.44km
Poeke Castle, Aalter: 51.041207724 3.450018773 - 19.27km
De Marnix de Sainte-Aldegonde Castle, Bornem: 51.102824254 4.21612214 - 34.91km
Wissekerke Castle, Bazel: 51.145421326 4.301663414 - 41.72km
Cortewalle Castle, Beveren: 51.213818551 4.265149774 - 41.84km
Rivieren Castle, Ganshoren: 50.876433399 4.311275282 - 45.73km
Cleydael Castle, Aartselaar: 51.138134445 4.363969892 - 45.79km

Calculating distances to a given route

As shown in previous examples the DbGeography type can contain a specific location with latitude and longitude coordinates. But it can also contain other spatial types like a LineString and Polygon which hold a list of coordinates. For a route you have to use the LineString WKT format and specify a list of coordinates.

LINESTRING(4.216122 51.102824, 4.265149 51.213818, 4.301663 51.145421)

Of course it is quite easy to write a helper function to create the WKT string like I did for the CreatePoint method. But there is also another solution. Besides the spatial datatypes in the System.Data.Entity assembly, there are also spatial types and helper classes in the Microsoft.SqlServer.Types assembly and namespace. There is a SqlGeography type and SqlGeographyBuilder class to create geography types. So I created a helper method that uses these classes to create a SqlGeography type with a LineString and finally that is converted to a DbGeography type.

public static DbGeography CreateLineString(List<DbGeography> points)
{
    var builder = new SqlGeographyBuilder();
    builder.SetSrid(4326);
    builder.BeginGeography(OpenGisGeographyType.LineString);
 
    var i = 0;
    foreach (var point in points)
    {
        if (i == 0)
        {
            builder.BeginFigure(point.Latitude.Value, point.Longitude.Value);
            i++;
        }
        else
        {
            builder.AddLine(point.Latitude.Value, point.Longitude.Value);
        }
    }
    builder.EndFigure();
    builder.EndGeography();
 
    var dbGeography = DbGeography.FromBinary(
      builder.ConstructedGeography.STAsBinary().Value, SRID);
    return dbGeography;
}

I calculated the route from the city of Antwerp to the city Ghent with Google Earth and then I exported all these coordinates. In my demo application I called the GreatePoint and CreateLineString helper functions to get a DbGeography object which holds a LineString. In a Linq query I can use the same Distance function to calculate the distace to the nearest point in the given route.

private static void QueryCastlesOnRoute(ScipBeDemoEntities context)
{ 
    var points = new List<DbGeography>();
    points.Add(GeographyHelper.CreatePoint(51.219170, 4.403820));
    points.Add(GeographyHelper.CreatePoint(51.218945, 4.404461));
    points.Add(GeographyHelper.CreatePoint(51.218539, 4.404464));
    ...
    points.Add(GeographyHelper.CreatePoint(51.054099, 3.717785));
    points.Add(GeographyHelper.CreatePoint(51.054358, 3.717459));
    var lineString = GeographyHelper.CreateLineString(points);
 
    var radiusInKm = 4;
    var radiusInMeters = radiusInKm * 1000;
 
    var castles =
        from c in context.Castles
        let distance = c.Location.Distance(lineString)
        where distance <= radiusInMeters            
        select new
        {
            c.Description,
            c.Location,
            Distance = distance / 1000
        };
 
    foreach (var castle in castles.OrderBy(c => c.Description))
    {
        Console.WriteLine(string.Format("{0}: {1} {2} - {3:0.00}km",
            castle.Description,
            castle.Location.Latitude,
            castle.Location.Longitude,
            castle.Distance));
    }
}

Cortewalle Castle, Beveren: 51.213818551 4.265149774 - 3.65km
Gravensteen Castle, Ghent: 51.057189625 3.720707743 - 0.39km
Het Steen Castle, Antwerp: 51.222708023 4.397398218 - 0.60km

Locations that intersect with an area

Another spatial type is the Polygon. It also holds a collection of coordinates which describe the boundaries of an area. The first and last point should be the same and make sure to use double brackets in the WKT format.

POLYGON((4.266112120 51.31391191, 4.332895687 51.27651498, ..., 4.266112120 51.31391191))

To create a polygon I extended and renamed my previous CreateLineString method. I changed it into CreateGeographyType and I passed a enum parameter of type OpenGisGeographyType. Now I can create LineString and Polygon objects with this function.

/// <summary>
/// Create geography type of type LineString or Polygon with given list of points
/// (=coordinates with latitude and longitude).
/// </summary>
/// <param name="points">List of points (DbGeography objects).</param>
/// <param name="geographyType">LineString or Polygon.</param>
/// <returns>DbGeography object.</returns>
public static DbGeography CreateGeographyType(
   List<DbGeography> points, OpenGisGeographyType geographyType)
{
    Contract.Requires<ArgumentOutOfRangeException>(points != null);
    Contract.Requires<ArgumentOutOfRangeException>(points.Any());
    Contract.Requires<ArgumentOutOfRangeException>(
      geographyType == OpenGisGeographyType.LineString 
      || geographyType == OpenGisGeographyType.Polygon);
 
    var builder = new SqlGeographyBuilder();
    builder.SetSrid(4326);
    builder.BeginGeography(geographyType);
 
    var i = 0;
    foreach (var point in points)
    {
        if (i == 0)
        {
            builder.BeginFigure(point.Latitude.Value, point.Longitude.Value);
            i++;
        }
        else
        {
            builder.AddLine(point.Latitude.Value, point.Longitude.Value);
        }
    }
    builder.EndFigure();
    builder.EndGeography();
 
    var dbGeography = DbGeography.FromBinary(
      builder.ConstructedGeography.STAsBinary().Value, SRID);
    return dbGeography;
}

In following example I created a polygon with the rough boundaries of the province of Antwerp in Belgium. By using the Intersect method of the DbGeography type I can query all locations which can be found in this given area.

private static void QueryCastlesInArea(ScipBeDemoEntities context)
{
    var points = new List<DbGeography>();
    points.Add(GeographyHelper.CreatePoint(51.31391191024072, 4.266112120769479));
    points.Add(GeographyHelper.CreatePoint(51.27651498062908, 4.332895687078189));
    points.Add(GeographyHelper.CreatePoint(51.24748068635581, 4.304982047677257));
    points.Add(GeographyHelper.CreatePoint(51.18347621420882, 4.336282813016014));
    ...
    points.Add(GeographyHelper.CreatePoint(51.31391191024072, 4.266112120769479));
    var polygon = GeographyHelper.CreateGeographyType(points, OpenGisGeographyType.Polygon);
 
    var castles =
        from c in context.Castles
        where c.Location.Intersects(polygon)
        select new
        {
            c.Description,
            c.Location,
        };
 
    foreach (var castle in castles.OrderBy(c => c.Description))
    {
        Console.WriteLine(string.Format("{0}: {1} {2}",
            castle.Description,
            castle.Location.Latitude,
            castle.Location.Longitude));
    }
}

Borrekens Castle, Vorselaar: 51.213167616266 4.77747750082014
Cleydael Castle, Aartselaar: 51.138134445 4.363969892
De Marnix de Sainte-Aldegonde Castle, Bornem: 51.102824254 4.21612214
Het Steen Castle, Antwerp: 51.222708023 4.397398218

So Entity Framework 5.0 finally supports spatial data and now we can use simple Linq queries to query geographical data, which is quite cool isn't it? If you have any suggestions or comments be sure to let me know.