Use ST_StayPoint to identify stay points in a trajectory - ApsaraDB RDS

ST_StayPoint detects all stay points in a trajectory — locations where a moving object remained within a defined spatial radius for a minimum duration.

Two variants are available:

Function	Use when
`ST_StayPoint`	Returns each stay point with its start and end time. Use the clustered variant when the trajectory contains multiple round trips over the same area.
`ST_stayPointSpatialClustered`	The trajectory contains multiple round trips over the same area. Merges spatially overlapping stay points and returns the total duration at each location.

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)

Parameters

Parameter	Description
`traj`	The trajectory object.
`resample_int`	The resampling interval. Resampling evens out the spacing of sampling points along the trajectory. A shorter interval produces more accurate stay point detection but increases computation time. To skip resampling, set this to a large value such as `'100000 day'`. For details, see ST_Resample.
`dr`	The spatial radius threshold. A sampling point is considered "around" a candidate stay point if it falls within this distance. The unit depends on the Spatial Reference Identifier (SRID) of the trajectory: SRID 0 uses Euclidean distance units; SRID 4326 uses meters.
`dt`	The time window threshold. A sampling point is considered "around" a candidate stay point if the time difference is within this interval. Set this value slightly longer than the expected duration of a single stay.
`thres`	The minimum number of sampling points that must be "around" a candidate point for it to qualify as a stay point. A sampling point is "around" a candidate if its distance is less than `dr` and its time difference is less than `dt`. When multiple consecutive points qualify, the one surrounded by the most sampling points is selected as the final stay point.
`merger`	(`ST_stayPointSpatialClustered` only) The merge distance threshold. Two stay points detected at different times are merged into one if the distance between them is less than this value.

Return value

ST_StayPoint returns a set of trstaypoint values. trstaypoint is a composite type with the following columns:

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

ST_stayPointSpatialClustered returns a table with the following columns:

Column	Type	Description
`point`	geometry	The position of the merged stay cluster.
`length`	interval	The total duration across all stays merged into this cluster.

Description

A point in the trajectory qualifies as a stay point when at least thres sampling points fall within dr distance and dt time of that point.

Calculating `thres`: Divide the minimum stay duration by the resampling interval, then add 1. For example, to detect stays of 30 minutes or longer with a 5-minute resampling interval:

thres = (30 min / 5 min) + 1 = 7

With those parameters, set dr to 250 m, dt to 40 minutes (slightly longer than the 30-minute minimum stay). This example detects stays within a 500 m zone.

When a trajectory has multiple round trips over the same area, use ST_stayPointSpatialClustered to aggregate spatially similar stay points. The function returns the total number of stay locations (as the number of rows) and the total stay duration at each location.

Stay points typically correspond to meaningful locations — such as parking lots, fuel stations, or frequently visited sites — where the moving object stopped or lingered.

Examples

Detect stay points in a single-pass 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(
        'STPOINT'::leaftype,
        '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)',
        '2000-01-01',
        '2000-01-01'::timestamp + '1 day'::interval * (ST_NPoints('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)') - 1),
        '{}'
    ) AS traj
) tj;

The query returns four stay points with their positions (as human-readable WKT via ST_AsText) and time ranges:

 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

The point column stores coordinates in binary WKB format. Use ST_AsText() to convert to human-readable WKT coordinates, as shown in the query above.

Aggregate stay points across 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(
        'STPOINT'::leaftype,
        '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)',
        '2000-01-01',
        '2000-01-01'::timestamp + '1 day'::interval * (ST_NPoints('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)') - 1),
        '{}'
    ) AS traj
) tj;

The same trajectory produces two merged stay clusters when using ST_stayPointSpatialClustered with a merge distance of 3 (the final parameter):

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

The four individual stay points from the previous example are consolidated into two clusters based on their spatial proximity. The length column shows the total accumulated stay duration at each location.