Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Engine Works

Under the hood of Alteryx: tips, tricks and how-tos.
ChadM
Alteryx Alumni (Retired)

10/28/2015 Edit: The app mentioned in this post is no longer available as it references a service that is no longer supported. The Alteryx Content team is currently developing international geocoding solutions that will be available soon.

 

TomTom Logo

A few months ago I was contacted by another Alteryx associate that had an interesting client request.  The request was to use Alteryx in tandem with TomTom’s new Global Geocoder in order to take advantage of their worldwide geocoding capabilities.  Naturally, I was intrigued, and the following are the results.

TomTom is one of the world’s leading suppliers of location and navigation products.   They provide a host of solutions including portable navigation devices, in-dash infotainment systems, fleet management solutions, and maps & data services.  In this case, we are taking advantage of their global geocoder as Alteryx only offers geocoding for the U.S. and Canada. 

 

API License Key & Developer Portal

In order for the geocoding API to work properly, one must obtain an API key from their developer portal.  For a free developer key, TomTom offers a trial period that covers 90 days or 50,000 geocodes (at the time of this writing), whichever comes first.   Sign up for this key at the Developer Portal here.  The Developer Portal is TomTom’s source for all information regarding the geocoder.  Users can also contact TomTom to purchase one of several tiered geocoding packages if you need something more specific to fit your needs.

 

Once the API key has been obtained, we simply need to plug it into the API, then make the correct call.  There are two methods used to call the geocoder. 

  1. Single point location – The single point location is used to geocode one location at a time.  The geocoder has the potential to geocode an address, point of interest (such as the Grand Canyon), or place (such as The Louvre).
  2. Batch Geocoder – The batch geocoder is used to geocode many addresses at once.  Currently, the batch geocoder is only able to handle 100 addresses at once, but this is set to increase in the future.

 

Using Alteryx with the Geocoder for a single point

For a single point location geocode, Alteryx needs no assistance.   Many of you may be aware of a very powerful developer tool in the Alteryx arsenal called the Download Tool  DownloadTool.  This tool allows a user to place a URL into a data stream, then at runtime Alteryx will download all HTML information in that URL and output the information to your data stream.  This is especially helpful for situations like this where the single point location API is nothing more than a URL.

Example: I need to geocode the Alteryx Boulder address at 3825 Iris, Boulder CO.  Using the tags I’ve found using the Developer Portal, I create my URL to pass to the Alteryx Download tool. 

 

The URL looks like this:

https://api.tomtom.com/lbs/geocoding/geocode?ST=3825&T=iris&L=boulder&AA=co&PC=80301&key=gt567nxmxcf...

 

Now here is the URL broken down:

  1. (https://api.tomtom.com/lbs/geocoding) specifically calls the TomTom Geocoder.
  2. (/geocode?) refers to a single point address.
  3. (ST) Refers to the street number.
  4. (T) Refers to the street name.
  5. (L) Refers to the city.
  6. (AA) Refers to the state or province.
  7. (PC) Refers to the postal code.
  8. (Key) Refers to my developer API key.

Applying this to Alteryx, we need to use a series of tools:

 

Text Tool: This tool contains a field I named called URL, with one row of data containing the full API call listed above.

Download Tool: This tool takes the API URL from the above text tool, grabs the data from the Geocoder, then outputs to XML format.

XML Parse Tool: This tool takes the returned XML values and parses them to a standard table format so we can further analyse the data.

Browse Tool: Review our results.

 

The final output is a parsed record that contains all of TomTom’s geocoded fields, including a score and confidence field that helps you to determine whether or not the record has been geocoded to your needs.

 

Using Alteryx with the Geocoder for multiple points

For a single point geocoding job, the Download tool is perfect.  Unfortunately, it is not set up well for geocoding multiple locations at once.  Sure, we could create a batch macro, but then you are making a single call to TomTom’s Geocoder for each record.  This is not only incredibly inefficient, it also dings you for each record.  For a true batch situation, we need to call on our old friend cURL to handle the process.

For those that do not know, cURL is a tool that allows a developer to scrape information from a website at the command line level.  Naturally, we will use the RunCMD tool  within Alteryx to handle cURL and the API call.

 

RunCMD is a bit different than other tools in that it is specifically used to call a process outside of Alteryx.  The setup for RunCMD is as follows:

 

Write Source: This is a file created by Alteryx just before RunCMD executes.  This file is then used by the program called within the RunCMD tool.  In this case, we are writing out an xml file that TomTom’s Geocoder can process.

 

Command: This is the name of the program to run.

 

Command Arguments: These are all of the parameters required in order for cURL to execute the batch geocode properly.  This is the most complicated part of the process as TomTom has very specific arguments that need to be used.

 

Read Results: The final output that the RunCMD tool will create.  Alteryx then treats this as it’s input file for the rest of the data stream. 

In the case of the TomTom Batch Geocoder, the results are read in as XML and output as XML.  Since Alteryx has the capability to read XML directly, we have no issue with results returned by TomTom. 

 

One final note is that this portion of the process is not as smooth as using the Download tool for a single location.  I say this as if for some reason there are no records returned, the TomTom Batch Geocoder will not return any results.  This is actually not completely uncommon in many other processes, and luckily we have an easy workaround in creating a “dummy” text input file with only headers.  We then join this input file to the original data stream, forcing the headers from the dummy file to output first.

The final caveat is if TomTom updates the fields being output, this will also need updating.  

 

Based on my experience, the TomTom Global Geocoder is an extremely powerful tool on it's own.  Combining Alteryx to create an easy solution accessible to the public at a reasonable cost creates limitless possibilities.  

 

 

Comments
JohnKTran
6 - Meteoroid

Wow! Is there a toolset or macros for this? 

 

Would love to be able to use a global geocoder.

 

Especially, since I am trying to calculate the distance between companies in different countries.