PolarDB:Vector tile pyramid model

Overview

The vector tile pyramid model is designed for rapid visualization of tens of millions of spatial geometric data records. It creates sparse indexes on spatial geometric data, preprocesses data-intensive regions, and outputs vector tiles in the MVT or PBF format. The model is capable of preprocessing hundreds of millions of spatial geometric data records in minutes and visualizing them on the client side instantly.

GanosBase Geometry Pyramid is a spatial-temporal extension for PolarDB for PostgreSQL . It can create a vector tile pyramid on a table with a geometry column, which allows you to query tiles at different levels for rapid rendering. Compared to the traditional map tiling approach, the vector tile pyramid model is faster and more cost-effective.

• Faster pyramid creation: Experiments on an average 8-core PolarDB for PostgreSQL cluster show that it takes only six minutes to create a vector tile pyramid from 70 million building plane data points.

• Cost-effective storage: By storing only vector tiles that contain a large amount of data, the model reduces storage costs.

Features

You can create, query, update, and drop a vector tile pyramid.

• Create a vector tile pyramid: Use the ST_BuildPyramid or ST_BuildPyramidUseGeomSideLen function to create a vector tile pyramid on a table that contains the geometry column. A spatial index on the column is required.

• Query a vector tile pyramid: Use the ST_Tile function or the ST_AsPNG function. The ST_Tile function returns specified vector tiles in the MVT format, and the ST_AsPNG function renders tiles and returns PNG images.

• Drop a vector tile pyramid: Use the ST_DeletePyramid function to drop an existing vector tile pyramid.

• Update a vector tile pyramid: Use the ST_UpdatePyramid function to update a vector tile pyramid after updating the corresponding table.

For more information, see Geometry Pyramid SQL reference.

Scenarios

Geometry objects, representing real-world entities such as roads, buildings, or points of interest (POI), are crucial for visualizing the distribution of physical objects. This visualization effectively conveys the information within the geospatial data. The vector tile pyramid is useful for handling datasets that are stored as geometry objects and require efficient visualization. This section describes how the vector tile pyramid model can be used to visualize three types of geometry data: the point, line, and plane.

• Visualizing POI and trajectory points

  POI or trajectory points, which represent locations or moving objects on maps, are typically stored as the Point type in a database. Leveraging the vector tile pyramid, users can visualize the density of these points across different areas on the fly to assess their activity levels.

• Visualizing roads or waterways

  In transportation applications, roads and waterways are typically stored as the LineString type in the database. The vector tile pyramid model allows users to dynamically query the distribution of roads and waterways for efficient route planning:

  

• Visualizing buildings, rivers, and forests

  In urban planning, buildings, rivers, and forests are stored as the Polygon type in the database. The vector tile pyramid model allows users to visualize the distribution of buildings and rivers, helping them make informed decisions:

  

Concepts

Vector tiling

Vector tiling refers to generating vector tiles from vector data, such as vector types (the point, line, or plane) and relative coordinates of a point or points that make up the vector. The information contained in vector tiles is accessible to front-end software such as web browsers or GIS applications, which apply user-defined styles on the information to render the vector data. To put it simply, vector tiles informs a front-end application of what to display, while the front-end application displays it according to user-defined styles. As hardware advances, the client application is capable of efficiently rendering vector tiles, making vector tiles increasingly popular.

MVT

Mapbox Vector Tile (MVT) is a widely used format for storing and transferring vector tile data. It defines how to encode a set of vector features and their attributes. The internal structure of an MVT file includes a set of named layers. Each layer contains geometry features and metadata. Geometry features are encoded geometry types, coordinates, and commands like MoveTo, LineTo, and ClosePath. Metadata consists of key-value pairs that store attribute names and their corresponding values. The MVT format is supported by most popular front-end applications.

Dynamic vector tiling

Dynamic vector tiling is the process in which the database system retrieves relevant data, processes it into vector tiles, and returns those tiles, in response to a visualization request. For example, in PostGIS, dynamic vector tiling follows this process:

1. Retrieve the requested geometry objects from the table by performing a spatial query within the vector tile's extent.

2. Transform the retrieved geometry objects to the vector tile's coordinate system. This step also includes the simplification and filtering of geometry objects. It is performed using the ST_AsMVTGeom function.

3. Encode the transformed geometry objects into a binary structure according to the MVT specifications. This step is performed using the ST_AsMVT function.

GanosBase improves the rendering efficiency by offering the following functions:

• ST_IsRandomSampled: checks if a geometry object has been randomly sampled.

