PolarDB:Trajectory FAQ

How do I convert coordinate points into a trajectory object?

Use ST_MakeTrajectory to build a trajectory object from coordinate point data. The example below reads x, y, t, and speed columns from a point table, aggregates them into arrays, and inserts the result as a trajectory.

-- Enable the Ganos trajectory extension.
CREATE EXTENSION ganos_trajectory CASCADE;

-- Create a point table.
CREATE TABLE points (id INTEGER, x FLOAT8, y FLOAT8, t TIMESTAMP, speed FLOAT8);
INSERT INTO points VALUES (1, 128.1, 28.1, '2019-01-01 00:00:00', 100);
INSERT INTO points VALUES (2, 128.2, 28.2, '2019-01-01 00:00:01', 101);
INSERT INTO points VALUES (3, 128.3, 28.3, '2019-01-01 00:00:02', 102);
INSERT INTO points VALUES (4, 128.4, 28.4, '2019-01-01 00:00:04', 103);

-- Create a trajectory table.
CREATE TABLE traj (id INTEGER, traj TRAJECTORY);

-- Aggregate coordinate points into a trajectory object.
INSERT INTO traj (id, traj)
SELECT 1,
    ST_MakeTrajectory('STPOINT'::leaftype, x, y, 4326, t, ARRAY['speed'], NULL, s, NULL)
FROM (
    SELECT array_agg(x ORDER BY id) AS x,
           array_agg(y ORDER BY id) AS y,
           array_agg(t ORDER BY id) AS t,
           array_agg(speed ORDER BY id) AS s
    FROM points
) a;

What if the built-in ST\_MakeTrajectory constructor doesn't meet my requirements?

Define a custom overloaded function using the same C library entry point. The function signature always has six fixed parameters — type, x, y, srid, timespan, and attrs_name — followed by your custom attribute parameters.

The example below adds five custom attributes: two INT8 fields, two FLOAT4 fields, and one TIMESTAMP field.

CREATE OR REPLACE FUNCTION ST_MakeTrajectory(
    type       leaftype,
    x          FLOAT8[],
    y          FLOAT8[],
    srid       INTEGER,
    timespan   TIMESTAMP[],
    attrs_name cstring[],
    attr1      INT8[],       -- custom attribute 1 (int8)
    attr2      INT8[],       -- custom attribute 2 (int8)
    attr3      FLOAT4[],     -- custom attribute 3 (float4)
    attr4      FLOAT4[],     -- custom attribute 4 (float4)
    attr5      TIMESTAMP[]   -- custom attribute 5 (timestamp)
)
RETURNS TRAJECTORY
AS '$libdir/libpg-trajectory16', 'sqltr_traj_make_all_array'
LANGUAGE 'c' IMMUTABLE PARALLEL SAFE;

Call this function the same way you call the built-in version. PostgreSQL resolves the correct overload based on the argument types you pass.

How do I append trajectory points to an existing trajectory object?

Use ST_append. It has two signatures depending on what you're appending:

-- Append from raw spatial + timestamp data.
trajectory ST_append(trajectory traj, geometry spatial, timestamp[] timespan, text str_theme_json);

-- Append from another trajectory object.
trajectory ST_append(trajectory traj, trajectory tail);

The examples below all use the second signature — appending a new trajectory object to an existing one.

Set up the initial data

-- Enable the Ganos trajectory extension.
CREATE EXTENSION ganos_trajectory CASCADE;

-- Create a point table and insert initial points.
CREATE TABLE points (id INTEGER, x FLOAT8, y FLOAT8, t TIMESTAMP, speed FLOAT8);
INSERT INTO points VALUES (1, 128.1, 28.1, '2019-01-01 00:00:00', 100);
INSERT INTO points VALUES (2, 128.2, 28.2, '2019-01-01 00:00:01', 101);
INSERT INTO points VALUES (3, 128.3, 28.3, '2019-01-01 00:00:02', 102);
INSERT INTO points VALUES (4, 128.4, 28.4, '2019-01-01 00:00:04', 103);

-- Create a trajectory table and insert the initial trajectory object.
CREATE TABLE traj (id INTEGER, traj TRAJECTORY);
INSERT INTO traj (id, traj)
SELECT 1,
    ST_MakeTrajectory('STPOINT'::leaftype, x, y, 4326, t, ARRAY['speed'], NULL, s, NULL)
FROM (
    SELECT array_agg(x ORDER BY id) AS x,
           array_agg(y ORDER BY id) AS y,
           array_agg(t ORDER BY id) AS t,
           array_agg(speed ORDER BY id) AS s
    FROM points
) a;

