PolarDB:GanosBase GeomGrid

GeomGrid and H3Grid data types

GanosBase GeomGrid provides two geographic grid data types:

• GeomGrid — the GeoSOT-based geographic grid type. Supports 2D and 3D grids.

• H3Grid — the H3-based geographic grid type. Supports 2D hexagonal grids.

Both types support:

• Import and export: Convert from Text, Bytea, Geometry, Meshgeom, Sfmesh, and Vomesh to the grid type, and back.

• Spatial operations: Get grid levels and coordinates, calculate parent and child grids, merge grids, and subdivide grids.

• Spatial relationship identification: Identify intersections, containment, and other spatial relationships between a grid and geometry types (Geometry, Meshgeom, Sfmesh, Vomesh, GeomGrid).

For the full function reference, see GeomGrid SQL reference.

Choose between GeoSOT and H3

Hexagonal grids have a key advantage for aggregation and movement analysis: all six neighbors of a hexagon are equidistant from the center. Rectangular grids have two classes of neighbors (edge-sharing and corner-sharing) at different distances, which complicates proximity calculations.

GeoSOT encoding

GeoSOT transforms the Earth into a 512°×512° plane (expanded from the actual 180°×360° surface). The center of this plane is at the intersection of the equator and the prime meridian. Quad-tree recursive subdivision from this base produces up to 32 grid levels, covering the entire globe down to several centimeters.

2D grid codes

A GeoSOT 2D grid code is a quaternary number up to 32 bits long:

Longitude and latitude values use the A°B'C.D" format. Each component is converted to binary:

• Degree (A) → 8-bit binary

• Minute (B) → 6-bit binary

• Second (C) → 6-bit binary

• Fractional second (D) → 11-bit binary

The components are concatenated into a 31-bit binary number per coordinate axis. Latitude is placed before longitude to produce a 62-bit Morton code. A hemisphere prefix (G0–G3) is prepended to the Morton code to form the grid code:

3D grid codes

The 3D grid adds a height dimension to the 2D grid. Each height grid corresponds to a 1° ground surface grid. The height axis is divided into 256 levels upward from the surface and 256 levels downward toward the Earth's core. The height level is converted to a binary string and combined with the latitude/longitude values using the same Morton code scheme.

H3 encoding

H3 projects the Earth onto a sphere-circumscribed icosahedron — a polyhedron with 20 spherical triangles and 12 vertices.

H3 creates hexagonal grids on each triangular face, dividing the globe into 122 base cells. Each base cell is then recursively subdivided into 7 child cells.

The following figure shows how base cell 20 is subdivided:

An H3 code is up to 63 bits long and is typically serialized as a hexadecimal string. The bit layout encodes the resolution level and geographic location of the cell:

Indexes

GanosBase GeomGrid supports three index types to accelerate spatial queries:

Prerequisites

Before you begin, ensure that you have:

• A PolarDB for PostgreSQL (Compatible with Oracle) instance

• Database superuser or extension creation privileges

Create the extension

Run the following SQL to create the GanosBase GeomGrid extension:

CREATE EXTENSION Ganos_GeomGrid CASCADE;

Create the extension in the public schema to avoid permission issues:

CREATE EXTENSION ganos_geometry WITH SCHEMA public CASCADE;

GeoSOT grid operations

Create a table

-- Create a table with geometry and grid code columns
CREATE TABLE t_grid(
  id     integer,
  geom   geometry,    -- Geometry object
  grid1  geomgrid[],  -- Grid codes at precision level 10
  grid2  geomgrid[],  -- Grid codes at precision level 15
  grid3  geomgrid[]   -- Grid codes at precision level 26
);

Insert data

-- Insert point data (SRID 4490: China Geodetic Coordinate System 2000)
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 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);

Generate grid codes

All three examples use ST_AsGrid to generate GeoSOT grid codes at different precision levels.

-- Generate 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);

-- Generate a degenerated grid code (pass true as the third argument)
UPDATE t_grid SET grid1 = ST_AsGrid(geom, 18, true) WHERE id = 5;

-- Generate a 3D grid code
UPDATE t_grid SET grid1 = ST_As3DGrid(geom, 25) WHERE id = 6;

Create indexes

-- Create GridGin indexes on each grid code column
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);

Query data

-- Find rows whose grid2 is contained in a specific grid
SELECT id
FROM t_grid
WHERE grid2 = ARRAY[ST_GridFromText('G001310322230230')];

-- Find rows whose grid3 intersects a specific grid
SELECT id
FROM t_grid
WHERE grid3 @> ARRAY[ST_GridFromText('G00131032223023031031033223')];

-- Find rows whose grid3 intersects any grid in an array
SELECT id
FROM t_grid
WHERE grid3 && ARRAY[ST_GridFromText('G00131032223023031031211001'),
                     ST_GridFromText('G00131032223023031031211111')];

-- Find rows whose grid3 intersects the grid codes derived from a geometry
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

Create a table

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

Insert 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);

Generate H3 grid codes

-- Generate grid codes at resolution 7
UPDATE h3_grid SET h3 = ST_AsH3Grid(geom, 7);

-- Generate degenerated grid codes
UPDATE h3_grid SET h3 = ST_AsH3Grid(geom, 7, true);

Create an index

CREATE INDEX h3_grid_btree ON h3_grid(h3);

Query data

-- Display H3 codes as text
SELECT ST_AsText(h3[1]) FROM h3_grid;

-- Query rows by H3 code comparison
SELECT * FROM h3_grid WHERE h3 > ARRAY[ST_H3FromText('884a126689fffff')];

Drop the extension

DROP EXTENSION Ganos_GeomGrid CASCADE;