ApsaraDB RDS:ST_{2D|2DT|3D|3DT}DWithin

How it works

The function family covers four measurement modes:

• 2D and 3D — check whether the distance between the 2D or 3D projections of two objects falls within the threshold. The time dimension is ignored.

• 2DT and 3DT — check whether the distance between two objects in the specified dimension, at each point in time, falls within the threshold. The time dimension is factored into the measurement.

When ts and te are provided, the function compares sub-trajectories extracted from the given time range. Without ts and te, it compares the complete trajectories.

ST_DistanceWithin(..) is equivalent to ST_2DDWithin(...) when one of the inputs is a geometry, and equivalent to ST_3DTDWithin(...) when both inputs are trajectories.

Syntax

bool ST_{2D|3D}DWithin(geometry geom, trajectory traj, float8 dist);
bool ST_{2D|3D}DWithin(trajectory traj, geometry geom, float8 dist);
bool ST_{2D|3D}DWithin(geometry geom, trajectory traj, timestamp ts, timestamp te, float8 dist);
bool ST_{2D|3D}DWithin(trajectory traj, geometry geom, timestamp ts, timestamp te, float8 dist);
bool ST_{2D|2DT|3D|3DT}DWithin(boxndf box, trajectory traj, float8 dist);
bool ST_{2D|2DT|3D|3DT}DWithin(trajectory traj, boxndf box, float8 dist);
bool ST_{2D|2DT|3D|3DT}DWithin(boxndf box, trajectory traj, timestamp ts, timestamp te, float8 dist);
bool ST_{2D|2DT|3D|3DT}DWithin(trajectory traj, boxndf box, timestamp ts, timestamp te, float8 dist);
bool ST_{2D|2DT|3D|3DT}DWithin(trajectory traj1, trajectory traj2, float8 dist);
bool ST_{2D|2DT|3D|3DT}DWithin(trajectory traj1, trajectory traj2, timestamp ts, timestamp te, float8 dist);

The ST_{2D|3D} variants accept a geometry as one of the inputs. The ST_{2D|2DT|3D|3DT} variants accept a boxndf or two trajectories.

Parameters

Example

The following example creates two trajectories and checks whether their 2D, 3D, 2DT, and 3DT distances fall within specified thresholds over different time ranges.

All calls use ST_2dDWithin, ST_3dDWithin, ST_2dtDWithin, and ST_3dtDWithin with the same trajectory pair. The result shows that a threshold of 19 is too small when te = ST_PGEpochToTS(49), but a threshold of 20 is sufficient when te = ST_PGEpochToTS(50).

WITH traj AS(
    Select ST_makeTrajectory('STPOINT', 'LINESTRING(0 0 10, 50 0 10, 100 0 10)'::geometry,
                             ('[' || ST_PGEpochToTS(0) || ',' || ST_PGEpochToTS(100) || ')')::tsrange,
                             '{"leafcount":3,"attributes":{"velocity": {"type": "integer", "length": 2,"nullable" : true,"value": [120,130,140]}, "accuracy": {"type": "float", "length": 4, "nullable" : false,"value": [120,130,140]}, "bearing": {"type": "float", "length": 8, "nullable" : false,"value": [120,130,140]}, "acceleration": {"type": "string", "length": 20, "nullable" : true,"value": ["120","130","140"]}, "active": {"type": "timestamp", "nullable" : false,"value": ["Fri Jan 01 11:35:00 2010", "Fri Jan 01 12:35:00 2010", "Fri Jan 01 13:30:00 2010"]}}, "events": [{"2" : "Fri Jan 02 15:00:00 2010"}, {"3" : "Fri Jan 02 15:30:00 2010"}]}') a,
           ST_makeTrajectory('STPOINT', 'LINESTRING(0 20, 50 20, 100 20)'::geometry,
                             ('[' || ST_PGEpochToTS(0) || ',' || ST_PGEpochToTS(100) ||')')::tsrange,
                             '{"leafcount":3,"attributes":{"velocity": {"type": "integer", "length": 2,"nullable" : true,"value": [120,130,140]}, "accuracy": {"type": "float", "length": 4, "nullable" : false,"value": [120,130,140]}, "bearing": {"type": "float", "length": 8, "nullable" : false,"value": [120,130,140]}, "acceleration": {"type": "string", "length": 20, "nullable" : true,"value": ["120","130","140"]}, "active": {"type": "timestamp", "nullable" : false,"value": ["Fri Jan 01 11:35:00 2010", "Fri Jan 01 12:35:00 2010", "Fri Jan 01 13:30:00 2010"]}}, "events": [{"2" : "Fri Jan 02 15:00:00 2010"}, {"3" : "Fri Jan 02 15:30:00 2010"}]}') b
)
SELECT ST_2dDWithin(a,b, ST_PGEpochToTS(0),ST_PGEpochToTS(49), 19), ST_3dDWithin(a,b, ST_PGEpochToTS(0),ST_PGEpochToTS(49), 19),
       ST_2dDWithin(a,b, ST_PGEpochToTS(0),ST_PGEpochToTS(50), 20), ST_3dDWithin(a,b, ST_PGEpochToTS(0),ST_PGEpochToTS(50), 20),
       ST_2dDWithin(a,b, ST_PGEpochToTS(0),ST_PGEpochToTS(70), 21), ST_3dDWithin(a,b, ST_PGEpochToTS(0),ST_PGEpochToTS(70), 21),
       ST_2dtDWithin(a,b, ST_PGEpochToTS(0),ST_PGEpochToTS(49), 19), ST_3dtDWithin(a,b, ST_PGEpochToTS(0),ST_PGEpochToTS(49), 19),
       ST_2dtDWithin(a,b, ST_PGEpochToTS(0),ST_PGEpochToTS(50), 20), ST_3dtDWithin(a,b, ST_PGEpochToTS(0),ST_PGEpochToTS(50), 20),
       ST_2dtDWithin(a,b, ST_PGEpochToTS(0),ST_PGEpochToTS(70), 21), ST_3dtDWithin(a,b, ST_PGEpochToTS(0),ST_PGEpochToTS(70), 21) from traj;
NOTICE:  One or both of the geometries is missing z-value. The unknown z-value will be regarded as "any value"
NOTICE:  One or both of the geometries is missing z-value. The unknown z-value will be regarded as "any value"
 st_2ddwithin | st_3ddwithin | st_2ddwithin | st_3ddwithin | st_2ddwithin | st_3ddwithin | st_2dtdwithin | st_3dtdwithin | st_2dtdwithin | st_3dtdwithin | st_2dtdwithin | st_3dtdwithin
--------------+--------------+--------------+--------------+--------------+--------------+---------------+---------------+---------------+---------------+---------------+---------------
 f            | f            | t            | t            | t            | t            | f             | f             | t             | t             | t             | t

The results confirm that:

• With ts = ST_PGEpochToTS(0), te = ST_PGEpochToTS(49), and dist = 19: all variants return false — the trajectories are not within 19 units of each other during this time range.

• With te = ST_PGEpochToTS(50) and dist = 20, or te = ST_PGEpochToTS(70) and dist = 21: all variants return true.