Isochrones (or driving time analysis) macro for Alteryx

One of the analysis that I have been asked to do more often at clients during the last months is drivetime analysis. Basically to be able to understand the area that can be covered driving from an origin point A in order to analyse distribution capacity, stores area of influence, overlap of branch capacity, etc. The industries interested in this kind of analysis is very variate: transportation, retail, banking, healthcare, and others. Anyone that has a store or branch, a distribution center or a sales representative moving around can get interesting insights with this kind of analysis.

Alteryx has it’s own drivetime engine that works perfectly and that gives very accurate results. But with some clients that I’ve been working with, sometimes the usecase is very niche and doesn’t justify the expenditure or analysts need an example of the outcome to present to a customer before buying the engine, so I have developed a macro that uses Open Route Service to develop this kind of analysis.

Open Route Service

My colleage Dan Farmer shared with me the website https://openrouteservice.org. Open Route Service is a open source service (as you can guess by it’s name) that offers different API services for spatial capabilities. It’s free to use even for commercial use cases, and it has a limit of 2,500 queries per day and user. Anyone can register in their website in a few seconds, request a token to use the API and start using the different services they offer: route calculation, POI, geocoding, reverse geocoding, isochrones and time & distance matrix.

It also has an API playground section very easy to use that allows to see and test the different parameters you can use in each service and the query structure needed to use them.

How to use the API in Alteryx

During the last days I have been working on different macros to be used in Alteryx for some of the services Open Route Service provides. The first macro, available for free already in the Alteryx Gallery here allows any Alteryx user with a Open Route Service token (also free to create) to calcualte isochrones and perform drivetime analysis. To use it, it’s only necessary to have an Open Route Sergice token/key, the latitude or longitude of the starting point or points and select the different options from the parameters that are available.

The macro allows the suer to specify different output options for the isochrone. You can specify if you want to perform the analysis based on time or distance, the time and distance you want to use (for example, a drivetime analysis of 60 minutes from my specified location) and also the profile type. Allowing us to chose between driving, cycling, walking and other options. You can even create different isochrones from a single starting point by adding commas in the range field. For example, if I specify 60,90,120 driving by car time, it will generate from each starting point i my data set three different isochrones: one with a driving time of 60 minutes from the starting point, one with 90 minutes and a third one with 120 minutes.

Specifying those parameters, the API key and the fields that contains the latitude and longitude information, the macro will generate an output like this.

In this particular case, I have configured the macro to create a time based isochrone, using the driving by car profile and 60 minutes from the starting point. Now we can see very clearly that the area covered by car in that time is bigger through highways.

If we have internal data in our company with the location of customers, stores, POI, etc. we can analyse if our customers are too far from our stores and we need to open new stores in certain areas, if some locations are too far from our distribution centers and we need to open new ones or if our sales representatives visiting customers are too far and are letting some customers out of their routes.

Additionally, the API returns the estimate of the population covered in the area, that can add additional value to our analysis if some areas have a low population density and we want to improve the performance our business performance.

The workflow behind the macro

The workflow is not very complex. It has several interface tools to configure and chose the different parameters, and then it follows a quite standard process to generate the neccesary URL call to the API service, sends the query using the download tool, parses the response in JSON format and cleanse and prepares the data to generate a polygon that we can use in Alteryx.

The API service returns a row for each point of the isonchrones with the latitude and longitude, so the workflow cleans those rows, and reshapes the data to be able to create the polygon in the correct sequence to have a meaningful output and avoid needing any extra work after the macro.

Where can I download the macro?

As mentioned before, the macro is available in the Alteryx Gallery here: https://gallery.alteryx.com/#!app/Calculate-Isochrones/5c1931b00462d724a455ae28. Once you download the macro, remember that you will need to register in the Open Route Service website and create a token/key to paste in the corresponding field of the macro. Any ideas or suggestions to improve the macro are more than welcome.

Pablo Sáenz de Tejada

Madrid, ES