PolarDB:ST_UpdatePyramid - PolarDB

Partially updates a vector pyramid within a specified bounding box extent. Call this function after inserting, deleting, or updating rows in the source vector table so that the pyramid reflects the latest data.

Syntax

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

Parameters

Parameter	Type	Description
`table`	cstring	The name of the vector table.
`geom_field`	cstring	The name of the geometry column in the vector table.
`id_field`	cstring	The name of the ID column in the vector table.
`update_extent`	BOX2D	The coordinate range to update, in Box2D format. The coordinate system is specified by `sourceSRS` in the `rules` parameter. For example, `Box2D(ST_GeomFromText('LINESTRING(-120 -80, -100 -50)'))` defines the range `-120 < longitude < -100, -80 < latitude < -50`.
`rules`	cstring	A JSON string that controls the update behavior. Contains two fields: `updateBoxScale` and `sourceSRS`.

The `rules` parameter

Field	Description	Default
`updateBoxScale`	The scale factor that determines the uppermost pyramid layer to update. The function updates only those layers where the maximum width of the layer tiles divided by the maximum width of `update_extent` is less than this value. A smaller value updates fewer tiles but may leave some tiles stale; a larger value updates more tiles and ensures broader coverage. Setting `updateBoxScale` to `100000` with `maxLevel` at `16` propagates the update from the bottom layer all the way to the root node.	`10`
`sourceSRS`	The EPSG coordinate system code for the `update_extent` coordinates.	`4326`

updateBoxScale balances update accuracy against performance. Tune this value based on how much of the pyramid needs to reflect changes after each data modification.

Return value

Returns true if the update succeeds.

Example

The following example creates a line geometry table, builds a vector pyramid, inserts additional rows, then updates the pyramid for the affected extent.

-- Create the table and populate initial data
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 pyramid
SELECT ST_BuildPyramid('test_update_line', 'geom', 'id', '{"parallel":16}');
st_buildpyramid
-----------------
t
(1 row)

-- Insert new rows that fall in a different coordinate range
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;

-- Update the pyramid for the extent that covers the new rows
SELECT ST_UpdatePyramid('test_update_line', 'geom', 'id', Box2D(ST_GeomFromText('LINESTRING(-20 -20, 20 20)')), '{"updateBoxScale":10}');
st_updatepyramid
-----------------
t
(1 row)

All examples use Box2D(ST_GeomFromText(...)) to define the update extent and pass updateBoxScale as a JSON string in the rules parameter.

Syntax

Parameters

The rules parameter

Return value

Example

The `rules` parameter