INTERSECT operators-AnalyticDB(AnalyticDB)-阿里云帮助中心

INTERSECT operators check whether the bounding boxes of two objects overlap in one or more spatial or temporal dimensions. Bounding boxes are generated by the ST_MakeBox function.

Syntax

{geometry, trajectory, boxndf} /&/ {geometry, trajectory, boxndf}
{trajectory, boxndf} #&# {trajectory, boxndf}
{geometry, trajectory, boxndf} && {geometry, trajectory, boxndf}
{geometry, trajectory, boxndf} &/& {geometry, trajectory, boxndf}
{trajectory, boxndf} &#& {trajectory, boxndf}
{trajectory, boxndf} &/#& {trajectory, boxndf}

Parameters

Parameter	Description
Left operand	The object whose bounding box you want to compare.
Right operand	The object whose bounding box you want to compare.

Operators

The operator symbols encode the dimensions checked: / indicates the z dimension, # indicates the t (time) dimension, and the presence of both indicates both. The base && checks the x and y dimensions.

Operator	Supported types	Dimensions checked
`/&/`	geometry, trajectory, boxndf	z
`#&#`	trajectory, boxndf	t
`&&`	geometry, trajectory, boxndf	x, y
`&/&`	geometry, trajectory, boxndf	x, y, z
`&#&`	trajectory, boxndf	x, y, t
`&/#&`	trajectory, boxndf	x, y, z, t, and t

All operators return a boolean: t if the bounding boxes intersect in the specified dimensions, f if they do not.

Example

The following query creates two bounding boxes — a (a 3D box spanning (0,0,0) to (5,5,5)) and b (a 4D box with spatial range (6,6,3) to (8,8,5) and time range 2010-04-12 to 2013-02-01) — and applies all six operators.

WITH box AS(
    SELECT ST_MakeBox3d(0,0,0,5,5,5) a,
           ST_MakeBox3dt(6,6, 3,'2010-04-12 00:00:00',8,8,5,'2013-02-01 00:00:00') b
)
SELECT a /&/ b AS OpZ, a #&# b AS OpT, a && b AS Op2D, a &/& b AS Op3d, a &#& b AS Op2DT, a &/#& b AS Op3DT from box;
 opz | opt | op2d | op3d | op2dt | op3dt
-----+-----+------+------+-------+-------
 t   | t   | f    | f    | f     | f

Boxes a and b overlap in the z dimension (OpZ = t) and in the t dimension (OpT = t), but not in the x/y dimensions (Op2D = f). Because the x/y bounding boxes do not intersect, all operators that include the x and y dimensions (Op2D, Op3d, Op2DT, Op3DT) return f.