{"guid":"fb1d6f2b-e06d-5bfb-9ecb-0768ed3a2fb0","title":"Analyzing floating car data with clickhouse db, postgres and R","subtitle":null,"slug":"bucharest-259-analyzing-floating-car-data-with-clickhouse-db-postgres-and-r","link":"https://talks.2019.foss4g.org/bucharest/talk/9TE3FC/","description":"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. \nIn 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. \nThe talk will be relatively code-focused (mainly SQL and R) but also show some ind-depth analyses of car data.\n\nNone","original_language":"eng","persons":["Tom van Tilburg \u0026 Anne Blankert"],"tags":["bucharest","259","2019","General"],"view_count":239,"promoted":false,"date":"2019-08-29T00:00:00.000+02:00","release_date":"2019-08-29T02:00:00.000+02:00","updated_at":"2026-02-18T13:15:11.504+01:00","length":1079,"duration":1079,"thumb_url":"https://static.media.ccc.de/media/conferences/foss4g2019/259-hd.jpg","poster_url":"https://static.media.ccc.de/media/conferences/foss4g2019/259-hd_preview.jpg","timeline_url":"https://static.media.ccc.de/media/conferences/foss4g2019/259-hd.timeline.jpg","thumbnails_url":"https://static.media.ccc.de/media/conferences/foss4g2019/259-hd.thumbnails.vtt","frontend_link":"https://media.ccc.de/v/bucharest-259-analyzing-floating-car-data-with-clickhouse-db-postgres-and-r","url":"https://media.ccc.de/public/events/fb1d6f2b-e06d-5bfb-9ecb-0768ed3a2fb0","conference_title":"FOSS4G 2019","conference_url":"https://media.ccc.de/public/conferences/foss4g2019","related":[{"event_id":2775,"event_guid":"3d7c4060-7e31-11e5-b91e-c23ca85e9bc3","weight":1},{"event_id":2834,"event_guid":"1b6ed158-e206-416a-8705-9b285a428dbf","weight":1},{"event_id":4844,"event_guid":"5c5e888e-4556-405b-a205-e59b97db99e1","weight":1},{"event_id":6492,"event_guid":"bc545b26-8319-43fb-abc2-f624ef414ee8","weight":1},{"event_id":7477,"event_guid":"9227fcf3-b7b2-5a7a-b135-3e7a18341a82","weight":1},{"event_id":7484,"event_guid":"b4d6c8c6-688b-5273-aa8f-a2044128ac13","weight":2},{"event_id":7487,"event_guid":"6f1b8b15-b44a-592c-b3e9-96bd9e0fbce3","weight":1},{"event_id":7495,"event_guid":"bffc8bd9-0df8-5543-8c24-254c05092bbd","weight":3},{"event_id":7501,"event_guid":"a4aa4222-f2ca-5e0c-94f6-4c7a3595d1a2","weight":3},{"event_id":7502,"event_guid":"22e35ec7-d84e-5a95-a490-adc14715792b","weight":3},{"event_id":7504,"event_guid":"c54aa667-78de-55fb-85e7-60f2eaffd2a4","weight":2},{"event_id":7514,"event_guid":"0d63b0b4-259d-5114-8c8f-18604b514f7d","weight":4},{"event_id":7518,"event_guid":"8773035b-d16c-5cf3-902d-43abc7e5dab7","weight":1},{"event_id":7519,"event_guid":"33229e10-d954-564b-bb6f-f48e5b5d3882","weight":1},{"event_id":7522,"event_guid":"74922ed0-86dd-5568-98fb-6fc812852441","weight":3},{"event_id":7523,"event_guid":"a95cf31f-07d2-5255-bfa2-6fb38923a15f","weight":3},{"event_id":7542,"event_guid":"7e354528-0b5a-512d-be47-bdc7447bb93b","weight":2},{"event_id":7550,"event_guid":"53494dbe-b19b-5e77-a2d1-9cb5a6d5447d","weight":3},{"event_id":7552,"event_guid":"4c2f4b81-1316-502d-870c-dc39567b782e","weight":2},{"event_id":7553,"event_guid":"9f0999f8-6941-5308-b1a8-f094c738245f","weight":1},{"event_id":7565,"event_guid":"3bf3bca5-f60f-5af0-b8a8-8a0a229b234b","weight":2},{"event_id":7579,"event_guid":"0112454d-62b2-5132-8971-73c7fdb972d0","weight":4},{"event_id":7592,"event_guid":"31bcb788-988b-56f6-90ba-69ebac85b83d","weight":1},{"event_id":7602,"event_guid":"7e1569ea-459c-5b0f-9aa3-59393152500f","weight":1},{"event_id":7607,"event_guid":"d0794cae-d707-584c-8879-6c438a603486","weight":1},{"event_id":7608,"event_guid":"13e2ef4d-8375-580b-a797-c54a85faa0aa","weight":1},{"event_id":7619,"event_guid":"21ffa487-5dfd-57d3-acce-8fd048ead7cf","weight":3},{"event_id":7630,"event_guid":"2922816b-f00a-5fc1-97e6-69df1a0c98ab","weight":1},{"event_id":7652,"event_guid":"e04c27d0-8ff8-5bf4-b932-34db4e4fdf5b","weight":2},{"event_id":7662,"event_guid":"a8fbf537-cbe1-5f17-aa73-97d64132f660","weight":1},{"event_id":7669,"event_guid":"7627a093-69e4-5c72-b8b5-0f05c27ce815","weight":2},{"event_id":7674,"event_guid":"ac484924-85f6-5195-bf5b-9f689f24ef57","weight":1},{"event_id":7681,"event_guid":"a1326956-88be-5af4-b747-5cefc95e8232","weight":2},{"event_id":7688,"event_guid":"9884da36-043b-5822-920a-f125c93faf74","weight":1},{"event_id":7692,"event_guid":"3c0ea464-ce0e-5676-a977-075148d7bbdd","weight":4},{"event_id":7695,"event_guid":"5df0ebe1-cfe2-54c4-a0c8-40e35a6de826","weight":1},{"event_id":7707,"event_guid":"8f9b67d8-fd60-522f-9a5b-6f0f81626e5c","weight":1},{"event_id":7708,"event_guid":"f41c767d-1f3d-5e46-a477-1a9dde63e146","weight":1},{"event_id":7729,"event_guid":"9d030aa8-27aa-509b-913f-ee9eff97257b","weight":1},{"event_id":7749,"event_guid":"5117b85e-052d-5b96-bcc3-f14e953b887a","weight":3},{"event_id":7755,"event_guid":"f651ccfa-c224-5cfa-b76b-f7b44b4ef563","weight":2},{"event_id":7756,"event_guid":"c4299de5-e39e-576d-93bb-7ae47fee42af","weight":1}],"recordings":[{"size":147,"length":1079,"mime_type":"video/mp4","language":"eng","filename":"bucharest-259-eng-Analyzing_floating_car_data_with_clickhouse_db_postgres_and_R_hd.mp4","state":"new","folder":"h264-hd","high_quality":true,"width":1920,"height":1080,"updated_at":"2019-08-29T18:05:19.627+02:00","recording_url":"https://cdn.media.ccc.de/events/foss4g2019/h264-hd/bucharest-259-eng-Analyzing_floating_car_data_with_clickhouse_db_postgres_and_R_hd.mp4","url":"https://media.ccc.de/public/recordings/38391","event_url":"https://media.ccc.de/public/events/fb1d6f2b-e06d-5bfb-9ecb-0768ed3a2fb0","conference_url":"https://media.ccc.de/public/conferences/foss4g2019"},{"size":40,"length":1079,"mime_type":"video/mp4","language":"eng","filename":"bucharest-259-eng-Analyzing_floating_car_data_with_clickhouse_db_postgres_and_R_sd.mp4","state":"new","folder":"h264-sd","high_quality":false,"width":720,"height":576,"updated_at":"2019-08-29T18:30:30.251+02:00","recording_url":"https://cdn.media.ccc.de/events/foss4g2019/h264-sd/bucharest-259-eng-Analyzing_floating_car_data_with_clickhouse_db_postgres_and_R_sd.mp4","url":"https://media.ccc.de/public/recordings/38494","event_url":"https://media.ccc.de/public/events/fb1d6f2b-e06d-5bfb-9ecb-0768ed3a2fb0","conference_url":"https://media.ccc.de/public/conferences/foss4g2019"},{"size":62,"length":1079,"mime_type":"video/webm","language":"eng","filename":"bucharest-259-eng-Analyzing_floating_car_data_with_clickhouse_db_postgres_and_R_webm-sd.webm","state":"new","folder":"webm-sd","high_quality":false,"width":720,"height":576,"updated_at":"2019-08-29T18:30:36.050+02:00","recording_url":"https://cdn.media.ccc.de/events/foss4g2019/webm-sd/bucharest-259-eng-Analyzing_floating_car_data_with_clickhouse_db_postgres_and_R_webm-sd.webm","url":"https://media.ccc.de/public/recordings/38495","event_url":"https://media.ccc.de/public/events/fb1d6f2b-e06d-5bfb-9ecb-0768ed3a2fb0","conference_url":"https://media.ccc.de/public/conferences/foss4g2019"},{"size":16,"length":1079,"mime_type":"audio/mpeg","language":"eng","filename":"bucharest-259-eng-Analyzing_floating_car_data_with_clickhouse_db_postgres_and_R_mp3.mp3","state":"new","folder":"mp3","high_quality":false,"width":0,"height":0,"updated_at":"2019-08-29T18:30:45.431+02:00","recording_url":"https://cdn.media.ccc.de/events/foss4g2019/mp3/bucharest-259-eng-Analyzing_floating_car_data_with_clickhouse_db_postgres_and_R_mp3.mp3","url":"https://media.ccc.de/public/recordings/38496","event_url":"https://media.ccc.de/public/events/fb1d6f2b-e06d-5bfb-9ecb-0768ed3a2fb0","conference_url":"https://media.ccc.de/public/conferences/foss4g2019"},{"size":11,"length":1079,"mime_type":"audio/opus","language":"eng","filename":"bucharest-259-eng-Analyzing_floating_car_data_with_clickhouse_db_postgres_and_R_opus.opus","state":"new","folder":"opus","high_quality":false,"width":0,"height":0,"updated_at":"2019-08-29T18:30:54.150+02:00","recording_url":"https://cdn.media.ccc.de/events/foss4g2019/opus/bucharest-259-eng-Analyzing_floating_car_data_with_clickhouse_db_postgres_and_R_opus.opus","url":"https://media.ccc.de/public/recordings/38497","event_url":"https://media.ccc.de/public/events/fb1d6f2b-e06d-5bfb-9ecb-0768ed3a2fb0","conference_url":"https://media.ccc.de/public/conferences/foss4g2019"},{"size":200,"length":1079,"mime_type":"video/webm","language":"eng","filename":"bucharest-259-eng-Analyzing_floating_car_data_with_clickhouse_db_postgres_and_R_webm-hd.webm","state":"new","folder":"webm-hd","high_quality":true,"width":1920,"height":1080,"updated_at":"2019-08-29T18:31:01.615+02:00","recording_url":"https://cdn.media.ccc.de/events/foss4g2019/webm-hd/bucharest-259-eng-Analyzing_floating_car_data_with_clickhouse_db_postgres_and_R_webm-hd.webm","url":"https://media.ccc.de/public/recordings/38498","event_url":"https://media.ccc.de/public/events/fb1d6f2b-e06d-5bfb-9ecb-0768ed3a2fb0","conference_url":"https://media.ccc.de/public/conferences/foss4g2019"}]}