ST_TrajMerge

将多条轨迹合并为一条。

语法

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(默认值),虽然略微降低性能,但可以保障结果的准确。

示例

  1. 准备数据:

    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. 使用场景:

    • 不排序和去除重复点:

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