Trajectory FAQ

How do I convert coordinate point data into a trajectory object?

ST_MakeTrajectory constructs a trajectory object from arrays of coordinates, timestamps, and optional attribute values. The following example aggregates rows from a point table and inserts the result as a single trajectory.

-- Enable the 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 coordinates and insert as 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?

Create a user-defined overloaded function with the attribute types you need. The signature has six fixed parameters followed by your custom attribute arrays.

The following example defines a constructor for trajectories with five 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[],
    -- Custom attributes (types vary per use case):
    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;

The parameters break down as follows:

Call this function the same way you would call the built-in constructor.

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

ST_append merges new spatial and temporal data into an existing trajectory. Two overloads are available:

-- Append geometry + timestamps + theme JSON
trajectory ST_append(trajectory traj, geometry spatial, timestamp[] timespan, text str_theme_json);

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

The following example builds an initial trajectory, adds new points to the point table, then uses ST_append to update the trajectory object.

-- Create the extension and initial data
CREATE EXTENSION ganos_trajectory CASCADE;

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

-- Add new points to the source 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 new point (queried from the point table)
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 literal 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 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?

Set the GUC variable ganos.trajectory.attr_string_length to the desired character length.

SET ganos.trajectory.attr_string_length = 32;

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

Trajectory attributes are stored as arrays inside the trajectory object, so extract and expand them with ST_trajAttrsAsInteger (or the corresponding typed function) before applying aggregate functions.

The following example 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 AS a
)
SELECT avg(v)  -- Returns 50.5
FROM (
    SELECT unnest(ST_trajAttrsAsInteger(a, 'velocity')) AS v
    FROM traj
) t;

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