PolarDB:Fast GanosBase vector rendering: fast 2D and 3D vector rendering

Fast 2D vector rendering

Traditional systems rely on offline tiling to support vector data visualization. When a visualization request is received, pre-built tiles are processed and returned to the client. However, this scheme has two significant drawbacks. First, it is slow. Running offline tiling on large-scale datasets typically takes several hours or even days to complete. Second, it is resource-intensive. In map services that support up to 16 zoom levels, tens of billions of tiles need to be pre-stored, resulting in massive storage overhead. Additionally, the "slow" issue also leads to unfriendly data updates. This issue is common in GIS systems with weak data management capabilities. Due to a lack of awareness of data update content and its impact on tile ranges, even a small local update requires full tile rebuilding, which is time-consuming and laborious.

The GanosBase fast rendering engine provides the fast 2D vector rendering feature, which specializes in visualizing massive 2D vector data, including points, lines, and polygons. This feature effectively solves the preceding issues. GanosBase innovatively introduces a sparse pyramid indexing method that skips building tiles for sparse data regions. It also combines database query optimization with a visibility filtering algorithm to filter out data that does not affect the display effect, resolving the two major pain points of prolonged slicing time and massive storage overhead. Moreover, GanosBase supports dynamic updates of sparse pyramids. When vector data undergoes small local updates, GanosBase can automatically identify the affected tiles and limit updates to the smallest possible range, eliminating the need to rebuild the entire sparse pyramid and significantly improving update efficiency. Testing showed that constructing a sparse pyramid for a dataset with 70 million housing records takes only 6 minutes in a standard 8-core PolarDB for PostgreSQL cluster. Furthermore, when over 1 million records are updated, the sparse pyramid can be updated in less than 1 minute. The average time to return a tile in response to a visualization request is under 1 millisecond. With such high performance, the required disk storage space is only approximately 3 GB.

3D vector visualization

GanosBase supports fast rendering of 2D vectors and has collaborated with Alibaba Cloud's DataV team to support 3D vector data visualization. By extending the existing 2D vector tiling standard, GanosBase and DataV jointly developed 3D vector tiling for visualizing 3D vector objects, as shown in the following image:

Fast 2D vector rendering

Prepare the data table

Prepare a data table with a Geometry attribute column. GanosBase provides various functions, including ST_GeomFromText, ST_GeomFromWKT, and ST_GeomFromWKB, for writing vector data into the data table. To use the vector fast rendering feature of GanosBase, make that the data table has a primary key ID column and a Geometry attribute column.

1. Before you use the fast rendering feature of GanosBase, install the ganos_geometry_pyramid fast rendering extension.

   CREATE EXTENSION ganos_geometry_pyramid CASCADE;

2. Create a data table that contains only a primary key ID column and a geom column.

   CREATE TABLE try_ganos_viz(id SERIAL NOT NULL, geom Geometry);

3. Use a script or other tools to import a vector dataset into the table. The script or tool must be able to generate an SQL statement similar to the following:

   INSERT INTO try_ganos_viz(geom) VALUES (ST_GeomFromText('WKT FORMAT TEXT', 4326));

   This SQL statement stores data in the 4326 coordinate system format, but you can specify other coordinate system formats.

4. After importing all data, build a spatial index on the Geometry attribute column.

   CREATE INDEX ON try_ganos_viz USING gist(geom);

Build a sparse pyramid

St_BuildPyramid

The sparse pyramid building feature is encapsulated in the ST_BuildPyramid function. For more information, see ST_BuildPyramid. The details are as follows:

• Create a sparse pyramid by using default parameters.

  SELECT ST_BuildPyramid('try_ganos_viz', 'geom', 'id', '');

• Specify the parallelism level when building the sparse pyramid. In this example, the pyramid is built with 32 parallel threads.

  SELECT ST_BuildPyramid('try_ganos_viz', 'geom', 'id', '{"parallel":32}');

• Specify the tile size and extend it when building the sparse pyramid.

  SELECT ST_BuildPyramid('try_ganos_viz', 'geom', 'id', '{"parallel":32, "tileSize":4096, "tileExtend":128}');

