Geocoding in a Pinch: Power Query and the Google Maps API

A colleague of mine recently asked if I knew of an easy way to get latitude and longitude coordinates from city and state information.  Of course I do!  There are plenty of ways, and many of the best ones involve a service like Melissa Data and less development work.  For little effort and usually a price, you can obtain a reference table for cities or tables for zip codes or census tracts that include latitude and longitude alongside the primary city at that grain.  For the ambitious though, you can access an API and geocode your data using a variety of common languages.  It can even be done with M in Power Query.

Depending on the use case, an API may or may not be a good fit.  Weighing different methods is not in the scope of this post though.  I simply want to pass along a quick way to geocode data from city/state or address/city/state using Power Query for a handful of records.  To do this, I’ll connect to the Google Maps API because the address information is passed as a parameter in the URL.  As an added benefit, the code also provides an example of how to retrieve values using JSON.

NOTA BENE: The method outlined here is not meant for large scale operations–hence I want to emphasize that this post’s title states “in a pinch”.  The free version of the Google Maps API only allows 2,500 requests in a 24 hour period and restricts requests to 5 per second.

In addition to the limitations of the free tier Google Maps API, there are some additional performance considerations, so plan ahead.  API calls can be costly, so avoid unnecessary requests.  For example, consider your grain.  If you have 10,000 individual addresses that are all in the same city–but you only want the coordinates for the city center point–you can make one request instead of ten thousand.  In cases like this, it would be inefficient to use the original table due to the many addresses located in the same city.  The better method here would be to build a separate table with distinct city values first, make API requests for the records in that city table, then join back to the original table.  Plan ahead!

Now for some M…

1. Start with your source.  Out of convenience, I am using a small table in Excel that contains two separate columns for city and state.  Remember that you are not limited to this grain–you could work with individual addresses too.  Load the source data into Power Query.
   
2. The method (one method…) used to access Google Maps involves appending an address parameter value to the end of the base URL.  You do not need to worry about URL encoding for spaces or commas: http://maps.googleapis.com/maps/api/geocode/json?address=SampleAddress
3. Add a new column in Power Query that appends the city and state column to the base URL.
   
4. At this stage, I am ready to send my web request.  Because the response from the web request will be JSON, I need to wrap my Web.Contents() function with Json.Document() instead of Web.Page().
   
5. The latitude and longitude are deeply nested in the JSON, but I can click into the first record using the UI to find the path down to both values.  At this point, Power Query has generated a lot of M code that shows how to get the data that I need.  The problem with clicking through the UI is that it only gets me the coordinates for the first location.
   
6. I can use this structure as a start though, condense that into one line, and remove the extra code. In this case, the formula for latitude appears below.  Note that “GetJson{0}” from the first record has been replaced with “each GetJson{_}” to enumerate.
   
7. Add a second column using the same procedure for longitude, and I’m done!  Here’s the full code:

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    BuildUrl = Table.AddColumn(Source, "Custom", each "http://maps.googleapis.com/maps/api/geocode/json?address=" & [City]  & "," & [State]),
    RenameUrlColumn = Table.RenameColumns(BuildUrl,{{"Custom", "GoogleMapsGeocodeURL"}}),
    GetJson = Table.AddColumn(RenameUrlColumn, "JsonDoc", each Json.Document(Web.Contents([GoogleMapsGeocodeURL]))),

    Latitude = Table.AddColumn(GetJson, "Latitude", each GetJson{_}[JsonDoc][results]{0}[geometry][location][lat]),
    Longitude = Table.AddColumn(Latitude, "Longitude", each Latitude{_}[JsonDoc][results]{0}[geometry][location][lng])
in
    Longitude

While it’s not the best method available, it is straightforward to geocode data for a quick lookup using Power Query.  Given a small list of cities and states, I have the data, and it took less than five minutes!  For requirements in the range of a handful to a few hundred records, that’s a great time saver. Simply remember the limitations of the free API, and do not forget to plan ahead!