• ST_AsMVTGeomEx: removes transformed geometry objects with small pixels.

• ST_AsMVTEx: removes visually insignificant geometry objects after data visualization.

You can use any of those functions to speed up dynamic vector tiling.

Pre-tiling

Pre-tiling involves generating vector tiles in batch, storing them, and serving them to the client on demand.

Sparse pyramid

The vector tile pyramid model uses a sparse pyramid structure. Vector tiles for regions with low object density are dynamically generated, while vector tiles for regions with high object density are generated in batch and stored. This approach combines dynamic vector tiling and pre-tiling. The sparse pyramid structure is shown in the following figure. Vector tiles with two or more geometry objects are generated and stored. Upon request, they are directly delivered to the client. Grey tiles with one or none geometry object are generated on demand.

Rendering

Rendering refers to the process in which the front-end software creates an image based on the information in the received vector tile data. Traditionally, the rendering is completed by the front-end software. With the vector tile pyramid model, the rendering can also occur at the database end: The database renders the map and sends the resulting image to the front-end software for display.

Process of using the vector tile pyramid

You need to first create a vector tile pyramid before you can perform queries on it. If the data table is updated, you need to update the vector tile pyramid before performing queries.

Comparing the vector tile pyramid to dynamic vector tiling

This section compares the vector tile pyramid model and dynamic vector tiling. Dynamic vector tiling requires not as much preprocessing as the vector tile pyramid, but introduces additional overheads of retrieving geometry data within the tile extent and generating vector tiles on demand. The update of the table does not affect dynamic vector tiling but requires the vector tile pyramid to be updated using the ST_UpdatePyramid function. We recommend choosing dynamic vector tiling over the pyramid vector model if its performance meets your demand. Otherwise, you can use the vector tile pyramid instead.

Overview

This section describes how to use the GanosBase Geometry Pyramid extension, such as installing the extension, creating a vector tile pyramid, querying vector tile data, updating a vector tile pyramid, and using advanced functionality of this extension.

Basic usage

• Install the extension:

  CREATE EXTENSION ganos_geometry_pyramid CASCADE;

  Note

  Install the extension into the public schema to avoid potential permission issues.

  CREATE extension ganos_geometry_pyramid WITH schema public;

• Create a vector tile pyramid for a table:

  -- Create a vector tile pyramid for the table named test
  -- Specify the id column to be the int4 or int8 data type
  -- Specify the name of the geometry column in the test table. You must first create a spatial index on the column
  -- The JSON string specifies the coordinate system for the data in the geometry column to be EPSG:4326
  SELECT ST_BuildPyramid('test', 'geom', 'id', '{"sourceSRS":4326}');

• Query vector tile data:

  -- Query data from the tile with ID 0_0_0. You can query tiles using any ID. The system returns the tile if it exists. If the specified tile does not exist, a null response is returned.
  -- Query a vector tile using its ID in the format z_x_y. The default projection coordinate system is EPSG:3857.
  SELECT ST_Tile('test', '0_0_0');

• Update the vector tile pyramid:

  After the table is updated, you need to update the vector tile pyramid to view the latest map. Invoke the ST_UpdatePyramid function to pass in the new data:

  -- Insert three geometry data records
  INSERT INTO test(id, geom) VALUES (1, ST_GeomFromEWKT('SRID=4326;POINT(10.1 10.1)'));
  INSERT INTO test(id, geom) VALUES (2, ST_GeomFromEWKT('SRID=4326;LINESTRING(10.1 10.1,11 11)'));
  INSERT INTO test(id, geom) VALUES (3, ST_GeomFromEWKT('SRID=4326;POLYGON((10 10,11 11,11 12,10 10))'));
  
  -- Update the vector tile pyramid, using the extent defined by Box2D.
  SELECT ST_UpdatePyramid('test', 'geom', 'id', ST_SetSRID(ST_MakeBox2D(ST_Point(9, 9), ST_Point(12, 12)), 4326));

  For localized updates affecting a small spatial extent (such as less than 1% of the total area), use the ST_UpdatePyramid function to update the vector tile pyramid. When updates affect a large part of the whole geospatial region, it is more efficient to create a new vector tile pyramid using the ST_BuildPyramid function.

  -- Insert three new geometry records affecting different areas
  INSERT INTO test(id, geom) VALUES (4, ST_GeomFromEWKT('SRID=4326;POINT(-59 -45)'));
  INSERT INTO test(id, geom) VALUES (5, ST_GeomFromEWKT('SRID=4326;LINESTRING(110 60,115 70)'));
  INSERT INTO test(id, geom) VALUES (6, ST_GeomFromEWKT('SRID=4326;POLYGON((-120 59,-110 65,-110 70,-120 59))'));
  
  -- Build a new vector tile pyramid. The old one is automatically dropped.
  SELECT ST_BuildPyramid('test', 'geom', 'id', '{"sourceSRS":4326}');

