Deprecated
Google has now stopped its Earth API and Maps API is a paid for API nowadays. Some capabilities have also been either removed or changed. Therefore, I had to remove all examples of VizMap applications I had created. I didn’t remove the entire topic as I thought some of the code may still be useful with some modifications. 

GeoCode addresses with Google Maps API

If you haven’t read about Bing rules on doing this, please do.
In this section we will look at what Google responds to a request for an address geoMapping. Using this data we will complete columns as defined in your sheet, and use a parameter sheet to describe how to treat the returned data. This all uses cDataSet to abstract data and make all this a trivial exercise.

Quick start

  1. Download the  Google Maps workbook (button above)
  2. Amend Columns A,B,C in the VenueMaster workbook to your data
  3. Hit one of the buttons on that page to geocode (with Google, Bing, yahoo , create a KML file or plot the addresses on a map using Google, Yahoo, Bing or OVI.
  4. Read how it works below.  You will normally not need to do any coding – just possible tweak a parameter sheet. For example here is all that is needed to Plot addresses on a google map or a Bing Map, where the argument is the name of a parameter sheet.

How to interact with Google MAPS API

Given an incomplete address, let’s look at how to ask Google Maps to clean it up, decode the response and organize it into country, state and other components.  In order to see what this is about, download and try the example.
Google provides a response from this URL , http://maps.googleapis.com/maps/api/geocode/json?address=xx, where ‘xx’ is the address.  In previous articles,  we already know how to deal with a jSon response from Google and how to populate Excel with the response.  The subject of this article is to decode the response. As you will see it is not an ‘exact science’, so the decoding process is highly configurable so I can tweak it over time and find out more about exactly how it works.  Remember that this is the first stage in plotting summarized data on a map, and as per the Google Terms and Conditions, you should not use this as a way to just clean up address data.
In this example we are going to populate a ‘customer master’ which has an unstructured address, with Google’s interpretation of its components.

Parameter Layout

The cDataSet capability makes getting parameters easy. The parameter sheet has 2 blocks. One that describes the key input fields, and another that  describes the treatment of the API response.
Key Input Fields
Two mandatory fields are required, an ID and the address to be decomposed. The first parameter block describes the column names you have chosen to give them.

Google Maps API parameters

Any additional column names present in your sheet that match any of the Column Names which have a component definition will be populated according to the rules given below – more details on that later.


geomap Response

Once deserialized, these are the component parts of a response to a request to geomap Google Headquarters. It should be straightforward to relate this to the parameters above. Note the special treatment though for ‘state’ and ‘fullkey’. Since various countries have different levels of administrative areas you can specify a mapping between country code and level, and fullkey means that the full key can be relied on to rely the required result.

Main Code

Here are the steps
  1. Create datasets of the Customer master Sheet, the decoding rules parameter block and the Fields parameter block.
  2. Check that the ID and Input address column exist.
  3. For each row in the Customer Master,  send a  Google API request with the unstructured input address
  4. Deserialize the response, and assuming status is “OK”, go off and decode each column according to the parameter rules.

Finding the appropriate values and populating

  1. Find the Country this address applies to in order to deal with any special country level issues, and from that encode the state (if its needed)
  2. For each cell that is required to be copied over, find the most appropriate match in the API response. Note this needs a recursive approach since we need to get deep enough into the structure to match the depth of the parameter component.

Summary

Although the complete code is given here for educational purposes, it is unlikely that it would need much modification since the structure and rules are configurable through the parameter sheet. This capability is really just a mashup of previously covered topics such as Data Manipulation ClassesExcel JSON conversion, and  Get Data From Google Docs . The next step will be to summarize the geocoded addresses so we can visualize the results as a Google Maps layer.
For further discussion, comments,   questions, feedback please visit our forum.
For help and more information join our forum, follow the blog, follow me on Twitter