Today, we had to download data from the Department for Transport in order to analyse traffic counts on UK roads. Oh, and we went even further back in time to 2012’s Tableau 8 (LODs? What LODS?).
Interesting side note, version 8.0 is also incompatible with Tableau Public…
The data retrieval was quite straightforward, despite Andy’s attempts at obfuscating it. Each of us had to choose a region of the UK then download the data from each local authority (or county) separately.
You can select to download either a whole region or each Local Authority area.
Data Prep
Andy seemed to think bringing each of them into Alteryx for processing would require a macro. Perhaps he forgot about wildcard inputs…
Alteryx is smart enough to automatically union files together!
The next challenge was converting each road’s easting & northing value into latitude and longitude. This was accomplished with some spatial tools – create points from the E & N values using the British National Grid reference system, then spatial info in order to pull the lat & long out of each of those points.
Beginning of the workflow with some data cleanup then converting easting and northing to latitude and longitude.
But these points don’t mean much, we want to look at the roads!
I took a look to see if I could find any spatial files for the entire length of each named road. I found the Ordnance Survey’s Open Road data set.
Ordnance Survey to the Rescue!
It is a complete set of ESRI shape files, broken down into 100km squares for the road network across England, Scotland and Wales. Perfect!
Pretty cool, right?
One small problem occurred to me at this point – Tableau version 8 doesn’t support spatial files.
Actual footage of my reaction.
So, I had a go at getting around this by turning each road into a set of points which I could plot in Tableau to mimic a complete road. I started by group each road together into one entry so I’d have one spatial object for each named road.
Then I split those lines into individual points, each of which had a sequence number associated with it, so I could plot the line and tell Tableau to draw a line between each point based on that sequence. Sounds ideal right? Well, Alteryx didn’t quite number my points in the right order…
The A12 does not, in fact, jump around like that.
At this point, dejected and defeated, I decided to leave the individual roads for another day and stick to county level since time was starting to run low.
All that remained to do in Alteryx was a bit more data prep and I was into Tableau.
The Viz
To my surprise, Tableau actually recognised the local authorities in the dataset as counties for the purpose of geographic role! That allowed me to look at each county in terms of the average number of vehicles per day on all their roads.
I took two approaches, for the rest of the viz. Trends over time and a look at the busiest roads, both broken down by either the county or the mode of transport.
The biggest challenges now feel like dealing with the limitations of the aged versions of Tableau. Lacking the ability to include spatial files was a serious setback which moved my dashboard in quite a different direction.
At the end of the day, Andy gave us the wonderful news that we’ll be back onto Tableau 2018.3 tomorrow.
I feel like Marty McFly.
