PolarDB:ST_TrajMerge

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

Return values

The merged trajectory.

Description

• You can use this function to merge multiple trajectories into one.

• You can use the doSort parameter to sort merged trajectories and trajectory points. You can also use the doDeduplicate parameter to merge duplicate trajectory points of adjacent trajectories. For example, if the end point of a trajectory is the start point of another trajectory, these points can be merged.

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

Example

1. 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);

2. Scenarios:

   • Do not sort the points in the result or remove duplicate points:

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

     The following sample result contains duplicate points and the timestamps are not correctly sorted:

      {"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 the points in the result but do not remove duplicate points:

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

     The following sample result contains duplicate points, but all points in the result are correctly arranged by time:

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

   • Remove duplicate points but do not sort the points in the result:

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

     The following sample result contains out-of-order timestamps whose corresponding duplicate points are not removed:

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

   • Remove duplicate points and sort the points in the result:

     SELECT st_TrajMerge(traj) FROM test_trajs_merge;

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

   • Merge the trajectories of a trajectory array. Remove duplicate points and sort the points in the result:

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

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