Online visualization of billion-scale vector spatial data with 100 lines of code using

Test data

Sample rows:

-- buildings
gid|geom
---|----
  1|MULTIPOLYGON(((-88.066953 34.916114 0,-88.066704 34.916114 0,-88.066704 34.91602 0,-88.066953 34.91602 0,-88.066953 34.916114 0)))
  2|MULTIPOLYGON(((-87.924658 34.994797 0,-87.924791 34.99476 0,-87.924817 34.994824 0,-87.924685 34.994861 0,-87.924658 34.994797 0)))

-- points
id|geom
--|----
 1|POINT (113.5350205 22.1851929)
 2|POINT (113.5334245 22.1829781)

Architecture

The stack has three layers: the database server running GanosBase, a Python service layer that converts tile requests into SQL, and the browser client rendering the tiles.

Server-side code

The backend uses Flask to expose two tile endpoints and Psycopg2 to query the database. All tile fetching — whether raster or vector — follows the same pattern: convert a tile request into a SQL call, return the binary result.

Save the following code as Vector.py and start the server with python Vector.py. The service runs 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):
    """
    Connection pool for multithreading, improving response in high concurrency scenarios for 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)
        # Convention for 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 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"
        }
        # When using psycopg2, it is more efficient to return binary data as a hexadecimal string
        sql = f"select encode(st_aspng('{self.pyramid_table}','{z}_{x}_{y}','{json.dumps(config)}'),'hex')"
        result = self.poll_query(sql)
        # Only when returning as a hexadecimal string is it necessary to convert it back
        result = binascii.a2b_hex(result)
        return result


class MvtViewer(VectorViewer):
    def get_mvt(self, x, y, z):
        # When using psycopg2, it is more efficient to return binary data as a hexadecimal string
        sql = f"select encode(st_tile('{self.pyramid_table}','{z}_{x}_{y}'),'hex')"
        result = self.poll_query(sql)
        # Only when returning as a hexadecimal string is it necessary to convert it back
        result = binascii.a2b_hex(result)
        return result


app = Flask(__name__)


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

# Define table name, field name, 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)

Replace the placeholders in CONNECTION with your actual database credentials:

Client-side code

The frontend uses Mapbox GL JS to render both raster and vector tile layers. Create Vector.html in the same directory as Vector.py, then open http://localhost:5000/vector once the server is running.

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

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

        // Add vector tile layer and apply styles
        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>

Visualization

Dynamic visualization effect of vector tiles

Adjust visualization styles on the client side by modifying the layer paint properties:

{
  "circle-radius": 4,
  "circle-color": "#000000",
  "circle-stroke-width": 2,
  "circle-opacity": 0.3,
  "circle-stroke-color": "#003399",
  "circle-stroke-opacity": 0.9
}

Dynamic visualization effect of raster tiles