Working with Spatial Data Part I – Geocoding Text-Based Spatial Data for Use In SSIS Packages



SSIS Package Development:

Once  you have completed the environment setup as instructed above.  To create the SSIS package:

1) Create a new SSIS project using Business Intelligence Development Studio (BIDS) and name it “BI-Satellite”.

2) Create an OLE DB connection to the database of our “Address” table.

3) Add a Data Flow task to the package, edit it and add an OLE DB Source Adapter, name this “Address”. and configure it to use the connection we created in step-2 and read data from the Address table.

4) Create two derived columns of the string data type using derived columns transformation and name them  latitude and longitude. Assign blank values to these fields, use the “Advanced Editor” and edit the length of these fields to a considerably large size. I assigned the size of “100″ although a length of 20 should be sufficient to accomodate latitude or longitude values. You might be wondering if  we should be using decimal or any numeric data-types, but there are CLR based functions which can be used with latitudes and longitudes as inputs in string format, so we need  keep these as strings for now.



5) Add a Script transformation and name it “Geocode”, select the type as “Transformation” and Scripting language as Visual C#. Edit the latitude and longitude columns and make them  writable, as shown in the below screenshot. Edit the script component, by right-clicking in the project explorer and selecting “Add existing item”. Select the proxy class “Geocode.cs” that we created earlier and then build it from the Build menu.
Add references to the System.Web.Services and System.Xml assemblies (do this by right-clicking the project explorer and selecting “Add Reference”). We now need to reference these assemblies to use our proxy class. 



6) Now add the code as you can see from the below snippet. I have used modified code from the Bing Maps sample application for .NET that ships with Microsoft samples, as the sample code  adds web-references to the project which creates a client and  is not exactly the same as adding reference in the Scripting environment of Script transformation. Accessing a web-service by creating a proxy is one of the most standard ways of accessing a web-service from SSIS Script transformation in my view. Build this code, and if all the steps are followed correctly, the build should succeed.

In the GeocodeAddress function, we are creating a GeocodeRequest object  and passing the key which we created in the BingMaps portal. Next,  we set the filter to return only results with the best precision since one request can result in multiple possible results if the textual location information is not precise. Then we create an object of the GeocodeService class, and make a call to the Geocode function, passing GeocodeRequest object as the parameter. We collect results of the this  in GeocodeResponse object and read the values of the latitude and longitude properties.

Finally, call is made  to the “GeocodeAddress” function for each row and sets values of Latitude and Longitude fields from the values retrieved from this function.



using System;

using System.Data;

using Microsoft.SqlServer.Dts.Pipeline.Wrapper;

using Microsoft.SqlServer.Dts.Runtime.Wrapper; 

[Microsoft.SqlServer.Dts.Pipeline.SSISScriptComponentEntryPointAttribute]

public class ScriptMain : UserComponent

{  

    public override void PreExecute()

    {

        base.PreExecute();

        /*

          Add your code here for preprocessing or remove if not needed

        */

    } 

    public override void PostExecute()

    {

        base.PostExecute();

        /*

          Add your code here for postprocessing or remove if not needed

          You can set read/write variables here, for example:

          Variables.MyIntVar = 100

        */      

    } 

    public override void Input0_ProcessInputRow(Input0Buffer Row)

    {

        /*

          Add your code here

        */

        string latitude = ”", longitude = ”";

        GeocodeAddress(Row.AddressLine1 + ”, “ + Row.AddressLine2 + ”, “ + Row.City


 , ref latitude, ref longitude);

        Row.Latitude = latitude;

        Row.Longitude = longitude;

    } 

    private void GeocodeAddress(string address, ref string latitude, ref string longitude)

    {       

        string key = @”****************************************************”;

        GeocodeRequest geocodeRequest = new GeocodeRequest(); 

        // Set the credentials using a valid Bing Maps key

        geocodeRequest.Credentials = new Credentials();

        geocodeRequest.Credentials.ApplicationId = key;

        //geocodeRequest.Credentials.Token = key; 

        // Set the full address query

        geocodeRequest.Query = address; 

       // Set the options to only return high confidence results

        ConfidenceFilter[] filters = new ConfidenceFilter[1];

        filters[0] = new ConfidenceFilter();

        filters[0].MinimumConfidence = Confidence.High; 

        // Add the filters to the options

        GeocodeOptions geocodeOptions = new GeocodeOptions();

        geocodeOptions.Filters = filters;

        geocodeRequest.Options = geocodeOptions; 

        // Make the geocode request

        GeocodeService geocodeService = new GeocodeService();

        GeocodeResponse geocodeResponse = geocodeService.Geocode(geocodeRequest); 

        if (geocodeResponse.Results.Length > 0)

        {

            latitude = geocodeResponse.Results[0].Locations[0].Latitude.ToString();

            longitude = geocodeResponse.Results[0].Locations[0].Longitude.ToString();

        }

    } 
 

}


7) Returning back to the package, now we need to add a destination. We just need to view to geocoded values, so simply add a Multicast transformation which can act as a destination for our package.
Continues…

Pages: 1 2 3




Related Articles :

No comments yet... Be the first to leave a reply!

Software Reviews | Book Reviews | FAQs | Tips | Articles | Performance Tuning | Audit | BI | Clustering | Developer | Reporting | DBA | ASP.NET Ado | Views tips | | Developer FAQs | Replication Tips | OS Tips | Misc Tips | Index Tuning Tips | Hints Tips | High Availability Tips | Hardware Tips | ETL Tips | Components Tips | Configuration Tips | App Dev Tips | OLAP Tips | Admin Tips | Software Reviews | Error | Clustering FAQs | Performance Tuning FAQs | DBA FAQs |