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

Architecture overview

A GanosBase vector tile service has three components:

• Database: PolarDB for PostgreSQL with GanosBase. Stores geometry data, builds and updates the sparse pyramid, and answers tile queries.

• Backend server: A Python/Flask service that translates HTTP tile requests (z/x/y) into SQL calls and returns the tile bytes.

• Client application: A Mapbox frontend that renders vector and raster tile layers.

Fast 2D vector rendering

Traditional map services rely on offline tiling: pre-build all tiles for every zoom level, store them, and serve them on demand. This approach has two hard limits:

• Build time. Tiling a large dataset can take hours or days.

• Storage. Supporting up to 16 zoom levels requires storing tens of billions of tiles.

Even a small local data update forces a full rebuild because the system cannot tell which tiles are affected.

GanosBase solves both problems with a sparse pyramid — a multi-level index that skips tiles in empty or sparse regions and records only what is needed. Database query optimization and a visibility filtering algorithm prune data that has no effect on the rendered output, so neither build time nor storage grow linearly with dataset size.

When data changes, ST_UpdatePyramid identifies the affected tiles from a bounding box and updates only that range — no full rebuild needed.

Performance on a standard 8-core PolarDB for PostgreSQL cluster:

3D vector visualization

GanosBase collaborated with Alibaba Cloud's DataV team to extend the 2D Mapbox Vector Tile (MVT) standard to support Geometry3D data. The two functions — ST_AsMVTGeom3D and ST_AsMVT3D — mirror their PostGIS counterparts (ST_AsMVTGeom and ST_AsMVT), but carry the Z coordinate through the tiling pipeline. ST_AsMVTGeom3D clips and reprojects each geometry into MVT image coordinates; ST_AsMVT3D aggregates the transformed rows into a single tile.

Prerequisites

Before you begin, make sure that you have:

• A PolarDB for PostgreSQL cluster

• A data table with a primary key id column and a Geometry attribute column

Set up the data table

1. Install the ganos_geometry_pyramid extension.

   CREATE EXTENSION ganos_geometry_pyramid CASCADE;

2. Create a table with a primary key and a geometry column.

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

3. Import your vector dataset. Each insert must use one of the supported input functions — ST_GeomFromText, ST_GeomFromWKT, or ST_GeomFromWKB. The example below uses Well-Known Text (WKT) format with SRID 4326 (WGS84); substitute any valid coordinate system.

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

4. After importing all data, build a spatial index on the geometry column. GanosBase query optimizations rely on this index to efficiently filter tiles.

   CREATE INDEX ON try_ganos_viz USING gist(geom);

Build a sparse pyramid

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

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

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

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

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

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);

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

Update a pyramid

When data changes, call ST_UpdatePyramid with the bounding box of the affected area. GanosBase identifies and updates only the tiles that intersect that region — no parallelism argument is needed, as the function automatically inherits the setting from the original ST_BuildPyramid or ST_BuildPyramidUseGeomSideLen call. For the full parameter reference, see ST_UpdatePyramid.

Update all affected tiles within a bounding box (here lon1=0, lat1=-10, lon2=20, lat2=30, with maxLevel=16):

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

Limit the update to lower-level tiles by reducing updateBoxScale. A smaller value skips higher zoom levels and updates only the tiles immediately around the changed area:

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

Updating a pyramid involves updating the sparse pyramid, deleting old tiles, and generating new tiles. If a large portion of the dataset changes, rebuild the pyramid directly with ST_BuildPyramid or ST_BuildPyramidUseGeomSideLen instead of running an incremental update.

Get vector tiles

Vector tiles preserve feature attributes and enable smooth zoom transitions on the client side. GanosBase provides ST_Tile to retrieve Mapbox Vector Tile (MVT) data in real time. For the full parameter reference, see ST_Tile.

Both of the following statements return the tile at zoom level 1, x-coordinate 1, y-coordinate 0:

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

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

Get raster tiles

Raster tiles are PNG images rendered server-side. Unlike vector tiles, they do not require client-side rendering, which lowers the requirements on client hardware. ST_AsPng renders vector geometry into a raster tile with basic symbolization — suitable for lightweight scenarios that do not need complex client-side styling. For the full parameter reference, see ST_AsPng.

