Use ST_TrajMerge to merge sort and deduplicate trajectories - ApsaraDB RDS

Merges multiple trajectories into a single trajectory.

Syntax

-- Aggregate form: merges all rows in a trajectory column
trajectory ST_TrajMerge(trajectory SET_ trajs);
trajectory ST_TrajMerge(trajectory SET trajs, bool doSort, bool doDeduplicate);

-- Array form: merges a trajectory array (e.g., built with array_agg())
trajectory ST_TrajMerge(trajectory[] trajs, bool doSort default true, bool doDeduplicate default true);

Parameters

Parameter	Description	Default
`trajs`	The trajectory column (aggregate form) or trajectory array to merge.	—
`doSort`	Sorts all trajectory points in the result by timestamp.	`true`
`doDeduplicate`	Whether to remove duplicate points from the result trajectory.	`true`

Return value

The merged trajectory.

Description

ST_TrajMerge combines multiple trajectory objects into one. The two parameters control the quality of the result:

doSort reorders all points by timestamp after merging. Skip this only when the input trajectories are already in chronological order.
doDeduplicate removes duplicate trajectory points of adjacent trajectories. For example, when the end point of one trajectory is identical to the start point of the next, these points can be merged.
Both parameters default to true. The sort and deduplication steps add some overhead, but they produce accurate results regardless of input order.

Example

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 holds four trajectories spanning 2000-01-01 to 2000-01-05, one empty trajectory, and one NULL row.

Merge without sorting or deduplication

SELECT st_TrajMerge(traj, false, false) FROM test_trajs_merge;

Result (leafcount: 8): duplicate points are present and timestamps are out of order.

{"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"]}}

Merge with sorting only

SELECT st_TrajMerge(traj, true, false) FROM test_trajs_merge;

Result (leafcount: 8): all points are in chronological order, but duplicate points remain.

{"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"]}}

Merge with deduplication only

SELECT st_TrajMerge(traj, false, true) FROM test_trajs_merge;

Result (leafcount: 7): timestamps are out of order, so adjacent duplicates are not correctly identified and some remain.

{"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"]}}

Merge with sorting and deduplication (default)

SELECT st_TrajMerge(traj) FROM test_trajs_merge;

Result (leafcount: 6): all points are in order and all duplicate endpoints are removed.

{"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"]}}

Merge a trajectory array

Use array_agg() to collect trajectories into an array before passing them to the array form of ST_TrajMerge.

SELECT st_TrajMerge(array_agg(traj), true, true) FROM test_trajs_merge;

Result (leafcount: 6): same output as the aggregate form with sorting and deduplication enabled.

{"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"]}}

Parameter combination summary

`doSort`	`doDeduplicate`	Points in order	Duplicates removed	`leafcount`
`false`	`false`	No	No	8
`true`	`false`	Yes	No	8
`false`	`true`	No	Partial	7
`true`	`true` (default)	Yes	Yes	6

Note

We recommend that you set doSort and doDeduplicate to true, which is the default. Although the performance is slightly affected, the accuracy of the results is ensured.