How to enrich your account data with latitude, longitude and geography data?

In SQL 2008 R2 one of the new interesting visualization features are the maps. To use the maps for you accounts you need to enrich the account data with geography data. In this blog I will explain how you can enrich your account data with Google geography data by making use of a SQL Server integration packages. To enrich your account data you can make use of Google or BING Maps. I uses Google because:

• Performance to enrich your account data with geography data is much better than Bing Maps.
• With Google I can use the GUID, which is the key, in my table as the key. In BING Maps the GUID is not recognized, you need to use an Integer.

To enrich your data via Google you need to have an API key which you can get when you sign up for the Google Maps API. Please read the terms and conditions in which situation you can use this API key. Otherwise you need to use the Google Maps API Premier.


This example is based on an Exact Globe or Exact Synergy database in which the table Cicmpy exists. The Cicmpy table is used to store all account information. In case you want to update account data from another table, some minor extra changes need to be done.  Please execute downloaded script: CreateCicmpyGeoUpdate.SQL This script will add some columns to the Cicmpy table and will create a table to update the geography data by the SSIS package.

1. Download the CicmpyGeoUpdate.rar Extract the CicmpyGeodataGoogleMaps.dtsx package in folder on your server. The package need to be configured for your environment.
2. Create a new SSIS project in Microsoft Visual Studio 2008.
3. Add the CicmpyGeodataGoogleMaps.dtsx package to your project.

• In the solution explorer, right mouse click on SSIS packages, add existing package)
• Choose File System as package location and browse to the location where you have downloaded CicmpyGeodataGoogleMaps.dtsx
• Open the package by double clicking on the package name in the Solution Explorer.

4. Configure the Connection Manager. The connection manager connection is used by the package to know in which database he needs to enrich the account data.

• Select GRAA1005-2.ExactGeoTest, right mouse, Edit.
• Specify your SQL Server and Database name
• Press Test Connection to test if you can connect to the database.

5. Configure the package
   

• Open the dataflow task. Press on the Data Flow tab.
  






• Configure the OLE DB Source connection. Select OLE DB Source, right mouse click, Edit. Check if the correct OLE DB connection manager is selected.
  

• In case you are using another table for your account data you need to rewrite the SQL query. Change your table and columns but use the AS Address, AS ZIPCode for the column names of the result set. These columns names are used in the script to recognize the street, zip code etc.
• Install the Google Maps API key in the Script. Edit Script Component. Select Script Component, right mouse click, Edit.
• Press the Edit Script button.
• Open the GeoCode.vb and paste your Google Maps API key between the " "
   

• Save the script and build the project and close the project.
• Configure the OLE DB Destination. Check the configured OLE DB Connection Manager. Select 'Table or View fast load' as Data access mode and select CicmpyGeoUpdate table 

5. Save the package and you can start the package.
6. The package will insert all geography data in the table CicmpyGeoUpdate with the GUID of the account as the key. Execute next script to update the account table Cicmpy with all geography data.
   
   UPDATE Cicmpy
   SET GeographyCol = Geography::Point(CGU.Lat, CGU.Long, 4326),
   Lat=CGU.LAT,Long=CGU.Long,
   Accuracy = CGU.Accuracy
   FROM Cicmpy C
   INNER JOIN CicmpyGeoUpdate CGU ON C.Cmp_wwn = CGU.cmp_wwn
   WHERE CGU.Lat is not NULL
   
7. Everytime the package is executed, records are added to the CicmpyGeoUpdate table. After a succesfull update of the account data in step 6 you can truncate the table CicmpyGeoUpdate.
   
   Truncate table CicmpyGeoUpdate
   
   Enjoy using geography data. Later on I will post a blog about using geography data with SQL 2008 R2 Reporting Services.