The ZIP file with the data for this tutorial contains a set of CSV files as follows:

We decompress the file with the data into a directory. This can be done using the command.

unzip berlinmod_data.zip

We suppose in the following that the directory used is as follows /home/mobilitydb/data/.

In the following, we can use the mobilitydb database provided in the container. This database has already installed the MobilityDB extension. Alternatively, you may use another database. In that case, you can install the MobilityDB extension in your database by using the following command.

CREATE EXTENSION MobilityDB CASCADE;

By using CASCADE we load the required PostGIS extension prior to loading MobilityDB.

We create the tables to be loaded with the data in the CSV files as follows.

CREATE TABLE Instants (
  InstantId integer PRIMARY KEY,
  Instant timestamptz NOT NULL
  );
CREATE TABLE Periods (
  PeriodId integer PRIMARY KEY,
  Tstart TimestampTz NOT NULL,
  Tend TimestampTz NOT NULL,
  Period period
  );
CREATE TABLE Points (
  PointId integer PRIMARY KEY,
  PosX double precision NOT NULL,
  PosY double precision NOT NULL,
  Geom Geometry(Point)
  );
CREATE TABLE RegionsInput (
  RegionId integer,
  PointNo integer,
  XPos double precision NOT NULL,
  YPos double precision NOT NULL,
  PRIMARY KEY (RegionId, PointNo)
  );
CREATE TABLE Regions (
  RegionId integer PRIMARY KEY,
  Geom Geometry(Polygon) NOT NULL
  );
CREATE TABLE Vehicles (
  VehId integer PRIMARY KEY,
  Licence text NOT NULL,
  Type text NOT NULL,
  Model text NOT NULL
  );
CREATE TABLE Licences (
  LicenceId integer PRIMARY KEY,
  Licence text NOT NULL,
  VehId integer NOT NULL REFERENCES Vehicles(VehId)
  );
CREATE TABLE TripsInput (
  TripId integer NOT NULL,
  VehId integer NOT NULL REFERENCES Vehicles(VehId),
  PosX float NOT NULL,
  PosY float NOT NULL,
  T timestamptz NOT NULL,
  PRIMARY KEY (TripId, T)
  );
CREATE TABLE Trips (
  TripId integer PRIMARY KEY,
  VehId integer NOT NULL REFERENCES Vehicles(VehId),
  Trip tgeompoint NOT NULL
  );

We created one table for each CSV file. In addition, we created a table Regions in order to assemble all points composing the polygon of a region into a single geometry and a table Trips in order to assemble all instants composing a trip into a single temporal point.

We can load the CSV files into the corresponding tables as follows.

COPY Instants(InstantId, Instant) FROM '/home/mobilitydb/data/instants.csv' 
  DELIMITER ',' CSV HEADER;
COPY Periods(PeriodId, Tstart, Tend) FROM '/home/mobilitydb/data/periods.csv' 
  DELIMITER ',' CSV HEADER;
UPDATE Periods
SET Period = period(Tstart, Tend);
COPY Points(PointId, PosX, PosY) FROM '/home/mobilitydb/data/points.csv' 
  DELIMITER ',' CSV HEADER;
UPDATE Points
SET Geom = ST_Transform(ST_SetSRID(ST_MakePoint(PosX, PosY), 4326), 5676);
COPY RegionsInput(RegionId, PointId, XPos, YPos) FROM 
  '/home/mobilitydb/data/regions.csv' DELIMITER  ',' CSV HEADER;
COPY Vehicles(VehId, Licence, Type, Model) FROM '/home/mobilitydb/data/vehicles.csv' 
  DELIMITER ',' CSV HEADER;
COPY Licences(LicenceId, Licence, VehId) FROM '/home/mobilitydb/data/licences.csv' 
  DELIMITER ',' CSV HEADER;
COPY TripsInput(TripId, VehId, PosX, PosY, T) FROM '/home/mobilitydb/data/trips.csv' 
  DELIMITER ',' CSV HEADER;

The following query is used to load table Regions from the data in table RegionsInput.

INSERT INTO Regions(RegionId, Geom)
SELECT RegionId, ST_MakePolygon(ST_MakeLine(array_agg(
  ST_Transform(ST_SetSRID(ST_MakePoint(PosX, PosY), 4326), 5676) ORDER BY PointNo)))
FROM RegionsInput
GROUP BY RegionId;

There are many nested functions, so reading from the innermost:

The following query is used to load table Trips from the data in table TripsInput.

INSERT INTO Trips(TripId, VehId, Trip)
SELECT TripId, VehId, tgeompoint_seq(array_agg(tgeompoint_inst(
  ST_Transform(ST_SetSRID(ST_MakePoint(PosX, PosY), 4326), 5676), T) ORDER BY T))
FROM TripsInput
GROUP BY VehId, TripId;

There are many nested functions, so reading from the innermost:

Finally, we create indexes on traditional, spatial, temporal or spatiotemporal attributes as well as views to select a subset of the rows from the corresponding tables. This can be done as follows.

CREATE INDEX Instants_Instant_Idx ON Instants USING btree(Instant);
CREATE INDEX Periods_Period_Idx ON Periods USING gist(Period);
CREATE INDEX Points_Geom_Idx ON Points USING gist(Geom);
CREATE INDEX Regions_Geom_Idx ON Regions USING gist(Geom);
CREATE INDEX Trips_VehId_Idx ON Trips USING btree(VehId);
CREATE INDEX Trips_Trip_gist_Idx ON Trips USING gist(trip);

CREATE VIEW Instants1 AS SELECT * FROM Instants LIMIT 10;
CREATE VIEW Periods1 AS SELECT * FROM Periods LIMIT 10;
CREATE VIEW Points1 AS SELECT * FROM Points LIMIT 10;
CREATE VIEW Regions1 AS SELECT * FROM Regions LIMIT 10;
CREATE VIEW Vehicles1 AS SELECT * FROM Vehicles LIMIT 10;
CREATE VIEW Trips1 AS SELECT * FROM Trips LIMIT 100;