The following statement returns tile 1_1_0 as a PNG image using the specified rendering parameters:

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

Set up the data table

1. Install the ganos_geometry extension.

   CREATE EXTENSION ganos_geometry CASCADE;

2. Create a table with a primary key and a geometry column.

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

   Import 3D geometry data using ST_GeomFromText, ST_GeomFromWKT, or ST_GeomFromWKB.

Get 3D vector tiles

Retrieving a 3D vector tile uses two functions in sequence:

1. ST_AsMVTGeom3D — converts Geometry3D data's coordinate space to the MVT coordinate space and optionally clips the data to the tile boundary. It is specifically designed for 3D vector data, extending PostGIS's ST_AsMVTGeom.

2. ST_AsMVT3D — aggregates the transformed rows into a single tile layer.

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;

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

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;

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

Server-side code

The backend exposes two tile endpoints: /vector/png/<z>/<x>/<y> for raster tiles and /vector/mvt/<z>/<x>/<y> for vector tiles. A ReallyThreadedConnectionPool wraps psycopg2's ThreadedConnectionPool with a Semaphore to prevent connection starvation under high tile-request concurrency.

Install the Python dependency first:

pip install psycopg2

Save the following as Vector.py. Replace the CONNECTION string with your database credentials, then run python Vector.py to start the service on port 5000.

# -*- coding: utf-8 -*-
# @File : Vector.py

import json
from psycopg2 import pool
from threading import Semaphore
from flask import Flask, jsonify, Response, send_from_directory
import binascii

# Connection parameters.
CONNECTION = "dbname=<database_name> user=<user_name> password=<user_password> host=<host> port=<port>"


class ReallyThreadedConnectionPool(pool.ThreadedConnectionPool):
    """
    A connection pool for multi-threading, which improves response in high-concurrency scenarios such as map tiles.
    """
    def __init__(self, minconn, maxconn, *args, **kwargs):
        self._semaphore = Semaphore(maxconn)
        super().__init__(minconn, maxconn, *args, **kwargs)

    def getconn(self, *args, **kwargs):
        self._semaphore.acquire()
        return super().getconn(*args, **kwargs)

    def putconn(self, *args, **kwargs):
        super().putconn(*args, **kwargs)
        self._semaphore.release()


class VectorViewer:
    def __init__(self, connect, table_name, column_name, fid):
        self.table_name = table_name
        self.column_name = column_name
        # Create a connection pool.
        self.connect = ReallyThreadedConnectionPool(5, 10, connect)
        # Define the pyramid table name.
        self.pyramid_table = f"{self.table_name}_{self.column_name}"
        self.fid = fid
        self.tileSize = 512
        # self._build_pyramid()

    def _build_pyramid(self):
        """Create a pyramid"""
        config = {
            "name": self.pyramid_table,
            "tileSize": self.tileSize
        }
        sql = f"select st_BuildPyramid('{self.table_name}','{self.column_name}','{self.fid}','{json.dumps(config)}')"
        self.poll_query(sql)

    def poll_query(self, query: str):
        pg_connection = self.connect.getconn()
        pg_cursor = pg_connection.cursor()
        pg_cursor.execute(query)
        record = pg_cursor.fetchone()
        pg_connection.commit()
        pg_cursor.close()
        self.connect.putconn(pg_connection)
        if record is not None:
            return record[0]


class PngViewer(VectorViewer):
    def get_png(self, x, y, z):
        # Default parameters.
        config = {
            "point_size": 5,
            "line_width": 2,
            "line_color": "#003399FF",
            "fill_color": "#6699CCCC",
            "background": "#FFFFFF00"
        }
        # If you use psycopg2, returning binary data as a hexadecimal string is more efficient.
        sql = f"select encode(st_aspng('{self.pyramid_table}','{z}_{x}_{y}','{json.dumps(config)}'),'hex')"
        result = self.poll_query(sql)
        # If the data is returned as a hexadecimal string, convert it to get the original binary data.
        result = binascii.a2b_hex(result)
        return result


