Archive for May, 2012

  |  
May 27, 2012

Routing Sans Frontières

I noticed a question on StackOverflow recently, concerning the ability of various web-mapping services (Google Maps, Bing Map et al.) to plan routes across national borders. I have to say that it’s a question I’ve never really considered before – all the trans-national routes I’ve ever tried to calculate have worked as expected, just the same as those contained within a single country. However, having given it some thought, it certainly is an interesting issue.

Even across contiguous land masses such as mainland Europe, most mapping services manage their datasets on a country-by-country basis. This makes sense, because the providers of that data are often governments or other national agencies, and the quality, completeness, and timeliness of data available will therefore vary between countries.

Bing and Google both offer similar country coverage for their mapping services:

  • Google publishes this spreadsheet detailing the features it offers in different countries.
  • Microsoft does the same for Bing Maps, on this MSDN page.

So far, so good, but what about situations where you plan a route that passes through more than one country? Even though the dataset may internally be partitioned into separate countries, you’d still expect those national datasets to be “connected” where appropriate, at the points where a road crosses the boundary between two countries. However, it seems that, as a by-product of managing datasets at a national level, some mapping providers don’t consider certain routes to be valid because they don’t regard roads as contiguous when they cross a national boundary.

For example, Google will plot a route between Ipiales and Pasto, in Colombia, or between Tulcan and Quito, in Ecuador, say, but it cannot calculate a route between Tulcan, Ecuador and Ipiales, Colombia… despite the fact that they lie only a few miles from each other, connected by the Pan-American Highway:

image

Bing Maps, however, does calculate this route:

image

There are other examples; Google Maps, for example, doesn’t appear to calculate any route that crosses into or out of China:

image

Which, again, Bing Maps does without complaint via the international border crossing between Zamyn-Üüd and Erenhot:

image

Of course, there are some other routes where it perhaps makes sense to be slightly cautious of crossing national borders. Bing Maps suggests that travelling from Cairo to Damascus is a “simple” 10 hour drive into Israel, Jordan, and then into Syria…:

image

Whether it makes sense to even attempt to complete this journey, Google Maps opts to send you the long way round via the Turkey/Syria border instead:

image

In practice, I’m certain I probably wouldn’t attempt to drive either of these routes…

Posted in Bing Maps | 1 Comment »

Tags: , ,
May 7, 2012

“Advanced Shapes” (i.e. Donut Polygons) on Bing Maps

I notice that the MSDN Reference for Bing Maps was recently updated to include details of a new “Advanced Shapes” API. I don’t know exactly when this came out and I’ve not seen any releases from the product team about it, but it seems basically to be an official release of the code written by Ricky Brundritt a few years back.

After loading the Advanced Shapes module, the functionality of the default Microsoft.Maps.Polygon object is extended to allow for MultiPolygons and Polygons containing interior rings. (i.e. holes).

It’s nice that this feature has finally been officially incorporated into the Bing Maps code – it’s surprising how often you need to define these sorts of shapes. For example, the following code listing defines a polygon representing the shape of South Africa which, thanks to the Advanced Shapes API, can now correctly exclude the fully-enclosed Lesotho by defining an interior ring around it:

