Recently we published a blog post titled "How to Create a Spatial Web Service That Connects a Database to Bing Maps Using EF5." This post showed how to create a web service that connected Bing Maps to a database using the spatial functionality in Entity Framework 5 (EF5). In that example we only implemented a basic nearby search query. In this post, we are going to expand upon that application and add in more advance spatial search queries such as find in polygon, find in bounding box and find along a route. You can download the full source code and sample database here.
Some of these spatial queries are going to require more advanced spatial functionalities than what is available through EF5. The Entity Framework exposes only a subset of the spatial functionalities available in SQL Server. Fortunately all the spatial functionality in SQL Server is available as a .NET library that we can use in our application. If you don’t already have SQL 2008 or above installed locally, get an express version of SQL 2012. Once installed you should be able to add a reference to the Microsoft.SqlServer.Types.dll which is located in the Assemblies -> Extensions.
Add Queries To The Service
The first thing we will need to do is add these queries to our service interface. To do this open the ISpatialService.cs file and update to the following:
using BM_EF5_WebService.Common; using System.ServiceModel; using System.ServiceModel.Web;
namespace BM_EF5_WebService { [ServiceContract(Namespace = "SpatialService")] [ServiceKnownType(typeof(Response))] public interface ISpatialService { /// <summary> /// Finds all locations that are within a specified distance of a central coordinate for a specified layer. /// </summary> /// <param name="latitude">Center latitude value.</param> /// <param name="longitude">Center longitude value.</param> /// <param name="radius">Search radius in Meters</param> /// <param name="layerName">Name of the layer (SQL table) to search against.</param> /// <returns>A list of results</returns> [OperationContract] [WebGet(ResponseFormat = WebMessageFormat.Json, BodyStyle = WebMessageBodyStyle.Bare)] Response FindNearBy(double latitude, double longitude, double radius, string layerName);
/// <summary> /// Finds all locations that are within a polygon for a specified layer. /// </summary> /// <param name="polygonWKT">Well Known Text for a Polygon.</param> /// <param name="layerName">Name of the layer (SQL table) to search against.</param> /// <returns>A list of results</returns> [OperationContract] [WebGet(ResponseFormat = WebMessageFormat.Json, BodyStyle = WebMessageBodyStyle.Bare)] Response FindInPolygon(string polygonWKT, string layerName);
/// <summary> /// Find all locations that are within a bounding box for a specified layer. /// </summary> /// <param name="north">North Latitude</param> /// <param name="east">East Longitude</param> /// <param name="south">South Latitude</param> /// <param name="west">West Longitude</param> /// <param name="layerName">Name of the layer (SQL table) to search against.</param> /// <returns>A list of results</returns> [OperationContract] [WebGet(ResponseFormat = WebMessageFormat.Json, BodyStyle = WebMessageBodyStyle.Bare)] Response FindByBoundingBox(double north, double east, double south, double west, string layerName);
/// <summary> /// Finds all locations that are within a specified distance (radius) of a route path for a specified layer. /// </summary> /// <param name="waypoints">A pipe (|) delimited list of waypoints a route goes through.</param> /// <param name="radius">Search radius aourd the route in meters.</param> /// <param name="bingMapsKey">Bing Maps key for requesting route.</param> /// <param name="layerName">Name of the layer (SQL table) to search against.</param> /// <returns>A list of results</returns> [OperationContract] [WebGet(ResponseFormat = WebMessageFormat.Json, BodyStyle = WebMessageBodyStyle.Bare)] Response FindNearRoute(string waypoints, double radius, string bingMapsKey, string layerName); }
}
Next we will need to implement these new methods in our service. Open up the SpatialService.cs file and right click on the ISpatialService value which the service inherits from and select Implement Interface -> Implement Interface. This will add the method blocks for these new search queries and should look like this:
public Response FindInPolygon(string polygonWKT, string layerName) { throw new NotImplementedException(); }
public Response FindByBoundingBox(double north, double east, double south, double west, string layerName) { throw new NotImplementedException(); }
public Response FindNearRoute(string waypoints, double radius, string bingMapsKey, bool returnBoundary, string layerName) { throw new NotImplementedException();
}
At this point you should be able to build the application without any issues. If you try and call any of these new queries, an exception will be thrown. We will replace the exception code with the query functionality as we go along.
You might be wondering why we are passing a Bing Maps key into the FindNearRoute method. The reason is that we will need to use the Bing Maps REST services to generate a route line for us. We could just store this key in the code for our service. However, if you are using one of the interactive Bing Maps controls you can generate a special Bing Maps key from it called a session key. This session key will make the route request a part of the map session and mark it as a non-billable transaction in your account.
Find By Polygon Query
For this query we will take in a Well Known Text value that represents a polygon and then use this to perform an intersection test against the appropriate data table. The first thing we will need to do in this method is turn the Well Known Text into a DbGeography object like so:
DbGeography polygon = DbGeography.FromText(polygonWKT, 4326);
The next step is to create the LINQ query that will find all locations in our database that intersect with the polygon. This type of LINQ Query looks like this:
from c in e.Cities where c.Location.Intersects(polygon) select new Common.City() { Name = c.Name, CountryISO = c.Country_ISO, Population = c.Population.HasValue ? (int)c.Population.Value : 0, WKT = c.Location.AsText()
}
Now this query will only work with the Cities table in our database. We would have to create a slightly different query for the Countries table. We can specify which query our application can use by checking the value of the layer name property. Putting this all together our FindInPolygon method looks like this:
public Response FindInPolygon(string polygonWKT, string layerName) { Response r = new Response();
try { DbGeography polygon = DbGeography.FromText(polygonWKT, 4326);
if (polygon.Area.HasValue && polygon.Area.Value > (580000000000000 / 2)){ SqlGeography sqlPolygon = SqlGeography.STGeomFromWKB(new System.Data.SqlTypes.SqlBytes(polygon.AsBinary()), 4326); sqlPolygon = sqlPolygon.ReorientObject(); polygon = DbGeography.FromBinary(sqlPolygon.STAsBinary().Value); }
using (SpatialSampleEntities e = new SpatialSampleEntities()) { switch (layerName.ToLowerInvariant()) { case "cities": r.Results = (from c in e.Cities where c.Location.Intersects(polygon) select new Common.City() { Name = c.Name, CountryISO = c.Country_ISO, Population = c.Population.HasValue ? (int)c.Population.Value : 0, WKT = c.Location.AsText() }).ToList<BaseEntity>(); break; case "countries": r.Results = (from c in e.Countries where c.Boundary.Intersects(polygon) select new Common.Country() { ISO = c.ISO, Population = c.Population.HasValue ? (int)c.Population.Value : 0, Name = c.Name, WKT = c.Boundary.AsText() }).ToList<BaseEntity>(); break; default: r.Error = "Invalid Layer Name."; break; }
if (r.Results != null) { r.Results.ForEach(c => c.WKT = shortenCoordinate.Replace(c.WKT, "$1")); } } } catch (Exception ex) { r.Error = ex.Message; }
return r;
}
We can now test this new search query. To test the service you need to put together a query URL and then simply open it in a browser. The following query looks for cities that are within triangle over part of the USA.
http://localhost:65521/SpatialService.svc/FindInPolygon?polygonWKT=POLYGON((-100 40,-102.5 45,-105 40,-100 40))&layerName=Cities
Running this query we end up with the following response:
{ "Results": [ { "__type":"City:#BM_EF5_WebService.Common", "Name":"Greeley", "WKT":"POINT (-104.70913 40.42331)", "CountryISO":"US", "Population":92889 },{ "__type":"City:#BM_EF5_WebService.Common", "Name":"Scottsbluff", "WKT":"POINT (-103.66717 41.86663)", "CountryISO":"US", "Population":15039 },{ "__type":"City:#BM_EF5_WebService.Common", "Name":"Evans", "WKT":"POINT (-104.69219 40.37637)", "CountryISO":"US", "Population":18537 },{ "__type":"City:#BM_EF5_WebService.Common", "Name":"North Platte", "WKT":"POINT (-100.76542 41.12389)", "CountryISO":"US", "Population":24733 } ]
}
Now we called this FindInPolygon, but in reality we can pass in any shape into this method and do an intersection search against any shape. For example, you could pass in a line and find all the countries that the line goes through. This could be useful if you wanted to know all the countries a route went through.
It is also worth noting that really complex polygons will result in much longer Well Known Text strings being generated. This can be an issue as browsers have a limit on how long URL’s can be. If you find that this becomes a problem for you, then modifying this part of the service to use a POST request rather than a GET request.
Tip: Use the Shape Toolbox and the Well Known Text Reader/Writer modules for Bing Maps together to allow your users to draw out a search area on the map and generate a polygon Well Known Text to pass to the service.
Caution: One thing we need to be aware of is that when it comes to spatial polygons the order of the coordinates are very important. Changing the order can cause them appear to rotate a different direction which would result in the inverse area being searched. If you were using SQL 2008, you would likely get an error, as this would generate a huge polygon that spans more than a hemisphere in size. However, in SQL 2012 and SQL Azure there is better support for things like this and it will actually work and return all the locations but those we expected. For example, changing the Well Known Text of our polygon to this: POLYGON((-100 40,-105 40,-102.5 45,-100 40)) results in a shape that looks like this:
If you find this becomes an issue in your application, you can get around this by checking what the area is of the polygon and if it is too large then reverse the orientation of the polygon. To do this we require using the SQL Spatial library we added earlier .NET so that you can make use of the ReorientObject method that reverses the order of the points. Insert this after creating the initial DbGeography polygon the following code will change the orientation of any polygon that is larger than half the surface area of the globe:
if (polygon.Area.HasValue && polygon.Area.Value > (580000000000000 / 2)){ SqlGeography sqlPolygon = SqlGeography.STGeomFromWKB(new System.Data.SqlTypes.SqlBytes(polygon.AsBinary()), 4326); sqlPolygon = sqlPolygon.ReorientObject(); polygon = DbGeography.FromBinary(sqlPolygon.STAsBinary().Value);
}
Find By Bounding Box
Now you are probably wondering why I choose to start off with the find by polygon query rather than this one. Well, that’s easy. A bounding box is nothing more than a square polygon. All we need to do is create the Well Known Text for the bounding box using the north, south, east and west coordinates and then pass this to the FindInPolygon method we just created. Putting this together, we end up with the following code:
public Response FindByBoundingBox(double north, double east, double south, double west, string layerName) { //Create poylgon of bounding box string bboxWKT = string.Format("POLYGON(({0} {1},{2} {1},{2} {3},{0} {3},{0} {1}))", east, north, west, south); return FindInPolygon(bboxWKT, layerName);
}
We can now test this new search query. To test the service you need to put together a query URL and then simply open it in a browser. The following query looks for cities that are within small bounding box over part of the UK.
http://localhost:65521/SpatialService.svc/FindByBoundingBox?north=51.2&east=0&south=51&west=0.2&layerName=Cities
Running this query, we end up with the following response:
{ "Results": [ { "__type":"City:#BM_EF5_WebService.Common", "Name":"Crowborough", "WKT":"POINT (0.16171 51.06044)", "CountryISO":"GB", "Population":20733 } ]
}
Note that if you implemented the functionality that reorients the polygon if its area is too big in the FindInPolygon method then this will not return any results if you pass in a full globe bounding box. A possible solution is to add a Boolean value to that method indicating if that logic should be used or not.
Tip: Dynamically update the map with the data that is in view by attaching to the viewchangeend event of the map, and use the bounding box of the map to call this service. You may find that using a throttled event handler is a good choice as that will help reduce the number of calls to the service if the user pauses momentarily when panning and zooming.
Find Near Route
The find along a route query is a bit more complex than what we have covered so far. For this query we will have to generate a route line between the specified waypoints. We can do this using the Bing Maps REST Routing Service. To make use of the Bing Maps REST services we will follow the documentation on Using the REST services with .NET. To implement this add a new file to the project called BingMapsRESTServiceModels.cs. Open this file up and delete its contents, then copy and paste the JSON Data Contracts from the Bing Maps REST services which are available here. In the documentation asynchronous calls are being made to the Bing Maps Service. Since we are going to be making these calls from inside our service, we need the response inline, so we will make a synchronous call instead. To make things nice we will create a private method in the SpatialService.cs file call GetResponse that will make this synchronous call for us and will deserialize the response for us. This method looks like this:
private BingMapsRESTService.Common.JSON.Response GetResponse(Uri uri) { WebClient client = new WebClient(); using (var stream = client.OpenRead(uri)) { DataContractJsonSerializer ser = new DataContractJsonSerializer(typeof(BingMapsRESTService.Common.JSON.Response)); return ser.ReadObject(stream) as BingMapsRESTService.Common.JSON.Response; }
}
Going back to the FindNearRoute method, one of the first things we will need to do is split the waypoint parameter on the pipe delimiter character and then use the waypoints to create a route request URL for the Bing Maps REST services. Once a URL is created we can then request the route from Bing Maps and use the returned route path information to create a SqlGeography object. The route line has a lot of points in it, and we will want to reduce the resolution to speed up the next step of generating a buffer around the line that is equal to our search radius. I’ve found a tolerance of 100 works well for the Reduce and BufferWithTolerance methods when working with route lines. Once we have generated our buffer, we can convert the SqlGeography object into a DbGeography object and use it to do an intersection test against our database. Putting this all together, we end up with the following code for the FindNearRoute method.
public Response FindNearRoute(string waypoints, double radius, string bingMapsKey, string layerName) { Response response = new Response();
try { //Create the request URL for the route service string[] wp = waypoints.Split(new char[]{'|'});
if (wp.Length < 2) { throw new Exception("Invalid number of waypoints."); }
StringBuilder request = new StringBuilder("http://dev.virtualearth.net/REST/V1/Routes/Driving?rpo=Points");
for (int i = 0; i < wp.Length; i++) { request.AppendFormat("&wp.{0}={1}", i, wp[i]); }
request.AppendFormat("&key={0}", bingMapsKey);
Uri routeRequest = new Uri(request.ToString());
BingMapsRESTService.Common.JSON.Response r = GetResponse(routeRequest);
if (r == null || r.ResourceSets == null || r.ResourceSets.Length == 0 || r.ResourceSets[0].Resources == null || r.ResourceSets[0].Resources.Length == 0) { throw new Exception("Unable to calculate route between waypoints."); } BingMapsRESTService.Common.JSON.Route route = (BingMapsRESTService.Common.JSON.Route)r.ResourceSets[0].Resources[0];
if(route.RoutePath == null || route.RoutePath.Line == null || route.RoutePath.Line.Coordinates == null || route.RoutePath.Line.Coordinates.Length == 0) { throw new Exception("Unable to calculate route between waypoints."); }
var coords = route.RoutePath.Line.Coordinates;
//Turn the coordinate array into an SQLGeography object SqlGeographyBuilder builder = new SqlGeographyBuilder(); builder.SetSrid(4326);
builder.BeginGeography(OpenGisGeographyType.LineString); builder.BeginFigure(coords[0][0], coords[0][1]); for (var i = 1; i < coords.Length; i++) { if (coords[i].Length >= 2) { builder.AddLine(coords[i][0], coords[i][1]); } }
builder.EndFigure(); builder.EndGeography();
//Reduce the resolution of the line and give is a buffer equal to our radius var buffer = builder.ConstructedGeography.Reduce(100); buffer = buffer.BufferWithTolerance(radius, 100, false);
//Turn the SqlGeography object into a DbGeography object DbGeography routeBuffer = DbGeography.FromBinary(buffer.STAsBinary().Value, 4326);
using (SpatialSampleEntities e = new SpatialSampleEntities()) { switch (layerName.ToLowerInvariant()) { case "cities": response.Results = (from c in e.Cities where c.Location.Intersects(routeBuffer) select new Common.City() { Name = c.Name, CountryISO = c.Country_ISO, Population = c.Population.HasValue ? (int)c.Population.Value : 0, WKT = c.Location.AsText() }).ToList<BaseEntity>(); break; case "countries": response.Results = (from c in e.Countries where c.Boundary.Intersects(routeBuffer) select new Common.Country() { ISO = c.ISO, Population = c.Population.HasValue ? (int)c.Population.Value : 0, Name = c.Name, WKT = c.Boundary.AsText() }).ToList<BaseEntity>(); break; default: response.Error = "Invalid Layer Name."; break; }
if (response.Results != null) { response.Results.ForEach(c => c.WKT = shortenCoordinate.Replace(c.WKT, "$1")); } } } catch(Exception ex){ response.Error = ex.Message; }
return response;
}
We can now test this new search query. To test the service you need to put together a query URL and then simply open it in a browser. You can pass in string addresses or coordinates as waypoints. If passing in coordinates simple use the format "latitude, longitude". The following query looks for cities that are within 10km of a route between London and Paris.
http://localhost:65521/SpatialService.svc/FindNearRoute?waypoints=London|Paris&radius=10000&layerName=Cities&bingMapsKey=Your_Bing_Maps_Key
Running this query returns a large number of results.
Tip: If you are curious about what the route buffer looks like, add the following line of code after the buffer is generated and put a break point in your code after it.
string wkt = new string(buffer.STAsText().Value);
When you run your code you can then grab the Well Known Text for the route buffer. You can then use the test application that comes with the Well Known Text Reader/Writer module to render this buffer on Bing Maps. By doing this for the test query we did earlier, we end up with this:
Adding These Queries To the Map
At this point we have proven that our advance spatial queries work well, but looking at a bunch of JSON results isn’t all that interesting. Open up the index.html file and add the following JavaScript functions.
function FindByPolygon(layer) { var request = "http://localhost:65521/SpatialService.svc/FindInPolygon?" + "polygonWKT=POLYGON((-100 40,-102.5 45,-105 40,-100 40))&layerName=" + layer + "&callback=?";
CallRESTService(request, DisplayData); }
function FindByBoundingBox(layer) { var bbox = map.getBounds();
var request = "http://localhost:65521/SpatialService.svc/FindByBoundingBox?" + "north=" + bbox.getNorth() + "&east=" + bbox.getWest() + "&south=" + bbox.getSouth() + "&west=" + bbox.getEast() + "&layerName=" + layer + "&callback=?";
CallRESTService(request, DisplayData); }
function FindNearRoute(layer) { //Generate Session key map.getCredentials(function (c) { var request = "http://localhost:65521/SpatialService.svc/FindNearRoute?" + "waypoints=London|Paris&radius=10000&bingMapsKey=" + c + "&layerName=" + layer + "&callback=?";
CallRESTService(request, DisplayData); });
}
Next add the following HTML into the body of the page.
<br /> <input type="button" value="Get Cities in Polygon" onclick="FindByPolygon('Cities')" /> <input type="button" value="Get Nearby in Polygon" onclick="FindByPolygon('Countries')" /><br /> <input type="button" value="Get Cities in View" onclick="FindByBoundingBox('Cities')" /> <input type="button" value="Get Countries in View" onclick="FindByBoundingBox('Countries')" /><br /> <input type="button" value="Find Cities Near Route" onclick="FindNearRoute('Cities')" /> <input type="button" value="Find Nearby Near Route" onclick="FindNearRoute('Countries')" />
This JavaScript consists of three functions; FindByPolygon, FindByBoundingbox, and FindNearRoute. The FindByPolygon function searches for locations inside a triangle over the US. The FindByBoundingBox function finds all locations that are within the current map view. Be careful, if you do this when zoomed all the way out you will be loaded a lot of data onto the map. Note this method will throw an error if you have implemented the logic to reorient the polygon when used with the full globe. The FindNearRoute function finds all locations that are within 10km of the route from London to Paris.
Before testing this code out ensure that the proper port number is specified in the REST requests inside the search functions. If you now run the application and press Find Cities Near Route button it will find all cities that are within 10km of the route from London to Paris. Here is a screenshot of the cities returned when zoomed into Paris:
Here is a screenshot of countries that are in the current map view using a bounding box search while zoomed in over South Africa:
Recap
We have now seen how we can create some advance spatial queries using Entity Framework 5. This time around we also made use of the SQL Spatial library so that we could do some spatial calculations right inside the service. Taking things a bit further you could use this service as a way to offload complex spatial calculates to the server side.
– Ricky Brundritt, EMEA Bing Maps Technology Solution Professional
