Use ST_BuildPyramid to create a vector pyramid - PolarDB

ST_BuildPyramid builds a vector pyramid for a spatial geometry data table. The pyramid pre-tiles your vector data into Mapbox Vector Tiles (MVT) across multiple zoom levels so that map rendering stays fast regardless of zoom level or dataset size.

Syntax

boolean ST_BuildPyramid(cstring table, cstring geom, cstring fid, cstring config)

Parameters

Parameter	Description
`table`	The name of the spatial geometry data table.
`geom`	The name of the geometry field.
`fid`	The name of the element ID field.
`config`	A JSON string that controls how the pyramid is built. Pass an empty string (`''`) to use all default values.

Config reference

The config parameter accepts the following fields as a JSON string. All fields are optional — omitting a field applies its default value.

Field	Type	Default	Constraints	Description
`name`	string	Same as table name	—	The name of the pyramid.
`parallel`	int	`0`	—	The number of tasks that build the pyramid concurrently. Set to `0` to remove the limit. When using this field, also set the PostgreSQL `max_prepared_transactions` parameter.
`tileSize`	int	`1024`	1–4095	The width and height of each tile in pixels.
`tileExtend`	int	`8`	> 0	The tile buffer size in pixels. A buffer extends each tile slightly beyond its boundary so that features near tile edges render without gaps.
`userExtent`	array[double]	`null`	—	The geographic extent to index, as `[minx, miny, maxx, maxy]`. Pass `[]` to leave unset.
`splitSize`	int	`10000`	—	The maximum number of elements per index node before the node splits. A higher value produces a sparser pyramid.
`maxLevel`	int	`16`	0–20	The highest zoom level to build.
`sourceSRS`	int	`-1`	—	The Spatial Reference System (SRS) EPSG code of the source data. If `-1`, the system reads the Spatial Reference Identifier (SRID) from the table metadata.
`destSRS`	int	`3857`	`3857` or `4326`	The EPSG code of the output tile coordinate system. EPSG 3857 (Web Mercator) is the standard for web maps. Use EPSG 4326 (WGS 84) for geographic coordinate output.
`buildRules`	array[object]	`null`	—	Per-level rules that control filtering, attribute encoding, and feature merging. Each rule applies to a set of zoom levels. See Build rules for the rule structure.

Build rules

Each object in buildRules must contain the following fields:

Field	Type	Description
`level`	array[int]	The zoom levels to which this rule applies.
`value`	object	The rule settings. Contains the sub-fields below.
`value.filter`	string	The expression that is used to filter data in PostgreSQL.
`value.attrFields`	array[string]	The name of the attribute field when the MVT format is used.
`value.merge`	array[string]	The filter conditions that are used to add data records to a group.

Examples

Quick start

Pass an empty string as config to build a pyramid with default settings:

-- Build a pyramid for the spatial geometry data table "roads"
SELECT ST_BuildPyramid('roads', 'geom', 'id', '');

Expected output:

 st_buildpyramid
-----------------
 t

A return value of t (true) indicates the pyramid was built successfully.

Build with a custom config

The following example builds a pyramid named hello with parallel construction, a custom tile size, a global geographic extent, and per-level build rules:

SELECT ST_BuildPyramid('roads', 'geom', 'id', '{
  "name": "hello",
  "parallel": 4,
  "tileSize": 512,
  "tileExtend": 8,
  "userExtent": [-180, -90, 180, 90],
  "splitSize": 5000,
  "maxLevel": 16,
  "destSRS": 3857,
  "buildRules": [
    {
      "level": [0, 1, 2],
      "value": {
        "filter": "code != 0",
        "attrFields": ["name", "color"],
        "merge": ["code=1"]
      }
    }
  ]
}');

Config field choices explained:

`parallel: 4` — Speeds up pyramid construction by running up to 4 tasks concurrently. Requires max_prepared_transactions to be set in PostgreSQL.
`tileSize: 512` — Reduces tile size from the default 1024 px to 512 px, which lowers bandwidth for web map clients.
`userExtent: [-180, -90, 180, 90]` — Indexes the entire globe. Replace with a tighter bounding box to skip regions with no data.
`splitSize: 5000` — Produces a denser pyramid than the default (10000), which improves rendering quality for datasets with high feature density.
`buildRules` — Applies a rule to zoom levels 0–2 (world and continent view):
- filter: "code != 0" — Excludes features where code equals 0, removing noise at low zoom levels.
- attrFields: ["name", "color"] — Only name and color are encoded into tiles at these levels, reducing tile size.
- merge: ["code=1"] — Merges features that share code=1 into a single record.

Usage notes

To use parallel construction (parallel > 0), set the PostgreSQL max_prepared_transactions parameter before calling the function.
destSRS supports only EPSG 3857 and EPSG 4326. EPSG 3857 is the default and is compatible with most web mapping frameworks.
The tileExtend field sets the tile buffer size.