Taxi Data - Traffic Congestion Map


Currently Showing

  • Day: Weekday
  • Hour: 11

Traffic Congestion Map


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.

A project by Arthur Dolmajian.