Append a single new point (queried from the table)

INSERT INTO points VALUES (5, 128.5, 28.5, '2019-01-01 00:00:05', 105);

WITH point_traj AS (
    SELECT ST_MakeTrajectory('STPOINT'::leaftype, x, y, 4326, t, ARRAY['speed'], NULL, s, NULL) AS traj
    FROM (
        SELECT array_agg(x ORDER BY id) AS x,
               array_agg(y ORDER BY id) AS y,
               array_agg(t ORDER BY id) AS t,
               array_agg(speed ORDER BY id) AS s
        FROM points WHERE id = 5
    ) a
)
UPDATE traj
SET traj = ST_append(traj.traj, a.traj)
FROM point_traj a
WHERE traj.id = 1;

Append a single new point (inline values)

WITH point_traj AS (
    SELECT ST_MakeTrajectory(
        'STPOINT'::leaftype,
        ARRAY[128.5::FLOAT8],
        ARRAY[28.5::FLOAT8],
        4326,
        ARRAY['2019-01-01 00:00:05'::TIMESTAMP],
        ARRAY['speed'],
        NULL,
        ARRAY[106::FLOAT8],
        NULL
    ) AS traj
)
UPDATE traj
SET traj = ST_append(traj.traj, a.traj)
FROM point_traj a
WHERE traj.id = 1;

Append multiple new points

INSERT INTO points VALUES (6, 128.6, 28.6, '2019-01-01 00:00:06', 106);
INSERT INTO points VALUES (7, 128.7, 28.7, '2019-01-01 00:00:07', 107);

WITH point_traj AS (
    SELECT ST_MakeTrajectory('STPOINT'::leaftype, x, y, 4326, t, ARRAY['speed'], NULL, s, NULL) AS traj
    FROM (
        SELECT array_agg(x ORDER BY id) AS x,
               array_agg(y ORDER BY id) AS y,
               array_agg(t ORDER BY id) AS t,
               array_agg(speed ORDER BY id) AS s
        FROM points WHERE id > 5
    ) a
)
UPDATE traj
SET traj = ST_append(traj.traj, a.traj)
FROM point_traj a
WHERE traj.id = 1;

How do I enable LZ4 compression for trajectory data?

LZ4 is an advanced compression algorithm that has a higher compression ratio and execution speed.

Session level — applies to the current session only:

-- Enable LZ4 compression.
SET toast_compression_use_lz4 = true;

-- Revert to the default PostgreSQL compression algorithm.
SET toast_compression_use_lz4 = false;

Database level — applies to all future sessions:

-- Enable LZ4 compression for the entire database.
ALTER DATABASE dbname SET toast_compression_use_lz4 = true;

-- Revert to the default compression algorithm for the entire database.
ALTER DATABASE dbname SET toast_compression_use_lz4 = false;

How do I set a default length for string-type attribute fields?

Set the ganos.trajectory.attr_string_length GUC variable to the desired length for string-type attribute fields in trajectory objects created in the current session.

SET ganos.trajectory.attr_string_length = 32;

How do I calculate the maximum, minimum, or average value of an attribute field?

Use ST_trajAttrsAsInteger (or the equivalent function for other data types) with unnest to expand attribute values into a set of rows, then apply a standard aggregate function.

The example below calculates the average of the velocity attribute:

WITH traj AS (
    SELECT '{"trajectory":{"version":1,"type":"STPOINT","leafcount":2,"start_time":"2010-01-01 11:30:00","end_time":"2010-01-01 12:30:00","spatial":"SRID=4326;LINESTRING(1 1,3 5)","timeline":["2010-01-01 11:30:00","2010-01-01 12:30:00"],"attributes":{"leafcount":2,"velocity":{"type":"integer","length":4,"nullable":true,"value":[1,100]},"speed":{"type":"float","length":8,"nullable":true,"value":[null,1.0]},"angel":{"type":"string","length":64,"nullable":true,"value":["test",null]},"tngel2":{"type":"timestamp","length":8,"nullable":true,"value":["2010-01-01 12:30:00",null]},"bearing":{"type":"bool","length":1,"nullable":true,"value":[null,true]}}}}'::trajectory a
)
SELECT avg(v)
FROM (
    SELECT unnest(ST_trajAttrsAsInteger(a, 'velocity')) AS v
    FROM traj
) t;

Replace avg with max or min to get the maximum or minimum value instead.