ST_StayPoint GPS Trajectory Analysis for Stay Point Detection - PolarDB

ST_StayPoint() returns all stay points in a trajectory — locations where a moving object remained within a defined spatial radius for at least a minimum duration. ST_stayPointSpatialClustered() extends this by merging spatially close stay points across multiple trips, making it useful for identifying frequently visited locations such as parking lots or fuel stations.

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)

How stay points are detected

The function determines stay points in the following steps:

The trajectory is resampled at the interval specified by resample_int, producing evenly distributed sampling points.
For each candidate point, the function counts how many sampling points fall within spatial distance dr and time window dt.
If that count exceeds thres, the candidate is classified as a stay point.
When multiple consecutive points qualify, the one surrounded by the most sampling points becomes the last stay point.

For ST_stayPointSpatialClustered(): after all stay points are identified, any two stay points from different time periods whose spatial distance is less than merger are merged into a single point. The merged point's length reflects the total accumulated stay duration.

Parameters

Parameter	Type	Description
`traj`	`trajectory`	The trajectory object to analyze.
`resample_int`	`interval`	Resampling interval. Shorter intervals produce more accurate results but increase computation time. To skip resampling, set this to a large value such as `'100000 day'`. See ST_Resample.
`dr`	`float`	Spatial distance threshold. The unit depends on the trajectory's spatial reference identifier (SRID): Euclidean distance units for SRID 0, meters for SRID 4326.
`dt`	`interval`	Time window threshold. Set this slightly longer than the expected duration of a single stay.
`thres`	`float`	Minimum number of sampling points that must fall within `dr` and `dt` for a candidate point to qualify as a stay point. Calculate as: `thres = minimum_stay_duration / resample_int + 1`. For example, to detect stays longer than 30 minutes with a 5-minute resampling interval: `thres = 30 / 5 + 1 = 7`.
`merger`	`float`	(`ST_stayPointSpatialClustered` only) Maximum distance between two stay points from different time periods for them to be merged into one.

Return values

ST_StayPoint() returns a set of trstaypoint composite type rows:

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

ST_stayPointSpatialClustered() returns:

Column	Type	Description
`point`	`geometry`	The representative position of the merged stay cluster.
`length`	`interval`	Total accumulated stay duration across all merged stay points.

Calculate parameters for a specific use case

Example: Detect locations where a vehicle stayed within a 500-meter radius for more than 30 minutes.

Parameter	Value	Reasoning
`resample_int`	`'5 minutes'`	Resample the trajectory every 5 minutes for balanced accuracy and performance.
`dr`	`250`	Half the diameter (500 m), since sampling points within 250 m of the center still fall within the 500-meter radius.
`dt`	`'40 minutes'`	Slightly longer than the 30-minute minimum stay duration to account for edge cases.
`thres`	`7`	Calculated as `30 / 5 + 1 = 7`.

Examples

Identify stay points

The following example detects all stay points in a trajectory where an object remained within a spatial distance of 3 units for at least 3 days, using a 12-hour resampling interval and a minimum threshold of 3 surrounding sampling points.

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 stay points across multiple trips

When a trajectory contains multiple round-trips over the same route, use ST_stayPointSpatialClustered() to merge spatially close stay points and get the total time spent at each location. The following example uses the same trajectory and parameters as the previous query, with a merge distance of 3 to combine nearby stay points.

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)

The four stay points from the first query are reduced to two clusters, each showing the representative location and the total accumulated stay duration.