PolarDB:GanosBase Trajectory

Overview

A trajectory model is a collection of moving objects that meet certain conditions. It is usually used in areas such as transportation, logistics, travelling, and automobile.

GanosBase Trajectory is a spatio-temporal engine extension of PostgreSQL (PolarDB for PostgreSQL (Compatible with Oracle)). Trajectory is a self-developed data type provided by GanosBase to store the sampling points and attributes of moving objects and analyze the objects.

Features

Trajectory supports multiple features for trajectory data storage and analysis:

• Trajectory construction: allows you to build trajectories based on data sources such as geometries, timestamps, arrays, and data tables.

• Trajectory editing: allows you to edit the object information such as the time, geometries, attributes, and events of a trajectory. You can also perform other operations such as simplifying, splitting, and smoothing a trajectory, extracting sub-trajectories, and editing the spatio-temporal features of a trajectory.

• Trajectory analysis: allows you to identify the spatial relationship and spatio-temporal relationship, analyze the similarity, and extract features of trajectories.

• Trajectory indexing: allows you to create a spatio-temporal index to accelerate the preceding queries and analyses.

For more information, see Trajectory SQL reference.

Scenarios

GanosBase Trajectory can be used in the following scenarios.

• Archiving historical trajectories

  In some areas such as shared bicycles, GanosBase Trajectory can be used to aggregate high-frequency sampling points into trajectories. Such trajectories can be simplified and downsampled by using the trajectory simplification algorithm, and then stored in the database or OSS buckets to save storage costs. GanosBase allows you to access trajectory data in a unified manner, regardless of whether the data is stored in a database or in OSS.

• Spatio-temporal data analysis and similarity analysis of trajectories

  GanosBase Trajectory allows you to query trajectories that pass through a specific spatio-temporal area or are similar to a specific trajectory. In this way, you can identify the trajectories that you can use for your business operations such as identifying and supporting a specific type of customers, and issuing coupons.

• Trajectory feature extraction and analysis

  GanosBase Trajectory can be used to cleanse historical trajectories of users by resampling and removing drift points and extract statistical information such as length, duration, stay points, and curves. Then you can use the information for machine learning based on neural network or other algorithms to generate user profiles that can be used for user management and recommendation.

Overview

A trajectory records the spatial positions of a moving object in the real world at different time points. The trajectory of an object is a sequence of points with spatio-temporal information, which records the spatial position of the object at different time points.

For example, if an object reports its latitude and longitude (114.35,39.28) at 17:42:30 on April 11, 2020, the following record is generated in the database.

The sampling points of the trajectory usually record additional information, such as speed and direction. In the following example, speed is recorded in the trajectory data:

Over time, a set of trajectory points are generated. In this example, the records of three trajectory points are generated.

These trajectory points compose a spatio-temporal trajectory, as shown in the following figure.

GanosBase Trajectory aggregates multiple trajectory points of a moving object for storage instead of storing individual trajectory points. This allows you to save storage costs by compressing multiple trajectory points. You can also perform operations on the entire aggregated trajectory rather than on a single point, such as trajectory intersection identification, sub-trajectory extraction, and trajectory similarity identification.

In addition, GanosBase supports the event attribute to record events at different time points that are irrelevant to a single sampling point. The events are recorded as 2-tuples in the {type:timestamp} format. In this format, type is the user-defined event number (integer type) and timestamp is the time when an event occurs.

Storage

The trajectory data of a moving object is usually stored in one of the following methods:

• Method 1: The data is stored as rows in a table. Each row records the information such as the time, x-coordinate, and y-coordinate of a point.

• Method 2: The data is stored as 2D or 3D geometries of the LINESTRING or LINESTRING M type. The M dimension is used to store timestamps.

• Method 3: The data is stored as the Trajectory type.

Method 1 provides high-performance update operations, low query performance, and high storage space usage. Method 2 provides regular update operations, high-performance spatial queries, and low storage space usage, but it does not support attributes. Method 3 provides enhanced temporal data processing capability based on method 2, and supports attributes and events. Choose the storage method based on your requirements. When you query the data, you can convert it into other representations for processing and analysis.

