How to get started with trajectory tables-PolarDB(PolarDB)-阿里云帮助中心

Trajectory tables store trajectory objects — aggregated sequences of spatio-temporal sample points — in a single row per entity. Compared to raw sample-point tables, trajectory tables reduce storage costs and make full-path spatio-temporal queries more efficient.

The examples on this page use a travel app dataset to show how to create a trajectory table, populate it using three insertion methods, and query it with a spatial index.

Create a trajectory table

CREATE TABLE trajectory_table(
    userid numeric PRIMARY KEY,
    traj trajectory
);

The traj column uses the trajectory data type provided by GanosBase, PolarDB's spatio-temporal engine.

Insert trajectories

GanosBase supports three ways to construct a trajectory object. Choose based on your data source:

Method	When to use	Key function or syntax
Convert a vertex table	Raw sample points already exist in a table	`ST_MakeTrajectory()` + `GROUP BY` + `ST_Sort()`
Construct from geometry and timestamps	You have a LINESTRING and a timestamp array	`ST_MakeTrajectory()`
Cast from a JSON string	Your application generates trajectory data as JSON	`::trajectory` cast

Convert a vertex table

Aggregate sample points from an existing vertex table using array_agg() and ST_MakeTrajectory().

GROUP BY does not guarantee chronological ordering. Always wrap the result with ST_Sort() to sort sample points by timestamp before inserting.

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;

For more information about vertex tables, see Use vertex tables.

Construct from geometry and timestamps

Pass a LINESTRING geometry, a timestamp array, and an attribute JSON string directly to ST_MakeTrajectory(). The trajectory below consists of three points with their intensity attribute values.

INSERT INTO trajectory_table
SELECT 3, ST_MakeTrajectory(
            'STPOINT'::leaftype,
            st_geomfromtext('LINESTRING(114.35 39.28 4,114.36 39.28 4,114.35 39.29 4)', 4326),
            ARRAY['2020-04-11 17:42:30'::timestamp,'2020-04-11 17:43:30'::timestamp,'2020-04-11 17:45:00'::timestamp],
            '{"leafcount":3,"attributes":{"intensity":{"type":"integer","length":4,"nullable":true,"value":[80,30,50]}}}'
);

ST_MakeTrajectory() also accepts GanosBase geometry types. For the full function signature and geometry type reference, see ST_makeTrajectory and Geometries in GanosBase.

Cast from a JSON string

If your application serializes trajectory data as JSON, cast the string directly to the trajectory type.

INSERT INTO trajectory_table
SELECT 4,
'{"trajectory":{"version":1,"type":"STPOINT","leafcount":3,"start_time":"2020-04-11 17:42:30","end_time":"2020-04-11 17:45:00","spatial":"SRID=4326;LINESTRING(114.35 39.28 4,114.36 39.28 4,114.35 39.29 4)","timeline":["2020-04-11 17:42:30","2020-04-11 17:43:30","2020-04-11 17:45:00"],"attributes":{"leafcount":3,"intensity":{"type":"integer","length":4,"nullable":true,"value":[80,30,50]}}}}'
::trajectory;

Verify the inserted data

Run a quick query to confirm the trajectory was stored correctly:

SELECT userid, traj FROM trajectory_table WHERE userid = 3;

Query trajectories with a spatial index

Trajectory data spans both spatial and temporal dimensions, so a standard B-tree index is not suited for multi-dimensional range queries. Create a GiST (Generalized Search Tree) index on the traj column to accelerate spatial queries and avoid full-table scans on large datasets.

-- Create a GiST index on the trajectory column
CREATE INDEX ON trajectory_table USING gist(traj);

With the index in place, run spatial queries using GanosBase spatio-temporal functions. The following query returns the userid of all trajectories that intersect a two-dimensional rectangle:

-- Return users whose trajectories intersect the specified bounding box
SELECT userid FROM trajectory_table WHERE ST_2DIntersects(traj, ST_MakeEnvelope(114.33,39.28,14.331,39.282));

What's next

ST_makeTrajectory — full parameter reference for trajectory construction
Use vertex tables — how to prepare sample point data before converting to trajectories
Geometries in GanosBase — geometry type reference for use with ST_MakeTrajectory()