Update vector pyramids with ST_UpdatePyramid - PolarDB

Refreshes the tiles within a specified bounding box of a pre-built vector pyramid after the source vector table is modified (rows inserted, updated, or deleted).

Syntax

boolean ST_UpdatePyramid(cstring table, cstring geom_field, cstring id_field, BOX2D update_extent, cstring rules);

Parameters

Parameter	Description
`table`	The name of the vector table.
`geom_field`	The name of the geometry column in the vector table.
`id_field`	The name of the ID column in the vector table.
`update_extent`	The coordinate range to refresh, specified as a Box2D bounding box. The coordinate reference system is determined by the `sourceSRS` field in `rules`. Default: EPSG 4326. Example: `Box2D(ST_GeomFromText('LINESTRING(-120 -80, -100 -50)'))` covers the range `-120 < longitude < -100, -80 < latitude < -50`. Note that `ST_GeomFromText` with a `LINESTRING` is a common way to construct a Box2D from two corner coordinates.
`rules`	A JSON string that controls how the pyramid is updated. Accepts the `updateBoxScale` and `sourceSRS` fields.

Rules parameter fields

Field	Description	Default
`updateBoxScale`	Controls how far up the pyramid hierarchy the update propagates. The update stops at the level where the tile width divided by the `update_extent` width first exceeds this value. Set to `100000` with `maxLevel=16` to propagate the update from leaf nodes all the way to the root.	`10`
`sourceSRS`	The EPSG code of the coordinate reference system used by `update_extent`.	`4326`

Note: The updateBoxScale field is used to balance the accuracy and efficiency of updates.

Set a smaller value when the changed area is compact. Fewer tiles are refreshed, but some higher-level tiles will not reflect the update.

Set a larger value when data changes span a wide area and the update must propagate to higher pyramid levels. Setting updateBoxScale=100000 with maxLevel=16 propagates the update from the bottom level to the root.

Example

The following example creates a line table, builds an initial vector pyramid, inserts additional rows covering a new geographic region, and then refreshes only the pyramid tiles that overlap the newly inserted rows.

-- Create the table and populate it with 10,000 line features
DROP TABLE IF EXISTS test_update_line;
CREATE TABLE test_update_line(id int primary key, geom geometry(LineString, 4326));

INSERT INTO test_update_line(id, geom)
SELECT i,
    ST_GeomFromText(format('LINESTRING(%s,%s,%s,%s)',
                        (floor(i/100)*0.01+10), (floor(i%100)*0.01+10),
                        (floor(i/100+1)*0.01+10), (floor(i%100+1)*0.01+10)), 4326)
FROM generate_series(1, 10000) i;

CREATE INDEX ON test_update_line USING gist(geom);

-- Build the initial vector pyramid
SELECT ST_BuildPyramid('test_update_line', 'geom', 'id', '{"parallel":16}');
-- Returns: t

-- Insert 5,000 additional rows that extend into a new region (-10 to +10 degrees)
INSERT INTO test_update_line(id, geom)
SELECT i,
    ST_GeomFromText(
        format('LINESTRING(%s,%s,%s,%s)',
              (floor(i/100)*0.01-10), (floor(i%100)*0.01-10),
              (floor(i/100+1)*0.01+10), (floor(i%100+1)*0.01+10)),
        4326)
FROM generate_series(10001, 15000) i;

-- Refresh the pyramid tiles that cover the newly inserted rows.
-- The Box2D bounding box must enclose the coordinate range of the new data (-20 to +20 degrees).
SELECT ST_UpdatePyramid('test_update_line', 'geom', 'id',
    Box2D(ST_GeomFromText('LINESTRING(-20 -20, 20 20)')),
    '{"updateBoxScale":10}');
-- Returns: t