ST_UpdatePyramid - - Alibaba Cloud Documentation Center

This topic describes the ST_UpdatePyramid function. This function updates some vector pyramids. The update range is specified by the update_extent parameter. The value of this parameter is labeled by Box2D. You can call this function when the original vector table is modified. For example, if data is added to or deleted from the table, you can call the ST_UpdatePyramid function by passing the update range of the coordinates to the update_extent parameter. This way, you can view the update result.

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 column that has the Geometry attribute in the vector table.
id_field	The name of the column that has the ID attribute in the vector table.
update_extent	The range of the coordinates that you want to update. The range is in the Box2D format. The coordinates in the EPSG format are specified by the rules parameter. Default value: 4326. For example, Box2D(ST_GeomFromText('LINESTRING(-120 -80, -100 -50)')) specifies the update range of `-120 < Longitude < -100, -80 < Latitude < -50`.
rules	The rules that are used to update some vector pyramids. The value of this parameter is in the JSON format and consists of the updateBoxScale and sourceSRS fields. The updateBoxScale field specifies the uppermost layer to which the updates are passed. For example, if updateBoxScale is set to 2 and the maximum width of the specified update range is 100, only the level-1 tiles of the vector pyramid are updated. The reason is that the maximum width of the level-1 tiles divided by the 100 is less than 2, and the maximum width of the level-0 tiles divided by 100 is greater than 2. In this example, the global range includes the global longitude and latitude. The default value of the updateBoxScale field is 10. The sourceSRS field specifies the value of the update_extent parameter in the EPSG format. Default value: 4326. Note The UpdateBoxScale field is used to balance the accuracy and efficiency of updates. When updateBoxScale is set to a small value, a small number of tiles are updated, but some tiles cannot reflect the result of the update. When updateBoxScale is set to a large value, a large number of tiles are updated, but the update result is reflected by more tiles. For example, if updateBoxScale is set to 100000 and maxLevel is 16, the update is passed from the bottom to the root node.

Examples

-- Delete the table that has the same name as other tables.
DROP TABLE IF EXISTS test_update_line;

-- Create a test table.
CREATE TABLE test_update_line(id int primary key, geom geometry(LineString, 4326));

-- Insert data into the test table.
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 an index for the geom column.
CREATE INDEX ON test_update_line using gist(geom);

-- Create a vector pyramid to accelerate the display of data. In addition, if the Raster extension is installed, you must add ::cstring to each parameter to avoid conflicts. 
SELECT ST_BuildPyramid('test_update_line', 'geom', 'id', '{"parallel":16}');

-- Insert test data.
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 partial data of the vector pyramid.
SELECT ST_UpdatePyramid('test_update_line', 'geom', 'id', Box2D(ST_GeomFromText('LINESTRING(-20 -20, 20 20)')), '{"updateBoxScale":10}';