-- Store data as table rows. Each row represents a trajectory point.
CREATE TABLE sample_points(userid numeric, sample_time timestamp, x double precision, y double precision, z double precision, intensity int);
INSERT INTO sample_points VALUES
                            (1,'2020-04-11 17:42:30',114.35, 39.28, 4, 80),
                            (1,'2020-04-11 17:43:30',114.36, 39.28, 4, 30),
                            (1,'2020-04-11 17:45:00',114.35, 39.29, 4, 50),
                            (2,'2020-04-11 17:42:30',114.3, 39, 34, 60),
                            (2,'2020-04-11 17:43:30',114.3, 39, 38, 58);

-- Convert the data from table rows to the trajectory type. Each row represents a trajectory point.
CREATE TABLE trajectory_table(userid numeric PRIMARY KEY, traj trajectory);

INSERT INTO trajectory_table
SELECT userid, ST_Sort(ST_MakeTrajectory(pnts.tjraw, true, '{"intensity"}'::cstring[]))
FROM
  (SELECT sample_points.userid, array_agg(ROW(sample_points.sample_time, sample_points.x, sample_points.y, sample_points.z, sample_points.intensity)) as tjraw FROM sample_points GROUP BY userid) pnts;

-- Convert the data from the trajectory type to table rows.
SELECT f.* from trajectory_table,ST_AsTable(traj) as f(t timestamp, x double precision, y double precision, z double precision, intensity integer);

-- Convert the data from the trajectory type to the LINESTRING type, which contains only spatial information.
SELECT ST_trajspatial(traj) FROM trajectory_table;

-- Use the spatial and temporal data of the LINESTRING type to construct a trajectory.
SELECT ST_MakeTrajectory('STPOINT'::leaftype, st_geomfromtext('LINESTRING (114 35, 115 36, 116 37)', 4326), '[2010-01-01 14:30, 2010-01-01 15:30)'::tsrange, '{}');

Output format

A trajectory in GanosBase Trajectory consists of four parts: time, space, attributes, and events. When it is converted into text for output, it also contains four parts and follows the JSON format. Sample code:

{
  "trajectory": {
    "version": 1,
    "type": "STPOINT",
    "leafcount": 3,
    "start_time": "2010-01-01 14:30:00",
    "end_time": "2010-01-01 15:30:00",
    "spatial": "SRID=4326;LINESTRING(114 35,115 36,116 37)",
    "timeline": [
      "2010-01-01 14:30:00",
      "2010-01-01 15:00:00",
      "2010-01-01 15:30:00"
    ],
    "attributes": {
      "leafcount": 3,
      "velocity": {
        "type": "integer",
        "length": 2,
        "nullable": true,
        "value": [120, 130, 140]
      },
      "accuracy": {
        "type": "float",
        "length": 4,
        "nullable": false,
        "value": [120.0, 130.0, 140.0]
      },
      "bearing": {
        "type": "float",
        "length": 8,
        "nullable": false,
        "value": [120.0, 130.0, 140.0]
      },
      "acceleration": {
        "type": "string",
        "length": 20,
        "nullable": true,
        "value": ["120", "130", "140"]
      },
      "active": {
        "type": "timestamp",
        "length": 8,
        "nullable": false,
        "value": [
          "2010-01-01 14:30:00",
          "2010-01-01 15:00:00",
          "2010-01-01 15:30:00"
        ]
      }
    },
    "events": [
      {
        "1": "2010-01-01 14:30:00"
      },
      {
        "2": "2010-01-01 15:00:00"
      },
      {
        "3": "2010-01-01 15:30:00"
      }
    ]
  }
}

The following section describes the parameters in the preceding sample:

• version and type: fixed values that indicates the version and type.

• leafcount: the number of sampling points of the trajectory.

• start_time and end_time: the start time and end time of the trajectory.

• spatial: the spatial shape of the trajectory in the WKT format.

• timeline: the timestamp of the sampling point in the format of a string array.

• attributes: the attributes of the trajectory. The value of leafcount indicates the number of elements. Other keys are the names of attributes. In each attribute, type indicates the data type, length indicates the length of the data type, and nullable indicates whether the attribute accepts null values.

