For reasons unknown, Russell will write about himself in the third person today.
Russell was working with data which had some geospatial attributes (Address, City, State, Zip) but wanted his viz to be able to render down to street level. Russell decided it was time to write a tool that the average Joe could use to get the latitude and longitude of a location given an address. That Joe is likely a Tableau user who could lean on Data Blending to get this newly generated lat/lon info into their viz.
His Requirements:
- The geocoding service must be free
- The service must be called from inside a tool just about everyone already owns.
Russell decided on writing some code against a Yahoo API from inside Excel. But lo and behold, Juice Analytics beat him to it! They not only beat him to it, they did so about a million years ago with their Juice Analytics Excel Geocoding Tool.
Russell was overjoyed until he discovered that said tool used a depreciated Yahoo geocoding service that he could no longer get access to. Russell also had lots of rows he wanted to geocode, which the code couldn’t handle. He found it also didn’t support Excel 64-bit.
This story has a happy ending. Russell has updated the Geocoding Tool so that:
- It leverages a supported Yahoo geocoding API that you can subscribe to for free
- It’ll geocode > 65K rows
- You can use either a 32-bit or 64-bit version of Excel 2007 or 2010
Russell did this rather quickly and left 95% of what was already in the workbook in place. Any problems or errors you run into are likely Russell’s fault – not those of the fine folks at Juice Analytics.
Here’s what you’re going to need to do:
1. Download either the 32-bit or 64-bit version of the workbook for Excel 2007 or Excel 2010 (end of the post)
2. Read this tutorial on how to modify Excel’s settings to allow the code in the workbook to run: http://production-scheduling.com/run-macros-excel-2007-2010/. Until you follow these instructions you can click on the “Geocode” buttons all you want, and nothing will happen. I promise.
3. (Optional geeks only step) Read about the API here: http://developer.yahoo.com/geo/placefinder/
4. (Pain in the ass step). You must get an ID from Yahoo that you’ll plug into the workbook. The process is fast and you’ll get your ID a few seconds later – but no one likes to fill out forms.
- Go here: https://developer.apps.yahoo.com/dashboard/createKey.html
- Give your Application an Application Name – anything will do
- Change Kind of Application to Desktop/Client
- Add a Description if you wish, remove the URL to the FaveIcon
- Leave the Access Scope option as it is.
- Accept the Terms of Use.
That’s it. The fine folks at Yahoo will get you a public key and a secret something-or-other key. Save both somewhere safe, although you’ll only need the first (public) key.
After that, open up the workbook you’ve downloaded and paste your Yahoo Key into the “Yahoo ID” cell on the first sheet.
On the second sheet, you’ll plug in the addresses you want to geocode. Russell and Juice Analytics have left you a couple sample rows to play with.
A couple notes:
Russell didn’t even look at the Google Earth stuff. Maybe it works, maybe it doesn’t. Google Earth just isn’t my thing. Sorry.
Russell re-worked the <field> in Juice Analytics’s version of the worksheet and ended up with a “Precision” field which communicates “how well” the API did geocoding the particular address. Here’s what the number you’ll see means:
…and you’ll find the Excel workbooks (x86 and x64) in the folder below. Select the file you want (don’t click on the file name itself or the workbook will render in your browser), and on the right side of the browser you’ll see options to view, download, etc…
