All Products
Search

This Product

    PolarDB:ST_TrajMerge

    Document Center

    PolarDB:ST_TrajMerge

    Last Updated:Mar 28, 2026

    Merges multiple trajectories into a single trajectory.

    Syntax

    -- Aggregate form (SET_ variant)
trajectory ST_TrajMerge(trajectory SET_ trajs);

-- Aggregate form with explicit parameters
trajectory ST_TrajMerge(trajectory SET trajs, bool doSort, bool doDeduplicate);

-- Array form (default: doSort = true, doDeduplicate = true)
trajectory ST_TrajMerge(trajectory[] trajs, bool doSort default true, bool doDeduplicate default true);

    Returns: trajectory — the merged trajectory.

    Parameters

    ParameterDescriptionDefault
    trajsTrajectory column (aggregate form) or trajectory array to merge—
    doSortSorts trajectory points in the result by timestamptrue
    doDeduplicateRemoves duplicate points at boundaries between adjacent trajectories. For example, if the end point of one trajectory shares the same position and timestamp as the start point of the next, the duplicate is removed.true

    Usage notes

    • Keep both doSort and doDeduplicate set to true (the default). This has a slight performance overhead but ensures accurate results.

    Examples

    Prepare sample 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 trajectory segments, one empty trajectory, and one NULL row. The four segments form a continuous path from January 1 to January 5.

    Merge with sorting and deduplication (recommended)

    Use the default behavior to get a clean, time-ordered result with duplicate boundary points removed.

    -- Recommended: doSort = true (default), doDeduplicate = true (default)
SELECT ST_TrajMerge(traj) FROM test_trajs_merge;

    Result (leafcount = 6, all 6 points are unique and time-ordered):

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

    Pass a trajectory array using array_agg when you need the array form of the function.

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

    Result (same as the aggregate form above, leafcount = 6):

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

    Effect of doSort and doDeduplicate

    The following examples show how each parameter combination affects the result. Use these only for diagnostic or testing purposes — for production, keep both parameters set to true.

    No sorting, no deduplication (doSort = false, doDeduplicate = false):

    SELECT ST_TrajMerge(traj, false, false) FROM test_trajs_merge;
-- WARNING: result has duplicate points and unsorted timestamps (leafcount = 8)

    Result:

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

    Sorting only, no deduplication (doSort = true, doDeduplicate = false):

    SELECT ST_TrajMerge(traj, true, false) FROM test_trajs_merge;
-- WARNING: result has duplicate points, though timestamps are sorted (leafcount = 8)

    Result:

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

    Deduplication only, no sorting (doSort = false, doDeduplicate = true):

    SELECT ST_TrajMerge(traj, false, true) FROM test_trajs_merge;
-- WARNING: timestamps are out of order; deduplication only removes duplicates at adjacent boundaries in the unsorted input (leafcount = 7)

    Result:

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