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

Fast 2D vector rendering

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

The GanosBase fast rendering engine provides the fast 2D vector rendering feature that specializes in visualizing massive 2D vector data (including points, lines, and polygons). This feature effectively solves the issues mentioned above. 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. This approach effectively resolves 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 local updates in a small range, GanosBase can automatically identify the affected tiles and limits updates to the smallest possible range, avoiding the need to rebuild the entire sparse pyramid. This significantly improves update efficiency. Testing has shown that constructing a sparse pyramid for a dataset containing 70 million housing records takes only 6 minutes on a standard 8-core PolarDB for PostgreSQL (Compatible with Oracle) cluster. Furthermore, when over one 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 not only supports fast rendering of 2D vectors but has also collaborated with Alibaba Cloud's DataV team to develop support for 3D vector data visualization. By extending the existing 2D vector tiling standard, GanosBase and DataV have jointly achieved 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 that contains 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, you only need to make that the data table contains a primary key ID column and a Geometry attribute column.

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

   CREATE EXTENSIION 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 the SQL statement similar to the following:

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

   This SQL statement stores the data in the 4326 coordinate system format, but you can also 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 following introduces it in detail through examples.

• 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. This example builds the pyramid with 32 parallel threads.

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

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

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

• GanosBase allows you to control the structure of the sparse pyramid based on a comprehensive consideration of performance and storage costs. You can use the maxLevel and splitSize parameters to achieve this control.

  • If you prioritize query performance over storage costs, you can set 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 set a larger splitSize value to reduce the number of tiles that need to be maintained. Additionally, you can set a smaller maxLevel value to control the height of the pyramid, 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 construction rules of the sparse pyramid. For example, you can specify that only features with an area greater than 100 can visualized in tiles at levels 0 to 5. This can be achieved using the following statement:

  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 for flexible and customizable filtering of features during the construction of the sparse pyramid.

ST_BuildPyramidUseGeomSideLen

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

Before using 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. Additionally, you need to 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 calling ST_BuildPyramidUseGeomSideLen, you need to provide the name of the new column, and the rest of the parameters are the same as those for 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 construction rules of the sparse pyramid.

Update a pyramid

When the data in a table 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.

• Suppose you have inserted multiple rows of data within the longitude and latitude range of[(lon1, lat1), (lon2, lat2)]. 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 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. In map services, compared with traditional raster tiles, vector tiles can achieve more seamless zooming and provide better visual effects. 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 are 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 using 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 using the 3D vector data visualization feature, you need to 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 rows of data into a single 3D vector tile. Each row of data is converted to Geometry3D data in the MVT coordinate space, and these aggregated data form 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)