SSIS – Script Task to obtain Geo-Cordinates from Address Text via Google API

Hi,

This post is about a quick solution to a problem which I have come across quite few times.

In few instances, I was required to obtain the “Latitude” and “Longitude” coordinates of the address stored as strings/text in the columns of the database.

This was primarily required to leverage the mapping features of the various reporting tools.

So a quick approach to resolve the above was, I leveraged the Google Geocoding API which you can read more following the link.

The main steps involved in the solution are:

1. Using the C# script task in SSIS, for each row of data. I am invoking the google geo-code api enquiring the address information via GET request along with my specific API key.
   
   The URL which I fire for each row to google api is formatted as follows
   
https://maps.googleapis.com/maps/api/geocode/xml?address={Addresss I want to search}&key={Google API Key}

   
2. In the above URL , for each row I am substituting the parameters highlighted above within the curly braces, and obtain the XML response from the Google geo-code API.
3. Post which I then parse and populate the relevant transformation required output columns, with the code as listed below:

   
   using System.Net;
   using System.IO;
   using System.Xml;
   
       public override void Input0_ProcessInputRow(Input0Buffer Row)
       {
           /*
            * Add your code here
            */
           var xmlDoc = new XmlDocument();
           string url = "https://maps.googleapis.com/maps/api/geocode/xml?address={0}&key={1}";
           var wGet = WebRequest.Create(String.Format(url, Row.SearchAddress , Variables.webApiKey));
           wGet.Method = WebRequestMethods.Http.Get;
           var response = wGet.GetResponse();
           var status = ((HttpWebResponse)response).StatusDescription;
           if (status == "OK")
           {
               // Open the stream using a StreamReader for easy access.
               StreamReader reader = new StreamReader(response.GetResponseStream());
   
               // Read the content fully up to the end.
               string responseFromServer = reader.ReadToEnd();
               // Clean up the streams.
               reader.Close();
   
               xmlDoc.LoadXml(responseFromServer);
               var latNode = xmlDoc.DocumentElement.SelectSingleNode("/GeocodeResponse/result/geometry/location/lat");
               var lngNode = xmlDoc.DocumentElement.SelectSingleNode("/GeocodeResponse/result/geometry/location/lng");
   
               Row.Latitude = decimal.Parse(latNode.InnerText);
               Row.Longitude = decimal.Parse(lngNode.InnerText);
           }
           response.Close();
       }
   
   

The above methods works well, but if you are looking for some long list of address data conversion, one thing to mention is that google imposes a limit on number of request one API key is allowed to make (currently it stands 2500 free request allowed, daily).

But more can be read about the same on this link Google API Usage Limits

As an add-on bonus, I have also scripted the similar logic in a stand-alone excel-vba code available in the download pack along with this post.

Download Solution Files

Hope it helps !!!