How to use GanosBase Trajectory (PolarDB for PostgreSQL (Compatible with Oracle)) - PolarDB

GanosBase Trajectory stores and queries moving object data in PolarDB for PostgreSQL (Compatible with Oracle). It aggregates trajectory points into a single trajectory data type, reducing storage overhead and enabling spatio-temporal queries, similarity analysis, and feature extraction.

Quick start

Create the extension

CREATE extension ganos_trajectory cascade;

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

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

ST_MakeTrajectory accepts multiple input formats:

tsrange (rows 1 and 4) -- a PostgreSQL timestamp range.
Start and end timestamps (row 2) -- two separate timestamp values.
Timestamp array (row 3) -- an array of individual timestamps for each trajectory point.
Null attributes (row 4) -- omit attributes by passing null.

Create a spatio-temporal index

Create a trajgist index to accelerate spatial, temporal, and spatio-temporal queries:

CREATE index tr_index ON traj_table USING trajgist (traj);

Query trajectories

Spatial query -- find trajectories intersecting a polygon:

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))'));

Temporal query -- find trajectories within a time range:

SELECT id, traj FROM traj_table WHERE ST_TIntersects(traj, '2010-01-01 12:30:44'::timestamp,'2010-01-01 14:30:44'::timestamp);

Spatio-temporal query -- combine spatial and temporal filters:

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 start and end times

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)

Measure trajectory similarity

Use ST_EuclideanDistance to compute the Euclidean distance between two 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

DROP EXTENSION ganos_trajectory CASCADE;

Trajectory components

A trajectory records the spatial position of a moving object at different points in time. Each trajectory point captures a timestamp, coordinates (longitude and latitude), and optional attributes such as speed or direction.

For example, a vehicle reporting its position generates records like these:

time	x	y	speed
2020-04-11 17:42:30	114.35	39.28	4.3
2020-04-11 17:43:30	114.36	39.28	4.8
2020-04-11 17:45:00	114.35	39.29	3.5

GanosBase aggregates these trajectory points into a single trajectory object instead of storing individual rows. This saves storage space and supports operations on entire trajectories, such as intersection detection, sub-trajectory extraction, and similarity analysis.

Every trajectory consists of four parts:

Time -- timestamps for each trajectory point.
Space -- geographic coordinates forming a path.
Attributes -- per-point data such as velocity, accuracy, or bearing.
Events -- timestamped occurrences stored as 2-tuples in {type:timestamp} format, where type is a user-defined integer event number and timestamp is when the event occurred.

Supported operations

GanosBase Trajectory provides four categories of operations:

Construction -- build trajectories from geometries, timestamps, arrays, or data tables.
Editing -- modify time, geometries, attributes, and events. Simplify, split, smooth, or extract sub-trajectories.
Analysis -- identify spatial and spatio-temporal relationships, measure similarity, and extract features.
Indexing -- create spatio-temporal indexes to accelerate queries.

For the full function list, see Trajectory SQL reference.

Use cases

Archive historical trajectories

Aggregate high-frequency trajectory points into trajectories, then simplify and downsample them for long-term storage in the database or OSS buckets. GanosBase provides unified access to trajectory data regardless of where it is stored.

Spatio-temporal and similarity analysis

Query trajectories that pass through a specific spatio-temporal area or match a given trajectory pattern. Use the results for business operations such as customer segmentation and targeted promotions.

Feature extraction

Cleanse trajectory data by resampling and removing drift points. Extract statistics such as length, duration, stay points, and curves. Feed the extracted features into machine learning pipelines for user profiling and recommendations.

Storage methods

GanosBase supports three storage methods for trajectory data. Choose one based on your workload requirements.

Method	Description	Characteristics
Table rows	Each row stores one trajectory point (time, x, y).	High-performance updates. Low query performance. High storage usage.
2D/3D geometry	LINESTRING or LINESTRING M. The M dimension stores timestamps.	Regular updates. High-performance spatial queries. Low storage usage. Does not support attributes.
Trajectory type	GanosBase `trajectory` data type. Extends 2D/3D geometry with enhanced temporal processing.	Supports attributes and events.

Convert between formats as needed:

-- 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 table rows to the 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 the trajectory type back 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);

-- Extract spatial information as a LINESTRING.
SELECT ST_TrajSpatial(traj) FROM trajectory_table;

-- Construct a trajectory from a LINESTRING and 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

When converted to text, a trajectory outputs as JSON with four sections: time, space, attributes, and events.

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

Field descriptions

Field	Description
`version`, `type`	Fixed values indicating the trajectory version and type.
`leafcount`	Number of trajectory points.
`start_time`, `end_time`	Start and end timestamps of the trajectory.
`spatial`	Spatial shape in WKT (Well-Known Text) format.
`timeline`	Timestamp of each trajectory point, as a string array.
`attributes`	Per-point attributes. `leafcount` indicates the number of elements. Each attribute contains `type` (data type), `length` (data type length), `nullable` (whether null values are accepted), and `value` (array of values). Supported types: `integer`, `float`, `string`, `timestamp`.
`events`	Array of key-value pairs representing events.

Spatial reference system

A spatial reference system (SRS) defines how to associate a trajectory object with a specific location on Earth. GanosBase uses an integer SRID (Spatial Reference System Identifier) to reference the SRS definition. Each trajectory object is associated with an SRS through its own SRID value.

For more information, see Spatial reference.

Data column view

The trajectory_columns view lists all trajectory-type columns from the database system catalog.

Column	Type	Description
`t_table_catalog`	varchar(256)	Fixed value: `postgres`.
`t_table_schema`	varchar(256)	Schema of the table.
`t_table_name`	varchar(256)	Name of the table.
`t_trajectory_column`	varchar(256)	Name of the trajectory column.

Query all trajectory columns in the current database:

SELECT * FROM trajectory_columns;

Index

GanosBase supports the GiST (Generalized Search Tree) index for trajectory data. GiST is a balanced search tree commonly used for spatial indexing. It provides high query performance and allows custom rules for distributing and accessing data of any type on a balanced tree.

Implementation standard

GanosBase Trajectory supports and extends the interfaces defined by the OGC Moving Features standard. The geometric attributes of a trajectory are implemented based on the geometry model. Use the ST_TrajectorySpatial function to extract geometric attributes for further processing.

SQL reference

For the complete list of trajectory SQL functions, see Trajectory SQL reference.