Just a quick post this time. I had a requirement to convert a small number of UK based postcodes to their latitude/longitude locations so I could use that data within some Tableau dashboards that I’m working on. I had a look around to see what I could find and quickly found both sites that exposed an API to do lookups and also sites publishing the full data as a download as well. As this was largely a one off, I didn’t want to go through the process of downloading the data and importing it into a database so I decided to use one the many API’s and create a quick SQL CLR to retrieve this data.

There are many sites offering a lookup API but the one I ended up choosing returned the data in XML format (amongst other formats) and didn’t have any immediate restrictions. The site is http://uk-postcodes.com and details of their API is here http://uk-postcodes.com/api.

So I fired up visual studio 2012 and proceeded to create a CLR TVF that took in a postcode parameter and returned a single row table containing the key fields from the postcode lookup. For CLR TVF’s the main function must implement the IEnumerable interface and there needs to be a FillRow method which is called for each object returned in the enumerable collection from the main function. I even took the time to handle invalid postcodes and optionally throw an exception or return an empty record for that postcode.

You can download the entire VS2012 solution here which also has the compiled dll which you can create an asymmetric key from within SQL. This is best practice rather than setting the trustworthy database setting to on + granting external access to the database owner. The download contains example SQL scripts for deploying both ways.

Here is the main C# code with the two methods. The main function invokes the API and gets an XML response which is simply loaded into a list. The list is then returned and enumerated invoking the other method. The list will only contain a single xml document object, but the function needs to return an enumerable object. Bear in mind I’m not a full on out and out C# developer so happy to read [constructive] comments about better/cleaner ways to do this in C# for my own learning.

using System;
using System.Collections;
using System.Collections.Generic;
using System.Net;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
using System.Xml;
 
public partial class UserDefinedFunctions
{
    [Microsoft.SqlServer.Server.SqlFunction(
        DataAccess = DataAccessKind.None,
        SystemDataAccess = SystemDataAccessKind.None,
        FillRowMethodName="fillrow",
        TableDefinition=@"Postcode nvarchar(9)
                            , Latitude decimal(18, 15)
                            , Longitude decimal(18, 15)
                            , Easting decimal(10,1)
                            , Northing decimal(10,1)
                            , Geohash nvarchar(200)")]
    public static IEnumerable LookupPostcode(SqlString Postcode, SqlBoolean ThrowExceptions)
    {
        var PostcodeXml =new List<XmlDocument>();
 
        try
        {
            HttpWebRequest request = WebRequest.Create(string.Format("http://uk-postcodes.com/postcode/{0}.xml", Postcode.ToString())) as HttpWebRequest;
 
            using(HttpWebResponse response = request.GetResponse() as HttpWebResponse)
            {
                XmlDocument xml = new XmlDocument();
                xml.Load(response.GetResponseStream());
                PostcodeXml.Add(xml);
            }
        }
        catch(Exception e)
        {
            if((bool)ThrowExceptions)
            {
                throw(e);
            }
            else
            {
                XmlDocument xml = new XmlDocument();
                PostcodeXml.Add(xml);
            }
        }
 
        return PostcodeXml;
    }
 
 
    public static void fillrow(Object obj, out SqlString Postcode, out SqlDecimal Latitude, out SqlDecimal Longitude, out SqlDecimal Easting, out SqlDecimal Northing, out SqlString Geohash)
    {
        XmlDocument PostcodeXml = (XmlDocument)obj;
        Postcode = PostcodeXml.SelectSingleNode("/result/postcode") == null ? "Unknown": PostcodeXml.SelectSingleNode("/result/postcode").InnerText ;
        Latitude = PostcodeXml.SelectSingleNode("/result/geo/lat") == null ? SqlDecimal.Null : Convert.ToDecimal(PostcodeXml.SelectSingleNode("/result/geo/lat").InnerText);
        Longitude = PostcodeXml.SelectSingleNode("/result/geo/lng") == null ? SqlDecimal.Null : Convert.ToDecimal(PostcodeXml.SelectSingleNode("/result/geo/lng").InnerText);
        Easting = PostcodeXml.SelectSingleNode("/result/geo/easting") == null ? SqlDecimal.Null : Convert.ToDecimal(PostcodeXml.SelectSingleNode("/result/geo/easting").InnerText);
        Northing = PostcodeXml.SelectSingleNode("/result/geo/northing") == null ? SqlDecimal.Null : Convert.ToDecimal(PostcodeXml.SelectSingleNode("/result/geo/northing").InnerText);
        Geohash = PostcodeXml.SelectSingleNode("/result/geo/geohash") == null ? SqlString.Null : PostcodeXml.SelectSingleNode("/result/geo/geohash").InnerText;
    }
}

With the dll created I need to deploy it to SQL but to do that correctly, you need to create an asymmetric key, create a login from the new asymmetric key, grant external access assembly to the login and then finally create the assembly from file.

The following script will perform all of these actions but you'll need to copy the dll to the SQL server as the filename that it references in the script is local to the SQL server. Ensure that you put the dll in a folder that the SQL server sevice account can access and update the script accordingly.

USE master
GO
CREATE ASYMMETRIC KEY LookupPostcodedll_Key
FROM EXECUTABLE FILE = 'C:\SQLCLR_Postcode_Lookup.dll'
 
CREATE LOGIN CLR_LookupPostcode_Login FROM ASYMMETRIC KEY LookupPostcodedll_Key
 
GRANT EXTERNAL ACCESS ASSEMBLY TO [CLR_LookupPostcode_Login]
GO
USE MyDb
GO
 
CREATE USER CLR_LookupPostcode_Login FOR LOGIN CLR_LookupPostcode_Login
GO
 
CREATE ASSEMBLY SQLCLR_Postcode_Lookup FROM 'C:\SQLCLR_Postcode_Lookup.dll'
WITH PERMISSION_SET=EXTERNAL_ACCESS
 
GO
CREATE FUNCTION [dbo].[LookupPostcode]
(@Postcode NVARCHAR (4000), @ThrowExceptions BIT)
RETURNS
     TABLE (
        [Postcode]  NVARCHAR (9)     NULL,
        [Latitude]  DECIMAL (18, 15) NULL,
        [Longitude] DECIMAL (18, 15) NULL,
        [Easting]   DECIMAL (10, 1)  NULL,
        [Northing]  DECIMAL (10, 1)  NULL,
        [Geohash]   NVARCHAR (200)   NULL)
AS
EXTERNAL NAME [SQLCLR_Postcode_Lookup].[UserDefinedFunctions].[LookupPostcode]
 
GO
 
SELECT * FROM dbo.LookupPostcode('RG6 1WG', 1)

Running the above will deploy the CLR and then run a select against the TVF which will return:

Aaron Bertrand recently published this article about getting your SQL server instance to do work that it isn’t natively best at doing and to be honest I absolutely agree. SQL CLR calling external services is one such example and I wouldn’t advocate this approach for anything more than the odd ad-hoc lookup under specific conditions. I needed to resolve a few 100 postcodes in SQL server as a one off job and I may or may not use the CLR again the future. These type of low use/one off scenarios I think are fine for SQL to leverage CLR rather than creating an external process using whatever language suitable and then have to get the data back into SQL. If you need something production strength, robust, secure with concurrency to hit an external service then SQL CLR probably isn’t really the tool to achieve that.

Enjoy!

Follow me on twitter @sqlserverrocks

Subscribe to my blog RSS feed

Comment on this or any of my posts or contact me directly from http://www.olcot.co.uk