ST_AccompanyIntervals_云原生数据库 PolarDB(PolarDB)-阿里云帮助中心

计算两条轨迹伴随运动的区间。

语法

Table(timestamp start_time, timestamp end_time) ST_AccompanyIntervals(trajectory traj1, trajectory traj2, double precision tol_dist, interval merge_gap default '0 minute', interval min_length default '0 minute', character(1) dist_config defaut 'A');

Table(trajectory traj1, trajectory subtraj2) ST_AccompanyParts(trajectory traj1, trajectory traj2, double precision tol_dist, interval merge_gap default '0 minute', interval min_length default '0 minute', character(1) dist_config defaut 'A');

参数

参数名称	描述
traj1	轨迹对象1。
traj2	轨迹对象2。
tol_dist	伴随距离，小于此距离时认为两条轨迹伴随运动。使用对应SRID下默认的距离单位。说明通常情况下，当SRID=4326时，使用米为单位。
merge_gap	ST_AccompanyIntervals：如果两个时间段之间距离小于merge_gap，则将两个时间段合并。若不指定，不进行合并。 ST_AccompanyParts：当两组伴随子轨迹之间的距离小于merge_gap时，将两组子轨迹合并。若不指定，不进行合并。
min_length	当一个伴随时间段的时长小于min_length时，将其舍弃。若不指定，不进行舍弃。
dist_config	距离计算的方式，取值范围如下： `'W'`，表示将横纵坐标解读为按照WGS84坐标系下的坐标点来计算距离。 `'C'`，表示直接对横纵坐标计算距离。 `'M'`，表示以米为单位进行计算，等价于'W'。 `'D'`，表示以角度为单位进行计算，等价于'C'。 `'A'`或不指定时，表示自动选择（SRID为4326时使用`'W'`，其它使用`'C'`）。

返回值

返回列名称	描述
start_time	伴随时间开始。
end_time	伴随时间结束。
traj1	轨迹对象1上的子轨迹。
traj2	轨迹对象2上的子轨迹。

描述

同一时刻的提取轨迹上，两条轨迹之间距离小于tol_dist的轨迹时间段。
在ST_AccompanyIntervals中如果两个时间段之间距离小于merge_gap，则将两个时间段合并。执行完合并步骤之后，如果有一个伴随时间段长度依然小于min_length，将其舍弃。
在ST_AccompanyParts中如果两个子轨迹之间距离小于merge_gap，则将两个子轨迹合并。执行完合并步骤之后，如果有一个伴随时间段长度依然小于min_length，将其舍弃。
ST_AccompanyIntervals返回对应的时间段，而ST_AccompanyParts则返回这些时间段上traj1和traj2的子轨迹。

示例

ST_AccompanyIntervals

With traj AS
(
SELECT ST_MakeTrajectory('STPOINT','LINESTRING(0 0, 1 0, 0 0)'::geometry, '2000-01-01'::timestamp, '2000-01-03', NULL) as a, ST_MakeTrajectory('STPOINT','LINESTRING(0 0, 0 0, 0 0)'::geometry, '2000-01-01'::timestamp, '2000-01-03', NULL) as b
)
SELECT (ST_AccompanyIntervals(a,b,0.5)).* from traj;

返回结果如下：

     start_time      |      end_time       
---------------------+---------------------
 2000-01-01 00:00:00 | 2000-01-01 12:00:00
 2000-01-02 12:00:00 | 2000-01-03 00:00:00
(2 rows)

区间合并

With traj AS
(
SELECT ST_MakeTrajectory('STPOINT','LINESTRING(0 0, 1 0, 0 0)'::geometry, '2000-01-01'::timestamp, '2000-01-03', NULL) as a, ST_MakeTrajectory('STPOINT','LINESTRING(0 0, 0 0, 0 0)'::geometry, '2000-01-01'::timestamp, '2000-01-03', NULL) as b
)
SELECT (ST_AccompanyIntervals(a,b,0.5,'2 day')).* from traj;

返回结果如下：

     start_time      |      end_time       
---------------------+---------------------
 2000-01-01 00:00:00 | 2000-01-03 00:00:00
(1 row)

ST_AccompanyParts

With traj AS
(
SELECT ST_MakeTrajectory('STPOINT','LINESTRING(0 0, 1 0, 0 0)'::geometry, '2000-01-01'::timestamp, '2000-01-03', NULL) as a, ST_MakeTrajectory('STPOINT','LINESTRING(0 0, 0 0, 0 0)'::geometry, '2000-01-01'::timestamp, '2000-01-03', NULL) as b
)
SELECT (ST_AccompanyParts(a,b,0.5)).* from traj;

返回结果如下：

                                                                                                            traj1                                                                                                            |             
                                                                                             traj2                                                                                                           
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-------------
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 {"trajectory":{"version":1,"type":"STPOINT","leafcount":2,"start_time":"2000-01-01 00:00:00","end_time":"2000-01-01 12:00:00","spatial":"LINESTRING(0 0,0.5 0)","timeline":["2000-01-01 00:00:00","2000-01-01 12:00:00"]}} | {"trajectory
":{"version":1,"type":"STPOINT","leafcount":2,"start_time":"2000-01-01 00:00:00","end_time":"2000-01-01 12:00:00","spatial":"LINESTRING(0 0,0 0)","timeline":["2000-01-01 00:00:00","2000-01-01 12:00:00"]}}
 {"trajectory":{"version":1,"type":"STPOINT","leafcount":2,"start_time":"2000-01-02 12:00:00","end_time":"2000-01-03 00:00:00","spatial":"LINESTRING(0.5 0,0 0)","timeline":["2000-01-02 12:00:00","2000-01-03 00:00:00"]}} | {"trajectory
":{"version":1,"type":"STPOINT","leafcount":2,"start_time":"2000-01-02 12:00:00","end_time":"2000-01-03 00:00:00","spatial":"LINESTRING(0 0,0 0)","timeline":["2000-01-02 12:00:00","2000-01-03 00:00:00"]}}
(2 rows)