Wednesday, 14 September 2016

SSIS: Auto mapping Latitude and Longitude for an Address using Google API

If you are looking to generate Latitude, Longitude and other information related to a particular address automatically through google Maps api, given that you have an address (obviously) with the help of SSIS, you are at safe hands.
This logic is courtesy of MSDN blogs and enhanced in my way to keep things smooth and very generic
What all would you need
1)      Address for which you want to find the latitude and longitude
2)      Source and destination databases (can be same and can be different, I have kept it same. However ill show how can you add a different destination database)
3)      Visual Studio (Professional, community, enterprise) with SQL Server Data tools installed

Let’s begin

1)      Assuming that you have your databases ready with addresses as shown below: All I want to do here is take the streetNo, name, city ,state , postalcode (more the better and accurate) and fill the lat and long fields
2)      Now go ahead and create an SSIS project, which is New project in Visual Studio and selecting Integration Project as the template from SSDT tool that you have installed
3)      Drag a data flow task and name it the way you want
4)      Double click the data flow task and you will enter into the control flow section of the package
5)      Add an OLD DB source component into the control flow (source can be anything you want here Excel,csv, txt,any other database: select the respective component however)







6)      Add a connection in it and write a SQL query to select the table that we have as shown below


Query is as shown below:
SELECT  locationid,streetno+' '+streetName+' '+city+' '+state as address,latitude,longitude
FROM locations
WHERE  (latitude is null or longitude is null)





7)      We are concatenating all the fields as address as seen in the above query. This is an important part and remember your ‘AS’ field here. For me it is ‘address’
8)      Ok the fun part begins here, if you have no clue about google api , go ahead and paste this url in your browser

You will probably see an XML output with all the information you required for the address that you appended at the end of the URL
Now what we are trying to achieve is send this url with our address appended and get back the result. Sound difficult and yea it is you never worked on C# before. Basically our next step

Note: You can skip the tag &sensor=false from the URL since it is obsolete now. You might have to use Google API key and pass as a URL if you have large number of addresses to be resolved

9)      Add a script component to the control flow and connect to the OLE DB source component
10)   Double click the script component and go to Input columns and select all of them to be ReadWrite column as ‘Usage Type’. This is important. If you are not able to see the input columns, you probably not connected to the OLE DB source component or your SQL Query has issue
11)   Once done, go ahead and click Edit Script component and it will open a new Visual Studio editor where we would add some code
12)   Add a new class to the solution (I cannot show how to add a class, you can figure out probably if you never worked on Visual Studio projects)
You can name the class as GeoCoding or something else. Change the namespace name to something meaningful, this will be used later to make a call from the main class (I am naming it as mapsAPI)

Add following code:

using System.Text;
using Microsoft.SqlServer.Dts.Pipeline.Wrapper;
using Microsoft.SqlServer.Dts.Runtime.Wrapper;
using System.Data;
using System.Web;
using System.Xml;
using System.Net;
using System.Threading;
using System.IO;
namespace mapsAPI
{
public interface IGeoLocation
    {
        string streetNo { get; set; }
        string streetName { get; set; }
        string city { get; set; }
        string state { get; set; }
        string postalCode { get; set; }
        decimal latitude { get; set; }
        decimal longitude { get; set; }
    }

    public struct Location : IGeoLocation
    {
        private string _streetNo;
        private string _streetName;
        private string _city;
        private string _state;
        private string _postalCode;
        private decimal _latitude;
        private decimal _longitude;

        public string streetNo
        {
            get
            { return _streetNo; }
            set { _streetNo = value; }
        }
        public string streetName
        {
            get
            { return _streetName; }
            set { _streetName = value; }
        }
        public string city
        {
            get
            { return _city; }
            set { _city = value; }
        }
        public string state
        {
            get
            { return _state; }
            set { _state = value; }
        }
        public string postalCode
        {
            get
            { return _postalCode; }
            set { _postalCode = value; }
        }
        public Decimal latitude
        {
            get
            { return _latitude; }
            set { _latitude = value; }
        }
        public Decimal longitude
        {
            get
            { return _longitude; }
            set { _longitude = value; }
        }

    }

        public class geocode
        {
            const string _googleUri = "https://maps.googleapis.com/maps/api/geocode/xml?address=";

