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:
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….
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!
