Friday, May 1, 2020

Plotting UK population data using Alteryx and Tableau

While not a geographer or having any real GIS background, I was previously a transport planner and would need to make use of tools like MapInfo. So the ease and intuitiveness of spatial analysis in Alteryx has always pleased me. 

For a recent proof of concept I needed to get UK population data to enrich a financial dataset I had, so after a quick Google I stumbled across this dataset -

Upon downloading the file I discovered it was in ERSI asc file format, which isn't a spatial file type that can natively be read into Alteryx, but by reading it in as a csv file with no delimiters, I could see that it looked like a grid of numbers with some header information.

This helpful Wikipedia article helped explain what the values in the header represented, and I knew I could get on my way with Alteryx'ing my way through this.

Step 1: Create spatial points on a grid

My first step was to make sure I'd interpreted the values correctly, so I sketched out the boundary points on the grid:

Which when converting to points in Alteryx (using the British Grid system) and then creating a polygon showed me that I had a polygon which covered the entire UK.

Initially I thought the next step would be to make a grid from that polygon. However when I did this I ended up with more grid cells than I would have expected, I wasn't 100% sure why this was but given from the image above where visually the polygon isn't a true rectangle I assumed the reason would be due to the projection. Therefore I needed to find another way of creating my grid...

Using two generate rows tools it was possible to create my 652 mid points for my columns (x axis) and the 1211 rows (y axis), remembering to step in by 500m. Appending these together resulted in 789,752 records which I could then convert to points.

Step 2: Parse out the .asc file

After removing the header rows, it was clear to see the rest of the .asc file was a big grid of numbers which needed parsing out. Using the multirow formula tool and splitting text to rows on the space delimiter it was possible to create a table like below:

This table could then be joined onto the spatial data, based on matching the X and Y fields.

Step 3: Create 1km polygons

The grid created in the two steps above was most of the way to where I needed to get to, but the limitation was each population data point was just a dot on the map. To enable spatial analysis later on this needed to be converted to a polygon representing the 1sqKm grid the population is distributed over.

The technique I used to do this was to create a 0.7km non-overlapping trade area which results in a map looking like this:

Then all that was left to do was to export the file.

This could then be used either again in Alteryx along with spatial match to calculate the population living within a catchment area of a store, or presented in Tableau to understand changes in population density.

Which results in a nice looking map which is much easier to interpret than the gridded numbers in the original text input.

All in this workflow took about 1 hour to build including the research on ESRI .asc file formats and the initial experimentation of understanding the spatial tools to use.

Note: The data used in the graphics contains data supplied by Natural Environment Research Council.
©NERC (Centre for Ecology & Hydrology). Contains National Statistics data © Crown copyright and database right 2011.

No comments:

Post a Comment