When Tableau first launched their 2017 #ironviz competition I was over the moon! A geospatial competition? In Tableau? I’m in!
Armed with a little bit of Alteryx spatial processing knowledge and a lot of enthusiasm, I set my challenge; “Map every building, river, green space and postcode in London into one shapefile” How hard could that be? Well in truth, for me, it was hard. It took me every evening for about 2 weeks to figure everything out. If I was to do it now, it would probably take a few hours!
So here is how I did it…
My first port of call was Ordnance Survey Opendata from here I could the building shapefiles and the postcode data I needed. However, Ordnance Survey don’t map things by town or city, they map by grid squares which gave me a view like this;
In addition the postcode data is split in to 771 files and the column headers are held in a separate file (why Ordnance Survey, why?!) Luckily Alteryx made swift work or importing all of the data and adding the column headers in;
So now I had some of the data, but how do I get just London? London as city doesn’t have a boundary, but the councils that look after London do. So a quick jolly over to the London Councils website provided me with a list of the councils in London. But this doesn’t give me the boundary information. For some reason I had LSOA shapefiles from ONS on my laptop (probably from another half cocked idea I was trying out) which contain boundary data for census areas and they contain the council name within the code. Another easy job for Alteryx to deal with;
The result looked perfect!
Now all I had to do was match each building and postcode to a London council and clip the data to just leave the objects that are contained within London, again Alteryx handles this seamlessly with Spatial Match & Spatial Process tools. And as if by pure magic, the final result is tantalisingly close;
For the final part of this Alteryx magic show…rivers & green space! I found the river data in Ordnance Survey website but I couldn’t find green space, so an extensive Google search led me to Open Street Map. Wow! A complete resource of all objects in the Greater London area! Why had a just wasted my life with Ordnance survey? But on further exploration of the data I found that the building data was not as comprehensive as Ordnance Survey, so I just imported the river and green space data.
Success! I had done it! I win! Champion of the #ironviz!
Now just to output this as a shapefile and on to Tableau;
Oh no, this was unexpected!
So it turns out that the data I had imported was a mix of polygons, points & lines, which is fine unless you want to write them back to one shapefile, which I do, so I needed to work this out. I figured that buffering the points and lines would turn them into polygons, which is exactly what it did. Using the smallest buffer size of 0.001km (1 metre) I was able to create a polygons small enough that it wouldn’t make any difference to the final output;
One more attempt at an output;
What a relief it all came together and work beautifully! Thanks Alteryx, you worked like a charm…over to Tableau now!
And here is the result;
But there is one small issue, each time I tried to move the map or make any changes, Tableau would take at least 30 seconds to re-render the view, this was not what I had imagined. There was no way I could submit this to the competition with this poor usability.
So back to the drawing board I guess!
After much pondering I decided to focus on a particular subject rather than just “everything in London”, so I went for coffee shops in London (of which there are many). I’m not going to write about the coffee shop bit but just focus on the base map element.
I thought it would be good if the user could enter their postcode and Tableau render the polygons for everything within a 2 mile radius, that sounded achievable!
So I set to work…
First job, create a 2 mile trade area for each postcode, easy peasy with the trade area tool;
Then all I need to do is clip all of the polygons again but using the postcode trades areas instead of the London councils…fingers crossed;
This could be a bit of problem, only 1% processed and I have already exceeded the shapefile size limit of 2GB. Dang!
After several attempts at different combinations of workflow, different size trade areas, I ended up changing how the trade area is generated. Instead of creating a trade area for each postcode, I aggregated them up to postcode district level (if you aren’t familiar with UK postcodes, you can read my blog about it). Which was a very simple change to my workflow, I extracted the postcode district with a formula tool and used the summarize tool to find the centroid from which the new trade area was generated;
One more run of the workflow and my work here was done! Well almost, there was the small matter of building the dashboard in Tableau but that’s a minor detail. Here’s the final workflow and a basic setup in Tableau
Which eventually turned into this;
Suffice it to say, I didn’t win the competition but that’s more to do with my poor storytelling than the mapping element.
But thank you to everyone who gave me feedback along the way,
— LordNeilLord (@LordNeilLord) March 24, 2017
and thank you to Integrated Informatics Inc who selected it for their map of the day;
— Intg Informatics Inc (@iii_gis) March 31, 2017
I hope you found some use in this blog let me know if you have any question or feedback
Good night and God bless.