Optimize Spatial Pyramid Indexing with ST_BuildPyramidUseGeomSideLen - PolarDB

ST_BuildPyramidUseGeomSideLen builds a pyramid for a vector table that has a column storing the maximum side length of each geometry's bounding box, with a B-tree index on that column. Compared with ST_BuildPyramid, this function significantly improves pyramid build and update performance.

Use this function only when the vector table already has a geom_side_len column (containing the maximum bounding box side length for each geometry) with a B-tree index.

Syntax

boolean ST_BuildPyramidUseGeomSideLen(cstring table, cstring geom_field, cstring geom_side_len_field, cstring fid, cstring config);

Parameters

Parameter	Description
`table`	The name of the spatial geometry data table.
`geom_field`	The name of the geometry field.
`geom_side_len_field`	The name of the field that stores the maximum side length of the bounding box for each geometry in `geom_field`.
`fid`	The name of the element ID field.
`config`	A JSON string that configures the pyramid. The definition of the `config` parameter is the same as that in ST_BuildPyramid.

Examples

Step 1: Prepare the bounding box side length column

If the table does not have a geom_side_len column yet, add the column, populate it, and create a B-tree index.

-- Add the column
ALTER TABLE roads
ADD COLUMN geom_side_len DOUBLE PRECISION;

-- Populate the column with the maximum bounding box side length for each geometry
CREATE OR REPLACE FUNCTION add_max_len_values() RETURNS VOID AS $$
DECLARE
  t_curs CURSOR FOR
    SELECT * FROM roads;
  t_row test_polygon_geom_side_len%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 roads
      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();

-- Create the B-tree index
CREATE INDEX ON roads USING btree(geom_side_len);

If the geom_side_len column and index already exist, skip to step 2.

Step 2: Build the pyramid

SELECT ST_BuildPyramidUseGeomSideLen('roads', 'geom', 'geom_side_len', 'id',
  '{"parallel":4}');

This example builds a pyramid for the roads table using 4 parallel tasks.