Create UDFs to filter trajectories based on custom rules - PolarDB

The trajectory module of GanosBase lets you define custom SQL functions (user-defined functions, or UDFs) to edit trajectories using your own logic, giving you more flexibility.

How it works

Each UDF follows the same pattern:

Use ST_AsTable to expand a trajectory object into a relational table of trajectory points.
Apply a WHERE clause to filter points by any attribute column.
Use ST_MakeTrajectory with array_agg(ROW(...)) to reassemble the filtered points into a new trajectory.

This pattern lets you build reusable filter functions for any attribute in your trajectory data.

Examples

A device has three sensors (numbered 1, 2, and 3). Each time a sensor reports a location, the report includes a confidence attribute. The following UDFs filter trajectory points based on that data.

Filter by confidence value

UDF_validityGreaterThan returns a trajectory containing only the points whose validity (confidence) exceeds a given threshold.

CREATE FUNCTION UDF_validityGreaterThan(traj trajectory, threshold float8)
RETURNS trajectory
AS
'
SELECT ST_MakeTrajectory(array_agg(ROW(traj_table.*))) FROM
(
  SELECT * from ST_AsTable(traj) as f(t timestamp, x double precision, y double precision, sampler int, validity double precision)
) traj_table
WHERE validity > threshold
'
LANGUAGE SQL IMMUTABLE STRICT PARALLEL SAFE;

Filter by sensor number

UDF_SamplerIs returns a trajectory containing only the points reported by a specific sensor.

CREATE FUNCTION UDF_SamplerIs(traj trajectory, int sampler_num)
RETURNS trajectory
AS
'
SELECT ST_MakeTrajectory(array_agg(ROW(traj_table.*))) FROM
(
  SELECT * from ST_AsTable(traj) as f(t timestamp, x double precision, y double precision, sampler int, validity double precision)
) traj_table
WHERE sampler = sampler_num
'
LANGUAGE SQL IMMUTABLE STRICT PARALLEL SAFE;

Function options

Both UDFs use three options in the LANGUAGE clause:

Option	Meaning
`IMMUTABLE`	The function always returns the same result for the same inputs and does not modify the database. This allows the query planner to optimize calls with constant arguments.
`STRICT`	The function returns `NULL` if any argument is `NULL`, without executing the function body.
`PARALLEL SAFE`	The function can run in parallel query execution without restriction.

PARALLEL SAFE may not be supported by some database engine versions. If you see an error indicating that PARALLEL SAFE is not supported, remove it from the function definition.