This topic describes the ST_BuildPyramidUseGeomSideLen function. This function builds a pyramid if the original vector table has a column that records the length of the bounding box of vector data, and an index is created for this column. For example, a B-tree index is created. Compared with the ST_BuildPyramid function, the ST_BuildPyramidUseGeomSideLen function significantly improves the efficiency to build and update pyramids.

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 for the column that records the maximum side lengths of the bounding boxes of the geometries. The geometries are recorded in another column and specified by the geom_field parameter.
fid The name of the element ID field.
config The parameter that you use to build the pyramid.

The definition of the config parameter is the same as that in ST_BuildPyramid.

Examples

ALTER TABLE roads
ADD COLUMN geom_side_len DOUBLE PRECISION;
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 INDEX ON roads USING btree(geom_side_len);

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