Using Spatial Data in ASP.Net MVC with SQL Server 2012, Entity Framework 5 and Infragistics jQuery Map

[Infragistics] Mihail Mateev / Wednesday, July 11, 2012

One of the highly-anticipated features in the Entity Framework 5 is a Spatial support.

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

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 andDbGeography 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

Creating a Model with Spatial Data

Let's start by creating a simple Entity Framework model that includes entities from sample databases Northwind and SpatialDemo. The entity named world contains a geom property of type DbGeometry. Sample is using SQL Server 2012, but you could run it with SQL Server 2008.

[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();

Northwind Entities

Sample SpatialDemo database, including spatial data. Table world a filed from Geometry type, named “geom”. This field contains the contours of countries as polygons

Entity world, generated from SpatialDemo database contains a field “geom” from Geometry type.

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

Now it is a pretty easy to use spatial data in ASP.Net MVC applications.

Controller

Controller returns a view that contains a dashboard with Infragistics jQuery controls.

#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

CountryByName method 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 ret = new List();

    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

GetGeometryBoundary is a helper method used 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 multiPoints = new List();

    var numPoints = country.geom.Envelope.ElementAt(1).PointCount;

    for (int i = 1; i <= numpoints="" i="" pre="">
    {
        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

ContryInfo is 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

SpatialPoint is a helper class to keep a point data. You could use

#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

SpatialRect is a helper class to keep an extend of the country

#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

GetBounds is 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 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 from Infragistics jQuery Grid, Chart and Map.

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).

var countryUrl = "/Home/CountryByName?countryName=" + args.row.element[0].cells[1].textContent

  

...

  

$.getJSON(countryUrl,

  function (json, text) {

      $.each(json, function (index, value) {

          var country = value;

          var extend = country["Extend"];

  

          var zoom = $("#map").igMap("getZoomFromGeographic", extend);

          $("#map").igMap("option", "windowRect", zoom);

  

      });

});

Infragistics jQuery Map instance definition.

$("#map").igMap({

     width: "500px",

     height: "500px",

     panModifier: "control",

     horizontalZoomable: true,

     verticalZoomable: true,

     windowResponse: "immediate",

     overviewPlusDetailPaneVisibility: "visible",

     seriesMouseLeftButtonUp: function (ui, args) {

         var tets = args;

     }

 });

Infragistics jQuery Grid with zoom around the selected customer’s country.

$('#grid').igGrid({

     virtualization: false, height: 280, width: 650,

     dataSource: "/Home/Customers",

     autoGenerateColumns: false,

     columns: [

         { headerText: "Customer ID", key: "CustomerID", width: "120px", dataType: "string" },

         { headerText: "Country", key: "Country", width: "150px", dataType: "string" },

         { headerText: "City", key: "City", dataType: "string" },

         { headerText: "Contact Name", key: "ContactName", dataType: "string" },

         {headerText: "Phone", key: "Phone", dataType: "string" }

     ],

     features: [

  

      {

          name: 'Selection',

          mode: 'row',

          multipleSelection: false,

          rowSelectionChanged: function (ui, args) {

              $("#chart").igDataChart({

                  dataSource: "/Home/Orders?userID=" + args.row.element[0].cells[0].textContent

              });

  

              selected = args.row.element[0].cells[0].textContent; //keep track of selected user

              var countryUrl = "/Home/CountryByName?countryName=" + args.row.element[0].cells[1].textContent

  

  

             $.getJSON(countryUrl,

                function (json, text) {

                    $.each(json, function (index, value) {

                        var country = value;

                        var extend = country["Extend"];

  

                        var zoom = $("#map").igMap("getZoomFromGeographic", extend);

                        $("#map").igMap("option", "windowRect", zoom);

  

                    });

              });

  

          }

      }

     ,

  

     {

         name: 'Sorting',

         type: "remote"

     },

     {

         name: 'Paging',

         type: "local",

         pageSize: 10

     }]

  

 })

Spatial Data in Action

Run the application and select the “Spatial Data Dashboard” from the menu.

Choose a customer from the igGrid and see how the map shows the country from which the client

You can download source code of the sample here.

To run this sample you could download Northwind and SpatialDemo sample databases

As always, you can follow us on Twitter: @mihailmateev and @Infragistics , all tweets with hashtag #infragistcs and stay in touch on Facebook, Google+ , LinkedIn and Infragistics Friends User Group !