Loading the Data

Loading the Data
Prev	Chapter 1. MobilityDB Tutorial	Next

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

instants.csv with fields InstantId and Instant contains timestamps used for queries.
licences.csv with fields LicenceId, Licence and VehicleId contains vehicle licences used for queries.
periods.csv with fields PeriodId, Begin, and End contains periods used for the queries.
points.csv with fields PointId, PosX, and PosY contains points used for queries.
regions.csv with fields RegionId, PointNo, PosX, and PosY and Yend contains the polygons used for queries.
trips.csv with fields TripId, VehicleId, PosX, PosY, and Instant contains vehicles movements and pauses.
vehicles.csv with fields VehicleId, Licence, VehicleType, and Model contains the vehicle descriptions.

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 tstzspan );
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 (
  VehicleId integer PRIMARY KEY,
  Licence text NOT NULL,
  VehicleType text NOT NULL,
  Model text NOT NULL );
CREATE TABLE Licences (
  LicenceId integer PRIMARY KEY,
  Licence text NOT NULL,
  VehicleId integer NOT NULL REFERENCES Vehicles(VehicleId) );
CREATE TABLE TripsInput (
  TripId integer NOT NULL,
  VehicleId integer NOT NULL REFERENCES Vehicles(VehicleId),
  PosX float NOT NULL,
  PosY float NOT NULL,
  t timestamptz NOT NULL,
  PRIMARY KEY (TripId, t) );
CREATE TABLE Trips (
  TripId integer PRIMARY KEY,
  VehicleId integer NOT NULL REFERENCES Vehicles(VehicleId),
  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 = span(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(VehicleId, Licence, VehicleType, Model)
  FROM '/home/mobilitydb/data/vehicles.csv' DELIMITER ',' CSV HEADER;
COPY Licences(LicenceId, Licence, VehicleId) FROM '/home/mobilitydb/data/licences.csv' 
  DELIMITER ',' CSV HEADER;
COPY TripsInput(TripId, VehicleId, 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:

Function ST_MakePoint construct a point from the PosX and PosY values.
Function ST_SetSRID sets the SRID of the point to 4326, that is, to the standard WGS84 GPS coordinates.
Function ST_Transform transforms the spherical GPS coordinates to plannar coordinates fitted for Belgium.
Function array_agg collects in an array all points of a region (as specified by the GROUP BY clause) and sort them by PointNo (as specified by the ORDER BY clause).
Function ST_MakeLine make a linestring from the array of all points in a region.
Function ST_MakePolygon make a polygon for the region from a linestring.

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

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

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

Function ST_MakePoint construct a point from the PosX and PosY values.
Function ST_SetSRID sets the SRID of the point to 4326.
Function ST_Transform transforms the spherical coordinates to plannar coordinates with SRID 5676.
Function tgeompoint_inst gets the point and the time values to create a temporal point of instant duration.
Function array_agg collects in an array all temporal instant points of a given vehicle and a given trip (as specified by the GROUP BY clause) and sort them by time (as specified by the ORDER BY clause).
Function tgeompoint_seq gets the array of temporal points and construct a temporal sequence point.

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(VehicleId);
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;