ST_Contains Functions for Trajectory Containment in PolarDB for Oracle - PolarDB

Returns true if the first object contains the second object along the specified axis.

Syntax

bool ST_TContains(tsrange r, trajectory traj);
bool ST_TContains(trajectory traj, tsrange r);
bool ST_2DContains(geometry geom, trajectory traj);
bool ST_2DContains(trajectory traj, geometry geom);
bool ST_2DContains(geometry geom, trajectory traj, timestamp ts, timestamp te);
bool ST_2DContains(trajectory traj, geometry geom, timestamp ts, timestamp te);
bool ST_{2D|2DT|3D|3DT}Contains(boxndf box, trajectory traj);
bool ST_{2D|2DT|3D|3DT}Contains(boxndf box, trajectory traj, timestamp ts, timestamp te);

Each variant checks containment along a different axis:

ST_TContains — time axis only
ST_2DContains — 2D spatial projection (X, Y)
ST_2DTContains — 2D spatial projection plus time (X, Y, T)
ST_3DContains — 3D spatial projection (X, Y, Z)
ST_3DTContains — 3D spatial projection plus time (X, Y, Z, T)

Parameters

Parameter	Description
`geom`	The geometry to compare.
`traj`	The trajectory to compare, or the source trajectory from which a sub-trajectory is extracted.
`box`	The bounding box to compare.
`r`	The time range for the query.
`ts`	The start of the time range used to extract a sub-trajectory. Optional.
`te`	The end of the time range used to extract a sub-trajectory. Optional.

Description

Checks whether the first argument contains the second argument. Behavior depends on the type of the first argument:

When the first argument is a geometry (geom)

Compares the 2D projections of the full trajectories, or of the sub-trajectory over [ts, te]. Returns true if the geometry contains — or is contained by — the other object.

When the first argument is a bounding box (box)

Checks whether the trajectory (or its sub-trajectory over [ts, te]) falls within the bounding box across all dimensions. If the bounding box, trajectory, or sub-trajectory has no value in a given dimension, that dimension is treated as matching any value and automatically satisfies the condition.

POLYHEDRALSURFACE geometry types are not supported.

Example

The following example constructs a trajectory along LINESTRING(0 0, 50 50, 100 100) over the time range [TS(0), TS(100)), and a 3D+time bounding box covering coordinates (0,0,0) to (50,50,50) and time range [TS(0), TS(49)]. It then tests containment for three sub-trajectory time ranges.

WITH traj AS(
    Select ST_makeTrajectory('STPOINT', 'LINESTRING(0 0, 50 50, 100 100)'::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,
           ST_MakeBox3dt(0,0,0,ST_PgEpochToTS(0), 50,50,50, ST_PgEpochToTS(49)) a
)
SELECT ST_2dContains(a,b,ST_PGEpochToTS(0), ST_PGEpochToTS(49)), ST_3dContains(a,b,ST_PGEpochToTS(0), ST_PGEpochToTS(49)),
       ST_2dtContains(a,b,ST_PGEpochToTS(0), ST_PGEpochToTS(49)), ST_3dtContains(a,b,ST_PGEpochToTS(0), ST_PGEpochToTS(49)),
       ST_2dContains(a,b,ST_PGEpochToTS(0), ST_PGEpochToTS(50)), ST_3dContains(a,b,ST_PGEpochToTS(0), ST_PGEpochToTS(50)),
       ST_2dtContains(a,b,ST_PGEpochToTS(0), ST_PGEpochToTS(50)), ST_3dtContains(a,b,ST_PGEpochToTS(0), ST_PGEpochToTS(50)),
       ST_2dContains(a,b,ST_PGEpochToTS(0), ST_PGEpochToTS(70)), ST_3dContains(a,b,ST_PGEpochToTS(0), ST_PGEpochToTS(70)),
       ST_2dtContains(a,b,ST_PGEpochToTS(0), ST_PGEpochToTS(70)), ST_3dtContains(a,b,ST_PGEpochToTS(0), ST_PGEpochToTS(70)) from traj;
 st_2dcontains | st_3dcontains | st_2dtcontains | st_3dtcontains | st_2dcontains | st_3dcontains | st_2dtcontains | st_3dtcontains | st_2dcontains | st_3dcontains | st_2dtcontains | st_3dtcontains
---------------+---------------+----------------+----------------+---------------+---------------+----------------+----------------+---------------+---------------+----------------+----------------
 t             | t             | t              | t              | t             | t             | f              | f              | f             | f             | f              | f

Result breakdown:

Sub-trajectory time range	ST_2DContains	ST_3DContains	ST_2DTContains	ST_3DTContains	Why
TS(0) to TS(49)	t	t	t	t	The sub-trajectory fits within the bounding box in all dimensions.
TS(0) to TS(50)	t	t	f	f	TS(50) falls outside the box's time range `[TS(0), TS(49)]`. Time-aware variants (`2DT`, `3DT`) return `false`; spatial-only variants (`2D`, `3D`) disregard the time axis and return `true`.
TS(0) to TS(70)	f	f	f	f	The sub-trajectory exceeds the bounding box in both spatial and time dimensions.