Trajectory FAQ

How do I convert coordinate points into a trajectory object?

Use ST_MakeTrajectory to build a trajectory object from arrays of coordinates, timestamps, and attributes stored in a point table.

-- Enable the 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 the point rows into a single 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;

The call pattern is ST_MakeTrajectory(type, x[], y[], srid, timespan[], attrs_name[], ...attr_values...). The NULL placeholders map to attribute value slots not used by the built-in overload.

What if the built-in ST_MakeTrajectory constructor doesn't fit my data schema?

Define a user-defined overloaded function backed by the same C library. The first six parameters are fixed (type, x[], y[], srid, timespan[], attrs_name[]); the remaining parameters are your custom attributes.

The following example creates a constructor with five custom attributes: two INT8, two FLOAT4, and one TIMESTAMP.

CREATE OR REPLACE FUNCTION ST_MakeTrajectory(
    type       leaftype,
    x          FLOAT8[],
    y          FLOAT8[],
    srid       INTEGER,
    timespan   TIMESTAMP[],
    attrs_name CSTRING[],
    attr1      INT8[],
    attr2      INT8[],
    attr3      FLOAT4[],
    attr4      FLOAT4[],
    attr5      TIMESTAMP[]
)
RETURNS trajectory
AS '$libdir/libpg-trajectory16', 'sqltr_traj_make_all_array'
LANGUAGE 'c' IMMUTABLE PARALLEL SAFE;

Call this function the same way as the built-in version—PostgreSQL resolves the correct overload based on argument types.

How do I append points to an existing trajectory object?

Use ST_append. Two overloads are available:

-- Overload 1: append a geometry point with timestamps and a JSON theme
trajectory ST_append(trajectory traj, geometry spatial, timestamp[] timespan, text str_theme_json);

-- Overload 2: append another trajectory object
trajectory ST_append(trajectory traj, trajectory tail);

Use Overload 1 when you have a raw geometry value and want to specify theme metadata as JSON. Use Overload 2 when you have already built the new points into a trajectory object—this is the more common pattern when working with point tables.

The examples below use Overload 2. They build on the initial trajectory created in the first question; insert three new point rows first.

-- Add new raw points to the point table
INSERT INTO points VALUES (5, 128.5, 28.5, '2019-01-01 00:00:05', 105);
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);

Append a single point (by row ID)

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 point (inline literal)

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 points at once

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 set a default length for string-type attribute fields?

Use the GUC variable ganos.trajectory.attr_string_length to set a default length for string-type attribute fields.

SET ganos.trajectory.attr_string_length = 32;

The value specifies the default length for string attributes. Set it to match the longest string value you expect to store.

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

Use st_trajAttrsAsInteger (or its typed equivalents) to extract the attribute as an array, then call unnest to expand the array into rows before applying an aggregate function. The intermediate unnest step is required because st_trajAttrsAsInteger returns all values as a single array, not as individual rows.

The following example computes the average velocity across all leaf points in a trajectory:

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 AS a
)
SELECT avg(v)
FROM (
    SELECT unnest(st_trajAttrsAsInteger(a, 'velocity')) AS v
    FROM traj
) t;
-- Result: 50.5

Replace avg with max or min to get the corresponding statistic.