SQL Azure and Bing Maps – Spatial Queries

img During the past few weeks we had the opportunity of working together with James Conard, Vittorio Bertocci and Doug Purdy on building Fabrikam Shipping; which was shown by the latter during TechEd North America keynote.

Fabrikam Shipping is a Real-World multi-tenant application running in windows azure which leverages all the power of .Net 4 and the new SQL Azure capabilities announced in the event.

Leveraging Bing Maps and SQL Azure Spatial Support.
One of the cool features shown during the demo, was the integration of SQL Azure’s spatial support with Bing maps. On this post, I will walk you through a possible implementation of this functionality.

  1. Retrieving the Coordinates of the location entered by the user.
  2. Getting the radius of the area that is being displayed in the map.
  3. Performing the spatial query against SQL Azure.

1- Retrieving the Coordinates of the location entered by the user.
For converting the location into coordinates, you can take advantage of the “find” feature provided by the Bing Maps SDK.

mapInstance.Find(null, location, null, null, null, null, null, null, null, null, callback)
  • mapInstance: is an instance of VEMap.
  • location: is the search value entered by the user
  • callback: is the function to invoke when the find operation completes.

In the callback function you will retrieve the coordinates of the map (and perform the tasks described in the following steps).

function callback() {
    coordinates = mapInstance.GetCenter();
}

2- Getting the radius of the area that is being displayed in the map.
The radius constrain can be obtained by transforming the MapView being displayed into distance. For doing this, you can use the Haversine formula.

function GetCurrentRadius(map) {
    var startPoint = map.GetMapView().BottomRightLatLong;
    var endPoint = map.GetMapView().TopLeftLatLong;
    // calculations
    var dLat = (endPoint.Latitude - startPoint.Latitude).toRad();
    var dLon = (endPoint.Longitude - startPoint.Longitude).toRad();
    var a = Math.sin(dLat / 2) * Math.sin(dLat / 2) + Math.cos(startPoint.Latitude.toRad()) * Math.cos(endPoint.Latitude.toRad()) * Math.sin(dLon / 2) * Math.sin(dLon / 2);
    var c = 2 * Math.atan2(Math.sqrt(a), Math.sqrt(1 - a));
    var diameter = c * 6371000 ;
    return diameter / 2;
}
Number.prototype.toRad = function () {
    return this * Math.PI / 180;
}

For more information on calculating distance you can visit this site.

3- Performing the spatial query against SQL Azure.
In Fabrikam Shipping, we exposed did this by exposing an OData Service Operation which invokes the EF Function wrapping the SQL Azure Stored Procedure that performs the spatial search (for in-depth information on spatial queries, visit pc’s blog)

Server-Side Code (C#)

[WebGet]
public IEnumerable<Shipment> SearchShipmentsInArea(double lat, double lon, double radius)
{
    return this.CurrentDataSource.GetShipmentsWithinArea(lat, lon, radius);
}

Client-Side Code (JS)

$.ajax({
  dataType: "json",
  url: "https://www.fabrikamshipping.com/DataService.svc/SearchShipmentsInArea/"
    + "?lat=" + mapInstance.GetCenter().Latitude
    + "&lon=" + mapInstance.GetCenter().Longitude
    + "&radius=" + Math.floor(GetCurrentRadius(mapInstance)),
  success: ShowSearchResultsInMap,
  error: SearchShipments_Error
});

ShowSearchResultsInMap simply adds the pushpins into the map.

Additional Resources:



Leave a Reply