PolarDB:Vector pyramid model

Prerequisites

Before you begin, make sure you have:

• A PolarDB for PostgreSQL cluster

• A table with a geometry column

• A spatial index on the geometry column

Install the extension

CREATE EXTENSION ganos_geometry_pyramid CASCADE;

Install the extension into the public schema to avoid potential permission issues.

CREATE EXTENSION ganos_geometry_pyramid WITH SCHEMA public;

Build a vector tile pyramid

-- Build a pyramid on the "test" table
-- The id column must be int4 or int8
-- sourceSRS specifies the coordinate system of the source data (EPSG:4326 here)
SELECT ST_BuildPyramid('test', 'geom', 'id', '{"sourceSRS":4326}');

After the build completes, the pyramid is ready for queries.

Query tiles

Tiles are addressed using the z/x/y scheme, where z is the zoom level, x is the column index, and y is the row index. All values are zero-based, with the origin at the top-left of the tile grid. Zoom level 0 covers the entire world in a single tile; each subsequent level quadruples the number of tiles. In the API, this address is formatted as z_x_y.

Use ST_Tile to return vector tiles in MVT format, or ST_AsPNG to render tiles and return PNG images.

-- Query the tile at zoom level 0, column 0, row 0
-- The default projection coordinate system is EPSG:3857
-- Returns the tile in MVT format if it exists; returns null if the tile does not exist
SELECT ST_Tile('test', '0_0_0');

Update the pyramid

After updating rows in the table, update the pyramid to reflect the changes.

For updates affecting a small spatial extent (less than 1% of the total area):

-- Insert new geometry records
INSERT INTO test(id, geom) VALUES (1, ST_GeomFromEWKT('SRID=4326;POINT(10.1 10.1)'));
INSERT INTO test(id, geom) VALUES (2, ST_GeomFromEWKT('SRID=4326;LINESTRING(10.1 10.1,11 11)'));
INSERT INTO test(id, geom) VALUES (3, ST_GeomFromEWKT('SRID=4326;POLYGON((10 10,11 11,11 12,10 10))'));

-- Update only the affected region using a bounding box
SELECT ST_UpdatePyramid('test', 'geom', 'id', ST_SetSRID(ST_MakeBox2D(ST_Point(9, 9), ST_Point(12, 12)), 4326));

For updates affecting a large portion of the dataset, rebuild the pyramid:

-- Insert records across different geographic regions
INSERT INTO test(id, geom) VALUES (4, ST_GeomFromEWKT('SRID=4326;POINT(-59 -45)'));
INSERT INTO test(id, geom) VALUES (5, ST_GeomFromEWKT('SRID=4326;LINESTRING(110 60,115 70)'));
INSERT INTO test(id, geom) VALUES (6, ST_GeomFromEWKT('SRID=4326;POLYGON((-120 59,-110 65,-110 70,-120 59))'));

-- Rebuild the pyramid; the old pyramid is dropped automatically
SELECT ST_BuildPyramid('test', 'geom', 'id', '{"sourceSRS":4326}');

Drop the pyramid and extension

-- Drop the pyramid
SELECT ST_DeletePyramid('test');

-- Drop the extension
DROP EXTENSION ganos_geometry_pyramid CASCADE;

Name a pyramid

By default, a pyramid takes the name of its table. Specify a custom name to create multiple pyramids on the same table:

-- Create a pyramid named "hello" on the "test" table
SELECT ST_BuildPyramid('test', 'geom', 'id', '{"name": "hello"}');

Build with parallel tasks

Parallel tasks speed up pyramid creation for large datasets. The default value of 0 uses the maximum allowed parallelism.

-- Build using 4 parallel tasks
SELECT ST_BuildPyramid('test', 'geom', 'id', '{"parallel": 4}');

Constraints:

• The parallel count must not exceed four times the number of configured cores.

• Parallel builds use the two-phase commit mechanism. Set max_prepared_transactions to 100 or higher in the PostgreSQL configuration, then restart the cluster.

Set tile parameters

-- Set tile size to 512 and tile extension to 8
SELECT ST_BuildPyramid('test', 'geom', 'id', '{
  "tileSize": 512,
  "tileExtend": 8
}');

Set the maximum pyramid level

The maximum level controls how many zoom levels are pre-built. Beyond this level, tiles are generated dynamically. If not set, the system calculates a maximum based on data density; the default maximum is 16.

-- Pre-build pyramid levels 0–12; generate levels above 12 dynamically
SELECT ST_BuildPyramid('test', 'geom', 'id', '{"maxLevel": 12}');

Apply level-based filtering with buildRules

Use buildRules to control which features appear at each zoom level and which attributes are stored in the tile. This reduces tile size at coarser zoom levels and avoids generating tiles for the top pyramid level, which can be time-consuming.

-- Level 0–5: empty tiles (filter "1!=1" is always false)
-- Level 6–9: features where code=1, with the "name" attribute
-- Level 10–15: all features, with "name" and "width" attributes
SELECT ST_BuildPyramid('test', 'geom', 'id', '{
  "buildRules":[
    {
      "level":[0,1,2,3,4,5],
      "value": {
        "filter": "1!=1"
      }
    },
    {
      "level":[6,7,8,9],
      "value": {
        "filter": "code=1",
        "attrFields": ["name"]
      }
    },
    {
      "level":[10,11,12,13,14,15],
      "value": {
        "attrFields": ["name", "width"]
      }
    }
  ]
}');

Merge features

Use merge within buildRules to combine adjacent features that share the same attribute value. This reduces the total feature count at coarser zoom levels.

-- Merge features where code=1 or code=2 at levels 0–5
SELECT ST_BuildPyramid('test', 'geom', 'id', '{
  "buildRules":[
    {
      "level":[0,1,2,3,4,5],
      "value": {
        "merge": ["code=1","code=2"]
      }
    }
  ]
}');

Use ST_BuildPyramidUseGeomSideLen for faster builds and updates

ST_BuildPyramidUseGeomSideLen is more efficient than ST_BuildPyramid for both creation and incremental updates. It requires an additional column that stores the maximum bounding-box span of each geometry (along either axis), with a B-tree index on that column.

-- Step 1: Add a column to store the geometry's maximum bounding-box span
ALTER TABLE test
ADD COLUMN geom_side_len DOUBLE PRECISION;

-- Step 2: Populate the column
CREATE OR REPLACE FUNCTION add_max_len_values() RETURNS VOID AS $$
DECLARE
  t_curs CURSOR FOR
    SELECT * FROM test;
  t_row test%ROWTYPE;
  gm GEOMETRY;
  x_min DOUBLE PRECISION;
  x_max DOUBLE PRECISION;
  y_min DOUBLE PRECISION;
  y_max DOUBLE PRECISION;
BEGIN
  FOR t_row IN t_curs LOOP
    SELECT t_row.geom INTO gm;
    SELECT ST_XMin(gm) INTO x_min;
    SELECT ST_XMax(gm) INTO x_max;
    SELECT ST_YMin(gm) INTO y_min;
    SELECT ST_YMax(gm) INTO y_max;
    UPDATE test
      SET geom_side_len = GREATEST(x_max - x_min, y_max - y_min)
    WHERE CURRENT OF t_curs;
  END LOOP;
END;
$$ LANGUAGE plpgsql;

SELECT add_max_len_values();

-- Step 3: Create a B-tree index on the column
CREATE INDEX ON test USING btree(geom_side_len);

-- Step 4: Build the pyramid using the span column
SELECT ST_BuildPyramidUseGeomSideLen('roads', 'geom', 'geom_side_len', 'id',
  '{"sourceSRS":4326}');