Bridge The Gap between Bing and Google Maps using SSIS

Overview: Business Intelligence (BI) is a much known term in comparison to the term Spatial Intelligence (SI). Spatial Intelligence is not limited to just space agencies, in fact it’s used day to day by many of us. A simple example can be analyzing usage of your blog from different parts of the world. By analyzing the tracking report on a geographical map from services like Google Analytics, one would be able to derive that which part of the globe, in what magnitude, is interested in any particular topic on one’s website. Geocoded data is one of the standard formats for making free form text based location data consumable by applications that are powered using spatial data. Web based map services providers are not just limited to Bing Maps or Google Maps, and there are more players in this league. Also Geocoding and Reverse Geocoding are not the only services used by applications that consume geospatial data. In Spatial Intelligence parlance, there are a variety of services available, and it’s just that in business intelligence focused areas, we find a good use of geocoding based services. Those who are curious to learn about the web based map service providers and a comparison of features and services offered by them, can read a very nice comparison available on wikipedia from here. Objective: In my viewpoint, IT cannot work in isolation and in the same way, organization need to use MS BI tools with spatial service providers other than Bing maps. Google maps has a similarly large user base, and considering different perspectives, two factors are very important in keeping MS BI and Google maps in alignment: 1) MS BI should be able to consume services provided by Google Maps for fuelling applications that deal with Spatial Intelligence. 2) Google Maps uses KML (Keyhole Markup Language) format and Bing Maps support GML (Geography Markup Language) format to the best of my knowledge. If MS BI tools are to be used with both these services and data formats simultaneously, a intermediary translation framework would be required for MS BI Tools to benefit from the services provided by Bing and Google (both) as service providers. In this article, our objective is to use SQL Server Integration Services (SSIS) as a medium to facilitate the above mentioned points. Key Factors influencing Design: Before we start the implementation exercise, there are certain points to keep in view which would help to understand the design decision behind this implementation approach. 1) Geocoding related services like Geocoding and Reverse Geocoding are primarily used for encoding or decoding spatial data in SQL Server. These services are provided by both Bing and Google. For those who are not aware of geocoding and reverse geocoding, geocoding is the process of converting text based location information to co-ordinates based on any spatial reference system e.g. latitudes and longitudes. And reverse geocoding is the process of deriving text based address information from those co-ordinates. 2) If we store a response received from Bing or Google Maps in a common structure based on the same spatial reference system, it will implicitly make the data compliant for consumption by both these services. In simple words, say we have a field of geography data type, we extract latitude and longitude values by making geocoding request to any one of the providers and store this data in the field of geography data type, we can extract the same values and make a reverse geocoding request to another service provider. By this the gap of different data formats used by different service providers would be bridged and applications can benefit with the option of using different service provider for catering the spatial servicing needs of their data. 3) Google Maps API is a web based service and not a webservice, which means functionally it is a service provided over the web but technically it is not available in the form of a webservice. So there’s no WSDL (Web Service Description Language) associated with it. Effectively one cannot make request to these services in the way we make requests to webservices. For example, Google geocode service is implemented as a HTTP based web request and we pass parameters in the form of query string parameters like parameters are passed in any web based querystring. 4) Google Maps web services return response in JSON or XML format, depending upon the format specified in the request. There are different dedicated URLs for each response type. XML is one of the better choices as it’s easy to store and parse by SQL Server and SSIS. A response in JSON (Javascript Object Notation) format is more suited for parsing with Javascript on a web page. Environment Setup: We will develop an SSIS package to make a geocode request to the Google Maps geocode web service, extract the latitude and longitude from  the response, create a Point object using these values and then store these in a field of the geography data type. For the this  we need to be ready with two artifacts. 1) To use Google Maps web services, we need to sign up for the service. Upon registration, we will  be provided with a unique key that allows the service to identify the user of the request. So sign up for the this  from this here, and save your private key which acts as your client id. 2) We need a dataset that we use to make a request for geocoding. Create any table that would contain a  location address unique enough to test for our demo application. For the purposes of this simple demonstration, I will use the dataset we created in Part II of BI-Satellite project as shown in the below screenshot, where the Location field is of geography datatype.