Build a Moving Object Trajectory System with GanosBase - AnalyticDB for PostgreSQL

GanosBase Trajectory is an extension of AnalyticDB for PostgreSQL that adds native support for spatio-temporal trajectory data. It provides data types, functions, and stored procedures for storing, indexing, querying, and analyzing trajectories — continuous location histories of moving features such as vehicles or people.

Key concepts

A trajectory captures how a moving feature moves through space over time. Each trajectory combines:

Spatial path — a LINESTRING geometry recording the sequence of positions
Time range — the start and end timestamps of the movement
Attributes — sensor readings sampled at each position, such as velocity, bearing, accuracy, and acceleration

The leaftype enumeration defines what each trajectory point represents: STPOINT for a spatio-temporal point, or STPOLYGON for a spatio-temporal polygon.

Get started

This section covers the full lifecycle: installing extensions, creating a table, inserting trajectories, building indexes, and running queries.

Prerequisites

Before you begin, ensure that you have:

An AnalyticDB for PostgreSQL instance
Superuser or extension-creation privileges on the target database

Install extensions

Install the extensions in dependency order:

CREATE EXTENSION ganos_spatialref;
CREATE EXTENSION ganos_geometry;
CREATE EXTENSION Ganos_trajectory;

ganos_spatialref and ganos_geometry are required dependencies of Ganos_trajectory. Create them first.

Create a trajectory table

Define the leaftype enum and create a trajectory table distributed by ID:

CREATE TYPE leaftype AS ENUM ('STPOINT', 'STPOLYGON');

CREATE TABLE traj_table (id integer, traj trajectory) DISTRIBUTED BY (id);

Insert trajectories

ST_MakeTrajectory constructs a trajectory from a geometry path, a time range, and an attribute JSON payload. The time range accepts three formats: a tsrange, a start and end timestamp pair, or a timestamp array.

INSERT INTO traj_table VALUES
  -- Option 1: tsrange
  (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]}
          }}'
  )),
  -- Option 2: start and end timestamps
  (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]}
          }}'
  )),
  -- Option 3: timestamp array (one timestamp per leaf point)
  (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]}
          }}'
  )),
  -- Minimal trajectory without attributes
  (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
  ));

The attribute JSON schema:

Field	Description
`leafcount`	Number of sample points in the trajectory
`attributes.<name>.type`	Data type of the attribute: `integer` or `float`
`attributes.<name>.length`	Byte length of each value
`attributes.<name>.nullable`	Whether null values are allowed
`attributes.<name>.value`	Array of attribute values, one per leaf point

Create trajectory indexes

GanosBase Trajectory uses GiST indexes to accelerate spatial, temporal, and spatio-temporal queries. Choose the index type that matches your query pattern:

Index type	Operator class	Accelerates
3D spatio-temporal (default)	(none — omit operator class)	`st_3dintersects` with or without time bounds, `st_TContains`
2D spatio-temporal	`trajgist_op_2dt`	`st_2dintersects` with time bounds
2D spatial only	`trajgist_op_2d`	`st_2dintersects` without time bounds

3D spatio-temporal index (default)

Covers spatial, temporal, and combined spatio-temporal queries:

CREATE INDEX tr_index ON traj_table USING gist (traj);

Use this index for:

-- Spatial filter: trajectories passing through a polygon
SELECT id FROM traj_test
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 filter: trajectories active within a time window
SELECT id FROM traj_text
WHERE st_TContains(traj,
  '2008-02-02 13:30:44'::timestamp,
  '2008-02-03 17:30:44'::timestamp);

-- Combined filter: trajectories that intersect a polygon within a time window
SELECT id FROM traj_test
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))'),
  '2008-02-02 13:30:44'::timestamp,
  '2008-02-03 17:30:44'::timestamp);

2D spatio-temporal index

For queries that combine 2D spatial and temporal filters:

CREATE INDEX tr_timespan_time_index ON traj_table USING gist (traj trajgist_op_2dt);

Use this index for:

SELECT id FROM traj_test
WHERE st_2dintersects(traj,
  ST_GeomFromText(
    'POLYGON((116.46747851805917 39.92317964155052,
              116.4986540687358  39.92317964155052,
              116.4986540687358  39.94452401711516,
              116.46747851805917 39.94452401711516,
              116.46747851805917 39.92317964155052))'),
  '2008-02-02 13:30:44'::timestamp,
  '2008-02-03 17:30:44'::timestamp);

2D spatial-only index

For queries that filter by spatial extent only, with no time constraint:

CREATE INDEX tr_timespan_time_index ON trajtab USING gist (traj trajgist_op_2d);

Use this index for:

SELECT id FROM traj_test
WHERE st_2dintersects(traj,
  ST_GeomFromText(
    'POLYGON((116.46747851805917 39.92317964155052,
              116.4986540687358  39.92317964155052,
              116.4986540687358  39.94452401711516,
              116.46747851805917 39.94452401711516,
              116.46747851805917 39.92317964155052))'));

You can create multiple indexes on the same table. AnalyticDB for PostgreSQL automatically selects the optimal index for each query.

Query trajectory data

Get time bounds

SELECT st_startTime(traj), st_endTime(traj) FROM traj_table;

Expected output:

     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
 2010-01-01 14:30:00 | 2010-01-01 15:30:00
 2010-01-01 11:30:00 | 2010-01-01 15:00:00
 2010-01-01 11:30:00 | 2010-01-01 15:00:00
 2010-01-01 11:30:00 | 2010-01-01 15:00:00
(8 rows)

Read an attribute value at a specific time

ST_velocityAtTime interpolates the velocity attribute at any point in the trajectory's time range, including between recorded leaf points:

SELECT ST_velocityAtTime(traj, '2010-01-01 12:45') FROM traj_table WHERE id > 5;

Expected output:

 st_velocityattime
-------------------
 5
 5
 4.16666666666667
(3 rows)

Measure proximity between trajectories

ST_euclideanDistance returns the Euclidean distance between two trajectories:

SELECT ST_euclideanDistance(
  (SELECT traj FROM traj_table WHERE id = 6),
  (SELECT traj FROM traj_table WHERE id = 7)
);

Expected output:

 st_euclideandistance
----------------------
   0.0334968923954815
(1 row)

Uninstall extensions

DROP Extension Ganos_Raster;
DROP extension ganos_geometry;
Drop Extension Ganos_trajectory;

SQL reference

For a full SQL reference — including all supported data types, functions, and stored procedures — see Basic concepts.