Builds a pyramid for a vector table that has a column storing the maximum side length of each geometry's bounding box, with an index (such as a B-tree index) on that column. Compared with ST_BuildPyramid, this function significantly improves pyramid build and update performance.
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 data table. |
geom_field | The name of the geometry column. |
geom_side_len_field | The name of the column that stores the maximum side length of each geometry's bounding box. The geometries are stored in the column specified by geom_field. |
fid | The name of the feature ID column. |
config | The pyramid build configuration. The definition is the same as in ST_BuildPyramid. |
Examples
The following example adds a geom_side_len column to the roads table, populates it with the maximum bounding box side length for each geometry, creates a B-tree index on the column, and then builds a pyramid.
-- Add a column to store the maximum bounding box side length
ALTER TABLE roads
ADD COLUMN geom_side_len DOUBLE PRECISION;
-- Populate geom_side_len with GREATEST(width, height) of each geometry's bounding box
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 a B-tree index on the side length column to improve pyramid build performance
CREATE INDEX ON roads USING btree(geom_side_len);
-- Build the pyramid using 4 parallel workers
SELECT ST_BuildPyramidUseGeomSideLen('roads', 'geom', 'geom_side_len', 'id',
'{"parallel":4}');