Bridge The Gap between Bing and Google Maps using SSIS
SSIS Package Development:
The below steps show how to develop an SSIS package that implements the concept discussed above.
1) Create a new SSIS project using the Business Intelligence Development Studio (BIDS) and give it a relevant name. Add a user variable named “UpdateCommand” from the control flow with package level scope.
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 it “Address”. Configure it to use the connection we created in step-1 and read data from the Address table.
4) Add a Script transformation and name it “Geocode”, set the type as “Transformation” and the Scripting language as Visual C#. Make sure all input fields are set to READ-ONLY mode in the script environment. We also require the “UpdateCommand” variable in READ-WRITE mode in the script environment. Configure the script transform accordingly.
5) Add this code to the script. In the code, we are making a webrequest to the Geocode URL of Google Maps, and embed the parameters as query string parameters. The parameters to focus on are “address”, “sensor” and “key”, which are the minimum requirements for google maps web services to process the request. The address parameter can be passed as text based address information, the sensor parameter specifies whether a sensor device like a GPS navigator consumes this data and the key parameter specifies the id of the requesting application. We have selectively used the web url which returns the response in “XML” format.
After collecting the response, we extract the latitude and longitude information and use these to create a Point geometry using the STGeomFromText method of the geography datatype. We create a string of update commands that would be executed to update the “Location” field for each record and assign it to the “UpdateCommand” variable in the PostExecute method of the Script transformation.
6) Switch to Control Flow, add an Execute SQL Task successive to the Data Flow Task and configure it to use the “UpdateCommand” variable. Execute the package.
If all the steps are followed correctly, you should find that the values in the “Location” field have been updated for all the records in the address table.
Summary: We made a geocode request to Google Maps Geocode web service, extracted the geographical co-ordinates, mapped it to a Point geometry and stored the same values in a field of geography datatype.
Geographical coordinates from the same field can be extracted, and used to make reverse geocoding request to any service provider like Google Maps, Bing Maps or others. So by storing the data in a spatial field, we can keep the data in a consumable format for the applications that consume spatial data. Also by extracting required pieces of information out of the value stored in the spatial field, we can use the same for requesting a variety of services by a variety of web based spatial service providers.
Part-I of BI-Satellite project demonstrated how to use Bing Maps with SSIS and this article demonstrated how to use Google Maps with SSIS, and by coupling the data provided by these service providers in spatial data types, the gap existing due to the different data formats used by Bing Maps and Google Maps can be made transparent to applications.
Pages: 1 2