Vector pyramids are structures that are designed to display tens of millions of spatial geometry data records.

Overview

Vector pyramids allow you to create sparse indexes on spatial geometry data records and to preprocess data-intensive areas based on the specified rules. This way, you can obtain standard data records in the MVT and PBF formats.

Vector pyramids provided by Ganos allow you to preprocess hundreds of millions of spatial geometry data records in minutes and to display the preprocessing results in seconds.

Quick start

  • Create an extension.
    CREATE EXTENSION ganos_geometry_pyramid CASCADE;
  • Create test data.
    CREATE TABLE test(id int primary key, geom geometry(Point,4326), code int, name text, width float);
    INSERT INTO test(id, geom)
        SELECT  i,
                ST_GeomFromText(
                  format('POINT(%s %s)', floor(i / 100) * 0.01, (i % 100) * 0.01),
                  4326)
        FROM generate_series(1,10000) i;
    CREATE INDEX ON test USING gist(geom);
  • Create a pyramid for a spatial table.
    --Create a pyramid for the data table named test.  
    --Specify the name of the spatial fields in the test table. Before you specify the name of a spatial field, you must create a spatial index for the field. 
    SELECT ST_BuildPyramid('test', 'geom', 'id', '');
  • Read MVT-formatted data records from the pyramid that you created.
    --Read the data record with the tile ID 0_0_0 from the pyramid. You can specify any tile ID. The system returns a data record regardless of whether the specified tile ID can be found in the pyramid. 
    --Make sure that the specified tile ID follows the z_x_y format and the EPSG:3857 coordinate system. 
    SELECT ST_Tile('test', '0_0_0');
  • Delete the pyramid that you created.
    --Delete the pyramid named test. 
    SELECT ST_DeletePyramid('test');
  • Delete the extension that you created.
    DROP EXTENSION ganos_geometry_pyramid CASCADE;

Parameter settings

  • Pyramid name

    The default name of a pyramid is the same as the data table to which the pyramid belongs. You can change the default name to map one data table to multiple pyramids.

    --Create a pyramid named hello for the data table named test. 
    SELECT ST_BuildPyramid('test', 'geom', 'id', '{"name": "hello"}');
  • Number of parallel tasks

    You can specify the maximum number of tasks that can run in parallel to create pyramids. The default value is 0. The value 0 indicates that the number of parallel tasks is not limited. The value cannot exceed four times the number of cores that are configured.

    Pyramids are created in parallel in compliance with the two-phase commit (2PC) protocol. You must set the max_prepared_transactions parameter to a value that is greater than or equal to 100. After you set the parameter, you must restart your PolarDB cluster to make the new parameter setting take effect.

    --Configure four cores to create pyramids in parallel. 
    SELECT ST_BuildPyramid('test', 'geom', 'id','{"parallel": 4}');
  • Tile parameters

    You can specify the tile size and the tile extension size.

    • Valid tile sizes are multiples of 256 within the range of 0 to 4096.
    • Valid tile extension sizes are within the range of 0 to 256.
    --Set the tile size to 512 and the tile extension size to 8. 
    SELECT ST_BuildPyramid('test', 'geom', 'id','{
                            "tileSize": 512,
                            "tileExtend": 8
                           }');
  • Maximum number of pyramid layers

    You can specify the maximum number of layers in a pyramid. The default value is 16. If the number of zoom levels is greater than the maximum number of pyramid layers, the layers beyond the maximum number are no longer included in the pyramid. If you do not set this parameter, the system provides a maximum number based on data density.

    --Set the maximum number of pyramid layers to 12. If the number of zoom levels exceeds 12, the system reads data in real time to generate data records that are in the MVT format. 
    SELECT ST_BuildPyramid('test', 'geom', 'id', '{"maxLevel": 12}');
  • Layered processing

    You can specify unique criteria that are used to process each layer of a pyramid. The criteria include the fields that you want to display and the filter conditions.

    You can specify the criteria by using the buildRules element.

    The system requires a long period of time to generate the top layer of a pyramid. You can use layered processing rules to skip the top layer.

    --Enable layered processing.
    --Specify only the "1!=1" filter condition for Layers 0 through 5.The system generates empty data records in the MVT format and does not display the data of Layers 0 through 5. 
    --Specify the name field that you want to display and the "code = 1" filter condition for Layers 6 through 9. 
    --Specify only the name and width fields that you want to display for Layers 10 through 15. 
    SELECT ST_BuildPyramid('test', 'geom', 'id', '{
                            "buildRules":[
                              {
                                "level":[0,1,2,3,4,5],
                                "value": {
                                  "filter": "1 != 1"
                                }
                              },
                              {
                                "level":[6,7,8,9],
                                "value": {
                                  "filter": "code = 1",
                                  "attrFields": ["name"]
                                }
                              },
                              {
                                "level":[10,11,12,13,14,15],
                                "value": {
                                  "attrFields": ["name", "width"]
                                }
                              }                       
                            ]
                           }');

Advanced features

You can merge the data records within a specific tile based on attributes to reduce the total number of data records.
--Separately merge the data records whose codes are 1 and those whose codes are 2.
SELECT ST_BuildPyramid('test', 'geom', 'id', '{
                        "buildRules":[
                               {
                                   "level":[0,1,2,3,4,5],
                                   "value": {
                                       "merge": ["code=1","code=2"]
                                   }
                               }
                           ]
                       }');