Bulk Reverse Geocoding Macro for Alteryx 7.0 Beta!

Happy Holidays Alteryx Users!

Can I see a show of hands of those who are beta testing Alteryx 7.0?

If you are, then you already know about the great new features coming up in the next release. If not, check out these posts by Adam Riley (Chaos Reigns Within), Ned Harding, Linda Thompson and Rob McFadzean among others at the Alteryx Engine Works Blog. One post in particular by Ned discusses the new Parse XML Tool, which enables you to parse through an XML file and extract the data. This tool by itself opens up a whole host of possibilities accessing the numerous "free" API's (Application Programming Interfaces) available that pass back data in XML format - specifically some of the Google Map API Web Services used to create the macro I'll describe in this post.

http://geolocator.allaboutgis.net/

In this instance, using the Google Map Geocoding API, I was able to create a Bulk Reverse Geocoding Macro in the Beta release. Kudos go out to Cameron Steele of Intalytics and Jed Klink of McDonald's who provided much input on the end result, and thanks to Brett Gottdener at Alteryx on getting the batch macro configured correctly. It all started one day when Cameron and I were brainstorming about a concept. Working with the API, HTML 5.0 and JavaScript led to the creation of a world-wide Geocoding \ Reverse Geocoding Mashup called "Geolocator", of which you can view at this URL (image at right).

The GeoLocator Mashup provides basic geocoding functionality if you enter an address, or if you pick a point on the map, returns an address corresponding to that point (or reverse geocoding). But you need to know where you want to pick on the map to obtain your result. In talking with Jed about this mashup, which can also export the results of the geocode \ reverse geocode process as a csv file using a bit of jQuery, he wondered if it could do bulk reverse geocoding. Still working on a mashup for that, but it got me thinking about some of the new tools in the Alteryx beta. Sooo, back to the macro and how it functions...

To make the macro work, you input a list of Latitude and Longitude coordinate values. These are passed to the Google Maps Geocode Web Service using the Run Command Tool and cURL, which returns the results for each location. The results are passed to the Parse XML Tool, and for simplicity sake, I configured the tool to only parse for a specific Child element (formatted_address), or the first address returned per coordinate pair. Depending on the accuracy of the coordinates you pass and what country they are in typically defines the level of accuracy. To see a sample of what the Google Map Geocoding Web Service returns, click this link:

http://maps.google.com/maps/api/geocode/xml?latlng=38.976302,-94.665756&sensor=false&callback=parseme

To format the data after it is parsed, I used the Formula and Cross Tab Tools. Parsing the formatted_address element returns Address, Neighborhood, City, County, State, Zip Code, and Country for each record, if available.

One caveat though - use of the Google Map Geocoding API is limited to 2,500 geolocation requests per day, with Premier users being limited to 100,000 requests a day. If you exceed these amounts you will receive a "Request Denied" message instead of an address, so keep track of your record counts. Note that this macro was made primarily for the United States. The formatting will need to be modified for use on other countries.

Once Alteryx 7.0 is released, I'll provide the macro to Alteryx for posting on the Module Exchange for your use. If you are an Alteryx 7.0 Beta tester, let me know and I'll send the macro your way for further testing.

Shameless plug coming up! All About GIS is now offering Alteryx Training! We offer both distance learning as well as onsite training, for beginning users to power users. E-mail me for more details and book your training today!

Till next time, enjoy!
Ron @ allaboutgis.net