PolarDB:Geographic grid model

Choose between GeoSOT and H3

GeoSOT precision levels

The grid level determines cell size. Use this table to select the level that matches your precision requirement.

Prerequisites

Before you begin, make sure you have:

• A PolarDB for PostgreSQL cluster

• Database access with permission to create extensions

GeoSOT grid operations

1. Create the extension in the public schema to avoid permission issues.

   If the command fails with an error about a missing dependent extension such as ganos_sfmesh, contact us.

   CREATE EXTENSION Ganos_GeomGrid WITH SCHEMA public CASCADE;

2. Create a table with geometry and grid code columns.

   -- Table with geometry source and multi-precision grid columns.
   CREATE TABLE t_grid (
     id    integer,
     geom  geometry,    -- Source geometry
     grid1 geomgrid[],  -- Grid codes at precision level 10
     grid2 geomgrid[],  -- Grid codes at precision level 15
     grid3 geomgrid[]   -- Grid codes at precision level 26
   );

3. Insert geometry data.

   -- Insert point data.
   INSERT INTO t_grid (id, geom)
   VALUES
     (1, ST_GeomFromText('POINT(116.31522216796875 39.910277777777778)', 4490)),
     (2, ST_GeomFromText('POINT(116.31522217796875 39.910277776777778)', 4490)),
     (3, ST_GeomFromText('POINT(116.31522217797875 39.910277776787778)', 4490)),
     (4, ST_GeomFromText('POINT(116.31522227796875 39.910277776775778)', 4490));
   
   -- Insert polygon data.
   INSERT INTO t_grid (id, geom)
   VALUES (5, 'SRID=4490;POLYGON((-0.08077 -0.02814, 0.0482 -0.03, 0.07426 0.03724, -0.08077 -0.02814))'::geometry);
   
   -- Insert 3D arc data.
   INSERT INTO t_grid (id, geom)
   VALUES (6, 'SRID=4490;CIRCULARSTRING Z (-63.597471 44.8071 20,-63.597 44.807 0,-63.5974 44.807 40)'::geometry);

4. Calculate grid codes at different precision levels.

   -- Populate grid codes at three precision levels.
   UPDATE t_grid
   SET grid1 = ST_AsGrid(geom, 10),
       grid2 = ST_AsGrid(geom, 15),
       grid3 = ST_AsGrid(geom, 26);
   
   -- Calculate a degenerated grid code for a polygon (id = 5).
   UPDATE t_grid SET grid1 = ST_AsGrid(geom, 18, true) WHERE id = 5;
   
   -- Calculate a 3D grid code (id = 6).
   UPDATE t_grid SET grid1 = ST_As3DGrid(geom, 25) WHERE id = 6;

5. Create GIN indexes on each grid column to accelerate queries.

   CREATE INDEX idx_grid_gin1 ON t_grid USING GIN (grid1);
   CREATE INDEX idx_grid_gin2 ON t_grid USING GIN (grid2);
   CREATE INDEX idx_grid_gin3 ON t_grid USING GIN (grid3);

6. Query the grid data. All examples use array operators with the indexed grid columns.

   -- Find rows whose grid2 codes are contained in a specific grid.
   SELECT id FROM t_grid
   WHERE grid2 = ARRAY[ST_GridFromText('G001310322230230')];
   
   -- Find rows whose grid3 codes intersect with a specific grid.
   SELECT id FROM t_grid
   WHERE grid3 @> ARRAY[ST_GridFromText('G00131032223023031031033223')];
   
   -- Find rows whose grid3 codes intersect with any of multiple grids.
   SELECT id FROM t_grid
   WHERE grid3 && ARRAY[
     ST_GridFromText('G00131032223023031031211001'),
     ST_GridFromText('G00131032223023031031211111')
   ];
   
   -- Find rows whose grid3 codes intersect with the grid coverage of a line.
   SELECT id FROM t_grid
   WHERE grid3 &&
     ST_AsGrid(
       ST_GeomFromText('LINESTRING(116.31522216796875 39.910277777777778, 116.31522217797875 39.910277776787778)', 4490),
       26
     );

H3 grid operations

1. Create a table with an H3 grid column.

   CREATE TABLE h3_grid (
     id   integer,
     geom geometry,   -- Source geometry
     h3   h3grid[]    -- H3 grid codes
   );

2. Insert geometry data.

   INSERT INTO h3_grid VALUES (1, 'POINT(102.5 25.7)'::geometry);
   INSERT INTO h3_grid VALUES (2, 'POLYGON((-0.08077 -0.02814, 0.0482 -0.03, 0.07426 0.03724, -0.08077 -0.02814))'::geometry);

3. Calculate H3 grid codes.

   -- Regular H3 grid codes at resolution level 7.
   UPDATE h3_grid SET h3 = ST_AsH3Grid(geom, 7);
   
   -- Degenerated H3 grid codes at resolution level 7.
   UPDATE h3_grid SET h3 = ST_AsH3Grid(geom, 7, true);

4. Create a B-tree index on the H3 column.

   CREATE INDEX h3_grid_btree ON h3_grid (h3);

5. Query the H3 data.

   -- Display H3 codes as text.
   SELECT ST_AsText(h3[1]) FROM h3_grid;
   
   -- Query rows with H3 codes greater than a reference value.
   SELECT * FROM h3_grid WHERE h3 > ARRAY[ST_H3FromText('884a126689fffff')];

Remove the extension

Run the following command to remove GanosBase GeomGrid and its dependent objects.

DROP EXTENSION Ganos_GeomGrid CASCADE;