            private static Uri GetGeoCodeURI(string Source)
            {
                Source = HttpUtility.UrlEncode(Source);
                Source = _googleUri+Source + "&key=urkey";
                return new Uri(Source);
            }


            public static Location lookup(string Source)
            {
                //WebClient wc = new WebClient();
                Uri uri = GetGeoCodeURI(Source);
                //WebProxy proxyObj = new WebProxy(uri);
                //proxyObj.Credentials = new System.Net.NetworkCredential("username", "password");
                //wc.Proxy = proxyObj;

                WebRequest request = WebRequest.Create(uri);
               

                Location location = new Location();
                try
                {
                    WebResponse response = request.GetResponse();
                    Console.WriteLine(((HttpWebResponse)response).StatusDescription);
                    Stream dataStream = response.GetResponseStream();
                    StreamReader reader = new StreamReader(dataStream);
                    string geoCodeInfo = reader.ReadToEnd();
                    reader.Close();
                    response.Close();
                    //string geoCodeInfo = wc.DownloadString(uri);
                    XmlDocument xmlDoc = new XmlDocument();
                    xmlDoc.LoadXml(geoCodeInfo);
                    string status = xmlDoc.DocumentElement.SelectSingleNode("status").InnerText;
                    XmlNodeList nodeCol = xmlDoc.DocumentElement.SelectNodes("result");
                    foreach (XmlNode node in nodeCol)
                    {                           
                        location.latitude = Convert.ToDecimal(node.SelectSingleNode("geometry/location/lat").InnerText,System.Globalization.CultureInfo.InvariantCulture);
                        location.longitude = Convert.ToDecimal(node.SelectSingleNode("geometry/location/lng").InnerText, System.Globalization.CultureInfo.InvariantCulture);
                        foreach (XmlNode addrcomp in node.SelectNodes("address_component"))
                        {
                            if (addrcomp.SelectSingleNode("type").InnerText == "street_number")
                            {
                                location.streetNo = addrcomp.SelectSingleNode("short_name").InnerText;
                            }
                            if (addrcomp.SelectSingleNode("type").InnerText == "route")
                            {
                                location.streetName = addrcomp.SelectSingleNode("long_name").InnerText;
                            }
                            if (addrcomp.SelectSingleNode("type").InnerText == "locality")
                            {
                                location.city = addrcomp.SelectSingleNode("short_name").InnerText;
                            }
                            if (addrcomp.SelectSingleNode("type").InnerText == "administrative_area_level_1")
                            {
                                location.state = addrcomp.SelectSingleNode("short_name").InnerText;
                            }
                            if (addrcomp.SelectSingleNode("type").InnerText == "postal_code")
                            {
                                location.postalCode = addrcomp.SelectSingleNode("short_name").InnerText;
                            }

                        }


                    }

                    return location;
                }
                catch (Exception e)
                {
                    Console.WriteLine(e.Message);
                    return location;
                }
            }

        }
    }



This is it. Go back to the main class, open the namespace section and add the namespace we created in the Geocoding class. I will write using mapsAPI;
Go to the Input0_ProcessInputRow(Input0Buffer Row) method and write the following code
mapsAPI.Location geolocation = mapsAPI.geocode.lookup(Row.address);
Row.latitude = geolocation.latitude;
Row.longitude = geolocation.longitude;
It is important to understand this code, here Row.something is basically coming as input from your OLE DB source, remember step 7, it will be different for you depending on what you name
We are storing latitude and longitude here for now. But you can enhance it to add many fields, code is already written. Location object has all the information you need to get more fields from the source to fill it

13)   Now save and close the editor. If you know programming you probably realized what are we doing with the code. In case you have some understanding and are not sure, add a debugger to the main object so that you can figure out how it works when you run the project
14)   Final step is to add a destination component, if you have the same source as your destination than you are probably updating the fields so you will add a OLE DB command component, however if you have a destination table then you can add a OLE DB Destination component or respective type of destination component you have to fill up
15)   In my case of a OLE DB command, here is the command I’ll write
update locations set latitude = ?, longitude = ? where locationid = ?
This is called as PreparedStatement where ? symbolizes fields will be filled later. Go to the Column Mapping section and you can see for which ? you added you have respective Param component as shown below


Just map the respective fields and you are all set
Overall control flow will look like this


Run the package and enjoy.

Please add comments or questions below if you are facing some issues