Appends a trajectory point or a sub-trajectory to an existing trajectory and returns the updated trajectory.
Syntax
Overload 1 — Append a single point with attributes:
trajectory ST_Append(trajectory traj, geometry spatial, timestamp[] timespan, text str_attrs_json)Overload 2 — Append a sub-trajectory:
trajectory ST_Append(trajectory traj, trajectory tail)Parameters
| Parameter | Type | Description |
|---|---|---|
traj | trajectory | The original trajectory. |
spatial | geometry | The spatial geometry object of the new point. |
timespan | timestamp[] | The time array of the new point. The value can be a timeline. |
str_attrs_json | text | The attributes of the new point in JSON format. For the JSON structure, see ST_MakeTrajectory. |
tail | trajectory | The sub-trajectory to append. |
Examples
Append a single point
The following example appends a single point (POINT(114 35) at timestamp 2010-01-01 16:30) to a base trajectory that has 3 leafpoints. After appending, the trajectory has 4 leafpoints and the end_time advances to 2010-01-01 16:30:00.
-- Build a base trajectory with 3 leafpoints (leafcount: 3, end_time: 2010-01-01 15:30:00)
WITH traj AS (
SELECT 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":2,"nullable":true,"value":[120,130,140]},"accuracy":{"type":"float","length":4,"nullable":false,"value":[120,130,140]},"bearing":{"type":"float","length":8,"nullable":false,"value":[120,130,140]},"vesname":{"type":"string","length":20,"nullable":true,"value":["adsf","sdf","sdfff"]},"active":{"type":"timestamp","nullable":false,"value":["Fri Jan 01 14:30:00 2010","Fri Jan 01 15:00:00 2010","Fri Jan 01 15:30:00 2010"]}},"events":[{"1":"Fri Jan 01 14:30:00 2010"},{"2":"Fri Jan 01 15:00:00 2010"},{"3":"Fri Jan 01 15:30:00 2010"}]}'
) a
)
-- Append one point: leafcount becomes 4, end_time becomes 2010-01-01 16:30:00
SELECT ST_Append(
a,
'POINT(114 35)',
'{2010-01-01 16:30}'::timestamp[],
'{"leafcount":1,"attributes":{"velocity":{"type":"integer","length":2,"nullable":true,"value":[120]},"accuracy":{"type":"float","length":4,"nullable":false,"value":[120]},"bearing":{"type":"float","length":8,"nullable":false,"value":[120]},"vesname":{"type":"string","length":20,"nullable":true,"value":["adsfs"]},"active":{"type":"timestamp","nullable":false,"value":["Fri Jan 01 16:30:00 2010"]}}}'
) FROM traj;The result is a trajectory with leafcount: 4, start_time: 2010-01-01 14:30:00, and end_time: 2010-01-01 16:30:00.
Append a sub-trajectory
The following example merges two separate trajectories into one. Trajectory A has 3 leafpoints ending at 2010-01-01 15:00, and trajectory B has 3 leafpoints starting at 2010-01-02 15:30. After appending, the result has 6 leafpoints spanning both time ranges.
-- Trajectory A: LINESTRING(1 1, 6 6, 9 8), time [2010-01-01 11:30, 2010-01-01 15:00), leafcount: 3
-- Trajectory B: LINESTRING(7 7, 3 4, 1 5), time [2010-01-02 15:30, 2010-01-02 18:00), leafcount: 3
WITH traj AS (
SELECT
ST_MakeTrajectory(
'STPOINT',
'LINESTRING(1 1, 6 6, 9 8)'::geometry,
'[2010-01-01 11:30, 2010-01-01 15:00)'::tsrange,
'{"leafcount":3,"attributes":{"velocity":{"type":"integer","length":2,"nullable":true,"value":[120,130,140]},"accuracy":{"type":"float","length":4,"nullable":false,"value":[120,130,140]},"bearing":{"type":"float","length":8,"nullable":false,"value":[120,130,140]},"acceleration":{"type":"string","length":20,"nullable":true,"value":["120","130","140"]},"active":{"type":"timestamp","nullable":false,"value":["Fri Jan 01 14:30:00 2010","Fri Jan 01 15:00:00 2010","Fri Jan 01 15:30:00 2010"]}},"events":[{"1":"Fri Jan 01 14:30:00 2010"},{"2":"Fri Jan 01 15:00:00 2010"},{"3":"Fri Jan 01 15:30:00 2010"}]}'
) a,
ST_MakeTrajectory(
'STPOINT',
'LINESTRING(7 7, 3 4, 1 5)'::geometry,
'[2010-01-02 15:30, 2010-01-02 18:00)'::tsrange,
'{"leafcount":3,"attributes":{"velocity":{"type":"integer","length":2,"nullable":true,"value":[121,131,141]},"accuracy":{"type":"float","length":4,"nullable":false,"value":[121,131,141]},"bearing":{"type":"float","length":8,"nullable":false,"value":[121,131,141]},"acceleration":{"type":"string","length":20,"nullable":true,"value":["121","131","141"]},"active":{"type":"timestamp","nullable":false,"value":["Fri Jan 02 14:30:00 2010","Fri Jan 02 15:00:00 2010","Fri Jan 02 15:30:00 2010"]}},"events":[{"1":"Fri Jan 02 14:30:00 2010"},{"2":"Fri Jan 02 15:00:00 2010"},{"3":"Fri Jan 02 15:30:00 2010"}]}'
) b
)
-- Result: leafcount: 6, start_time: 2010-01-01 11:30:00, end_time: 2010-01-02 18:00:00
SELECT ST_Append(a, b) FROM traj;The result is a trajectory with leafcount: 6, start_time: 2010-01-01 11:30:00, and end_time: 2010-01-02 18:00:00. The spatial path combines both LINESTRINGs: LINESTRING(1 1, 6 6, 9 8, 7 7, 3 4, 1 5).