<!DOCTYPE HTML PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
  <title></title>
  <meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
  <script type="text/javascript" src="http://ecn.dev.virtualearth.net/mapcontrol/mapcontrol.ashx?v=7.0"></script>
  <script type="text/javascript">

    var MM = Microsoft.Maps;
    var map = null;

    function GetMap() {
      // Create a new map
      map = new Microsoft.Maps.Map(
        document.getElementById("mapDiv"),
        {
          credentials: "At3aYXF4p6L___G3tY3r0wnFr!ggingK3y___oKURkDSvOeRs",
          center: new Microsoft.Maps.Location(0, 0),
          zoom: 2
        }
      );

      // Load the Advanced Shapes API
      Microsoft.Maps.loadModule('Microsoft.Maps.AdvancedShapes', { callback: shapesModuleLoaded });

    }

    // Callback function
    function shapesModuleLoaded() {

      // Create polygon (South Africa) containing interior ring (Lesotho)
      var SouthAfrica = new Microsoft.Maps.Polygon([

        /* Exterior Ring */
        [new MM.Location(-28.6906,16.5234), new MM.Location(-29.4587,17.0947), new MM.Location(-30.2211,17.2705), new MM.Location(-31.0529,17.666), new MM.Location(-31.6908,18.1494), new MM.Location(-32.1384,18.3691), new MM.Location(-32.6949,18.3691), new MM.Location(-32.9165,18.2373), new MM.Location(-32.8427,18.0176), new MM.Location(-33.3213,18.1934), new MM.Location(-33.8339,18.5449), new MM.Location(-34.2708,18.457), new MM.Location(-34.0891,18.8086), new MM.Location(-34.597,19.3359), new MM.Location(-34.8859,19.9951), new MM.Location(-34.4522,20.7861), new MM.Location(-34.4884,21.9287), new MM.Location(-34.0891,22.5439), new MM.Location(-34.1618,23.291), new MM.Location(-33.9798,23.7744), new MM.Location(-34.2708,24.873), new MM.Location(-33.9798,25.1367), new MM.Location(-34.1254,25.6201), new MM.Location(-33.9069,25.8838), new MM.Location(-33.8339,26.7627), new MM.Location(-33.2479,27.8174), new MM.Location(-32.3243,28.96), new MM.Location(-31.7282,29.4873), new MM.Location(-30.6379,30.7178), new MM.Location(-29.6881,31.377), new MM.Location(-29.3056,31.5088), new MM.Location(-28.7291,32.3877), new MM.Location(-28.1107,32.6953), new MM.Location(-27.2937,32.915), new MM.Location(-26.9417,33.0029), new MM.Location(-26.9025,32.2119), new MM.Location(-27.3327,32.0361), new MM.Location(-27.3327,31.2891), new MM.Location(-27.1374,31.0693), new MM.Location(-26.7848,30.8936), new MM.Location(-26.4312,30.9814), new MM.Location(-26.037,31.2012), new MM.Location(-25.7999,31.5088), new MM.Location(-25.879,31.6846), new MM.Location(-25.958,32.0361), new MM.Location(-25.0856,32.168), new MM.Location(-24.2069,32.0361), new MM.Location(-23.4834,31.6846), new MM.Location(-22.3501,31.333), new MM.Location(-22.3907,30.498), new MM.Location(-22.1874,29.9707), new MM.Location(-22.1874,29.0918), new MM.Location(-22.5126,29.0479), new MM.Location(-22.6748,28.3447), new MM.Location(-23.0393,27.9932), new MM.Location(-23.4431,27.6416), new MM.Location(-23.6445,27.1582), new MM.Location(-24.287,26.9385), new MM.Location(-24.687,26.1035), new MM.Location(-25.4433,25.7959), new MM.Location(-25.7603,25.4883), new MM.Location(-25.879,24.8291), new MM.Location(-25.7999,24.3018), new MM.Location(-25.6415,23.8623), new MM.Location(-25.3639,23.4229), new MM.Location(-25.4433,22.9834), new MM.Location(-26.037,22.6758), new MM.Location(-26.3919,22.4121), new MM.Location(-26.6671,22.1924), new MM.Location(-26.9025,21.665), new MM.Location(-26.9417,21.2256), new MM.Location(-26.8633,20.9619), new MM.Location(-26.9808,20.8301), new MM.Location(-26.5885,20.6982), new MM.Location(-26.0765,21.0059), new MM.Location(-25.5226,20.7422), new MM.Location(-25.0856,20.4785), new MM.Location(-24.8864,20.127), new MM.Location(-28.4977,20.1709), new MM.Location(-28.5749,19.7754), new MM.Location(-28.7677,19.3799), new MM.Location(-28.9985,19.3799), new MM.Location(-28.9601,18.9844), new MM.Location(-28.9216,18.5449), new MM.Location(-28.9601,17.9736), new MM.Location(-28.8062,17.7539), new MM.Location(-28.7677,17.4902), new MM.Location(-28.5363,17.3584), new MM.Location(-28.3817,17.4023), new MM.Location(-28.1107,17.2266), new MM.Location(-28.1107,17.0508), new MM.Location(-28.3431,16.9189), new MM.Location(-28.5363,16.8311), new MM.Location(-28.5749,16.6992), new MM.Location(-28.6906,16.5234)],

        /* Interior Ring */
        [new MM.Location(-29.6403,27.0374), new MM.Location(-29.5257,27.356), new MM.Location(-29.2864,27.5098), new MM.Location(-29.0274,27.7185), new MM.Location(-28.8735,27.9492), new MM.Location(-28.8735,28.0481), new MM.Location(-28.7195,28.2019), new MM.Location(-28.7195,28.3887), new MM.Location(-28.6327,28.4546), new MM.Location(-28.5749,28.7073), new MM.Location(-28.9216,29.0369), new MM.Location(-29.1234,29.3445), new MM.Location(-29.363,29.5093), new MM.Location(-29.497,29.3665), new MM.Location(-29.5926,29.3665), new MM.Location(-29.6881,29.2126), new MM.Location(-29.8311,29.1467), new MM.Location(-29.9359,29.2017), new MM.Location(-30.1451,28.7622), new MM.Location(-30.1641,28.4326), new MM.Location(-30.278,28.2458), new MM.Location(-30.3729,28.3008), new MM.Location(-30.4392,28.2019), new MM.Location(-30.6852,28.103), new MM.Location(-30.6379,27.7515), new MM.Location(-30.3539,27.4219), new MM.Location(-30.1831,27.4329), new MM.Location(-30.0406,27.2681), new MM.Location(-29.8406,27.2021), new MM.Location(-29.6403,27.0374)]
      ], 

      // Set a few styling options
      {fillColor: new MM.Color(120, 0, 180, 0)}
);
      // Place the shape on the map
      map.entities.push(SouthAfrica);
    }
  </script>
