Engine Works
Under the hood of Alteryx: tips, tricks and how-tos.
Materials demonstrated in this blog can be downloaded here.
What is what3words
what3words is a geocoding system which divides the world into (57 trillion!) 3m x 3m squares, with each square assigned a unique, randomly assigned three word address.
The system is now used by emergency services across the UK to identify the locations of people in need of help.
The purpose of this post is to highlight how we can develop our own what3words database using Alteryx, not because we want to rip it off, but because it highlights a couple of analytical techniques which might be quite useful!
Now for the sake of this exercise, we’re going to demonstrate the techniques using a small area (well smaller than the world at least), and I’ve chosen the area of London where The Information Labs office resides, the City of London. Note, for our American friends, the City of London is not ‘London’ it’s a very small portion of it, it’s where all the banks have their offices!
In terms of data we’ll need two things. We’ll need a shapefile of the area we want to apply our logic against, and we’ll also need a set of words.
The City of London is approximately 3 square kilometers (that’s 3000000 square meters!), so if we were to divide this into 3m x 3m squares (or 9 square meters), we’ll have around 333,333 squares in total.
In order to generate this amount of unique three words combinations we’ll need just 70 words (the cube route of 333,333 is 69.34 but of course we can’t have part of a word so we must round up rather than down!).
Let’s make this ‘fun’ and use some words related to the Alteryx product. In order to do this I created a workflow which extracts a list of Designer tools from the Alteryx website and then parses these into a unique list of words.
Now whilst this isn’t important really for the purpose of this exercise (we could have used any words!) we’ve still included the workflow used to do this and it can be found in the working files shared at the start of this post!).
Building our Squares
Let’s start with inputting our shape file onto the Alteryx canvas, this is a relatively trivial task; the only thing that might not seem ‘normal’ here is that whenever you are working with spatial data in Alteryx, it’s always best to use a Browse tool so that you can visually see the map that you would expect.
It may be that your spatial data file either contains areas that you aren’t interested in, or is more granular than the level you require. In this case, I have the 2nd problem, but fortunately, the Make Grid tool can cater for this issue.
The Make Grid tool is going to allow us to build our different 3m x 3m squares, so we’ll connect this to our input file.
In terms of configuring the tool, firstly we must select the field which contains our spatial object. If your working with a .shp file and you have connected directly to it, then you’ll only have one spatial object field called ‘SpatialObj.’
We can then choose our ‘Grid Size’ which we can set to either kilometers or miles, we’ll go metric and choose kilometers - because we are building meter based grids!
The value that you place in the box represents the width/height of each grid object, NOT THE AREA (which I thought originally). In this case our width should then be 0.003 (3/1000).
Now we can choose how we wish to generate our grid, we can choose between the option of ‘Generate Single Grid for Entire Layer’ and ‘Generate Grid for Each Object.’ If you have only one line in your shape file, both options will produce the same result, however, if you have multiple objects then for the purpose of this exercise we want only a single grid (otherwise you may end up with overlapping squares which will have a different what3words value assigned to each).
If we throw a Browse tool onto this output, the result looks a bit strange, this is simply because the squares are so tiny, you have to zoom quite far in to create a clearer picture of what we have built so far!
The output from this tool will include a field titled ‘GridName’ which gives a unique identifier for each square, specifically including its position. This isn’t really important and we can drop this from our view using a Select tool.
Combinations of words
We can now move onto creating our 3 word combinations from our 70 word list. We’ll do this by making use of the Append Fields tool (twice).
Firstly, we append our words dataset against itself, this creates all two word combinations from our initial dataset.
As an example we go from…
|
word |
|
OUTPUT |
|
INPUT |
|
FORMULA |
to...
|
first_word |
second_word |
|
OUTPUT |
OUTPUT |
|
OUTPUT |
INPUT |
|
OUTPUT |
FORMULA |
|
INPUT |
OUTPUT |
|
INPUT |
INPUT |
|
INPUT |
FORMULA |
|
FORMULA |
OUTPUT |
|
FORMULA |
INPUT |
|
FORMULA |
FORMULA |
We can then append this dataset with our original input one final time, to create all three word combinations…
|
first_word |
second_word |
third _word |
|
OUTPUT |
OUTPUT |
OUTPUT |
|
OUTPUT |
INPUT |
OUTPUT |
|
OUTPUT |
FORMULA |
OUTPUT |
|
INPUT |
OUTPUT |
OUTPUT |
|
INPUT |
INPUT |
OUTPUT |
|
INPUT |
FORMULA |
OUTPUT |
|
FORMULA |
OUTPUT |
OUTPUT |
|
FORMULA |
INPUT |
OUTPUT |
|
FORMULA |
FORMULA |
OUTPUT |
|
OUTPUT |
OUTPUT |
INPUT |
|
OUTPUT |
INPUT |
INPUT |
|
OUTPUT |
FORMULA |
INPUT |
|
INPUT |
OUTPUT |
INPUT |
|
INPUT |
INPUT |
INPUT |
|
INPUT |
FORMULA |
INPUT |
|
FORMULA |
OUTPUT |
INPUT |
|
FORMULA |
INPUT |
INPUT |
|
FORMULA |
FORMULA |
INPUT |
|
OUTPUT |
OUTPUT |
FORMULA |
|
OUTPUT |
INPUT |
FORMULA |
|
OUTPUT |
FORMULA |
FORMULA |
|
INPUT |
OUTPUT |
FORMULA |
|
INPUT |
INPUT |
FORMULA |
|
INPUT |
FORMULA |
FORMULA |
|
FORMULA |
OUTPUT |
FORMULA |
|
FORMULA |
INPUT |
FORMULA |
|
FORMULA |
FORMULA |
FORMULA |
To make this a bit nicer, we can then filter this list in order to only leave combinations where values aren’t used more than once for each combination, which in this case would leave us with just 6 records…
|
first_word |
second_word |
third _word |
|
INPUT |
FORMULA |
OUTPUT |
|
FORMULA |
INPUT |
OUTPUT |
|
OUTPUT |
FORMULA |
INPUT |
|
FORMULA |
OUTPUT |
INPUT |
|
OUTPUT |
INPUT |
FORMULA |
|
INPUT |
OUTPUT |
FORMULA |
We can concatenate these three individual columns together using a simple formula statement before randomising the order using the rand() function followed by a sort tool.
The final piece of the puzzle is merging these sources together (our words and our squares). We can do this using the Join tool, and though we have no common field between our two sources, we can use the ‘Join by Record Position’ option in order to merge them together. Therefor the first square will be matched with the first what3word value, the second squares will be matched with the second what3word value, and so on. As we have more words than squares (assuming our maths was right at the start of this post), then all of our squares will successfully be assigned a what3word.
The output can then be visualised either within the Alteryx product or other products that support the visualisation of spatial datasources such as ArcGIS, Mapbox and Tableau.
I hope this was a fun read and we’ve taught you some useful techniques! If you want some further reading then there’s this great post highlighting how what3words works in far more depth than covered here, and discusses how the use of similar words may cause confusion when trying to understand someones location.
