将多条轨迹合并为一条。
语法
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 | 是否需要对结果的轨迹点进行排序,默认值为 |
doDeduplicate | 是否需要对结果轨迹去除重复点,默认值为 |
返回值
合并后的一条轨迹。
描述
将多条轨迹合并为一条。
支持使用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"]}}
文档内容是否对您有帮助?