MapQuest Reverse Geocoder Revisited


When I posted my first code, I got back a couple of replies. The first was from Kevin Boles:

I am certainly not a .NET guru, but it seems to me that the code you have
will only work on one row.  What happens if there is a multi-row-insert
batch??

Kevin is correct, but it is not an issue (see below). But, it is worth a second look, if batching was an issue. Before moving into the story, let’s revisit the code (from an earlier post).

[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
      {
        status = reader.GetString(16);
      }
      catch (Exception ex)
      {
        //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();

          //SqlConnection conn = new SqlConnection("context connection = true");
          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 (Exception ex)
        {
          //TODO: We are just ignoring mistakes. Is that okay?
        }
      }

      connection.Dispose();

      break;

    case TriggerAction.Update:
      break;

    case TriggerAction.Delete:
      break;
  }
}

When I initially coded the RG piece, I focused on a single insert, rather than batches. In this particular application, there will never be batches, at least not until the application is altered significantly. So, I saw no need to incur extra overhead.

In SQL Server CLR, when you spin up a context connection (see below), you are running directly against SQL Server in your code. You can only have one open at a time. This means we have a few choices:

  1. Feed the data from the Reader into Objects
  2. Use another data construct, like a DataSet

We will look at both of these. Bear in mind that both solutions use a DataReader, even though you will not see it in the second option. Underneath the hood, there is a reader filling the DataSet. You should also note that, providing the code is tight, the first option should be slightly faster. I am not one that codes strictly for performance, but filling a few fields in an object carries a bit less weight than filling a DataSet, at least in most instances. The difference is in milliseconds (perhaps microseconds), so I would not shift all of my "legacy" .NET code to objects for the small change in perf. As this is SQL Server, however, it is probably worth shaving the miniscule amount of time off of each trigger call.

Feed the Data from the Reader into Objects

In this particular example, I could do something like the (assuming, of course, RGAddress had all of these fields – in real life it does not, so I have to create a new object), with the following changes to the code

reader.Read();

reportID = reader.GetInt32(0);
latitude = reader.GetDouble(5);
longitude = reader.GetDouble(6);
street = reader.GetString(7);

becomes

List addressList = new List();

while(Reader.Read())
{
  RGAddress address = new RGAddress();
  address.ID = reader.GetInt32(0);
  address.Latitude = reader.GetDouble(5);
  address.Longitude = reader.GetDouble(6);
  address.Street = reader.GetString(7);

  //Add to list
  addressList.Add(address);
}

And there are a couple of changes to the code that saves. Here are the changed lines:

foreach(RGAddress a in addressList)
{
  MapQuestReverseGeocode geocode = 
      new MapQuestReverseGeocode(a.Latitude, a.Longitude);
  ...
  command2.Parameters.AddWithValue("@reportID", address.ID);
}

Not a huge amount of change here. If I were really coding this, I would use a lighter object than my RGAddress object (aka, Reverse Geocode Address — should probably rename, but it is a temporary object until this problem is solved properly).

Use a DataSet

Now, this could technically be any data container that can hold multiple rows, including LINQ to SQL, but a DataSet is a fairly easy construct to work with. In this case, I am imagining a strongly typed dataset, for both simplicity of coding (ie, Intellisense) and a bit better performance. The main change in the code is here. Instead of:

  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();

We use:

  connection.Open();
  SqlCommand command = connection.CreateCommand();
  SqlDataAdapter da = new SqlDataAdapter(command);
  da.TableMappings.Add("Table", "Report");
  ReportDataSet ds = new ReportDataSet();
  da.Fill(ds);

And then we have to iterate thru the rows.

foreach(ReportDataSet.ReportRow row in ds.ReportDataTable)
{
   MapQuestReverseGeocode geocode = 
       new MapQuestReverseGeocode(row.Latitude, row.Longitude);
   ...
}

The rest is identical to the reader, as the RGAddress object is created.

Security

The other reply I got from Niels Berglund:

Just had a look at your code, nice use of SQLCLR. One comment though:
I’d strongly advise AGAINST marking your database as trustworthy in
order to be able to deploy the assembly as UNSAFE. TRUSTWORTHY is a
"quick and dirty" solution for assemblies but you are opening up
potential security holes. You’d be better off using certificates and
sign your assembly instead.

If this was a long term patch, I would address this issue immediately. As it stands, this is a very short term patch to get customer support off my back long enough to have our vendor (who built the gateway piece) unwire Pepperwhite and wire MapQuest (via my reverse geocode DLL – no reason to have the wheel reinvented here0.

Remembering that the MapQuest DLL is not my DLL (it is supplied from MapQuest), I cannot simply sign it (at least not without reverse engineering the code). Presently, there are two issues with the MapQuest .NET DLL (in this respect):

  1. They are not safe assemblies
  2. They are not signed as unsafe assemblies

In addition, they are compiled 1.1, but that is a minor issue. The short story is you cannot set them up in SQL Server without trustworthy. Possible solutions:

  1. Reverse engineer the MapQuest DLL and make it safe
  2. Reverse engineer the MapQuest DLL and mark it as unsafe
  3. Use a SOA implementation and hide the MapQuest DLL under a "web service" (WCF, ASMX)
  4. Contact MapQuest to do #1 or #2.

The first two are an issue, as MapQuest can update the code at any time. In addition, there is probably a EULA issue with reverse engineering. I am not going to touch that one. That leaves web service or contacting MapQuest. Until MapQuest updates the DLL for SQL CLR (if ever), the only one of those options under my control is SOA. And, it is a perfectly acceptable answer (although I am not sure I will blog this).

Peace and Grace,
Greg

Advertisements

2 Responses to MapQuest Reverse Geocoder Revisited

  1. Unknown says:

    There’s a new .NET dll available on the beta page here: http://developer.mapquest.com, donno if it’ll fix your unsafe issue though.

  2. Gregory says:

    That is good news. I will have to check it out. If this turns out to be a long term solution, I will wrap it in a web or WCF service (the choice being whether I can load 3.5 on this machine right now with the other software loaded). I plan on replacing the current Reverse Geocoder with MapQuest and, as it will be outside of SQL Server, it should not be an issue. Thanks for the info though. I will see if it works.

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: