PolarDB:GanosBaseH3 geospatial grid capabilities and best practices

Prerequisites

Before you begin, make sure you have:

• A running PolarDB for PostgreSQL instance with GanosBase enabled

• An OSS bucket containing the FOIL CSV file (see Upload objects)

• Database superuser or extension-creation privileges

Set up extensions and schema

1. Install the GeomGrid extension. This also installs any required dependencies.

   CREATE EXTENSION ganos_geomgrid CASCADE;

2. Create the data table. The h3grid field type stores H3 cell codes. The field h3_lev13 holds the 13th level H3 code. Different levels of H3 grids have different resolutions. You can define the resolutions based on your business needs. For the spatial resolution corresponding to each level of H3, see the H3 resolution table.

   -- h3_lev13 stores the resolution 13 H3 cell for each pickup point
   CREATE TABLE FOIL2013 (
     id        text,
     lon       float,
     lat       float,
     h3_lev13  h3grid
   );

Import data from OSS

The FOIL dataset is a CSV file. Use the GanosBase foreign data wrapper (FDW) to load it directly from OSS without a separate ETL step.

1. Install the FDW extension.

   CREATE EXTENSION ganos_fdw CASCADE;

2. Create a server that points to the OSS CSV file. Replace the placeholders with your OSS credentials and bucket path. For datasource format details, see .

   CREATE SERVER csvserver
     FOREIGN DATA WRAPPER ganos_fdw
     OPTIONS (
       datasource 'OSS://<access_id>:<secrect_key>@[<Endpoint>]/<bucket>/path_to/file.csv',
       format 'CSV'
     );
   
   CREATE USER MAPPING FOR CURRENT_USER
     SERVER csvserver
     OPTIONS (user '<access_id>', password '<secrect_key>');

3. Map the CSV columns to a foreign table. The query interface is identical to a regular table.

   CREATE FOREIGN TABLE trip_data_1 (
     medallion         varchar,
     pickup_longitude  varchar,
     pickup_latitude   varchar
   )
   SERVER csvserver
   OPTIONS (layer 'trip_data_1');

4. Verify the foreign table returns data.

   SELECT * FROM trip_data_1;

5. Insert the CSV data into FOIL2013.

   INSERT INTO FOIL2013
   SELECT
     medallion AS id,
     CAST(pickup_longitude AS double precision) AS lon,
     CAST(pickup_latitude  AS double precision) AS lat
   FROM trip_data_1;

6. Verify the import.

   SELECT * FROM FOIL2013;

Encode pickup points as H3 cells

GanosBase supports several encoding functions:

Use ST_H3FromLatLng to encode every pickup point at resolution 13.

-- Populate h3_lev13 from the lat/lon columns at resolution 13
UPDATE FOIL2013
SET h3_lev13 = ST_H3FromLatLng(lat, lon, 13);

-- Inspect the encoded cells
SELECT id, lon, lat, ST_AsText(h3_lev13) AS h3
FROM FOIL2013
LIMIT 100;

Aggregate pickups into a heatmap

Group pickup points by H3 cell to get a per-cell count. This count table is the basis for the heatmap visualization in the next step.

-- Count pickups per resolution 13 cell
CREATE TABLE h3_count_lev13 AS
SELECT
  ST_AsText(h3_lev13) AS h3code,
  COUNT(*) AS count
FROM FOIL2013
GROUP BY h3_lev13;

-- Preview the busiest cells
SELECT ST_AsText(h3_lev13), ST_AsText(geometry), count
FROM h3_count_lev13
ORDER BY count DESC;

Query by grid distance

ST_GridDistance returns the number of grid steps between two H3 cells at the same resolution — similar to a hop count on a graph. The following query returns all pickup records within 10 grid steps of the coordinate (40.71481749, -73.99100368).

SELECT *
FROM foil2013
WHERE ST_GridDistance(
  ST_H3FromLatLng(40.71481749, -73.99100368, 13),
  h3_lev13
) < 10;

For the full ST_GridDistance reference, see ST_GridDistance.

Visualize H3 grids as vector tiles

GanosBase renders H3 cells as Mapbox Vector Tiles (MVT), letting you display per-cell statistics on an interactive map. The database serves tiles on demand; the front end colors each cell by pickup count.

GanosBase also supports rendering dynamically generated H3 cells that are not pre-stored in a table (using ST_AsMVTGeom(ST_H3FromLatLng(lat, lon, 10), ...)). Pre-storing cells, as in this example, is faster for repeated queries.

1. Create a GIST index on the h3_lev13 column to speed up spatial tile queries.

   CREATE INDEX ON h3_count_lev13 USING GIST(h3_lev13);

2. Retrieve the vector tile for tile coordinates (z=14, x=4826, y=6157). Each tile request from the front end calls a query in this form.

   SELECT ST_AsMVT(tile)
   FROM (
     SELECT
       ST_AsMVTGeom(
         h3_lev13,
         ST_Transform(ST_TileEnvelope(14, 4826, 6157), 4326)
       ) AS grid,
       count
     FROM h3_count_lev13
     WHERE h3_lev13 && ST_Transform(ST_TileEnvelope(14, 4826, 6157), 4326)
   ) AS tile;

3. Start the Python tile server and open http://localhost:5100 in your browser. The front end queries the database in real time as you pan and zoom the map, coloring each H3 cell by pickup count.

   

The front end requires only the two files in the Appendix — a Python script and an HTML file. Run the Python script to start the tile server on port 5100.

Python tile server

from quart import Quart, send_file, render_template
import asyncpg
import io
import re

## Database connection parameters
CONNECTION = {"host": "YOUR-HOST-NAME-OR-IP", "port": PORT_NO, "database": "DATABASE_NAME",
              "user": "USER_NAME", "password": "PASSWORD"}

## Target table name/field/ID
TABLE = "h3_count_lev13"
H3_COL = "h3_lev13"
H3_GEOM_COL = "geometry"
AGG_VAL_COL = "count"
COL_SRID = 4326

app = Quart(__name__, template_folder='./')


@app.before_serving
async def create_db_pool():
    app.db_pool = await asyncpg.create_pool(**CONNECTION)


@app.after_serving
async def close_db_pool():
    await app.db_pool.close()


@app.route("/")
async def home():
    sql = f'''
    SELECT ST_Extent(ST_Transform(ST_Envelope({H3_GEOM_COL}), 4326))
    FROM {TABLE};
    '''
    async with app.db_pool.acquire() as connection:
        box = await connection.fetchval(sql)
        box = re.findall('BOX\((.*?) (.*?),(.*?) (.*?)\)', box)[0]
        min_x, min_y, max_x, max_y = list(map(float, box))
        bounds = [[min_x, min_y], [max_x, max_y]]
        center = [(min_x + max_x) / 2, (min_y + max_y) / 2]
        return await render_template('./index.html', center=str(center), bounds=str(bounds))


@app.route("/h3_mvt/<int:z>/<int:x>/<int:y>")
async def h3_mvt(z, x, y):
    sql = f'''
    SELECT ST_AsMVT(tile.*)
    FROM
      (SELECT ST_AsMVTGeom({H3_COL}, ST_Transform(ST_TileEnvelope($1,$2,$3),{COL_SRID}), 4096, 512, true) geometry,
       {AGG_VAL_COL} count
      FROM {TABLE}
      WHERE ({H3_COL} && ST_Transform(ST_TileEnvelope($1,$2,$3),{COL_SRID}))) tile'''
    async with app.db_pool.acquire() as connection:
        tile = await connection.fetchval(sql, z, x, y)
        return await send_file(io.BytesIO(tile), mimetype='application/vnd.mapbox-vector-tile')

if __name__ == "__main__":
    app.run(port=5100)

index.html

<!DOCTYPE html>
<html>

<head>
  <meta charset="utf-8">
  <title>map viewer</title>
  <meta name="viewport" content="initial-scale=1,maximum-scale=1,user-scalable=no">
  <link href="https://api.mapbox.com/mapbox-gl-js/v2.14.1/mapbox-gl.css" rel="stylesheet">
  <script src="https://api.mapbox.com/mapbox-gl-js/v2.14.1/mapbox-gl.js"></script>
  <script src="https://cdnjs.cloudflare.com/ajax/libs/chroma-js/2.4.2/chroma.min.js"></script>
</head>

<body>
  <div id="map" style="position: absolute;left:0; top: 0; bottom: 0; width: 100%;cursor:pointer;"></div>
  <div class="counter"
    style="position: absolute;left:2%;font-size: 20px;padding: .1em .1em;text-shadow: 3px 3px 3px black;">
    <span>Current grid count:</span>
    <span id="count">0</span>
  </div>
  <script>
    let YOUR_TOKEN = "pk.eyJ1Ijoia3pmaWxlIiwiYSI6ImNqbHZueXdlZjB2cG4zdnFucGl1OHJsMjkifQ.kW_Utrh8ETQltRk6fnpa_A"

    mapboxgl.accessToken = YOUR_TOKEN;
    const map = new mapboxgl.Map({
      container: "map",
      style: "mapbox://styles/mapbox/navigation-night-v1",
      center: {{ center }},
      zoom: 1
    })

    map.on("load", () => {
      map.fitBounds({{ bounds }})

      map.on('mousemove', 'h3', (e) => {
        map.getCanvas().style.cursor = "default";
        if (e.features.length > 0)
          document.getElementById('count').innerText = e.features[0].properties.count
      })
      map.on('mouseleave', 'h3', () => {
        map.getCanvas().style.cursor = "grab";
        document.getElementById('count').innerText = 0
      })
      map.addSource("h3_source", {
        type: "vector",
        tiles: [`${window.location.href}h3_mvt/{z}/{x}/{y}`],
        tileSize: 512
      });

      // make color map
      const MIN = 1
      const MAX = 600
      const STEP = 10
      color_map = chroma.scale(["#536edb", "#5d96a5", "#68be70", "#91d54d", "#cddf37", "#fede28", "#fda938", "#fb7447", "#f75a40", "#f24734", "#e9352a", "#da2723", "#cb181d"])
        .domain([MIN, MAX]);
      let colors = []
      for (let i = MIN; i < MAX; i += STEP)
        colors.push(color_map(i).hex(), i)
      colors.push(color_map(MAX).hex())

      map.addLayer({
        id: "h3",
        type: "fill",
        source: "h3_source",
        "source-layer": "default",
        paint: {
          "fill-color": [
            "step", ["get", "count"],
            ...colors
          ],
          "fill-opacity": 0.8
        }
      });

    });
  </script>
</body>

</html>