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!
Spooky, I literally was looking into writing the exact same thing this morning.
Cool! My friends and I did something similar a while ago with Yahoo and then Bing maps API for geocoding in SQL Server!
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,
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?
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
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.
Solved, issue with getting default proxy.
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.
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 🙂
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.
See my comment above – the example listed the parameters in the wrong order – you need country *first*, going down to street address *last*
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
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.
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!!
Nice , you could use DataContractJsonSerializer if you define a DataContract for your returned type.
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?
Just a few more information, I am using SQL Server 2012 (but also tried with 2008 R2 and received the same error).
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!
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?
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
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…”
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.
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’)
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?
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)
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…
Unfortunately, this does not work..same error..NullReferenceException
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.
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??
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.
I too am getting the null error. I am on Windows 2008R2 and SQL 2012. I am able to reach bing mapping via browser on the machine. I even hard coded the constructed string into String URLTemplate. When I do manually, I am getting results and status =200. Is there an updated version of this?
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.
How would you modify this code to do reverse geocoding? I have latitude/longitudes and need to look up City, State, and Country.
I haven’t tested this on a large recordset yet, but I’ve noticed it chokes on # character in an Address and a null postal code.
Also I had to use a different PERMISSION_SET when creating the assembly.
use databasename
CREATE ASSEMBLY Geocoder
FROM ‘d:\Geocoder\bin\Geocoder.dll’
WITH PERMISSION_SET = UNSAFE;
GO
This is the query I used to geocode a table of places.
UPDATE Places
SET Places.Latitude = g.GC.Lat, Places.Longitude = g.GC.Long
FROM Places
INNER JOIN (
SELECT PlaceID, dbo.GeoCode(‘us’,k.[State],k.[City],k.[Zip],REPLACE(k.[Address1],’#’,”)) AS GC from Places k
) AS g on Places.PlaceID = g.PlaceID where Places.Latitude is null and Places.Zip is not null;
Fantastic example, just had to tweak it a little bit here and there for it to work. Also, wanted you to know that the Latitude and Longitude are actually reversed. In other words, the line SqlGeography.Point(Latitude, Longitude, 4326); needs to have the Longitude first, Latitude second. If you run the function you get the Point in reverse. When I plugged it into Bing maps it was putting my home in the middle of Antarctica, until I figured out the Latitude and Longitude are reversed, then it worked.
Thanks again for the great example, it really helped me get going with this project at work!
Kind regards,
David