ST_AccompanyIntervals - PolarDB - Alibaba Cloud Documentation Center

Calculates the time periods during which two trajectories accompany each other.

Syntax

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

Parameters

Parameter	Description
traj1	The trajectory numbered 1.
traj2	The trajectory numbered 2.
tol_dist	The accompanying distance. If the distance between two trajectories is less than this value, the two trajectories are considered to be accompanying each other. The default distance measurement unit in the corresponding spatial reference system is used. Note In most cases, the distance is measured in meters when the spatial reference system identifier (SRID) is 4326.
merge_gap	ST_AccompanyIntervals: If the interval between two time periods is less than the specified merge_gap, the two time periods are merged. Merging occurs only after you specify a merge_gap. ST_AccompanyParts: If the distance between two groups of accompanying sub-trajectories is less than the specified merge_gap, the two groups of sub-trajectories are merged. Merging occurs only after you specify a merge_gap.
min_length	If the duration of an accompanying time period is less than the specified min_length, the period is discarded. Discrading occurs only after you specify a min_length.
dist_config	The method used to calculate the distance. Valid values: `'W'`: calculates the distance by converting the horizontal and vertical coordinates into the coordinate points in the WGS84 coordinate system. `'C'`: calculates the distance directly by using the horizontal and vertical coordinates. `'M'`: calculates the distance in meters. The method is equivalent to the 'W' method. `'D'`: calculates the distance in degrees. The method is equivalent to the 'C' method. `'A'` or not specified: automatically selects the `'W'` method when the SRID is 4326 and the `'C'` method when the SRID is not 4326.

Return values

Return value	Description
start_time	The start time of the accompanying period.
end_time	The end time of the accompanying period.
traj1	The sub-trajectories on trajectory 1.
traj2	The sub-trajectories on trajectory 2.

Description

The function returns the time periods during which the distance between two trajectories is less than tol_dist.
ST_AccompanyIntervals: If the interval between two accompanying time periods is less than the specified merge_gap, the two time periods are merged. If the length of an accompanying time period after merging is still less than the specified min_length, the time period is discarded.
ST_AccompanyParts: If the distance between two groups of accompanying sub-trajectories is less than the specified merge_gap, the two groups of sub-trajectories are merged. If the length of an accompanying time period after merging is still less than the specified min_length, the time period is discarded.
The ST_AccompanyIntervals function returns the time periods during which two trajectories accompany each other. The ST_AccompanyParts returns the sub-trajectories on trajectory 1 and trajectory 2 during the time periods.

Examples

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;

Sample result:

     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)

Merge time periods

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;

Sample result:

     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;

Sample result:

                                                                                                            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)