GeomGrid SQL operators-ApsaraDB RDS(RDS)-阿里云帮助中心

GeomGrid SQL provides operators for spatial relationship queries between grid objects and geometry objects. Before a bounding-box check runs, GeomGrid and H3Grid objects are automatically converted to Geometry objects.

Supported operators

Operator	Description
`@>`	Returns true if the spatial extent of a grid object contains a geometry object
`<@`	Returns true if the spatial extent of a grid object is contained by a geometry object
`&&&`	Returns true if the spatial extent of a geographic grid object intersects a geometry object
`@@>`	Returns true if the spatial extent of a geographic grid object contains a geometry object
`<@@`	Returns true if the spatial extent of a geographic grid object is contained by a geometry object
`&&`	Returns true if the bounding boxes of two GeomGrid/Geometry or H3Grid/Geometry objects intersect
`~`	Returns true if the bounding box of A contains the bounding box of B
`@`	Returns true if the bounding box of A is contained by the bounding box of B

Operators @>, <@, &&&, @@>, and <@@ require the geometry object to use the CGC2000 spatial reference system (SRID=4490).

@>

Returns true if the spatial extent of a grid object contains a geometry object.

Syntax

boolean @>(geomgrid grid, geometry geom);
boolean @>(geometry geom, geomgrid grid);
boolean @>(geomgrid grid1, geomgrid grid2);

Parameters

Parameter	Description
`grid`	A grid object
`geom`	A geometry object. Must use CGC2000 (SRID=4490).

Examples

Check whether grid G00133103221330 contains grid G001331032213300013:

SELECT ST_GridFromText('G00133103221330') @>
       ST_GridFromText('G001331032213300013') AS t;

Output:

 t
---
 t

Check whether grid G001331032213300013 contains a point:

SELECT ST_GridFromText('G001331032213300013') @>
       ST_GeomFromText('POINT(116.31522216796875 39.910277777777778)', 4490) AS t;

Output:

 t
---
 f

<@

Returns true if the spatial extent of a grid object is contained by a geometry object.

Syntax

boolean <@(geomgrid grid, geometry geom);
boolean <@(geometry geom, geomgrid grid);
boolean <@(geomgrid grid1, geomgrid grid2);

Parameters

Parameter	Description
`grid`	A grid object
`geom`	A geometry object. Must use CGC2000 (SRID=4490).

Examples

Check whether grid G001331032213300013 is contained by grid G001331032213300:

SELECT ST_GridFromText('G001331032213300013') <@
       ST_GridFromText('G001331032213300') AS t;

Output:

 t
---
 t

Check whether a point is contained by grid G001331032213300013:

SELECT ST_GeomFromText('POINT(116.31522216796875 39.910277777777778)', 4490) <@
       ST_GridFromText('G001331032213300013') AS t;

Output:

 t
---
 f

&&&

Returns true if the spatial extent of a geographic grid object intersects a 3D geometry object.

Syntax

bool &&&(geometry geom3d, geomgrid gridcode);
bool &&&(geomgrid gridcode, geometry geom3d);
bool &&&(meshgeom geom3d, geomgrid gridcode);
bool &&&(geomgrid gridcode, meshgeom geom3d);
bool &&&(sfmesh geom3d, geomgrid gridcode);
bool &&&(geomgrid gridcode, sfmesh geom3d);
bool &&&(vomesh geom3d, geomgrid gridcode);
bool &&&(geomgrid gridcode, vomesh geom3d);
bool &&&(geomgrid gridcode1, geomgrid gridcode2);

Parameters

Parameter	Description
`gridcode` / `gridcode1` / `gridcode2`	A geographic grid object
`geom3d`	A 3D geometry object (`geometry`, `meshgeom`, `sfmesh`, or `vomesh`). Must use CGC2000 (SRID=4490).

Examples

Check whether two geographic grid objects intersect:

SELECT ST_GridFromText('GZ00262064') &&&
       ST_GridFromText('GZ00262063');

Output:

 st_3dintersects
-----------------
 t

Check whether a geographic grid object intersects a 3D point:

SELECT ST_GridFromText('GZ00262064446046072072') &&&
       'srid=4490;POINT(116.31522216796875 39.910277777777778 1001.8)'::geometry;

Output:

 st_3dintersects
-----------------
 f

@@>

Returns true if the spatial extent of a geographic grid object contains a 3D geometry object.

Syntax

bool @@>(geometry geom3d, geomgrid gridcode);
bool @@>(geomgrid gridcode, geometry geom3d);
bool @@>(meshgeom geom3d, geomgrid gridcode);
bool @@>(geomgrid gridcode, meshgeom geom3d);
bool @@>(sfmesh geom3d, geomgrid gridcode);
bool @@>(geomgrid gridcode, sfmesh geom3d);
bool @@>(vomesh geom3d, geomgrid gridcode);
bool @@>(geomgrid gridcode, vomesh geom3d);
bool @@>(geomgrid gridcode1, geomgrid gridcode2);

Parameters

Parameter	Description
`gridcode` / `gridcode1` / `gridcode2`	A geographic grid object
`geom3d`	A 3D geometry object (`geometry`, `meshgeom`, `sfmesh`, or `vomesh`). Must use CGC2000 (SRID=4490).

Examples

Check whether grid GZ00262064 contains grid GZ002620643:

SELECT ST_GridFromText('GZ00262064') @@>
       ST_GridFromText('GZ002620643');

Output:

 st_3dcontains
---------------
 t

Check whether a geographic grid object contains a 3D point:

SELECT ST_GridFromText('GZ00262064446046072072') @@>
       'srid=4490;POINT(116.31522216796875 39.910277777777778 1001.8)'::geometry;

Output:

 st_3dcontains
---------------
 t

<@@

Returns true if the spatial extent of a geographic grid object is contained by a 3D geometry object.

Syntax

bool <@@(geometry geom3d, geomgrid gridcode);
bool <@@(geomgrid gridcode, geometry geom3d);
bool <@@(meshgeom geom3d, geomgrid gridcode);
bool <@@(geomgrid gridcode, meshgeom geom3d);
bool <@@(sfmesh geom3d, geomgrid gridcode);
bool <@@(geomgrid gridcode, sfmesh geom3d);
bool <@@(vomesh geom3d, geomgrid gridcode);
bool <@@(geomgrid gridcode, vomesh geom3d);
bool <@@(geomgrid gridcode1, geomgrid gridcode2);

Parameters

Parameter	Description
`gridcode` / `gridcode1` / `gridcode2`	A geographic grid object
`geom3d`	A 3D geometry object (`geometry`, `meshgeom`, `sfmesh`, or `vomesh`). Must use CGC2000 (SRID=4490).

Example

Check whether grid GZ0026206435 is contained by grid GZ002620643:

SELECT ST_GridFromText('GZ0026206435') <@@
       ST_GridFromText('GZ002620643');

Output:

 st_3dwithin
-------------
 t

&&

Returns true if the bounding boxes of two GeomGrid/Geometry objects or two H3Grid/Geometry objects intersect.

Before the check runs, GeomGrid objects are converted to Geometry using the CGC2000 coordinate system (SRID=4490), and H3Grid objects are converted using the WGS 84 coordinate system (SRID=4326).

Syntax

bool &&(GeomGrid A, GeomGrid B);
bool &&(GeomGrid A, Geometry B);
bool &&(Geometry A, GeomGrid B);
bool &&(H3Grid A, H3Grid B);
bool &&(H3Grid A, Geometry B);
bool &&(Geometry A, H3Grid B);

Parameters

Parameter	Description
`A`	A GeomGrid, H3Grid, or Geometry object
`B`	A GeomGrid, H3Grid, or Geometry object

Examples

The examples below query geomgrid_gist_test and h3grid_gist_test, which contain sample GeomGrid and H3Grid data respectively.

GeomGrid && GeomGrid — count rows whose bounding box intersects grid G00:

SELECT COUNT(*) FROM geomgrid_gist_test
WHERE 'G00'::GeomGrid && code;

Output:

 count
-------
     5

GeomGrid && Geometry — count rows whose bounding box intersects a polygon:

SELECT COUNT(*) FROM geomgrid_gist_test
WHERE code && 'SRID=4490;POLYGON((80 20,90 20,90 25,80 25,80 20))'::Geometry;

Output:

 count
-------
     5

H3Grid && H3Grid — count rows whose bounding box intersects an H3Grid cell:

SELECT COUNT(*) FROM h3grid_gist_test
WHERE code && ST_H3FromLatLng(-17.5, -65.0, 1);

Output:

 count
-------
     1

Geometry && H3Grid — count rows whose bounding box intersects a polygon:

SELECT COUNT(*) FROM h3grid_gist_test
WHERE 'SRID=4490;POLYGON((-70 -20,-60 -20,-60 -10,-70 -10,-70 -20))'::Geometry && code;

Output:

 count
-------
     1

~

Returns true if the bounding box of A contains the bounding box of B.

Before the check runs, GeomGrid objects are converted to Geometry using the CGC2000 coordinate system (SRID=4490), and H3Grid objects are converted using the WGS 84 coordinate system (SRID=4326).

Syntax

bool ~(GeomGrid A, GeomGrid B);
bool ~(GeomGrid A, Geometry B);
bool ~(Geometry A, GeomGrid B);
bool ~(H3Grid A, H3Grid B);
bool ~(H3Grid A, Geometry B);
bool ~(Geometry A, H3Grid B);

Parameters

Parameter	Description
`A`	A GeomGrid, H3Grid, or Geometry object
`B`	A GeomGrid, H3Grid, or Geometry object

Examples

GeomGrid ~ GeomGrid — count rows whose bounding box is contained by grid G00:

SELECT COUNT(*) FROM geomgrid_gist_test
WHERE 'G00'::GeomGrid ~ code;

Output:

 count
-------
     5

Geometry ~ GeomGrid — count rows whose bounding box is contained by a polygon:

SELECT COUNT(*) FROM geomgrid_gist_test
WHERE 'SRID=4490;POLYGON((88 24,88.8 24,88.8 24.5,88 24.5,88 24))'::Geometry ~ code;

Output:

 count
-------
     2

H3Grid ~ H3Grid — count rows whose bounding box is contained by an H3Grid cell:

SELECT COUNT(*) FROM h3grid_gist_test
WHERE code ~ ST_H3FromLatLng(-17.5, -65.0, 1);

Output:

 count
-------
     0

Geometry ~ H3Grid — count rows whose bounding box is contained by a polygon:

SELECT COUNT(*) FROM h3grid_gist_test
WHERE 'SRID=4326;POLYGON((0 30,5 30,5 38,0 38,0 30))'::Geometry ~ code;

Output:

 count
-------
     1

@

Returns true if the bounding box of A is contained by the bounding box of B.

Before the check runs, GeomGrid objects are converted to Geometry using the CGC2000 coordinate system (SRID=4490), and H3Grid objects are converted using the WGS 84 coordinate system (SRID=4326).

Syntax

bool @(GeomGrid A, GeomGrid B);
bool @(GeomGrid A, Geometry B);
bool @(Geometry A, GeomGrid B);
bool @(H3Grid A, H3Grid B);
bool @(H3Grid A, Geometry B);
bool @(Geometry A, H3Grid B);

Parameters

Parameter	Description
`A`	A GeomGrid, H3Grid, or Geometry object
`B`	A GeomGrid, H3Grid, or Geometry object

Examples

GeomGrid @ GeomGrid — count rows whose bounding box is contained by grid G00:

SELECT COUNT(*) FROM geomgrid_gist_test
WHERE code @ 'G00'::GeomGrid;

Output:

 count
-------
     5

GeomGrid @ Geometry — count rows whose bounding box is contained by a polygon:

SELECT COUNT(*) FROM geomgrid_gist_test
WHERE code @ 'SRID=4490;POLYGON((88 24,88.8 24,88.8 24.5,88 24.5,88 24))'::Geometry;

Output:

 count
-------
     2

H3Grid @ H3Grid — count rows whose bounding box is contained by an H3Grid cell:

SELECT COUNT(*) FROM h3grid_gist_test
WHERE code @ ST_H3FromLatLng(-17.5, -65.0, 1);

Output:

 count
-------
     1

H3Grid @ Geometry — count rows whose bounding box is contained by a polygon:

SELECT COUNT(*) FROM h3grid_gist_test
WHERE code @ 'SRID=4490;POLYGON((0 30,5 30,5 38,0 38,0 30))'::Geometry;

Output:

 count
-------
     1

Related operators

The operators in this document form complementary pairs and groups:

Relationship	Operators
Contains / contained by (grid extent vs. geometry)	`@>` and `<@`
3D contains / contained by / intersects (geographic grid)	`@@>`, `<@@`, and `&&&`
Bounding box intersection	`&&`
Bounding box containment (A contains B / A contained by B)	`~` and `@`