The ~ operator returns true if the 3D bounding box of object A contains the 3D bounding box of object B, and false otherwise. It operates on sfmesh, meshgeom, and box3d objects in PolarDB for PostgreSQL.
Syntax
boolean ~(sfmesh A, sfmesh B);
boolean ~(meshgeom A, meshgeom B);
boolean ~(sfmesh A, box3d B);
boolean ~(box3d A, sfmesh B);Parameters
| Parameter | Description |
|---|---|
| A | The left-hand operand. Can be an sfmesh, meshgeom, or box3d object. |
| B | The right-hand operand. Can be an sfmesh, meshgeom, or box3d object. |
Return value
| Result | Condition |
|---|---|
true | The 3D bounding box of A contains the 3D bounding box of B. |
false | The 3D bounding box of A does not contain the 3D bounding box of B. |
Examples
Example 1: sfmesh ~ sfmesh
The query counts rows in mesh_gist_test where the literal mesh value (A) contains the bounding box of the stored sfmesh column (the_mesh).
-- ~(sfmesh, sfmesh)
select count(*) from mesh_gist_test where '{"version" : 1, "root" : 0, "meshgeoms" : ["MESHGEOM(PATCH(INDEXSURFACE Z (VERTEX(307248.723802283 296449.440306073 6500.00004882812,307248.723802283 296449.440306073 100,307258.460240141 296449.440306073 6500.00004882812,307258.460240141 296449.440306073 100,307258.460240141 296474.440306263 6500.00004882812,307258.460240141 296474.440306263 100,307248.723802283 296474.440306263 6500.00004882812,307248.723802283 296474.440306263 100),INDEX((0,1,2),(3,2,1),(4,5,6),(7,6,5),(3,1,7),(7,5,3),(2,3,4),(5,4,3),(0,2,4),(4,6,0),(1,0,7),(6,7,0)))))"], "primitives" : [{"meshgeom" : 0}], "nodes" : [{"primitive" : 0}]}'::mesh ~ the_mesh;Result:
1Example 2: meshgeom ~ meshgeom
The query counts rows in meshgeom_gist_test where the literal mesh cast to meshgeom (A) contains the bounding box of the stored meshgeom column (the_meshgeom).
-- ~(meshgeom, meshgeom)
select count(*) from meshgeom_gist_test where '{"version" : 1, "root" : 0, "meshgeoms" : ["MESHGEOM(PATCH(INDEXSURFACE Z (VERTEX(307248.723802283 296449.440306073 6500.00004882812,307248.723802283 296449.440306073 100,307258.460240141 296449.440306073 6500.00004882812,307258.460240141 296449.440306073 100,307258.460240141 296474.440306263 6500.00004882812,307258.460240141 296474.440306263 100,307248.723802283 296474.440306263 6500.00004882812,307248.723802283 296474.440306263 100),INDEX((0,1,2),(3,2,1),(4,5,6),(7,6,5),(3,1,7),(7,5,3),(2,3,4),(5,4,3),(0,2,4),(4,6,0),(1,0,7),(6,7,0)))))"], "primitives" : [{"meshgeom" : 0}], "nodes" : [{"primitive" : 0}]}'::mesh::meshgeom ~ the_meshgeom;Result:
1Example 3: sfmesh ~ box3d
The mesh is the left-hand operand (A). The query counts rows in mesh_gist_test where the mesh's bounding box contains the specified box3d.
-- ~(sfmesh, box3d)
select count(*) from mesh_gist_test where the_mesh ~ st_3dmakebox('POINT(206126.22379756 290161.940316926 5249.99990997314)'::geometry,'POINT(226126.22379756 300161.940316926 7249.99990997314)'::geometry);Result:
2Example 4: box3d ~ sfmesh
The box3d is the left-hand operand (A). The query counts rows where the box3d contains the mesh's bounding box — the reverse of Example 3.
-- ~(box3d, sfmesh)
select count(*) from mesh_gist_test where st_3dmakebox('POINT(206126.22379756 290161.940316926 5249.99990997314)'::geometry,'POINT(226126.22379756 300161.940316926 7249.99990997314)'::geometry) ~ the_mesh;Result:
0Examples 3 and 4 use the same bounding box and the same table. The results differ because ~ is not commutative: A containing B does not imply B contains A.