</head>
<body onload="GetMap();">
  <div id='mapDiv' style="position: relative; width: 540px; height: 480px;"></div>
</body>
</html>

and here’s what it looks like:

image

Posted in Bing Maps | 9 Comments »

Tags:
May 4, 2012

Geocoding in SQL Server with the Bing Maps Locations API

Almost every SQL Server database contains “spatial” data. That information might not use the dedicated geography or geometry spatial datatypes but, more likely, could be a table of customer addresses, the name of a city of region for which a sales manager is responsible, the itinerary of locations at which a delivery vehicle is scheduled to stop, etc. etc.

All of these are examples of spatial information – data that describes the location of objects on the surface of the earth. The problem is that this information is typically stored as a free-text string – “10 Downing Street”, “Round the Back of Sainsbury’s car park”, or “Manchester”, for example. Even semi-structured information such as a postcode, “NR1 6NN”, is not particularly useful for spatial queries. Instead, what is needed is a way to turn this text-based, descriptive information, into a structured spatial format (typically a single pair of latitude/longitude coordinates). And that’s where geocoding comes in.

In my SQLBits session last year, “Who Needs Google Maps?” I discussed a little about what is involved in creating your own geocoding function, based on parsing a supplied text address string and looking up the relevant coordinate location from a local gazetteer table. However, in practice, it’s very unlikely that you’ll ever want to create your own geocoding function when you can just use one somebody else has already made (such as the Bing Maps Locations API).

So, here’s a step-by-step guide to creating a geocoding function in SQL Server that calls the Locations API instead:

Step 1. Get a Bing Maps key