class MvtViewer(VectorViewer):
    def get_mvt(self, x, y, z):
        # If you use psycopg2, returning binary data as a hexadecimal string is more efficient.
        sql = f"select encode(st_tile('{self.pyramid_table}','{z}_{x}_{y}'),'hex')"
        result = self.poll_query(sql)
        # If the data is returned as a hexadecimal string, convert it to get the original binary data.
        result = binascii.a2b_hex(result)
        return result


app = Flask(__name__)


@app.route('/vector')
def vector_demo():
    return send_from_directory("./", "Vector.html")

# Define table names, field names, etc.


pngViewer = PngViewer(CONNECTION, 'usbf', 'geom', 'gid')


@app.route('/vector/png/<int:z>/<int:x>/<int:y>')
def vector_png(z, x, y):
    png = pngViewer.get_png(x, y, z)
    return Response(
        response=png,
        mimetype="image/png"
    )


mvtViewer = MvtViewer(CONNECTION, 'points', 'geom', 'gid')

@app.route('/vector/mvt/<int:z>/<int:x>/<int:y>')
def vector_mvt(z, x, y):
    mvt = mvtViewer.get_mvt(x, y, z)
    return Response(
        response=mvt,
        mimetype="application/vnd.mapbox-vector-tile"
    )


if __name__ == "__main__":
    app.run(port=5000, threaded=True)

The same tile-serving pattern works in any language or framework: wrap ST_Tile (vector) or ST_AsPng (raster) in an HTTP handler that accepts z, x, y and returns the raw bytes with the appropriate MIME type.

Advantages over traditional map services:

• Raster tile styles are controlled in code — modify the rendering parameters without regenerating tiles.

• No third-party tile server or special configuration is required.

• Any language or framework that can run SQL and serve HTTP can host the service.

Client-side code

The frontend uses Mapbox to render both tile layer types. Create a Vector.html file in the same directory as Vector.py. After starting the backend, open http://localhost:5000/vector in a browser.

The frontend loads both a raster tile layer (from the png endpoint) and a vector tile layer (from the mvt endpoint). Choose the layer type that fits your use case.

<!DOCTYPE html>
<html>
  <head>
    <meta charset="UTF-8" />
    <title></title>
    <link
      href="https://cdn.bootcdn.net/ajax/libs/mapbox-gl/1.13.0/mapbox-gl.min.css"
      rel="stylesheet"
    />
  </head>
  <script src="https://cdn.bootcdn.net/ajax/libs/mapbox-gl/1.13.0/mapbox-gl.min.js"></script>
  <script src="https://cdn.bootcdn.net/ajax/libs/axios/0.21.0/axios.min.js"></script>
  <body>
    <div id="map" style="height: 100vh" />
    <script>
      const sources = {
        osm: {
          type: "raster",
          tiles: ["https://b.tile.openstreetmap.org/{z}/{x}/{y}.png"],
          tileSize: 256,
        },
      };
      const layers = [
        {
          id: "base_map",
          type: "raster",
          source: "osm",
          layout: { visibility: "visible" },
        },
      ];
      const map = new mapboxgl.Map({
        container: "map",
        style: { version: 8, layers, sources },
      });
      map.on("load", async () => {
        map.resize();

        // Add a raster tile data source.
        map.addSource("png_source", {
          type: "raster",
          minzoom: 1,
          tiles: [`${window.location.href}/png/{z}/{x}/{y}`],
          tileSize: 512,
        });
        // Add a raster tile layer.
        map.addLayer({
          id: "png_layer",
          type: "raster",
          layout: { visibility: "visible" },
          source: "png_source",
        });

        // Add a vector tile data source.
        map.addSource("mvt_source", {
          type: "vector",
          minzoom: 1,
          tiles: [`${window.location.href}/mvt/{z}/{x}/{y}`],
          tileSize: 512,
        });

        // Add a vector tile layer and apply styles to the vector tiles.
        map.addLayer({
          id: "mvt_layer",
          paint: {
            "circle-radius": 4,
            "circle-color": "#6699CC",
            "circle-stroke-width": 2,
            "circle-opacity": 0.8,
            "circle-stroke-color": "#ffffff",
            "circle-stroke-opacity": 0.9,
          },
          type: "circle",
          source: "mvt_source",
          "source-layer": "points_geom",
        });

      });
    </script>
  </body>
</html>