• You can use the maxLevel and splitSize parameters to control the sparse pyramid's structure based on performance and storage costs.

  • If you prioritize query performance over storage costs, you can specify a smaller splitSize value. This ensures that as many tiles as possible are pre-built before querying. When a tile contains fewer elements than the splitSize value, it is dynamically built during querying.

  • If you prioritize storage costs, you can specify a larger splitSize value to reduce the number of tiles that must be maintained. You can also specify a smaller maxLevel value to control the pyramid's height, which also reduces the number of tiles.

  In the following example, a sparse pyramid with a maxLevel value of 10 and a splitSize value of 1000 is constructed.

  SELECT ST_BuildPyramid('try_ganos_viz', 'geom', '{"maxLevel":10, "splitSize":1000}');

• The buildRules parameter is provided to flexibly control the sparse pyramid's construction rules. For example, you can use the following statement to specify that only features with an area greater than 100 can be visualized in tiles at levels 0 to 5:

  SELECT ST_BuildPyramid('try_ganos_viz', 'geom', '{"maxLevel":10, "buildRules":[
                         {"level":[0,1,2,3,4,5], "value":{"filter": "ST_Area(geom)>100"}}
  ]}');

  The filter condition in the SQL statement can be any valid SQL condition that can be used in a WHERE clause. This allows you to flexibly filter features during the sparse pyramid construction.

ST_BuildPyramidUseGeomSideLen

The ST_BuildPyramidUseGeomSideLen function is an optimized version of the ST_BuildPyramid function for building vector pyramids. It is designed to improve the performance of large datasets with many small-area features. For more information, see ST_BuildPyramidUseGeomSideLen.

Before you use the ST_BuildPyramidUseGeomSideLen function, create a real-number attribute column in the data table to record the larger value between ST_XMax(geom)-ST_XMin(geom) and ST_YMax(geom)-ST_YMin(geom) for each geometry. You must create an index on this new column. For example, you can add a max_side_len column to the try_ganos_viz table and create a B-tree index on the column.

ALTER TABLE try_ganos_viz
ADD COLUMN max_side_len DOUBLE PRECISION;

CREATE OR REPLACE FUNCTION add_max_len_values() RETURNS VOID AS $$
DECLARE
  t_curs CURSOR FOR
    SELECT * FROM try_ganos_viz;
  t_row usbf%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 try_ganos_viz
      SET max_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 try_ganos_viz USING btree(max_side_len);

When you call ST_BuildPyramidUseGeomSideLen, you must provide the name of the new column. The rest of the parameters are the same as those of ST_BuildPyramid. To build a sparse pyramid on the try_ganos_viz table with the added max_side_len column, execute the following statement:

SELECT ST_BuildPyramidUseGeomSideLen('try_ganos_viz', 'geom', 'max_side_len', 'id', '{"parallel":32}');

ST_BuildPyramidUseGeomSideLen also supports various configuration parameters, allowing you to flexibly adjust the sparse pyramid's construction rules.

Update a pyramid

When table data is updated, GanosBase provides the ST_UpdatePyramid function to update the pyramid. You only need to provide the bounding box range of the data update. For more information, see ST_UpdatePyramid.

• For example, you inserted multiple rows of data within the longitude and latitude range of[(lon1, lat1), (lon2, lat2)] and you want to update all affected tiles (assuming maxLevel=16). You can execute the following statement:

  SELECT ST_UpdatePyramid('try_ganos_viz', 'geom', 'id', ST_MakeEnvelope(0,-10,20,30, 4326), '{"updateBoxScale":100000}');

  In this SQL statement, assume that lon1=0, lat1=-10, lon2=20, and lat2=30.

• If you do not want to perform a full-scale update, you can specify a smaller updateBoxScale parameter value to avoid updating smaller-level tiles. Executing the following statement only updates tiles with a slightly larger range and their lower-level tiles:

  SELECT ST_UpdatePyramid('try_ganos_viz', 'geom', 'id', ST_MakeEnvelope(0,-10,20,30, 4326), '{"updateBoxScale":2}');

Get vector tiles

