Use ST_StayPoint to identify stay points in a trajectory - PolarDB

Returns all stay points of a trajectory. A stay point is a location where a moving object remains within a defined spatial radius for a specified duration.

Syntax

SETOF trstaypoint ST_StayPoint(trajectory traj, interval resample_int, float dr, interval dt, float thres)

TABLE(point geometry, length interval) ST_stayPointSpatialClustered(trajectory traj, interval resample_int, float dr, interval dt, float thres, float merger)

Description

ST_StayPoint identifies stay points in a trajectory. A sampling point is considered to be around a candidate point if its spatial distance from that point is less than dr and the time difference is less than dt. If the number of such surrounding sampling points exceeds thres, the candidate point is a stay point. When multiple consecutive points qualify, the one surrounded by the most sampling points is selected as the stay point.

ST_stayPointSpatialClustered is designed for trajectories with multiple round trips. It aggregates spatially similar stay points and returns the total stay duration at each clustered location. This is useful for identifying frequently visited places such as gas stations or parking lots.

Parameter calculation example: To define a stay as remaining within 500 meters for more than 30 minutes, set resample_int to 5 minutes, dr to 250 meters, dt to 40 minutes, and thres to 7 (= 30 ÷ 5 + 1).

Parameters

Parameter	Type	Description
`traj`	trajectory	The trajectory object.
`resample_int`	interval	The resampling interval. Resampling distributes sampling points more evenly along the trajectory. A shorter interval produces more accurate stay points but increases computation time. If resampling is not needed, set this to a large value such as `'100000 day'`. For details, see ST_Resample.
`dr`	float	The spatial tolerance for distance between trajectory points. The unit is determined by the Spatial Reference Identifier (SRID) of the trajectory: SRID 0 uses Euclidean distance; SRID 4326 uses meters.
`dt`	interval	The time tolerance between trajectory points. Set this slightly longer than the expected duration of a single stay.
`thres`	float	The minimum number of surrounding sampling points required to classify a candidate point as a stay point.
`merger`	float	(`ST_stayPointSpatialClustered` only) The maximum distance between two stay points at different times for them to be merged into one.

Return values

ST_StayPoint returns a set of trstaypoint composite type values.

Column	Type	Description
`point`	geometry	The position of the stay point.
`startt`	timestamp	The time when the stay begins, based on the first identified stay point.
`endt`	timestamp	The time when the stay ends, based on the last identified stay point.

ST_stayPointSpatialClustered returns a table with the following columns.

Column	Type	Description
`point`	geometry	The position of the clustered stay point.
`length`	interval	The total stay duration at the clustered location.

Examples

Query all stay points of a trajectory:

SELECT ST_AsText((ST_StayPoint(traj, '12 hour', 3, '3 day', 3)).point),
       (ST_StayPoint(traj, '12 hour', 3, '3 day', 3)).*
FROM (
  SELECT ST_MakeTrajectory('LINESTRING(1 1, 2 2, 3 3, 10 10, 11 11, 12 12, 13 13, 3 3, 2 2, 2 2, 3 3, 8 8,10 10,9 9,8 8)') AS traj
) tj;

Output:

   st_astext    |                   point                    |       startt        |        endt
----------------+--------------------------------------------+---------------------+---------------------
 POINT(1 1)     | 0101000000000000000000F03F000000000000F03F | 2000-01-01 00:00:00 | 2000-01-03 00:00:00
 POINT(11 11)   | 010100000000000000000026400000000000002640 | 2000-01-04 00:00:00 | 2000-01-07 00:00:00
 POINT(3 3)     | 010100000000000000000008400000000000000840 | 2000-01-08 00:00:00 | 2000-01-11 00:00:00
 POINT(9.5 9.5) | 010100000000000000000023400000000000002340 | 2000-01-12 00:00:00 | 2000-01-15 00:00:00
(4 rows)

Aggregate spatially similar stay points for a trajectory with multiple round trips:

SELECT ST_AsText((ST_stayPointSpatialClustered(traj, '12 hour', 3, '3 day', 3, 3)).point),
       (ST_stayPointSpatialClustered(traj, '12 hour', 3, '3 day', 3, 3)).*
FROM (
  SELECT ST_MakeTrajectory('LINESTRING(1 1, 2 2, 3 3, 10 10, 11 11, 12 12, 13 13, 3 3, 2 2, 2 2, 3 3, 8 8,10 10,9 9,8 8)') AS traj
) tj;

Output:

     st_astext      |                   point                    | length
--------------------+--------------------------------------------+--------
 POINT(10.25 10.25) | 010100000000000000008024400000000000802440 | 6 days
 POINT(2 2)         | 010100000000000000000000400000000000000040 | 5 days
(2 rows)