GanosBase Trajectory

Introduction

A trajectory model is a collection of moving objects that meet certain conditions. It is often used in fields such as transportation, logistics, travel, and automotive.

Ganos Trajectory is a spatiotemporal engine extension for PolarDB for PostgreSQL (Compatible with Oracle). This data type, developed by Ganos, is used to store sampling points and attribute information of moving objects and to perform analysis tasks on them.

Function overview

Trajectory supports multiple features for trajectory data storage and analysis:

• Trajectory construction: Construct trajectories from data sources such as geometries, timestamps, arrays, and data tables.

• Trajectory editing: Edit object information such as the time, geometry, properties, and events of a trajectory. This feature also supports trajectory thinning, splitting, smoothing, sub-trajectory extraction, and spatiotemporal feature editing.

• Trajectory analysis: Perform spatial and spatiotemporal relationship determination, similarity analysis, and feature extraction for trajectories.

• Trajectory indexing: Create spatiotemporal indexes for trajectories to accelerate query and analysis.

For more information, see Trajectory SQL reference.

Main business scenarios

The following are typical application scenarios for the trajectory model.

• Archive storage for historical trajectories

  Aggregate high-frequency sampling points, such as those from shared bikes, into trajectories. You can use trajectory simplification algorithms to simplify and downsample the data. Store the simplified trajectories in a database or an external OSS bucket to reduce storage space and costs. Ganos lets you access trajectory data in a unified way, regardless of whether it is stored in a data table or in OSS.

• Spatiotemporal analysis and following relationship analysis of trajectories

  Specify a spatiotemporal area or trajectory of interest to find trajectories that pass through it or are similar to a known trajectory. This helps identify relevant trajectories in the database to support services such as locating specific user groups or distributing coupons.

• Feature extraction and analysis of trajectories

  Process a user's historical trajectory data by resampling and removing drift points. Extract statistical information such as length, duration, stay points, and turns. Use this information as feature input for machine learning in neural networks or other algorithms. This helps generate user personas to guide user management and recommendations.

Overview

In the real world, a moving object's spatial position is recorded at different times. A trajectory is a sequence of points containing time and space information that records the object's path over time.

For example, a shared bike reports its longitude and latitude coordinates (114.35, 39.28) at 2020-04-11 17:42:30. This can be represented as a record in the database:

Usually, other information is also recorded at the trajectory's sampling points, such as speed and direction. Assume that speed data is recorded:

Over time, a series of trajectory points will be generated. Assume there are three records:

These three points combine to form a spatiotemporal trajectory. Its shape is shown in the following figure:

The Ganos trajectory model aggregates and stores multiple trajectory points of a moving object. Compared to storing each trajectory point separately, this method has two advantages. First, you can compress multiple trajectory points to save storage costs. Second, you can perform various operations on the entire aggregated trajectory, not just on individual points. Examples include trajectory intersection, sub-trajectory extraction, and trajectory similarity determination.

In addition, Ganos supports saving event properties for a trajectory. These properties record event information that occurs at different times and is independent of individual sampling points. The format is a tuple of {type:timestamp}, where `type` is a user-defined event number (integer) and `timestamp` is the time the event occurred.

Storage methods

For business purposes, there are three common ways to represent the trajectory of a moving object:

• Method 1: Store as rows in a table. Each row records information such as time, x-coordinate, and y-coordinate.

• Method 2: Store as a 2D or 3D geometry type, LINESTRING or LINESTRING M. Use the M dimension to store timestamps.

• Method 3: Store as a trajectory type.

Method 1 is easy to update but has low query efficiency and uses more storage space. Method 2 has lower update efficiency but improves spatial query efficiency and saves storage space. However, it cannot store attribute information. Method 3 enhances the time processing capabilities of Method 2 and supports properties and events. You can choose the storage method that best suits your needs and convert it to other representations for processing and analysis during queries.

-- Construct data stored as rows, where 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 from row type to trajectory type
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 from trajectory type to row type
SELECT f.* from trajectory_table,ST_AsTable(traj) as f(t timestamp, x double precision, y double precision, z double precision, intensity integer);

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

-- Construct a trajectory using a LINESTRING spatial shape and a time range
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 Ganos Trajectory consists of four parts: time, space, properties, and events. When converted to text output, it is formatted into JSON according to these four parts. The following is an example:

{
  "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"
      }
    ]
  }
}

Where:

• version, type: Static fields.

• leafcount: The number of trajectory sampling points.

• start_time, end_time: The start and end times of the trajectory.

• spatial: The spatial shape of the trajectory, displayed in Well-Known Text (WKT) format.

• timeline: The timestamps of the sampling points, displayed as a character array.

• attributes: The properties of the trajectory. `leafcount` is the number of elements. The other keys are the property names. For each property, `type` is the data type, `length` is the length of the data type, and `nullable` indicates whether null values are allowed.

• events: The events of the trajectory, consisting of an array of key-value pairs.

Spatial reference system

A Spatial Reference System (SRS) defines how to associate a Trajectory object with a specific location on the Earth's surface.

Ganos uses an integer, known as a Spatial Reference System Identifier (SRID), to reference the SRS definition. A Trajectory object is associated with an SRS through its SRID.

For more information, see Spatial reference.

Data column view

The `trajectory_columns` view reads all trajectory columns from the database system catalog tables. Its structure is as follows:

You can use the following statement to query all geometric data columns in the current database:

SELECT * FROM trajectory_columns;

Index

Ganos provides a GiST index for trajectory data:

Implementation standards

The Ganos trajectory model implements the interfaces defined by the Open Geospatial Consortium (OGC) Moving Features standard and extends them. The geometric properties in a trajectory are implemented according to the geometry model. You can use the ST_trajectorySpatial function to extract and operate on the geometric part of a trajectory.

Introduction

This quick start guide demonstrates the basic usage of the Ganos Trajectory engine, including how to create extensions, create tables, insert data, create indexes, run queries, and perform similarity analysis.

For more advanced usage, see the following Trajectory best practices articles:

• Analysis of Common Processing Methods for Ganos Trajectory Sampling Points

• Reachability Analysis of Transport Vehicles Based on the Ganos Trajectory Engine

Syntax description

• Create an extension.

  CREATE extension ganos_trajectory cascade;

  Note

  Install 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.

   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 spatiotemporal filtering.
   CREATE index tr_index ON traj_table USING trajgist (traj);
  
   -- Accelerate spatial filtering during spatial queries.
   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))'));
  
   -- Accelerate time filtering during time queries.
   SELECT id, traj FROM traj_table WHERE st_TIntersects(traj, '2010-01-01 12:30:44'::timestamp,'2010-01-01 14:30:44'::timestamp);
  
   -- Accelerate spatiotemporal filtering during spatiotemporal queries.
   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 and end times of a trajectory.

   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 between 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 reference

For detailed SQL reference, see Trajectory SQL reference.