Gal Ratner
Gal Ratner is a Techie who lives and works in Los Angeles. Follow galratner on Twitter Google
Using ASP.NET MVC 4, ApiControllers and SQL Server 2012 to find the best cup of coffee in Seattle

ASP.NET MVC continues to innovate and in its current version (4) it offers support for Web APIs. Today we are going to examine a real life scenario of using Web APIs along with MVC 4 as we are utilizing the Google Earth plug-in to mark a defined area and SQL Server 2012 geography type to help us persist and calculate GIS data.
In our example we are going to mark an area on the globe using a polygon, calculate the center of the area, find all of the coffee shops closest to the center of the area and arrange them by distance. We are going to mark them on the map as well as show a full list including ranking.
Before we begin, let’s briefly go over the technologies we are going to use today:
ASP.NET Web API
Formally known as the WCF Web API this was a community driven project hosted on Codeplex (http://wcf.codeplex.com/wikipage?title=WCF%20HTTP). It was created to allow direct control over HTTP based communication between client and server applications. Its simplicity and compatibility for working within applications built with a RESTful architecture helped it gain popularity and find its way into the .NET 4.5 Framework under the namespace System.Net.Http.
There are lots of tutorials online about using the Web API and you can start learning it by going here: http://www.asp.net/web-api
ApiController
Residing in System.Web.Http, APIControllers utilize System.Net.Http to handle HTTP verbs and map them into methods. A GET verb will be mapped to a Get() method, POST to Post() and so on. Using client JavaScript libraries we are able to build a RESTful application fairly quickly with the default controller mapping. More on ApiController here: http://msdn.microsoft.com/en-us/library/system.web.http.apicontroller(v=vs.108).aspx
Knockout.js
A part of MVC 4, Knockout is a handy MVVM style JavaScript library and is designed for mobile and responsive applications. The library helps with binding of underlying data models to UI elements and templates and reflects changes to the data by updating the UI. There are many more features to Knockout, however, we are going to use it today to show a simple coffee shop list. More on Knockout can be found here: http://knockoutjs.com/
SQL Server 2012 geography
I have blogged about SQL geography in the past with SQL Server 2008 R2 (http://galratner.com/blogs/net/archive/2011/05/14/spatial-search-made-easy-with-google-maps-api-and-sql-server-2008.aspx). SQL Server 2012 adds more features to its geography and geometry spatial types such as circular strings, curve polygons, and compound curves. In addition it has spatial index improvements, new and updated methods and aggregates for all types and much more.
Google Earth API
Almost everybody is familiar with Google Earth. The Earth plug-in has an excellent API here: https://developers.google.com/earth/ and is JavaScript based. In today’s example I am going to make extensive use of its features to draw a polygon, fly to location and add placemarks.
Google Places API
Google Places is the equivalent of Yelp.com. It is a global business database and has an API that supports HTTPS REST queries. The API’s documentation can be found here: https://developers.google.com/maps/documentation/places/. An API key is needed and is provided for free by Google. We are going to use the Places API to query for all the coffee shops in a defined location.


Starting our example
Visual Studio 2011 comes with MVC 4 already baked in. As I am writing this post it is still in beta (5/2012) so we are going to use Visual Studio 2010 along with its version of MVC 4. It includes almost everything we need and works with .NET 4.0. You can download the installer here: http://www.microsoft.com/en-us/download/details.aspx?displaylang=en&id=28942
After installing MVC 4 we are going to create a new Web API project


 
Under the Controllers folder you will notice a new ApiController named ValuesController. This controller will be our base when communicating with the Earth plug-in.
First let’s navigate to the Index.cshtml and lay out our earth on the screen: We will add an earth div to the html and instantiation JavaScript functions.
HTML:

 

<div id="map3d" style="height700pxwidth100%;"></div>

 

JavaScript:

<script src="@Url.Content("~/Scripts/knockout-2.0.0.js")" type="text/javascript"></script>
    <script type="text/javascript" src="https://www.google.com/jsapi"></script>
    <meta name="viewport" content="width=device-width" />
    <script type="text/javascript">
      var ge; 
      var viewModel; // Our knockout.js model
      google.load("earth""1");
      google.load("maps""2");
 
      function init() {
          google.earth.createInstance('map3d', initCB, failureCB);
      }
 
      function initCB(instance) {
          ge = instance;
          ge.getWindow().setVisibility(true);
          ge.getNavigationControl().setVisibility(ge.VISIBILITY_SHOW);
          // Create an initial Look of the earth
          var la = ge.createLookAt('');
          la.set(@Model.Latitude, @Model.Longitude, 0, ge.ALTITUDE_RELATIVE_TO_GROUND, -8.541, 66.213, 2000);
          ge.getView().setAbstractView(la);
          viewModel = {
              CoffeeLocations: ko.observableArray([])
          };
          ko.applyBindings(viewModel);
          // Add the polygon stored in the database
          AddPolygon();
          // Search and show all coffee shops as placemarks
          GetCoffeeLocations();
          // Listen for mousemove on the globe.
          google.earth.addEventListener(ge.getGlobe(), 'mouseup'function (event) {
              event.preventDefault();
              getPolygonBorder().getCoordinates().pushLatLngAlt(event.getLatitude(), event.getLongitude(), 100);
          });
      }
 
      function failureCB(errorCode) {
      }
 
       function flyToLocation() {
        var geocodeLocation = document.getElementById('Location').value;
        var geocoder = new google.maps.ClientGeocoder();
        geocoder.getLatLng(geocodeLocation, function(point) {
        if (point) {
          var lookAt = ge.createLookAt('');
          lookAt.set(point.y, point.x, 10, ge.ALTITUDE_RELATIVE_TO_GROUND, -8.541, 66.213, 2000);
          ge.getView().setAbstractView(lookAt);
        }
      });
    }
 
      google.setOnLoadCallback(init);
  </script>
  <script src="@Url.Content("~/Scripts/EarthFunctions.js")" type="text/javascript"></script>
  <script src="@Url.Content("~/Scripts/PlacesFunctions.js")" type="text/javascript"></script>

 


After we instantiated the earth and directed it to start at a location, we can add our database and data layer.
Our database will be simple. We will use table Location to hold the information


 
Now let’s make the stored procedures we need in order to get and update the data:


CREATE PROCEDURE [dbo].[GetLocationText]
AS
BEGIN
    SET NOCOUNT ON;

    SELECT TOP 1
        LocationText
    FROM
        Location
END

GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[UpdateLocation]
    @LocationText VARCHAR(MAX)
AS
BEGIN

    SET NOCOUNT ON;

    IF NOT EXISTS(SELECT 1 FROM Location)
        BEGIN
            INSERT INTO
                Location (LocationValue)
            VALUES
                (geography::STGeomFromText(@LocationText, 4326))
        END
    ELSE
        BEGIN
            UPDATE
                Location
            SET
                LocationValue = (geography::STGeomFromText(@LocationText, 4326))
        END
END

UpdateLocation will update the location using a text representation of the polygon we want to save.
Finally we need to be able to find the center of our polygon.


CREATE PROCEDURE [dbo].[GetLocationCenter]

AS
BEGIN
    SET NOCOUNT ON;
   
    DECLARE @g geometry;
    DECLARE @p VARCHAR(MAX);
    SET @p = (SELECT TOP 1 LocationText FROM Location)
    SET @g = geometry::STGeomFromText(@p, 0)
    SELECT @g.STCentroid().ToString();
END



Notice we converted our geography type to simple geometry and used STCentroid() to calculate its center.


After we built our database we need to build the data layer. There is nothing smart here, just the same boilerplate code we always use for CRUD operations so I am going to skip this step. If you are interested in seeing the DAL code I have attached the complete example to the bottom of this tutorial and you can download and run it on your own.


Building the controllers


We will be using the ValuesController and adding a PlacesController to show all of the coffee shops.
Our ValuesController will have two methods: one to get our polygon from the database and one to save it. Here is the code for the ValuesController:

 

using System;
using System.Text;
using System.Collections.Generic;
using System.Linq;
using System.Net;
using System.Net.Http;
using System.Web.Http;
using System.Text.RegularExpressions;
 
using WebAPIExample.Models;
 
 
namespace WebAPIExample.Controllers
{
    public class ValuesController : ApiController
    {
 
        // GET /api/values
        public IEnumerable<GlobalPoint> Get()
        {
            List<GlobalPoint> myPolygon = new List<GlobalPoint>();
            try
            {
                string AreaWKT = DAL.Database.GetLocationText();
                string pattern = @"[-+]?[0-9]*\.?[0-9]+";
                MatchCollection matches = Regex.Matches(AreaWKT, pattern);
                for (int i = 0; i < matches.Count; i++)
                {
                    myPolygon.Add(new GlobalPoint()
                    {
                        Longitude = Convert.ToDouble(matches[i].Value),
                        Latitude = Convert.ToDouble(matches[++i].Value)
                    });
                }
            }
            catch (Exception ex)
            {
                throw new HttpResponseException("Error Getting polygon", ex); 
            }
            return myPolygon;
        }
 
        // POST /api/values
        public HttpResponseMessage Post(string ShapePoints)
        {
            // Validate input and concatenate a Polygon
            try
            {
                StringBuilder polygon = new StringBuilder();
                polygon.Append("POLYGON((");
                string[] points = ShapePoints.Split('|');
                for (int i = 0; i < points.Length; i++)
                {
                    double latitude = 0;
                    double longitude = 0;
                    if (double.TryParse(points[i], out latitude) && double.TryParse(points[++i], out longitude))
                        polygon.Append(longitude + " " + latitude + ",");
 
                }
                // Make sure the last latitude and longitude close the polygon
                if (points[0] != points[points.Length - 2] && points[1] != points[points.Length - 1])
                    polygon.Append(double.Parse(points[1]) + " " + double.Parse(points[0]) + ",");
                polygon.Replace(",""))", polygon.Length - 1, 1);
                // Save to database
                DAL.Database.UpdateLocation(polygon.ToString());
            }
            catch (Exception ex)
            {
                throw new HttpResponseException("Error Saving Map", ex);
            }
            return new HttpResponseMessage(HttpStatusCode.OK);
        }
    }
}

 


Notice that in order to save the polygon we need a collection of points containing the latitude and longitude of its corners. We are going to use them to concatenate a Well-known text (WKT) formatted string used to describe vector geometry objects. We can then convert our WKT polygon to a geography type by using STGeomFromText in our stored procedure.


After we built the controller we can now use it to draw and save our polygon. The code to do that is written entirely in JavaScript and looks like:

 

function AddPolygon() {
    // Create the placemark.
    var polygonPlacemark = ge.createPlacemark('');
 
    // Create the polygon.
    var polygon = ge.createPolygon('');
    polygon.setAltitudeMode(ge.ALTITUDE_RELATIVE_TO_GROUND);
    polygonPlacemark.setGeometry(polygon);
 
    // Add points for the outer shape.
    var border = ge.createLinearRing('');
    border.setAltitudeMode(ge.ALTITUDE_RELATIVE_TO_GROUND);
    AddPolygonCoordinates(border);
    polygon.setOuterBoundary(border);
 
    //Create a style and set width and color of line
    polygonPlacemark.setStyleSelector(ge.createStyle(''));
    var lineStyle = polygonPlacemark.getStyleSelector().getLineStyle();
    polygonPlacemark.getStyleSelector().getPolyStyle().setFill(0);
    lineStyle.setWidth(5);
    lineStyle.getColor().set('9900ffff');
    
 
    // Add the placemark to Earth.
    ge.getFeatures().appendChild(polygonPlacemark);
}
 
function AddPolygonCoordinates(border) {
    $.getJSON("/api/values"function (data) {
        $.each(data, function (i, ltem) {
            border.getCoordinates().pushLatLngAlt(ltem.lat, ltem.lng, 100);
        });
    });
}
 
function clearMap() {
    viewModel.CoffeeLocations([]);
    var features = ge.getFeatures();
    features.getFirstChild().getGeometry().getOuterBoundary().getCoordinates().clear();
}
 
function saveMap() {
    var features = ge.getFeatures();
    var points = features.getFirstChild().getGeometry().getOuterBoundary().getCoordinates();
    var polygonPoints = '';
    for (i = 0; i < points.getLength(); i++)
        polygonPoints += points.get(i).getLatitude() + "|" + points.get(i).getLongitude() + "|";
 
    $.post('/api/values', {ShapePoints : polygonPoints}, function (data) {
        $('#message').html("Map Saved");
    });
}
 
function getPolygonBorder() {
    var features = ge.getFeatures();
    return features.getFirstChild().getGeometry().getOuterBoundary();
}

 


$.getJSON("/api/values") is a jQuery function that simply makes a GET request to the controller and gets our list of points as JSON. Adding them to the map is simple: border.getCoordinates().pushLatLngAlt(ltem.lat, ltem.lng, 100);


We have also added a function to clear the map in case we need to create a new polygon.
Now that we have the code to get, update and show our polygon on Google earth lets search for close coffee shops!
We will use a PlacesController and make a simple call to Google Places with the center of our area as a starting point:

 

using System;
using System.Collections.Generic;
using System.Linq;
using System.Net.Http;
using System.Web.Http;
using System.Json;
using System.Net;
 
using WebAPIExample.Models;
using System.Text.RegularExpressions;
 
namespace WebAPIExample.Controllers
{
    public class PlacesController : ApiController
    {
        string APIKey = "your API Key";
        // GET /api/places
 
        public IEnumerable<Place> Get()
        {
            List<Place> places = new List<Place>();
            GlobalPoint centerPoint = Utils.GetCenter();
            using (WebClient client = new WebClient())
            {
                string requestURL = string.Format("https://maps.googleapis.com/maps/api/place/search/json?key={0}&location={1}&rankby=distance&types=cafe|bakery&sensor=false", APIKey, centerPoint.Latitude + "," + centerPoint.Longitude);
                string results = client.DownloadString(requestURL);
                var placesResponse = System.Web.Helpers.Json.Decode(results);
                foreach (var result in placesResponse.results)
                {
                    places.Add(new Place()
                    {
                        Name = result.name,
                        Rating = Convert.ToString(result.rating),
                        Latitude = Convert.ToDouble(result.geometry.location.lat),
                        Longitude = Convert.ToDouble(result.geometry.location.lng)
                    });
                }
                return places;
            }
        }
    }
}

 


Now that we have our controller already returning a list of coffee shops we need to show them on the map and bind them to a list along with their ratings:

function GetCoffeeLocations() {
    $.getJSON("/api/places"function (data) {
        viewModel.CoffeeLocations(data); // Bind the model
 
        $.each(data, function (i, ltem) {
            // Create the placemark.
            var placemark = ge.createPlacemark('');
            placemark.setName(ltem.name);
            // Set the placemark's location.  
            var point = ge.createPoint('');
            point.setLatitude(ltem.lat);
            point.setLongitude(ltem.lng);
            placemark.setGeometry(point);
 
            // Add the placemark to Earth.
            ge.getFeatures().appendChild(placemark);
        });
    });
}



Let’s add a knockout.js HTML template at the bottom of the map in order to show the list:

 

 <div data-bind="template: { name: 'point-template', foreach: viewModel.CoffeeLocations}"></div>
            <script type="text/html" id="point-template">
                <span data-bind="text: name"></span>
                (Rated: <span data-bind="text: rating"></span>)<br>
            </script>

 


Putting is together


Great! We are almost done. Just some minor enhancements to the UI to allow some “fly to” functionality and we are ready to aim the map to our favorite city!

 

<input id="FlyButton" type="button" value="Fly Here" onclick="flyToLocation();"/>

 

function flyToLocation() {
        var geocodeLocation = document.getElementById('Location').value;
        var geocoder = new google.maps.ClientGeocoder();
        geocoder.getLatLng(geocodeLocation, function(point) {
        if (point) {
          var lookAt = ge.createLookAt('');
          lookAt.set(point.y, point.x, 10, ge.ALTITUDE_RELATIVE_TO_GROUND, -8.541, 66.213, 2000);
          ge.getView().setAbstractView(lookAt);
        }
      });
    }

 


That’s it! If everything went to plan we should be looking at a complete map and list that looks like this:


 

It will show our polygon, coffee shops as place marks and a bottom list which includes names and ratings.


Conclusion


In this example we have put together a real life application that can be used as a part of any GIS aware product. We have seen how simple the Web API can be and how powerful the features of SQL Server geography are. The edition of some new slick client side frameworks such as knockout and the Google earth plug-in API helped us create an appealing and responsive user interface with minimal effort.
Don’t forget to download the attached code and experiment with it yourself.


Happy Coding!

Shout it


Posted 22 May 2012 8:27 PM by Gal Ratner
Attachment: WebAPIExample.zip

Powered by Community Server (Non-Commercial Edition), by Telligent Systems