• events: the events of the trajectory, which consists of multiple arrays of key-value pairs.

Spatial reference system

The spatial reference system (SRS) defines how to associate a trajectory object to a specific location on the surface of the Earth.

GanosBase uses an integer as the SRID to reference the SRS definition. A Trajectory object is associated with SRS by using its own SRID value.

For more information, see Spatial reference.

Data column view

In GanosBase, the trajectory_columns view reads all relevant columns of the trajectory type from the system catalog table of the database. The view contains the following columns:

Execute the following statement to query all trajectory type columns in the current database:

SELECT * FROM trajectory_columns;

Index

GanosBase supports the GiST index for trajectory data:

Implementation standard

GanosBase trajectory supports and extends the interfaces defined by the OGC Moving Features standard. The geometric attributes of the trajectory are implemented based on the geometry model. You can use the ST_trajectorySpatial function to extract the geometric attributes for further processing.

Overview

This section describes how to use the GanosBase Trajectory engine, including extension creation, table creation, data import, index creation, data query, and similarity analysis.

Syntax

• Create an extension.

  CREATE extension ganos_trajectory cascade;

  Note

  Create the extension in the public schema to avoid permission issues.

  CREATE extension ganos_trajectory WITH schema public cascade;

• Create a trajectory table.

   CREATE TABLE traj_table (id integer, traj trajectory);

• Insert trajectory data records into the trajectory table.

   INSERT INTO traj_table VALUES
   (1, ST_MakeTrajectory('STPOINT'::leaftype, st_geomfromtext('LINESTRING (114 35, 115 36, 116 37)', 4326), '[2010-01-01 14:30, 2010-01-01 15:30)'::tsrange, '{"leafcount": 3,"attributes" : {"velocity" : {"type":"integer","length":4,"nullable":false,"value":[120, 130, 140]},"accuracy":{"type":"integer","length":4,"nullable":false,"value":[120, 130, 140]},"bearing":{"type":"float","length":4,"nullable":false,"value":[120, 130, 140]},"acceleration":{"type":"float","length":4,"nullable":false,"value":[120, 130, 140]}}}')),
   (2, ST_MakeTrajectory('STPOINT'::leaftype, st_geomfromtext('LINESTRING (114 35, 115 36, 116 37)', 4326), '2010-01-01 14:30'::timestamp, '2010-01-01 15:30'::timestamp, '{"leafcount": 3,"attributes" : {"velocity" : {"type":"integer","length":4,"nullable":false,"value":[120, 130, 140]},"accuracy":{"type":"integer","length":4,"nullable":false,"value":[120, 130, 140]},"bearing":{"type":"float","length":4,"nullable":false,"value":[120, 130, 140]},"acceleration":{"type":"float","length":4,"nullable":false,"value":[120, 130, 140]}}}')),
   (3, ST_MakeTrajectory('STPOINT'::leaftype, st_geomfromtext('LINESTRING (114 35, 115 36, 116 37)', 4326), ARRAY['2010-01-01 14:30'::timestamp, '2010-01-01 15:00'::timestamp, '2010-01-01 15:30'::timestamp], '{"leafcount": 3,"attributes" : {"velocity" : {"type":"integer","length":4,"nullable":false,"value":[120, 130, 140]},"accuracy":{"type":"integer","length":4,"nullable":false,"value":[120, 130, 140]},"bearing":{"type":"float","length":4,"nullable":false,"value":[120, 130, 140]},"acceleration":{"type":"float","length":4,"nullable":false,"value":[120, 130, 140]}}}')),
   (4, ST_MakeTrajectory('STPOINT'::leaftype, st_geomfromtext('LINESTRING (114 35, 115 36, 116 37)', 4326), '[2010-01-01 14:30, 2010-01-01 15:30)'::tsrange, null));

