Using Spatial Data with SQL Server 2012, Entity Framework 5 and ASP.NET MVC

Introduction

Many developers have been asking since the release of SQL 2008 for support of Spatial data types in the Entity Framework. It was a dream for the Microsoft ORM users to create .NET business applications quickly, using spatial data.  In May of this year the release candidate for Entity Framework 5 (EF5) was announced.This version has increased performance when compared to earlier EF version and also has support for spatial types. The Spatial functionality in EF5 requires .NET 4.5. 

The Spatial functionality in EF5 requires .NET 4.5. This means you will need Visual Studios 2012 installed. You can download the release candidate for VS 2012 here: http://www.microsoft.com/visualstudio/en-us  

Background

Spatial Data in the Entity Framework

Prior to Entity Framework 5.0 on .NET 4.5 consuming of the data above required using stored procedures or raw SQL commands to access the spatial data. In Entity Framework 5 however, Microsoft introduced the new DbGeometry and DbGeography types. These immutable location types provide a bunch of functionality for manipulating spatial points using geometry functions which in turn can be used to do common spatial queries like I described in the SQL syntax above.

The DbGeography/DbGeometry types are immutable, meaning that you can't write to them once they've been created. They are a bit odd in that you need to use factory methods in order to instantiate them - they have no constructor() and you can't assign to properties like Latitude and
Longitude. 

It is important to mention that these types are defined in System.Data.Entity assembly in System.Data.Spatial namespace. By now you have probably used types SqlGeometry
and SqlGeography types, defined in Microsoft.SqlServer.Types namespace.

For example an entity named world contains a geom property of type DbGeometry.

[EdmScalarPropertyAttribute(EntityKeyProperty=false, IsNullable=true)]
[DataMemberAttribute()]
public global::System.Data.Spatial.DbGeometry geom
{
    get
    {
        return _geom;
    }
    set
    {
        OngeomChanging(value);
        ReportPropertyChanging("geom");
        _geom = StructuralObject.SetValidValue(value, true, "geom");
        ReportPropertyChanged("geom");
        OngeomChanged();
    }
}
private global::System.Data.Spatial.DbGeometry _geom;
partial void OngeomChanging(global::System.Data.Spatial.DbGeometry value);
partial void OngeomChanged();  

Using the code

ASP.NET MVC 4 Application with Entity Framework 5 RC and Spatial data.

Controller

Controller returns a view that contains UI controls, that show spatial elements.

Data maintenance is implemented in the controller. This article also is focused on this part of MVC. 

#region DashboardJs
public ActionResult DashboardJs()
{
    ViewBag.Message = "Spatial Data Dashboard";

    return View();
}
#endregion //DashboardJs

Spatial Data Maintenance

When you have a data from DbGeometry / DbGeography type you can’t serialize it. There are two options: 

To convert spatial data type to WKT (Well Known Text) and send it to the client (view) as part of JSON or XML 

• To use your own classes that could be serialized 
• This sample is demonstrates the second approach

You need to have a method that serializes results to JSON to be possible to use it in the view

#region CountryByName
[OutputCache(VaryByParam = "countryName", Duration = 120)]
public JsonResult CountryByName(string countryName)
{
    switch (countryName)
    {
        case "UK":
            countryName = "United Kingdom";
            break;
        case "USA":
            countryName = "United States";
            break;
    }
    var results = spDemo.worlds.Where(x => x.CNTRY_NAME == countryName);


    List<CountryInfo> ret = new List<CountryInfo>();
    foreach (world country in results)
    {
        CountryInfo info = new CountryInfo
        {
            Id = country.ID,
            Code = country.CODE,
            CountryName = country.CNTRY_NAME,
            Population = country.POP_CNTRY,
            Extend = GetGeometryBoundary(country)
        };
        ret.Add(info);
    }

    var retVal = Json(ret, JsonRequestBehavior.AllowGet);
    return retVal;
}
#endregion //CountryByName

You need to use also several helper methods to get a list of points, representing an envelope of a DbGeometry instance. Don't forget that DbGeometry/DbGeography point indexes start from 1 !. 

#region GetGeometryBoundary
public static SpatialRect GetGeometryBoundary(world country)
{
    List<SpatialPoint> multiPoints = new List<SpatialPoint>();
    var numPoints = country.geom.Envelope.ElementAt(1).PointCount;
    for (int i = 1; i <= numPoints; i++)
    {
        SpatialPoint pnt = new SpatialPoint((double)(
           country.geom.Envelope.ElementAt(1).PointAt(i).XCoordinate), 
           (double)(country.geom.Envelope.ElementAt(1).PointAt(i).YCoordinate));
        multiPoints.Add(pnt);

    }
    SpatialRect rect = multiPoints.GetBounds();
    return rect;
}
#endregion //GetGeometryBoundary

A helper class used to serialize data:

#region CountryInfo
public class CountryInfo
{
    public int Id { get; set; }
    public string Code { get; set; }
    public string CountryName { get; set; }
    public long? Population { get; set; }
    public SpatialRect Extend { get; set; }

}
#endregion //CountryInfo

A helper class to keep a point data.

#region SpatialPoint
public class SpatialPoint
{
    public SpatialPoint(double x, double y)
    {
        this.X = x;
        this.Y = y;
    }

    public double X { get; set; }
    public double Y { get; set; }
}
#endregion //SpatialPoint

A helper class to keep an extend of the geometry object (country in this case) 

#region SpatialRect
public struct SpatialRect
{
    public SpatialRect(double pLeft, double pTop, double pWidth, double pHeight)
    {
        left = pLeft;
        top = pTop;
        width = pWidth;
        height = pHeight;
    }

    public double left; 
    public double top; 
    public double width; 
    public double height; 
}
#endregion //SpatialRect

An extension method used to get a boundary of the list of points

#region Extensions
public static class Extensions
{

    #region GetBounds
    public static SpatialRect GetBounds(this IList<SpatialPoint> points)
    {
        double xmin = Double.PositiveInfinity;
        double ymin = Double.PositiveInfinity;
        double xmax = Double.NegativeInfinity;
        double ymax = Double.NegativeInfinity;

        SpatialPoint p;
        for (var i = 0; i < points.Count; i++)
        {
            p = points[i];
            xmin = Math.Min(xmin, p.X);
            ymin = Math.Min(ymin, p.Y);

            xmax = Math.Max(xmax, p.X);
            ymax = Math.Max(ymax, p.Y);
        }

        if (Double.IsInfinity(xmin) || Double.IsInfinity(ymin) || 
                  Double.IsInfinity(ymin) || Double.IsInfinity(ymax))
        {
            return new SpatialRect(0.0, 0.0, 0.0, 0.0);
        }

        return new SpatialRect(xmin, ymin, xmax - xmin, ymax - ymin);
    }
    #endregion //GetBounds
} 
#endregion //Extensions

View

The view presents a dashboard with UI components.

The most important part in the sample is how to query the controller’s method that returns spatial data (the country extend in this case). 

Mode details about one possible solution you could find in my blog here. 

Download source code here.

Points of Interest 

This approach could be used with different platforms. In this article is mentioned JSON serialization, but you  could use different WEB services (like WCF, OData) to serialize spatial data in different way - binary, XML or JSON. Hope this approach will be useful for developers on various client platforms. 

History  

I will try to add implementations for mobile web platforms soon.