Use of the Bing Maps Locations API is free for many applications, but may involve a cost depending on how many geocodes you request, and what you’re using them for (you can check the terms of use at http://www.microsoft.com/maps/product/terms.html). Either way, before using the service you first have to sign up for a key, which you can do at http://www.bingmapsportal.com – it only takes a few seconds to do and you can get an evaluation key instantly. The key is an alphanumeric string that looks a bit like: AhGSgD1Twhjx9WqxjJZznG3tY3r0wnFr!gg1ngK3yGnp9b3hupQUVbNdv6Wb0qW

Step 2. Create a Geocoding Function

Fire up Visual Studio and create a new C# Class Library project. Once  created, add a reference to the Microsoft.SqlServer.Types.dll library  (Project –> Add Reference, and select the Microsoft.SqlServer.Types.dll library from the .NET tab).

Then, edit the default Class1.cs file to read as follows, inserting your Bing Maps key where indicated:

using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
using System.Net;
using System.IO;
using System.Xml;
using Microsoft.SqlServer.Types;
using System.Collections.Generic; // Used for List

namespace ProSpatial
{
  public partial class UserDefinedFunctions
  {

    /* Generic function to return XML geocoded location from Bing Maps geocoding service */
    public static XmlDocument Geocode(
      string countryRegion,
      string adminDistrict,
      string locality,
      string postalCode,
      string addressLine
    )
    {
      // Variable to hold the geocode response
      XmlDocument xmlResponse = new XmlDocument();

      // Bing Maps key used to access the Locations API service
      string key = "ENTERYOURBINGMAPSKEYHERE";

      // URI template for making a geocode request
      string urltemplate = "http://dev.virtualearth.net/REST/v1/Locations?countryRegion={0}&adminDistrict={1}&locality={2}&postalCode={3}&addressLine={4}&key={5}&output=xml";

      // Insert the supplied parameters into the URL template
      string url = string.Format(urltemplate, countryRegion, adminDistrict, locality, postalCode, addressLine, key);

      try
      {
        // Initialise web request
        HttpWebRequest webrequest = null;
        HttpWebResponse webresponse = null;
        Stream stream = null;
        StreamReader streamReader = null;

        // Make request to the Locations API REST service
        webrequest = (HttpWebRequest)WebRequest.Create(url);
        webrequest.Method = "GET";
        webrequest.ContentLength = 0;

        // Retrieve the response
        webresponse = (HttpWebResponse)webrequest.GetResponse();
        stream = webresponse.GetResponseStream();
        streamReader = new StreamReader(stream);
        xmlResponse.LoadXml(streamReader.ReadToEnd());

        // Clean up
        webresponse.Close();
        stream.Dispose();
        streamReader.Dispose();
      }
      catch (Exception ex)
      {
        // Exception handling code here;
      }

      // Return an XMLDocument with the geocoded results 
      return xmlResponse;
    }

    /* Wrapper method to expose geocoding functionality as SQL Server User-Defined Function (UDF) */
    [Microsoft.SqlServer.Server.SqlFunction(DataAccess = DataAccessKind.Read)]
    public static SqlGeography GeocodeUDF(
      SqlString countryRegion,
      SqlString adminDistrict,
      SqlString locality,
      SqlString postalCode,
      SqlString addressLine
      )
    {

      // Document to hold the XML geocoded location
      XmlDocument geocodeResponse = new XmlDocument();

      // Attempt to geocode the requested address
      try
      {
        geocodeResponse = Geocode(
          (string)countryRegion,
          (string)adminDistrict,
          (string)locality,
          (string)postalCode,
          (string)addressLine
        );
      }
      // Failed to geocode the address
      catch (Exception ex)
      {
        SqlContext.Pipe.Send(ex.Message.ToString());
      }

      // Declare the XML namespace used in the geocoded response
      XmlNamespaceManager nsmgr = new XmlNamespaceManager(geocodeResponse.NameTable);
      nsmgr.AddNamespace("ab", "http://schemas.microsoft.com/search/local/ws/rest/v1");

      // Check that we received a valid response from the geocoding server
      if (geocodeResponse.GetElementsByTagName("StatusCode")[0].InnerText != "200")
      {
        throw new Exception("Didn't get correct response from geocoding server");
      }

      // Retrieve the list of geocoded locations
      XmlNodeList Locations = geocodeResponse.GetElementsByTagName("Location");

      // Create a geography Point instance of the first matching location
      double Latitude = double.Parse(Locations[0]["Point"]["Latitude"].InnerText);
      double Longitude = double.Parse(Locations[0]["Point"]["Longitude"].InnerText);
      SqlGeography Point = SqlGeography.Point(Latitude, Longitude, 4326);

      // Return the Point to SQL Server
      return Point;
    }
  };
}

This code listing contains both a generic geocoding method to call the Bing Maps Locations API (called Geocode), and a wrapper function to expose that functionality as a scalar User-Defined Function (called GeocodeUDF) that selects the top-matching geocoded coordinates for a supplied address string.

There are several other ways you might want to expose geocoding functionality in SQL Server; geocoding is an imprecise operation, and you often might get more than one possible match returned for a given address. To enable a choice between different possible geocoded locations, you might therefore prefer to return the results in a table, using a Table-Valued Function rather than a scalar UDF. Such a table could also return other columns of information, such as the confidence of the match, or the bounding box around a large feature rather than a single point location to represent its location. These ideas and more are discussed in the Geocoding chapter of Pro Spatial with SQL Server 2012, but for now we’ll stick with the simple case of a UDF that just selects the top hit.

Build the project containing the code above (Build –> Build Solution).

Step 3. Import the Assembly and Register the Function in SQL Server

In SQL Server, first make sure that user-defined CLR code is enabled:

EXEC sp_configure 'clr enabled', '1';
GO
RECONFIGURE;
GO

Then, to interact with the Locations API service, you need to set the appropriate database security permissions to allow access to external services. The easiest way to do this is to simply set the database to be trustworthy (in this example, I’m going to create my geocoding function in the ProSpatial database – change this line to match your database name as appropriate):

ALTER DATABASE ProSpatial SET TRUSTWORTHY ON;
GO

Now, import the assembly containing the geocoding function, and remember to give it EXTERNAL_ACCESS permission. You’ll have to edit the path and filename to match that of the project you created in Step 2:

CREATE ASSEMBLY Geocoder
FROM 'C:\Users\Alastair\Visual Studio 2012\Projects\Geocoder\bin\Debug\Geocoder.dll'  
WITH PERMISSION_SET = EXTERNAL_ACCESS;
GO

Finally, register the function that will call into the GeocodeUDF method in the Geocoder assembly:

CREATE FUNCTION dbo.Geocode(
  @addressLine nvarchar(max),
  @locality nvarchar(max),
  @adminDistrict nvarchar(max),
  @postalCode nvarchar(max),
  @countryRegion nvarchar(max) 
  ) RETURNS geography
AS EXTERNAL NAME 
Geocoder.[ProSpatial.UserDefinedFunctions].GeocodeUDF;

Step 4. Geocode!

Now, to geocode any address from SQL Server, call the Geocode function, supplying the street address, locality, administrative district, postcode, and country/region, as in the following examples: (PLEASE SEE UPDATE BELOW!)

-- Create a new geography Point by geocoding a provided address
-- For any address elements unknown, simply supply an empty string
DECLARE @g geography;
SET @g = dbo.Geocode('10 Downing Street', 'London', '', 'SW1A 2AA', 'UK');

-- Retrieve the WKT of the created instance
SELECT @g.ToString();

Here’s the results of running this query in SQL Server:

image

POINT(-0.127707 51.50355). Now let’s then check these coordinates on Bing Maps. Unsurprisingly, (since the coordinates of the point in SQL Server were obtained from the Bing Maps Locations API), they line up pretty well….

image

UPDATE

Following a few comments from folks who were having trouble getting this code to work, I realised that the example code listing above is wrong – the method signature of the Geocode SQLCLR method expects the countryRegion parameter *first*, then district, locality, postcode, and finally street address. So, my example should really have read:

SELECT dbo.Geocode(‘UK’, ‘LONDON’, ‘London’, ‘SW1A 2AA’, ’10 Downing Street’);

Or, if you prefer to supply the parameters in the more common street address, locality, postcode, country order, just change the method signature and recompile the assembly. Sorry for any confusion caused!

Posted in Bing Maps, SQL Server | 22 Comments »

Tags: , ,
  |  
Follow

Get every new post delivered to your Inbox.

Join 53 other followers