Disassembling Trips

meos_disassemble_berlinmod.c

This program reads a CSV file containing synthetic trip data in Brussels generated by the MobilityDB-BerlinMOD generator, disassembles the trips into individual observations, and write them in a CSV file ordered by timestamp.

The output of the program is given next.

55 trip records read.
76024 observation records written.

The CSV file generated by the program is given next.

tripid,vehid,day,seqno,geom,t
3,3,2020-06-01,1,POINT(496102.813654 6595154.145012),2020-06-01 08:10:09.852+02
3,3,2020-06-01,1,POINT(496105.474664 6595158.378099),2020-06-01 08:10:11.352+02
3,3,2020-06-01,1,POINT(496107.99001 6595162.379464),2020-06-01 08:10:12.202733+02
[...]

A similar result can be obtained in MobilityDB with the following SQL commands.

CREATE TABLE trips(tripid int, vehid int, day date, seqno int, trip tgeompoint, trip_input text);
COPY trips(tripid, vehid, day, seqno, trip_input) FROM '/home/MobilityDB/meos/examples/trips.csv' CSV HEADER;
UPDATE trips set trip = tgeompointFromHexEWKB(trip_input);
ALTER TABLE trips DROP COLUMN trip_input;
COPY (
  WITH temp(tripid, vehid, day, seqno, inst) AS (
    SELECT tripid, vehid, day, seqno, unnest(instants(trip)) AS inst
    FROM trips
    ORDER BY inst )
  SELECT tripid, vehid, day, seqno, ST_AsEWKT(getValue(inst)) AS geom, getTimestamp(inst) AS t
  FROM temp
) TO '/home/MobilityDB/meos/examples/trip_instants.csv' CSV HEADER;