使用ST_TrajMerge合并多条轨迹-云原生数据库 PolarDB-阿里云

将多条轨迹合并为一条。

语法

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

参数

参数名称	描述
trajs	需要聚合的轨迹列或轨迹数组。
doSort	是否需要对结果的轨迹点进行排序，默认值为`true`。
doDeduplicate	是否需要对结果轨迹去除重复点，默认值为`true`。

返回值

合并后的一条轨迹。

描述

将多条轨迹合并为一条。
支持使用doSort参数将聚合的轨迹和轨迹点进行重新排序，也支持使用doDeduplicate参数将相邻轨迹的重复轨迹点合并（例如前一条轨迹的终点是后一条轨迹的起点）。
建议将doSort和doDeduplicate选择为true（默认值），虽然略微降低性能，但可以保障结果的准确。

示例

准备数据：

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

使用场景：

不排序和去除重复点：

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

排序但不去除重复点：

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

去除重复点但不排序：

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

去除重复点并排序：

SELECT st_TrajMerge(traj) FROM test_trajs_merge;

结果显示如下：

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

使用轨迹数组作为参数，去除重复点并排序：

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

结果显示如下：

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