Just imagine: you have a small list of latitude and longitude coordinates and you need to find the nearest street address for those locations. How would you go about it? In this blog post I share how I built an Alteryx macro for this exact purpose, making use of the Nominatim API.
First of all, the process is called ‘reverse geocoding’. There are several paid online services for this, as well as some freemiums (first n requests are for free, excess of quotum requires payment). There are some free services as well, however, most of these require you to sign up in order to acquire a free API key. And you don’t want to hand over email and other precious info for everything free, right?
Enter Nominatim. This is a search engine for the free OpenStreetMap data. No sign up required. In Alteryx, one can connect to the Nominatim API using the Download Tool to use this functionality in a workflow. Be mindful, though: this free service should not be abused. Please refer to their official documentation and usage policy to determine whether your use case is valid. Most important regulations are: no bulk requests, and a maximum of one request per second.
The macro can be used to select two columns from an incoming data flow. It then uses these columns for latitude and longitude coordinates and queries the Nominatim API. The output is a table that consists of a list of latitude and longitude, city/town/village, postal code, road and house number. Click on the macro and select the column names in the configuration window:
How does the macro work on the inside?
To allow the user to select which fields to use for latitude and longitude, two Drop Downs are populated with the field names from the incoming data flow:
The connected Action Tool applies the field selection to the Select tool parameters:
The Select Tool selects the fields that are to be renamed to ‘latitude’ and ‘longitude’. No other fields pass into the macro.
Next, the query is constructed by adding the base URL as a field to every record and then concatenating the latitude and longitude values into the query:
For information on the search query and its parameters, look here.
In order to abide by the rules, the speed at which requests are fired at the API needs to be throttled down. For this, the Throttle Tool can be used. The Throttle Tool can be found in the ‘Developer’ tab of the tool palette. It can be configured to spit out x records per minute. In this case, use a value of 60, i.e. one record per second.
The Download Tool from the ‘Developer’ tab needs to be configured correctly, be sure to fill in any user-agent in the headers, or the response will be an error:
The query is configured such, that the result is sent back as a JSON file. The JSON Parse Tool from the ‘Developer’ tab can be used to parse the result:
After this the Text To Column Tool is used to split the header column:
Result:
Next, the first header column is used to filter on ‘address’ values:
Next, the results are cross-tabbed to obtain one result per row:
The query results from the Nominatim API differ depending on the input coordinates: city, town and village are different fields and might be absent if there’s no return value for this from any of your queries. To avoid errors in the workflow, a trick can be used: first, use a Text Input Tool to create the city, town and village fields with no values – just the headers.
Second, union this with the original data flow. Now, the fields will always be present, be it sometimes with null values.
To gain a clear output result, the results from city, town and village are combined into one column. This is not error prone, since if there’s a value in one column, there are no values in the other two columns:
Lastly, the necessary columns are selected using a Select Tool.
So there it is: a way to do reverse geocoding in Alteryx for free, no strings attached. Be sure to check the configuration of the download tool. And please, adhere to the Nominatim user policy.
If you have any questions about the macro, please let me know.
Happy data blending!
I hope this blog I shared can be useful for you! If you have any more questions don’t hesitate to contact us.
In need of further explanations and help? Join our workshops and trainings or hire a consultant!
Header image from: unsplash.com. Screenshots from author.