In search of a nice geographical dataset to try on microsofts Power BI suite I stumbled upon a year worth of data from a highway in The Netherlands. It is a piece of road I travel a lot so it sparked my interest, knowing the amount of traffic jams. The dataset contains the average speed (speed) and number of vehicles (flow) for every minute in the year 2011 for about 170 sensors in the A13 making quite a large dataset. Not only in data points but also to feed into PowerBI on a laptop.
The dataset is a matrix of the sensors as columns and the minute data as rows. A colleague and I transformed the coordinate system, added timestamps and loaded the data in a SQL-Server database. After that I selected a random day and loaded it in powerBI and visualized the speed as an animated bargraph.
As you can see the traffic jams are very well visible between the relative quite of the night and evening, and the somewhat more busy time during the day. There are some interesting things to notice:
- The speed seems to be more variable during the night and late evening. Just before morning rush hour, after evening rush hour and between both the speed balances out.
- To the right there is a lower speed. That is a piece of the highway where the maximum speed is 80km/h and there is ‘trajectcontrole’ (speed cameras).
- Just to the left of that there is a higher piece. Even more to the left there is a lower piece. What is that about? Maybe an on-ramp?
- The rush hour is very well visible although speed never really drops to 0. And just a small part of the time the speed drops on the complete stretch of road.
- You can see the ‘shockwave‘ effect during rush hour. This is the effect where speed drops and travels back through traffic. This will be more visible with a slowed down video.
I think this is a very nice way to visualize rush hour this kind of data. Not really good to analyze the data but after showing it during a few presentations I notices it sparks an interest and gets peoples attention.