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.
]]>