Merges multiple trajectories into one.
Syntax
trajectory ST_TrajMerge(trajectory SET_ trajs);
trajectory ST_TrajMerge(trajectory SET trajs, bool doSort, bool doDeduplicate);
trajectory ST_TrajMerge(trajectory[] trajs, bool doSort default true, bool doDeduplicate default true);Parameters
| Parameter | Type | Description | Default |
|---|---|---|---|
trajs | trajectory SET / trajectory[] | The trajectory column or trajectory array to aggregate. | — |
doSort | bool | Specifies whether to sort the trajectory points in the result by timestamp. | true |
doDeduplicate | bool | Specifies whether to remove duplicate points from the result. A duplicate point occurs when the end point of one trajectory is the start point of the next trajectory. | true |
Return value
The merged trajectory.
Description
ST_TrajMerge joins all input trajectories into one by concatenating their points in sequence. The function accepts either a column aggregate (using SET) or a trajectory array (using array_agg).
Keep both doSort and doDeduplicate set to true (the default) to get a correctly ordered trajectory with no redundant points. Deduplication requires sorted input to correctly identify adjacent duplicate points — disabling doSort while keeping doDeduplicate enabled causes some duplicates to be missed.
Examples
Prepare data
CREATE TABLE test_trajs_merge(traj trajectory);
INSERT INTO test_trajs_merge
VALUES
('{"trajectory":{"version":1,"type":"STPOINT","leafcount":2,"start_time":"2000-01-01 00:00:00","end_time":"2000-01-02 00:00:00","spatial":"LINESTRING(0 0,4 0)","timeline":["2000-01-01 00:00:00","2000-01-02 00:00:00"]}}'),
('{"trajectory":{"version":1,"type":"STPOINT","leafcount":2,"start_time":"2000-01-02 00:00:00","end_time":"2000-01-03 00:00:00","spatial":"LINESTRING(4 0,2 0)","timeline":["2000-01-02 00:00:00","2000-01-03 00:00:00"]}}'),
('{"trajectory":{"version":1,"type":"STPOINT","leafcount":2,"start_time":"2000-01-04 00:00:00","end_time":"2000-01-05 00:00:00","spatial":"LINESTRING(2 2,3 3)","timeline":["2000-01-04 00:00:00","2000-01-05 00:00:00"]}}'),
('{"trajectory":{"version":1,"type":"STPOINT","leafcount":2,"start_time":"2000-01-03 00:00:00","end_time":"2000-01-04 00:00:00","spatial":"LINESTRING(3 0,2 2)","timeline":["2000-01-03 00:00:00","2000-01-04 00:00:00"]}}'),
('TRAJECTORY EMPTY'),
(NULL);The table contains four trajectories spanning January 1–5, one empty trajectory, and one NULL row. The rows are inserted out of chronological order to demonstrate the effect of each parameter combination.
Merge with default settings
SELECT st_TrajMerge(traj) FROM test_trajs_merge;Result:
{"trajectory":{"version":1,"type":"STPOINT","leafcount":6,"start_time":"2000-01-01 00:00:00","end_time":"2000-01-05 00:00:00","spatial":"LINESTRING(0 0,4 0,3 0,2 0,2 2,3 3)","timeline":["2000-01-01 00:00:00","2000-01-02 00:00:00","2000-01-03 00:00:00","2000-01-03 00:00:00","2000-01-04 00:00:00","2000-01-05 00:00:00"]}}6 points, sorted by timestamp, with the shared point at the junction of adjacent trajectories deduplicated.
Merge a trajectory array
Use array_agg to pass trajectories as an array instead of a column aggregate:
SELECT st_TrajMerge(array_agg(traj), true, true) FROM test_trajs_merge;The result is identical to the default aggregate call above: 6 points, sorted, deduplicated.
Compare parameter combinations
The following table shows the effect of each doSort / doDeduplicate combination on the same dataset:
doSort | doDeduplicate | Points (leafcount) | Timestamps sorted | Duplicate points removed |
|---|---|---|---|---|
false | false | 8 | No | No |
true | false | 8 | Yes | No |
false | true | 7 | No | Partially — unsorted input causes some duplicates to be missed |
true | true (default) | 6 | Yes | Yes |
No sort, no dedup
SELECT st_TrajMerge(traj, false, false) FROM test_trajs_merge;{"trajectory":{"version":1,"type":"STPOINT","leafcount":8,"start_time":"2000-01-01 00:00:00","end_time":"2000-01-05 00:00:00","spatial":"LINESTRING(0 0,4 0,4 0,2 0,2 2,3 3,3 0,2 2)","timeline":["2000-01-01 00:00:00","2000-01-02 00:00:00","2000-01-02 00:00:00","2000-01-03 00:00:00","2000-01-04 00:00:00","2000-01-05 00:00:00","2000-01-03 00:00:00","2000-01-04 00:00:00"]}}Sort only, no dedup
SELECT st_TrajMerge(traj, true, false) FROM test_trajs_merge;{"trajectory":{"version":1,"type":"STPOINT","leafcount":8,"start_time":"2000-01-01 00:00:00","end_time":"2000-01-05 00:00:00","spatial":"LINESTRING(0 0,4 0,4 0,3 0,2 0,2 2,2 2,3 3)","timeline":["2000-01-01 00:00:00","2000-01-02 00:00:00","2000-01-02 00:00:00","2000-01-03 00:00:00","2000-01-03 00:00:00","2000-01-04 00:00:00","2000-01-04 00:00:00","2000-01-05 00:00:00"]}}Dedup only, no sort
SELECT st_TrajMerge(traj, false, true) FROM test_trajs_merge;{"trajectory":{"version":1,"type":"STPOINT","leafcount":7,"start_time":"2000-01-01 00:00:00","end_time":"2000-01-04 00:00:00","spatial":"LINESTRING(0 0,4 0,2 0,2 2,3 3,3 0,2 2)","timeline":["2000-01-01 00:00:00","2000-01-02 00:00:00","2000-01-03 00:00:00","2000-01-04 00:00:00","2000-01-05 00:00:00","2000-01-03 00:00:00","2000-01-04 00:00:00"]}}Because the input is not sorted, deduplication only removes the one adjacent duplicate it can detect. Out-of-order points remain.