We are updating the requirements for Community registration. As of 7/21/21 all users will be required to register a phone number with their My Alteryx accounts. If you have already registered, you will be prompted on your next login to add your phone number.

Engine Works

Under the hood of Alteryx: tips, tricks and how-tos.
BenMoss
17 - Castor
17 - Castor

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.

 

BenMoss_0-1623772573176.png

 

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!

 

BenMoss_1-1623772573221.png

 

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.

 

BenMoss_2-1623772573254.png

 

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.

 

BenMoss_3-1623772573261.png

 

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).

 

BenMoss_4-1623772573264.png

 

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!

 

BenMoss_5-1623772573291.png

 

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).

 

BenMoss_6-1623772573295.png

 

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.

 

BenMoss_7-1623772573301.png

 

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.

 

BenMoss_8-1623772573313.png

 

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.

 

BenMoss_9-1623772573336.png

 

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.

 

Comments
NeilR
Alteryx Community Team
Alteryx Community Team

Love this, and all your other blogs@BenMoss  

mceleavey
16 - Nebula

@BenMoss ,

 

I was literally reading about what3words when your blog landed.

Are you following me about?

Are you...watching me?

 

suspicious.gif

LeahK
Alteryx Community Team
Alteryx Community Team

True story... a few weeks ago some friends came over for a game night. Due to COVID and all, it's been awhile since these people had been to my house (for you UK Folks, a house is American for "flat")... to accommodate the whole working from home situation we purchased a new router + modem and, as we all know, such acquisitions come with creating a new network name and wifi password. Long story short, I shared the new password with my friends and one of the guys was like "Is your password the what3words thingy?" I didn't know what he was talking about... and now I do.

 

I suppose the end result is similar, except I didn't use any analytical techniques or alteryx for that matter. I played word roulette with my partner until we came to an agreement.

 

side note: I can't help but wonder if my "friend" was implying that I was stupid enough to use three word designations associated with where I live as my home wifi password 🙄