The map shows the ratio of the average travel speed (spot speeds) of a given link for a specific period of time to the posted speed limit. The available data used for this map is routes from December 2016. There wasn't enough data to get a picture of average speed/hour/given weekday or/weekend so I combined all weekdays together and the weekend together.
The project was completed entirely in PostgreSQL/PostGIS and PL/pgSQL using the taxi data and OpenStreetMap data.
Assumptions and Challenges
Most of the speed values recorded were actually invalid and I had to reconstruct them using position values
OpenStreetMap was missing some information on posted speed so I filled them in using the following conditions:
Any road of type "living_street" was assigned 30 kph
Any roads of type "residential" was assigned 40 kph
Everything else was assigned 60 kph
I might write a blog post one day detailing how the project was completed.