Reverse Geocoding with MapQuest in SQL Server 2005


We have been using a component called Pepperwhite for our mapping and reverse geocoding for quite some time. Not a bad component, overall, but the map data is dated and we are now ending up with numerous addresses that will not reverse geocode.

The long term solution, of course, is to replace Pepperwhite with MapQuest, but this requires some vendor support for our "gateway" piece. So, I need a solution to correct reverse geocoded addresses that fail. It has to be in SQL Server.

SQL CLR to the rescue. First, I have created a library that does the RG work on MapQuest. It is the core of the solution. I am not at liberty to share this piece, but you can create your own with the MapQuest API, if you are so inclined (NOTE: Requires commercial use of API, AFAIK).

Next, create a SQL project and add a CLR Trigger template. The code here is rather simple and still needs a refactoring. As such, I am presenting it as a research project only. Your use of this code is done at your own risk. Having said that, here is the code:

public partial class Triggers
{
  private static StringBuilder _builder = new StringBuilder(); 

  // Enter existing table or view for the target and uncomment the attribute line
  [Microsoft.SqlServer.Server.SqlTrigger(Name = "ReverseGeocodeTrigger"
    , Target = "tReport", Event = "FOR INSERT")]
  public static void ReverseGeocodeTrigger()
  {
    SqlTriggerContext triggContext = SqlContext.TriggerContext;
    string street, city, state, postalCode, status;
    double latitude, longitude;
    int reportID; 

    SqlConnection connection = new SqlConnection("context connection = true");
    connection.Open();
    SqlCommand command = connection.CreateCommand();
    SqlDataReader reader; 

    switch (triggContext.TriggerAction)
    {
      case TriggerAction.Insert:
        command.CommandText = "SELECT * from " + "inserted";
        reader = command.ExecuteReader(); 

        reader.Read(); 
        reportID = reader.GetInt32(0);
        latitude = reader.GetDouble(5);
        longitude = reader.GetDouble(6);
        street = reader.GetString(7);
        city = reader.GetString(8);
        state = reader.GetString(9);
        postalCode = reader.GetString(10); 

        try
        {
          //TODO: Test for null to avoid this kludge
          status = reader.GetString(16);
        }
        catch
        {
          //Just a catch for status. Will be null if fails here
          status = null;
        } 

        reader.Dispose(); 

        if (InvalidStreetAddressWithValidLatLong(street, latitude, longitude))
        {
          try
          {
            //Fix the geocoding with MapQuest
            MapQuestReverseGeocode geocode = 
                      new MapQuestReverseGeocode(latitude, longitude);
            RGAddress address = geocode.RunReverseGeocode(); 

            SqlCommand command2 = connection.CreateCommand(); 

            _builder.Length = 0;
            _builder.Append("UPDATE tReport SET sStreet = @street" + 
              " , sCity = @city, sState = @state," +
              " sPostalCode = @postalCode, sStatus = @status " +
              " where iReportID = @reportID"); 

            command2.CommandText = _builder.ToString();
            command2.Parameters.AddWithValue("@street", address.Address1);
            command2.Parameters.AddWithValue("@city", address.City);
            command2.Parameters.AddWithValue("@state", address.StateAbbreviation);
            command2.Parameters.AddWithValue("@postalCode", address.PostalCode);
            command2.Parameters.AddWithValue("@reportID", reportID); 

            if (status != null)
              command2.Parameters.AddWithValue("@status", status);
            else
              command2.Parameters.AddWithValue("@status", "C077ECED");
            command2.ExecuteNonQuery();
            command2.Dispose();
          }
          catch
          {
            //TODO: Figure out if you need to do anything here, as this is sloppy
          } 
          finally
          {
            command2.Dispose();
          } 
        } 

        connection.Dispose(); 

        break; 

      case TriggerAction.Update:
        //TODO: Will we ever need this? Copied from other code
        break; 

      case TriggerAction.Delete:
        //TODO: Will we ever need this? Copied from other code
        break;
    }
  } 

  private static bool InvalidStreetAddressWithValidLatLong(string street
	, double latitude, double longitude)
  {
    if ((latitude == 0) && (longitude == 0))
      return false; 

    if (street.Substring(0, 1) == "0")
      return true;
    else
      return false;
  }

There is really not much here. The bottom routine determines if the line in question is invalid. In our application, anything with a street that comes out as 0% is bogus. I am also testing latitude and longitude as I do not want to incur a trip across the net to map someone in the middle of the Atlantic Ocean (lat = 0, long = 0).

In essence, this CLR trigger performs the same operation a simple update operation inside a FOR INSERT trigger would perform, if a FOR INSERT trigger could contact MapQuest, of course.

In order to get this working, you have a few steps. First you have to make sure the database is set for SQL 9.0 compatibility, and not earlier. This is done with the stored procedure sp_dbcmptlevel. This step is done against the master database.

use master
GO
sp_dbcmptlevel '{Database name here}', 90
GO

You then have to set the database to trustworthy. This is not necessary for all types of CLR assemblies, but the MapQuest assembly uses some practices that cannot be marked safe. This step is also done against the master database.

use master
GO
sp_dbcmptlevel '{Database name here}', 90
GO

Now to the {database} in question. First, make sure the CLR is enabled.

use {database name here}
GO
sp_configure 'clr enabled', 1
GO
reconfigure
GO

Probably more goes than I actually need, but I will have to deal with that later. 🙂

You then either have to give rights to the account installing, which means logging in as sa, or similar, and giving rights to your account, or give ownership to an account that has rights to install and run assemblies. For simplicity, at least on the dev machine, the later is simpler.

use {database name here}
GO
EXEC sp_changedbowner 'sa'
GO

So far, so good. You now have to install your assemblies. In this case, I have rolled my RG component code into the main assembly, but I could potentially create a chain of assemblies, if I so desired. In my case, I have to register the MapQuest .NET DLL, from MapQuest, and my own. It looks like this.

use {database name here}
GO
create assembly MapQuest from 'd:clrmapquest.dll' with permission_set = unsafe
GO

create assembly MicrotrakMapQuest from 'd:clrReverseGeocode.dll' 
with permission_set = unsafe
GO

Now, everything is set in the SQL CLR. The only remaining step is to create the trigger. This is rather simple and follows the naming convention assembly.class.procedure. In this case ReverseGeocode.Triggers.ReverseGeocodeTrigger. Yeah, the naming is rather simple, but it works for now.

use {database name here}
GO
CREATE TRIGGER tReport_ReverseGeocode
ON tReport
FOR INSERT
AS
EXTERNAL NAME ReverseGeocode.Triggers.ReverseGeocodeTrigger
GO

Viola! This is all I have to do to get things running. In testing, it is catching all of the bad addresses. In beta, it is catching all of the bad addresses. And, so it is now in production. Wish I had a full QA department and a way to easily set unit tests up on SQL Server, but that is a wish I will not get in the immediate future. As the RG code is fully unit tested, I am not completely running out on a limb. Until next time.

Peace and Grace,
Greg

Advertisements

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

%d bloggers like this: