Use ST_AsGrid to query grids intersecting a geometry - PolarDB

Returns an array of geographic grid codes (geomgrid) that intersect with a given geometry.

Syntax

geomgrid[] ST_AsGrid(geometry geom, integer precision, bool degenerated default false);

Parameters

Parameter	Description
`geom`	The input geometry.
`precision`	The grid resolution level. Valid values: 1–32. Higher values produce more precise results.
`degenerated`	Specifies whether to use a degenerated grid. The following figures compare a degenerated grid and a standard grid. Default: `false`.

Description

ST_AsGrid requires the input geometry to use the CGC2000 spatial reference system (Spatial Reference System Identifier (SRID) 4490). If the geometry uses a different spatial reference system, ST_Transform is called automatically to convert the coordinates to CGC2000 before processing.

The function returns a geomgrid array containing all geographic grid cells that intersect with the geometry. The following figure illustrates how grid cells intersect with a point, a line, and a polygon.

Examples

All examples call ST_AsGrid with an SRID 4490 geometry and return the intersecting grid codes as text via ST_AsText.

Get grid codes for a point

SELECT ST_AsText(ST_AsGrid(
    ST_GeomFromText('POINT(116.31522216796875 39.910277777777778)', 4490), 15));

Output:

      st_astext
--------------------
 {G001310322230230}

Get grid codes for a line

SELECT ST_AsText(ST_AsGrid(
    ST_GeomFromText('LINESTRING(122.48077 51.72814, 122.47416 51.73714)', 4490), 18));

Output:

                                  st_astext
--------------------------------------------------------------------------------
 {G001331032213300011,G001331032213300013,G001331032213122320,G001331032213122322,
  G001331032213300100,G001331032213122303,G001331032213122321,G001331032213122312}

Get grid codes for a polygon (standard mode)

Uses degenerated=false (the default).

SELECT ST_AsText(ST_AsGrid(
    ST_GeomFromText('POLYGON((-0.08077 -0.02814, 0.0482 -0.03, 0.07426 0.03724, -0.08077 -0.02814))', 4490), 15));

Output (29 grid codes):

                                  st_astext
--------------------------------------------------------------------------------
 {G000000000000000,G000000000000001,G000000000000003,G000000000000010,G000000000
 000011,G000000000000012,G000000000000013,G000000000000031,G000000000000102,G0000
 00000000120,G100000000000000,G200000000000000,G200000000000001,G200000000000002,
 G200000000000003,G200000000000010,G200000000000011,G200000000000012,G20000000000
 0013,G300000000000000,G300000000000001,G300000000000002,G300000000000003,G300000
 000000010,G300000000000011,G300000000000012,G300000000000013,G300000000000102}

Get grid codes for a polygon (degenerated mode)

Uses degenerated=true.

SELECT ST_AsText(ST_AsGrid(
    ST_GeomFromText('POLYGON((-0.08077 -0.02814, 0.0482 -0.03, 0.07426 0.03724, -0.08077 -0.02814))', 4490), 15, true));

Output (13 grid codes, compared to 29 in standard mode):

                                  st_astext
--------------------------------------------------------------------------------
 {G000000000000000,G000000000000001,G000000000000003,G00000000000001,G0000000000
 00031,G000000000000102,G000000000000120,G100000000000000,G20000000000000,G200000
 00000001,G30000000000000,G30000000000001,G300000000000102}