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!

About these ads
This entry was posted in Bing Maps, SQL Server and tagged , , . Bookmark the permalink.

30 Responses to Geocoding in SQL Server with the Bing Maps Locations API

  1. rbrundritt says:

    Spooky, I literally was looking into writing the exact same thing this morning.

  2. AndrewJ says:

    Cool! My friends and I did something similar a while ago with Yahoo and then Bing maps API for geocoding in SQL Server!

  3. Jim Goose says:

    Sample is really nice, but it is not working for me with SQL Server 2008 R2. Seems that no HTTP request is sent (checked by WireShark).
    I tried to call the method also from console app and everythin is ok.
    Is there any big difference between 2012 and 2008?

    Thanks,

    • alastaira says:

      No – should work just as well in SQL Server 2008/R2/2012… is your SQL Server behind any sort of firewall that would prevent outgoing connections? Did you enable CLR and register the function with EXTERNAL_ACCESS? Any errors generated at all?

      • Jim Goose says:

        Hi,
        my issue is that the app is not able to get default proxy when method is called from SQL. As I am working in a company environment, I am behind a proxy and some firewall(s) too. When I put the proxy settings (uri) by hand (in code), it works fine. Do you have any experience with this? Thanks

      • Jim Goose says:

        I found the root of this behaviour. SQL Server service is running under Local System. There is no setting of proxy for this account. When I ran the server service under my account, it was working as required.

    • Jim Goose says:

      Solved, issue with getting default proxy.

  4. jeclipse says:

    Have you looked at calling the Bing web service APIs rather than building the request/response by hand? It would certainly eliminate the XmlDocuments (notorious memory hogs and rather inefficient … but not bad for a small request like this one) as well as all the parsing involved. IMHO, the code would also be a bit cleaner and easier to read as you’d be using classes rather than formatting and parsing.

    • alastaira says:

      You mean the Bing Maps SOAP services? They’re older than the REST services and generally not as accurate or reliable – MS currently recommend using the REST services instead. Although this involves a little manual string-formatting, it’s not that onerous.
      As for efficiency – I wouldn’t necessarily recommend that calling a webservice from the database layer is a good idea anyway, but I’m just showing that it is possible :)

  5. Kevin Coles says:

    What do I need to change in order to make this work with other countries? I can’t seen to get it to run on a canadian address.

  6. Mauricio says:

    hi does not work the example of geocoding
    DECLARE @g geography;
    SET @g = dbo.Geocoder_2(
    ’10 Downing Street’, ‘London’, ”, ‘SW1A 2AA’, ‘UK’);

    SELECT @g.ToString();

    I get the following message

    Msg 6522, Level 16, State 1, Line 2
    A .NET Framework error occurred during execution of user-defined routine or aggregate “Geocoder_2″:
    System.NullReferenceException: Object reference not set to an instance of an object.
    System.NullReferenceException:
    at Geocoding_2.UserDefinedFunctions.GeocodeUDF(SqlString countryRegion, SqlString adminDistrict, SqlString locality, SqlString postalCode, SqlString addressLine)
    .
    (1 row(s) affected)

    someone could help me?, i’m using microsoft sql server 2008 R2 and Visual Studio 2010 Ultimate, build the project in framework 2.0

    Thanks

  7. chrisc says:

    Does something need to be changed in order to get this to work for addresses in the US?
    Example:
    SET @g = dbo.Geocode(‘5085 Westheimer Rd’, ‘Houston’, ‘TX’, ‘77056’, ‘US’);
    Getting this when trying.
    Msg 6522, Level 16, State 1, Line 2
    A .NET Framework error occurred during execution of user-defined routine or aggregate “Geocode”:
    System.NullReferenceException: Object reference not set to an instance of an object.

    • alastaira says:

      Hi Chris,
      The reason is because I was a complete muppet and listed the parameters for the function in the wrong order in the example – if you look at the method signature it goes CountryRegion -> AdminDistrict -> Locality -> Postcode -> Address. So, to use your address, you need to write: SELECT dbo.Geocode(‘US’, ‘Texas’, ‘Houston’, ‘TX 77056′, ‘5085 Westheimer Rd’); (Or, if you prefer, change the method signature in the SQLCLR code to be the other way around)

      What’s really odd is that I didn’t spot this because it works with the 10 Downing Street example whichever way round you supply the parts of the address…. weird!!

  8. Alireza says:

    Nice , you could use DataContractJsonSerializer if you define a DataContract for your returned type.

  9. magalhaesv says:

    I am getting the following message:

    Msg 6522, Level 16, State 1, Line 2
    A .NET Framework error occurred during execution of user-defined routine or aggregate “Geocode”:
    System.FormatException: 24201: os valores de latitude devem estar entre -90 e 90 graus. (latitude values must be between -90 and 90 degrees)
    System.FormatException:
    em Microsoft.SqlServer.Types.SqlGeography.Point(Double latitude, Double longitude, Int32 srid)
    em ProSpatial.UserDefinedFunctions.GeocodeUDF(SqlString countryRegion, SqlString adminDistrict, SqlString locality, SqlString postalCode, SqlString addressLine)
    .
    By doing some tests here, I also noticed that if I some how change the value from latitude, I get the same message but for longitude, stating it should be between -15 and 15.

    Any idea on what could be the cause of this?

  10. magalhaesv says:

    Just a few more information, I am using SQL Server 2012 (but also tried with 2008 R2 and received the same error).

  11. magalhaesv says:

    Well, I am not a C# expert, so I don’t know if this is the best solution, but I could make it work by using the code bellow. It seems that the problem was that my system has a different locate (PT-BR instead of EN-US that is problably yours)

    double Latitude = double.Parse((Locations[0]["Point"]["Latitude"].InnerText), System.Globalization.NumberFormatInfo.InvariantInfo);
    double Longitude = double.Parse((Locations[0]["Point"]["Longitude"].InnerText), System.Globalization.NumberFormatInfo.InvariantInfo);

    Anyway, I would like to see your opinion on this!

  12. david schwartz says:

    Is it possible for sql server 2012 to return an image of a map like the maps generated in management studio under the spatial tab?

  13. Firtz Makossa says:

    Hi Alastair
    I am using the ProSpatial 2012 in creating Bing map which point to a location base on Street address.
    At the final stage in your code I tried to create a store procedure to in sql 2012 but I am getting error.
    Please see below the error;
    Msg 6506, Level 16, State 1, Procedure Geocode, Line 1
    Could not find method ‘GeocodeUDF’ for type ‘ProSpatialCh6.UserDefinedFunctions’ in assembly ‘ProSpatialCh6′. Please I need help . Thanks

    • alastaira says:

      Hi Firtz,
      Sorry to hear you’re having problems – the error says that it can’t find a method called GeocodeUDF in the UserDefinedFunctions class of the ProSpatialCh6 namespace of the ProSpatialCh6 assembly. So, one of those must be wrong ;) Are you using the code download that accompanies the book, or did you create the method yourself?

      I’m not near a computer to check at the moment, but can you try altering the namespace to “ProSpatial.Ch6″ rather than “ProSpatialCh6″? So, in the SQL statement in which you register the function (the one that’s generating the error), rather than “….AS EXTERNAL NAME ProSpatialCh6.[ProSpatialCh6.UserDefinedFunctions].GeocodeUDF….” try “…AS EXTERNAL NAME ProSpatialCh6.[ProSpatial.Ch6.UserDefinedFunctions].GeocodeUDF…”

  14. Firtz Makossa says:

    Hi Alastaira

    Thanks for your response. I was able to solve it by changing the assemblies and the namespace. There is another problem iin declaring and setting my data. This is the error message I am receiving. Please see below.

    Msg 6522, Level 16, State 1, Line 3
    A .NET Framework error occurred during execution of user-defined routine or aggregate “Geocode”:
    System.NullReferenceException: Object reference not set to an instance of an object.
    System.NullReferenceException:
    at ProSpatial.UserDefinedFunctions.GeocodeUDF(SqlString countryRegion, SqlString adminDistrict, SqlString locality, SqlString postalCode, SqlString addressLine)
    .

    I think you were showing a resolution to that on this page for I have tried by changing the method signature in SQLCLR but I’m not getting any where. I ‘m using this snytax.

    DECLARE @g geography;
    SET @g = dbo.Geocode(‘Ireland’,’Donegal’, ‘Letterkenny’, ‘000000’, ’13A Chestnut Grove’)

    I thinks you have to insert before select. Or this not right.

  15. bbourbeau says:

    I’m receiving the following eror

    Msg 6522, Level 16, State 1, Line 1
    A .NET Framework error occurred during execution of user-defined routine or aggregate “Geocode”:
    System.NullReferenceException: Object reference not set to an instance of an object.
    System.NullReferenceException:
    at ProSpatial.UserDefinedFunctions.GeocodeUDF(SqlString countryRegion, SqlString adminDistrict, SqlString locality, SqlString postalCode, SqlString addressLine)

    I’m using dbo.geocode(‘US’,’Ohio’,’Cincinnati’,’OH 45226′,’5053 Wooster Rd’)

  16. Mauricio says:

    hi alastaira
    disable antivirus and firewall, but equally still get the same error. In SQL I should just do the procedure or should I install any api?

  17. Hello Alastaira,

    I just bought your spatial sql sever 2012 book. and test your gecoding code. Many address can go through and get right Lat and Long. However, some get error the same error message as above. Wonder if you could give us some hints on this? Thanks a lot

    Msg 6522, Level 16, State 1, Line 1
    A .NET Framework error occurred during execution of user-defined routine or aggregate “Geocode”:
    System.NullReferenceException: Object reference not set to an instance of an object.
    System.NullReferenceException:
    at ProSpatial.UserDefinedFunctions.GeocodeUDF(SqlString countryRegion, SqlString adminDistrict, SqlString locality, SqlString postalCode, SqlString addressLine)

    • Jim says:

      You get this error if an exception occurs when the web request is sent, and the code ends up in this exception handler:
      catch (Exception ex)
      {
      // Exception handling code here;
      }
      Then xmlResponse isn’t filled in with anything and you end up with null reference exception later on when you try to access it.

      Most likely cause of exception is that your Bing maps key is invalid.
      You can always try just cut’n’pasting the url that the geocode function generates into a web browser and see what response you get…

  18. balikpinoy says:

    Unfortunately, this does not work..same error..NullReferenceException

  19. lizzyquatto says:

    Nice! Pulled down the code, compiled it, pulled it into SQL and it worked the first time without issue. Yes, I did see the parm order and had rearranged them before calling it. Great job Alastair – thanks for posting.

  20. Jim says:

    Anyone know how to fix the following?
    CREATE ASSEMBLY Geocoder
    FROM ‘d:\Geocoder\bin\Geocoder.dll’
    WITH PERMISSION_SET = EXTERNAL_ACCESS;
    GO
    ——-
    CREATE ASSEMBLY for assembly ‘Geocoder’ failed because assembly ‘Geocoder’ is not authorized for PERMISSION_SET = EXTERNAL_ACCESS. The assembly is authorized when either of the following is true: the database owner (DBO) has EXTERNAL ACCESS ASSEMBLY permission and the database has the TRUSTWORTHY database property on; or the assembly is signed with a certificate or an asymmetric key that has a corresponding login with EXTERNAL ACCESS ASSEMBLY permission.
    ——-

    OK, but I do have TRUSTWORTHY set to on. So I must have to grant myself external access assembly permission, right?
    GRANT external access assembly to [DOMAIN\a.user]
    ——-
    Cannot grant, deny, or revoke permissions to sa, dbo, entity owner, information_schema, sys, or yourself.

    OK, so log off, log on as someone else, and re-grant the permissions.
    That works.
    Log off and log back in as DOMAIN\a.user

    CREATE ASSEMBLY Geocoder
    FROM ‘d:\Geocoder\bin\Geocoder.dll’
    WITH PERMISSION_SET = EXTERNAL_ACCESS;
    GO
    ———-
    Could not obtain information about Windows NT group/user ‘DOMAIN\a.user’, error code 0x5.

    Now what??

  21. Is there any limit to the number of rows of data I can transpose onto Bing maps?
    Currently Power maps in Excel provides an easy plugin to map rowsets to the Geo Map (latitude/longitude coordinates provided), however if the number of rows exceed 1048576 then Excel can’t handle it anymore.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s