ApsaraDB RDS:Create UDFs to edit trajectories

Sensors 1, 2, and 3 are installed on a device of a user. When the sensors report locations, the confidence attribute is added. You can create UDFs to edit trajectories to obtain the following results:

• Filter a trajectory to find the trajectory points whose confidence value is greater than a specified threshold and return the trajectory that consists of these trajectory points.

  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 a trajectory to find the trajectory points that are reported by a specific sensor and return the trajectory that consists of these trajectory points.

  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;