• Create a trajectory index to accelerate various queries.

  -- Create a trajectory index to accelerate the process of filtering spatio-temporal data records.
   CREATE index tr_index ON traj_table USING trajgist (traj);
  
   -- Run spatial data queries. You can find that the trajectory index accelerates the process of filtering spatial data records.
   SELECT id, traj FROM traj_table WHERE st_3dintersects(traj, ST_GeomFromText('POLYGON((116.46747851805917 39.92317964155052,116.4986540687358 39.92317964155052,116.4986540687358 39.94452401711516,116.46747851805917 39.94452401711516,116.46747851805917 39.92317964155052))'));
  
   -- Run temporal data queries. You can find that the trajectory index accelerates the process of filtering temporal data records.
   SELECT id, traj FROM traj_table WHERE st_TIntersects(traj, '2010-01-01 12:30:44'::timestamp,'2010-01-01 14:30:44'::timestamp);
  
   -- Run spatio-temporal data queries. You can find that the trajectory index accelerates the process of filtering spatio-temporal data records.
   SELECT id, traj FROM traj_table WHERE st_3dintersects(traj, ST_GeomFromText('POLYGON((116.46747851805917 39.92317964155052,116.4986540687358 39.92317964155052,116.4986540687358 39.94452401711516,116.46747851805917 39.94452401711516,116.46747851805917 39.92317964155052))'),'2010-01-01 13:30:44'::timestamp,'2010-01-03 17:30:44'::timestamp);

• Query the start time and end time of trajectories.

   SELECT st_startTime(traj), st_endTime(traj) FROM traj_table ;
       st_starttime     |     st_endtime
   ---------------------+---------------------
    2010-01-01 14:30:00 | 2010-01-01 15:30:00
    2010-01-01 14:30:00 | 2010-01-01 15:30:00
    2010-01-01 14:30:00 | 2010-01-01 15:30:00
    2010-01-01 14:30:00 | 2010-01-01 15:30:00
   (4 rows)

• Analyze the similarity of trajectories.

   With traj AS (
     SELECT ST_makeTrajectory('STPOINT', 'LINESTRING(1 1, 5 6, 9 8)'::geometry, '[2010-01-01 11:30, 2010-01-01 15:00)'::tsrange,
     '{"leafcount":3,"attributes":{"velocity": {"type": "integer", "length": 2,"nullable" : true,"value": [120,130,140]}, "accuracy": {"type": "float", "length": 4, "nullable" : false,"value": [120,130,140]}, "bearing": {"type": "float", "length": 8, "nullable" : false,"value": [120,130,140]}, "acceleration": {"type": "string", "length": 20, "nullable" : true,"value": ["120","130","140"]}, "active": {"type": "timestamp", "nullable" : false,"value": ["Fri Jan 01 11:35:00 2010", "Fri Jan 01 12:35:00 2010", "Fri Jan 01 13:30:00 2010"]}}, "events": [{"2" : "Fri Jan 02 15:00:00 2010"}, {"3" : "Fri Jan 02 15:30:00 2010"}]}') a,
     ST_makeTrajectory('STPOINT', 'LINESTRING(1 0, 4 2, 9 6)'::geometry, '[2010-01-01 11:30, 2010-01-01 15:00)'::tsrange,
     '{"leafcount":3,"attributes":{"velocity": {"type": "integer", "length": 2,"nullable" : true,"value": [120,130,140]}, "accuracy": {"type": "float", "length": 4, "nullable" : false,"value": [120,130,140]}, "bearing": {"type": "float", "length": 8, "nullable" : false,"value": [120,130,140]}, "acceleration": {"type": "string", "length": 20, "nullable" : true,"value": ["120","130","140"]}, "active": {"type": "timestamp", "nullable" : false,"value": ["Fri Jan 01 11:35:00 2010", "Fri Jan 01 12:35:00 2010", "Fri Jan 01 13:30:00 2010"]}}, "events": [{"2" : "Fri Jan 02 15:00:00 2010"}, {"3" : "Fri Jan 02 15:30:00 2010"}]}') b)
   SELECT ST_euclideanDistance(a, b) FROM traj;
    st_euclideandistance
   ----------------------
      0.0888997369940162
   (1 row)

• Delete the extension (optional).

  DROP EXTENSION ganos_trajectory CASCADE;

SQL statements

For more information, see Trajectory SQL reference.