This blog will teach you how to create trade areas using travel time, rather than distance. For this example, I will draw trade areas which are a 30 minute walk from Wagamama locations in the UK using Alteryx.
Materials: TravelTime Isochrones API - https://traveltime.com/docs/api/reference/isochrones
**PLEASE NOTE: free access to the TravelTime API is limited to 10 API calls a day**
Whenever you use Alteryx to access an API, generally there's an example API request so you can check what sort of information you need to input, common components include:
- An access URL: this is where we’re retrieving the information from
- Headers: this is extra information that you send off with your request e.g. API keys which authorise access to the API’s contents
- A Body: some APIs request a more structured query, usually in the form of .json
- Click Get API Key - create a new account, this will send a Application ID and Application Key to your email - this allows us to access the information within the API.
- INPUT DATA: import your list of business locations along with their latitude and longitude.
3. FORMULA TOOL:
Create a Transport field in which you input your mode of transport (you can choose from walking, driving, public_transport) as a string
Create a Time field in which you input the time you have available in minutes as an integer
4. SECOND FORMULA TOOL:
Create X-Api-Key field where you add your API Key as a string
Create X-Application-Id field where you add your Application ID as a string
Create Content-Type field set to 'application/json'
5. RECORD ID: add a record ID, just to keep each location separate, this will come in handy when we need to crosstab the data later on.
6. FORMULA TOOL: the example request shows the query structure we need to follow:
- We already have all the information we need for our headers, but the body needs to be constructed and the TravelTime API needs quite a lot of information:
- Search Time: we need to tell the API when we’re proposing to travel i.e. the current date and time
- URL: this is our access URL https://api.traveltimeapp.com/v4/time-map
- Travel Time: convert the travel time, as the API works in seconds
- Search Time Format: the API requires the search time to be in a certain format
Creating the Body
- The reason why all my previous fields are strings is because they’re going into the body which will be one big string anyway.
- Copy and paste the example request from departure_searches onwards. (I use Visual Studio Code to edit my JSON before inputting it into Alteryx, this highlights when you’re missing a bracket etc.)
- You can probably tell where our fields need to go:
- All of our fields need to be converted to strings if not already done.
- As our JSON includes double quotes, use single quotes to identify a string
- Note that our ID, transportation and departure time fields all need to be surrounded by extra double quotes, which need to be manually added in.
Downloading the Data
7. DOWNLOAD TOOL:
- Basic Tab: The URL field should be set to URL, output to a string, with data encoded as Unicode UTF-8
- Headers Tab: In the Add values from these fields section, tick X-Api-Key, X-Application-ID, Content-Type
- Payload Tab: set HTTP action to POST and tick Take Query String/Body from Field and select your Body field.
- The download tool should output a JSON string, your DownloadHeaders should say something like 200 OK - this means that the request was successful.
If you’re getting an error, then your body is probably wrong, open up the cell viewer in browse and paste one of the body fields into your text editor and check if it looks exactly the same as the example request. You’re probably missing a bracket, or forgot to add extra quotes.
- Once you have your download working, cache and run the download tool immediately, this prevents you using up your precious API calls.
8. JSON PARSE TOOL: Select DownloadData as your JSON Field
9. SELECT: only the fields you want to keep
10. FILTER: the data to rows containing the word ‘shell’- this keeps the rows with geographic data only
- In the next few steps I removed ‘results.0.shapes.’ from the start of JSON_Name using a simple REPLACE() formula.
- I then split on the full stop using TEXT TO COLUMNS to give me three columns which I renamed to Shell Prefix, Shell Inner Index (make sure this is an Int64), and Lat/Long , I also renamed JSON Value String to Lat/Long Value for clarity
11. CROSS TAB: so that you have one column for latitude and longitude
- I grouped by everything but JSON_Name (we don’t need this anymore), Lat/Long and Lat/Long Value
- Column Headers: Lat/Long
- Values for New Columns: Lat/Long Value
- Method for Aggregating Values: First
- Now we have each postcode, and a bunch of coordinates that we will use to eventually draw the trade area.
12. CREATE POINTS: put lat and lng in their relative fields, this will allow each point to be mapped.
- This will join up each address' centroids drawing a polygon, using Shell Inner Index to determine the sequence in which to join up the points.
...and we're done!
Spatial objects in Alteryx can be viewed using a browse tool, but here’s how the polygons look when exported to Tableau as a data extract.