Analyzing floating car data with clickhouse db, postgres and R

Tom van Tilburg & Anne Blankert

Playlists: 'foss4g2019' videos starting here / audio / related events

Spatio-temporal datasets like sensor-data or floating car data can be rather overwhelming because they quickly get in the order of billions of records.
In this talk I show how we made billions of floating car data entries into a workable datastream that outputs visually attractive and useful maps and graphs over a routable network. I will start by summarizing the relatively new OS clickhouse database and how this column store helps in dealing with massive temporal datasets. Next I explain how we set up the pipeline with postgres/gis, pgrouting and R in order to create analysis in seconds and share some interesting results that you can get from these large trafficdatasets.
The talk will be relatively code-focused (mainly SQL and R) but also show some ind-depth analyses of car data.