• (Optional) Drop the pyramid:

  SELECT ST_DeletePyramid('test');

• (Optional) Drop the extension:

  DROP EXTENSION ganos_geometry_pyramid CASCADE;

Advanced usage

Name a pyramid

The vector tile pyramid shares the name of the table by default. You can specify a custom name for a vector tile pyramid if you want to create multiple vector tile pyramids for the table.

-- Create a vector tile pyramid named hello for the data table named test
SELECT ST_BuildPyramid('test', 'geom', 'id', '{"name": "hello"}');

Build a vector tile pyramid using parallel tasks

• You can specify the number of tasks that can run in parallel to create a vector tile pyramid. The default value is 0, which indicates the maximum allowable count.

• The value should not exceed four times the number of configured cores.

• As the two-phase commit mechanism is used to create a vector tile pyramid in parallel, you need to set the max_prepared_transactions parameter.

• Set max_prepared_transaction to 100 or higher. It becomes effective after restart.

-- Create a vector tile pyramid using four parallel tasks
SELECT ST_BuildPyramid('test', 'geom', 'id','{"parallel": 4}');

Set tile parameters

You can specify the tile size and the tile extension size. The tile size must be divisible by 256, with a maximum of 4096. The tile extension size ranges from 0 to 256.

When visualizing a full map from a massive volume of data, setting a relatively small tile size can increase the parallelism and efficiency of rendering.

-- Set the tile size to 512 and the tile extension size to 8
SELECT ST_BuildPyramid('test', 'geom', 'id','{
                        "tileSize": 512,
                        "tileExtend": 8
                      }');

Set the maximum level of a vector tile pyramid

When the map zoom exceeds the vector tile pyramid's maximum level, the system stops using lower layers or generating new pyramid levels. If you do not set this parameter, the system provides a maximum number based on data density. The default maximum level is 16.

-- Set the pyramid's maximum level to 12. When the zoom level exceeds 12, vector tiles are generated in real time.
SELECT ST_BuildPyramid('test', 'geom', 'id', '{"maxLevel": 12}');

Layered processing

• You can configure rules for filtering and attribute selection at each pyramid level.

• Use buildRules to specify rules at each level.

• It takes a long time to generate the top pyramid level. You can configure the rule to skip its generation.

-- Use layered processing
-- Level 0-5: Display no features by configuring the filter condition "1!=1", which is always false, thus vector tiles are empty.
-- Level 6-9: Display features with "code = 1" and store the name attribute in the tiles.
-- Level 10-15: Display all features and store the name and width attributes in the tiles.
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"]
                                }
                            }
                        ]
                       }');

Increase the efficiency of creating and updating a vector tile pyramid

In addition to ST_BuildPyramid, GanosBase offers ST_BuildPyramidUseGeomSideLen to create a vector tile pyramid. ST_BuildPyramidUseGeomSideLen is more efficient in creating and updating a vector tile pyramid. To use this function, the table must contain a column that stores the maximum span of the geometry along either the x-axis or y-axis, with an index on that column.

-- Add the geom_side_len column to the test table to store the maximum span of the geometry's bounding box
ALTER TABLE test
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 test;
  t_row test%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 test
      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 geom_side_len column
CREATE INDEX ON test USING btree(geom_side_len);

-- Specify the geom_side_len column to store the maximum span of the geometry's bounding box
SELECT ST_BuildPyramidUseGeomSideLen('roads', 'geom', 'geom_side_len', 'id',
  '{"sourceSRS":4326}');

Advanced functionality

Merge features

Features within a specific tile can be merged if their attributes meet certain conditions. This reduces the total number of features.

The following SQL statement merges features by adding the conditions ["code=1","code=2"] in "buildRules" > "value" > "merge".

This directs the database to merge features where the code attribute is equal to 1 or 2.

Other features are left intact.

-- Merge features that have a code attribute value of 1 or 2.
SELECT ST_BuildPyramid('test', 'geom', 'id', '{
                        "buildRules":[
                          {
                            "level":[0,1,2,3,4,5],
                            "value": {
                              "merge": ["code=1","code=2"]
                            }
                          }
                        ]
                      }');

SQL reference

For more information, see Geometry Pyramid SQL reference.