PolarDB:ST_removeDriftPoints

Syntax

trajectory ST_removeDriftPoints(trajectory traj, float8 speed, float8 dist, interval offset);

Parameters

Description

ST_removeDriftPoints deletes trajectory points that meet all three of the following conditions and returns the updated trajectory object:

• The speed of the trajectory point exceeds the speed threshold.

• The distance from the trajectory point to the previous trajectory point exceeds the dist threshold.

• The time interval exceeds the offset threshold.

A trajectory point is deleted only when all three conditions are met simultaneously.

The returned trajectory object always contains at least two trajectory points. If the input trajectory has only two points, no points are deleted.

Usage notes

Coordinate reference system

By default, the coordinate reference system (CRS) of a trajectory object is World Geodetic System 1984 (WGS 84). To use a different CRS, specify a spatial reference system identifier (SRID) when creating the trajectory object, or call ST_SetSRID to set the SRID afterward. The system calculates the spherical distance between two points based on the specified SRID. Spherical distances are in meters.

Examples

The following example removes drift points from a trajectory with 53 points using a speed threshold of 25.72 m/s, a distance threshold of 9,620 m, and a time interval threshold of 30 seconds. Two drift points are removed, leaving 49 points.

-- Check the initial point count
SELECT id, ST_leafcount(traj) FROM table_name WHERE id = 1;
 id | st_leafcount
----+--------------
  1 |           53
(1 row)

-- Remove drift points
UPDATE table_name SET traj = ST_removeDriftPoints(traj, 25.72, 9620, INTERVAL '30 seconds') WHERE id = 1;

-- Verify the result: two trajectory points are deleted
SELECT id, ST_leafcount(traj) FROM table_name WHERE id = 1;
 id | st_leafcount
----+--------------
  1 |           49
(1 row)