Vector tiles have the advantage of preserving feature information. Compared with traditional raster tiles, vector tiles can achieve more seamless zooming and provide better visual effects in map services. GanosBase provides the ST_Tile function to retrieve vector tiles in real-time. For more information, see ST_Tile.

Execute either of the following statements to get the tile that covers China at zoom level 1, x-coordinate 1, and y-coordinate 0.

SELECT ST_Tile('try_ganos_viz', '1_1_0');

SELECT ST_Tile('try_ganos_viz', 1, 0, 1);

Get raster tiles

GanosBase also supports raster tiles, which are still widely used today. Raster tiles are image-based tiles that, unlike vector tiles, do not support dynamic rendering on the client side. This makes them less demanding on client-side system performance. GanosBase provides the ST_AsPng function, which allows you to dynamically render vector data into raster tiles on the database side and return the result to the client. This function has basic raster symbolization capabilities and is suitable for lightweight scenarios that do not require complex symbolization. For more information, see ST_AsPng.

Execute the following statement to return a raster tile with the tile ID '1_1_0', which is rendered according to the provided rendering parameters and output in PNG format:

SELECT ST_AsPng('try_ganos_viz', '1_1_0', '{"point_size":5, "line_width":2, "line_color":"#003399FF", 
                "fill_color":"#6699CCCC", "background":"#FFFFFF00"}');

3D vector visualization

Prepare the data table

Prepare a data table that contains a Geometry3D attribute column. You can use functions such as ST_GeomFromText, ST_GeomFromWKT, and ST_GeomFromWKB to import data into the table.

1. Before you use the 3D vector visualization feature of GanosBase, install the ganos_geometry 3D vector visualization extension.

   CREATE EXTENSIION ganos_geometry CASCADE;

2. Create a data table that contains only the primary key id and the geom column.

   CREATE TABLE try_ganos_viz3d(id SERIAL NOT NULL, geom Geometry);

Get 3D vector tiles

Before you use the 3D vector data visualization feature, you must call two functions provided by GanosBase: ST_AsMVTGeom3D and ST_AsMVT3D.

• ST_AsMVTGeom3D

  The ST_AsMVTGeom3D function is an extension of PostGIS's ST_AsMVTGeom, specifically designed for 3D vector data. It enables the conversion of Geometry3D data's coordinate space to the MVT coordinate space and optionally clips the data based on the tile's bounding box. For more information, see ST_AsMVTGeom3D. The following example shows how to use it:

  SELECT ST_AsText(ST_AsMVTGeom3D(ST_Transform('SRID=4326; LINESTRING(-10 -10 30, -10 -20 30)'::geometry, 3857), ST_TileEnvelope(1, 0, 0))) AS geom;

  Sample result:

                                          geom                                        
  ------------------------------------------------------------------------------------
   MULTILINESTRING Z ((3868.44444444444 4324.7197219642 30,3868.44444444444 4352 30))
  (1 row)

• ST_AsMVT3D

  ST_AsMVT3D functions similarly to PostGIS's ST_AsMVT for 2D vector data, which aggregates multiple data rows into a single 3D vector tile. Each data row is converted into Geometry3D data in the MVT coordinate space, and the aggregated data forms a tile layer. For more information, see ST_AsMVT3D. The following example shows how to use it:

  WITH mvtgeom AS
  (
    SELECT ST_AsMVTGeom3D(
      ST_Transform('SRID=4326; MULTIPOLYGON(((100 50 0, -100 50 1, -100 -50 2, 100 -50 3, 100 50 0)), ((0 0 0, 1 0 1, 2 2 2, 0 0 0)))'::geometry, 3857),
      ST_TileEnvelope(1, 0, 0)) AS geom,  'test' AS name
  )
  SELECT ST_AsMVT3D(mvtgeom.*) FROM mvtgeom;

  Sample result:

                                                                                                                       st_asmvt3d                                                                                                                     
  ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
   \x1a760a0764656661756c74125812020000180322500d8044a842b83116ff23d80105802400080f0d810481041d162e000e2e590e0f0dd920dc0405168024d70106c727f3160d0f0dc827f4160e1600f31615c72700080f0d0000001600cc1808c80300000f1a046e616d6522060a04746573742880207802
  (1 row)