Returns the time periods during which two trajectories travel within a specified distance of each other. Use ST_AccompanyParts to retrieve the actual sub-trajectories for those periods instead of just the timestamps.
Syntax
-- Returns time periods
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 DEFAULT 'A'
);
-- Returns sub-trajectories
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 DEFAULT 'A'
);Parameters
| Parameter | Type | Default | Description |
|---|---|---|---|
traj1 | trajectory | — | The first trajectory. |
traj2 | trajectory | — | The second trajectory. |
tol_dist | double precision | — | Tolerance distance. Two trajectories are considered to be accompanying each other when the distance between them is less than this value. The distance unit follows the default unit of the spatial reference system. In most cases, the unit is meters when the spatial reference system identifier (SRID) is 4326. |
merge_gap | interval | '0 minute' | ST_AccompanyIntervals: merges two accompanying time periods if the gap between them is less than this value. ST_AccompanyParts: merges two groups of accompanying sub-trajectories if the distance between them is less than this value. Merging occurs only when merge_gap is specified. |
min_length | interval | '0 minute' | Discards any accompanying period whose duration is less than this value, applied after merging. Filtering occurs only when min_length is specified. |
dist_config | character(1) | 'A' | Distance calculation method. See the table below. |
dist_config values
| Value | Description |
|---|---|
'W' | Converts horizontal and vertical coordinates to WGS84 coordinate system points before calculating distance. |
'C' | Calculates distance directly using horizontal and vertical coordinates. |
'M' | Calculates distance in meters. Equivalent to 'W'. |
'D' | Calculates distance in degrees. Equivalent to 'C'. |
'A' (default) | Auto-selects 'W' when the SRID is 4326, and 'C' otherwise. |
Return values
ST_AccompanyIntervals
| Column | Type | Description |
|---|---|---|
start_time | timestamp | Start time of the accompanying period. |
end_time | timestamp | End time of the accompanying period. |
ST_AccompanyParts
| Column | Type | Description |
|---|---|---|
traj1 | trajectory | Sub-trajectories of the first trajectory during the accompanying periods. |
traj2 | trajectory | Sub-trajectories of the second trajectory during the accompanying periods. |
How it works
Both functions identify time periods when the distance between two trajectories is continuously less than tol_dist. ST_AccompanyIntervals returns a row of timestamps for each such period; ST_AccompanyParts returns the corresponding trajectory segments.
After identifying the accompanying periods, both functions apply the optional merge_gap and min_length filters in sequence:
Merge: If the gap between two adjacent accompanying periods is less than
merge_gap, the two periods are merged into one.Filter: If the duration of a period (after merging) is still less than
min_length, the period is discarded.
Examples
Find accompanying time periods
The following example creates two trajectories and returns the time periods during which they travel within a tolerance distance of 0.5.
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;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)The two trajectories accompany each other during two separate periods, with a gap in between.
Merge adjacent periods
Setting merge_gap to '2 day' merges the two periods from the previous example because the gap between them (24 hours) is less than 2 days.
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;Result:
start_time | end_time
-----------------------+-----------------------
2000-01-01 00:00:00 | 2000-01-03 00:00:00
(1 row)Retrieve sub-trajectories
Use ST_AccompanyParts with the same input to get the trajectory segments for each accompanying period instead of just timestamps.
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;Result (2 rows, one per accompanying period):
traj1 (sub-trajectory) | traj2 (sub-trajectory)
---------------------------------------------------------+--------------------------------------------------------
{"trajectory":{"version":1,"type":"STPOINT", | {"trajectory":{"version":1,"type":"STPOINT",
"start_time":"2000-01-01 00:00:00", | "start_time":"2000-01-01 00:00:00",
"end_time":"2000-01-01 12:00:00", | "end_time":"2000-01-01 12:00:00",
"spatial":"LINESTRING(0 0,0.5 0)",...}} | "spatial":"LINESTRING(0 0,0 0)",...}}
{"trajectory":{"version":1,"type":"STPOINT", | {"trajectory":{"version":1,"type":"STPOINT",
"start_time":"2000-01-02 12:00:00", | "start_time":"2000-01-02 12:00:00",
"end_time":"2000-01-03 00:00:00", | "end_time":"2000-01-03 00:00:00",
"spatial":"LINESTRING(0.5 0,0 0)",...}} | "spatial":"LINESTRING(0 0,0 0)",...}}
(2 rows)Each row corresponds to one accompanying period. The spatial paths show the actual routes traveled by each trajectory during that period.