Aggregating Trips

meos_aggregate_berlinmod.c

This program reads a CSV file containing synthetic trip data in Brussels generated by the MobilityDB-BerlinMOD generator and computes

the extent, that is, the bounding box of the spatial and time dimensions represented as an STBOX
the temporal count, that is, the evolution on time of the number of vehicles travelling at an hour granularity.

The output of the program is given next.

61 trip records read

Extent
------

SRID=3857;STBOX XT(((473212.810151,6578740.528027),(499152.544688,6607165.513683)),[2020-06-01 08:01:23.238+02, 2020-06-05 01:28:43.407526+02])

Temporal count
--------------

[3@2020-06-01 08:00:00+02, 2@2020-06-01 10:00:00+02, 2@2020-06-01 11:00:00+02)
[2@2020-06-01 16:00:00+02, 3@2020-06-01 17:00:00+02, 3@2020-06-01 19:00:00+02)
[1@2020-06-01 20:00:00+02, 2@2020-06-01 21:00:00+02, 1@2020-06-01 23:00:00+02, 1@2020-06-02 00:00:00+02)
[4@2020-06-02 08:00:00+02, 3@2020-06-02 09:00:00+02, 2@2020-06-02 10:00:00+02, 2@2020-06-02 11:00:00+02)
[2@2020-06-02 16:00:00+02, 4@2020-06-02 17:00:00+02, 2@2020-06-02 18:00:00+02, 2@2020-06-02 19:00:00+02)
[1@2020-06-02 20:00:00+02, 2@2020-06-02 21:00:00+02, 1@2020-06-02 22:00:00+02, 1@2020-06-02 23:00:00+02)
[4@2020-06-03 08:00:00+02, 5@2020-06-03 09:00:00+02, 1@2020-06-03 10:00:00+02, 1@2020-06-03 11:00:00+02)
[3@2020-06-03 16:00:00+02, 5@2020-06-03 17:00:00+02, 2@2020-06-03 18:00:00+02, 2@2020-06-03 19:00:00+02)
[2@2020-06-03 20:00:00+02, 1@2020-06-03 22:00:00+02, 1@2020-06-03 23:00:00+02)
[2@2020-06-04 08:00:00+02, 4@2020-06-04 09:00:00+02, 1@2020-06-04 10:00:00+02, 1@2020-06-04 11:00:00+02)
[5@2020-06-04 16:00:00+02, 5@2020-06-04 18:00:00+02)
[1@2020-06-04 20:00:00+02, 3@2020-06-04 21:00:00+02, 2@2020-06-04 23:00:00+02, 1@2020-06-05 00:00:00+02, 1@2020-06-05 02:00:00+02)

A similar result can be obtained in MobilityDB with the following SQL queries, assuming that the CSV file has been previously loaded into the trips table.

SELECT extent(trip) FROM trips;
SELECT unnest(sequences(tcount(tprecision(getTime(trip), interval '1 hour', timestamptz '2020-06-01'))))
FROM trips;