Database for Historical Point Data

This document outlines the MobilityDB database used to store and manage trajectory data for the SILAS Web demo application.

1. Database Structure

The MobilityDB database is designed to efficiently store and query trajectory data, leveraging the capabilities of the MobilityDB extension for PostgreSQL. The database schema includes the following tables:

1.1. trajectories

• Columns:

  • order_id: A unique identifier for each trajectory.
  • time_stamp: The timestamp of the trajectory point (in milliseconds).
  • altitude: The altitude of the trajectory point (in meters).
  • speed: The speed of the trajectory point (in meters per second).
  • yaw: The yaw angle of the trajectory point (in degrees).
  • longitude: The longitude of the trajectory point (in decimal degrees).
  • latitude: The latitude of the trajectory point (in decimal degrees).
  • vendor: The vendor of the device that generated the trajectory data.
  • sn: The serial number of the device that generated the trajectory data.

• Data Type:

  • order_id: TEXT
  • time_stamp: BIGINT
  • altitude: FLOAT
  • speed: FLOAT
  • yaw: FLOAT
  • longitude: FLOAT
  • latitude: FLOAT
  • vendor: TEXT
  • sn: TEXT

• Data Example:

2. Database Access

The database can be accessed using the psql command-line tool. To connect to the database, execute the following command:

$ sudo -i -u postgres
$ postgres@user:~$ psql -d mobilitydb_db -p 5432

3. Data Management

The database is managed using SQL queries. For example, to retrieve all trajectories within a specific time range and spatial bounds, you can use the following query:

SELECT * FROM trajectories
WHERE time_stamp BETWEEN 1697600500000 AND 1697624000000
AND longitude BETWEEN 114.010 AND 114.020
AND latitude BETWEEN 22.669 AND 22.670;