Click here to Skip to main content
Click here to Skip to main content

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

, 25 Aug 2012 CPOL
Rate this:
Please Sign up or sign in to vote.
One of the highly-anticipated features in the Entity Framework 5 is a Spatial support.

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.

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Share

About the Author

Mihail Mateev
Team Leader Infragistics
Bulgaria Bulgaria
Mihail Mateev is a Senior Technical Evangelist, Team Lead at Infragistics Inc. He worked as a Software developer and team lead on WPF and Silverlight Line of Business production lines of the company. Now he works like a Technical evangelist in the Infragistics.
Mihail worked in various areas related to technology Microsoft: Silverlight, WPF, Windows Phone 7, Visual Studio LightSwitch, WCF RIA Services, ASP.Net MVC, Windows Metro Applications, MS SQL Server and Windows Azure. He also write many jQuery related blogs.
Over the past ten years, Mihail has written articles for Bulgarian Computer World magazine, blogs about .Net technologies. He is a contributor and a technical editor of publications PACKT Publishing and Wiley. Mihail did presentations for .Net and Silverlight user groups in Bulgaria. He has an Experience with GIS system over .Net framework. He worked more than five years in ESRI Bulgaria like a Software developer and a trainer. Several years Mihail did a lectures about Geographic Information Systems in the Sofia University “St. Kliment Ohridski” , Faculty of Mathematics and Informatics. Mihail is also a lecturer about Computer Systems in the University of the Architecture, Civil Engineering and Geodesy in Sofia at Computer Aided Engineering Department. Mihail holds master's degrees in Structural Engineering and Applied Mathematics and Informatics.
Follow on   Twitter   Google+

Comments and Discussions

 
Questionbravo mihaile Pinmembernitzan levy14-Jul-14 7:29 
GeneralMy vote of 5 PinmemberPankaj Nikam31-Oct-12 20:38 
GeneralMy vote of 4 Pinmemberpradiprenushe27-Aug-12 3:38 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

| Advertise | Privacy | Terms of Use | Mobile
Web01 | 2.8.1411028.1 | Last Updated 25 Aug 2012
Article Copyright 2012 by Mihail Mateev
Everything else Copyright © CodeProject, 1999-2014
Layout: fixed | fluid