PolarDB:A low-code implementation based on GanosBase for browsing remote sensing images without pre-tiling: pyramid

Set up the database

1. Install the ganos_raster extension.

   CREATE EXTENSION ganos_raster CASCADE;

2. Create a table with a raster column.

   CREATE TABLE raster_table (ID INT PRIMARY KEY NOT NULL, name text, rast raster);

3. Import your image from OSS. Replace the placeholders with your OSS credentials and path.

   INSERT INTO raster_table VALUES (
     1,
     'xxxx image',
     ST_ImportFrom('chunk_table', 'oss://<access_id>:<secret_key>@<Endpoint>/<bucket>/path_to/file.tif')
   );

   For more information, see ST_ImportFrom.

4. Build a pyramid. A pyramid is required for performant browsing at any zoom level.

   UPDATE raster_table
   SET rast = st_buildpyramid(raster_table, 'chunk_table')
   WHERE name = 'xxxx image';

   For more information, see ST_BuildPyramid.

ST_AsImage

ST_AsImage returns image data for a specified geographic range and pyramid level.

bytea ST_AsImage(
  raster raster_obj,
  box extent,
  integer pyramidLevel DEFAULT 0,
  cstring bands DEFAULT '',
  cstring format DEFAULT 'PNG',
  cstring option DEFAULT ''
)

For detailed parameter information, see ST_AsImage.

ST_BestPyramidLevel

ST_BestPyramidLevel selects the optimal pyramid level for a given field of view.

integer ST_BestPyramidLevel(
  raster rast,
  box extent,
  integer width,
  integer height
)

For detailed parameter information, see ST_BestPyramidLevel.

Convert a frontend bounding box to the native box type

ST_AsImage and ST_BestPyramidLevel accept the native PostgreSQL box type, not the geometry type. The frontend sends a bounding box as a comma-separated string (for example, "-180,-58.08,180,58.08"). The conversion requires four steps:

SELECT Replace(
         Replace(
           Replace(
             box2d(
               st_transform(
                 -- Step 1: Build two corner points and union them into a geometry,
                 --         then assign the WGS 84 coordinate reference system (EPSG:4326)
                 st_setsrid(
                   ST_Multi(ST_Union(st_point(-180, -58.077876), st_point(180, 58.077876))),
                   4326
                 ),
                 -- Step 2: Project from WGS 84 into the raster's native CRS
                 st_srid(rast)
               )
             )::text,
             -- Step 3: Remove the 'BOX' prefix from the box2d text representation
             'BOX', ''
           ),
           -- Step 4a: Replace the comma separator between min and max coordinates
           ',', ')('
         ),
         -- Step 4b: Replace the space between x and y within each coordinate pair
         ' ', ','
       )::box
FROM raster_table
WHERE name = 'xxxx image';

GanosBase 6.0 and later provides direct conversion functions between the raster box type and the geometry box2d type. The nested Replace operation above simplifies to:

SELECT st_extent(rast)::box2d::box FROM raster_table WHERE name = 'xxxx image';

Get the image bounding box

Use ST_Envelope and ST_Transform to get the full extent of an image in WGS 84 coordinates for the frontend:

SELECT replace(
  (box2d(st_transform(st_envelope(rast), 4326)))::text,
  'BOX', ''
)
FROM raster_table
WHERE name = 'xxxx image';

Architecture

Backend

Save the following code as Raster.py and run python Raster.py to start the service on port 5000.

# -*- coding: utf-8 -*-
# Raster.py — GanosBase low-code raster viewer backend

import json
from flask import Flask, request, Response, send_from_directory
import binascii
import psycopg2

# Database connection string — replace with your actual values
CONNECTION = "dbname=<database_name> user=<user_name> password=<user_password> host=<host> port=<port>"

# OSS path to the source GeoTIFF — replace with your actual path
OSS_RASTER = "oss://<access_id>:<secret_key>@<Endpoint>/<bucket>/path_to/file.tif"

# Identifiers used in the database
RASTER_NAME   = "xxxx image"    # Logical name stored in the name column
CHUNK_TABLE   = "chunk_table"   # GanosBase chunk table for raster storage
RASTER_TABLE  = "raster_table"  # Primary table
RASTER_COLUMN = "rast"          # Raster column name

# Default JPEG rendering options
DEFAULT_CONFIG = {
    "strength": "ratio",
    "quality": 70
}


class RasterViewer:
    def __init__(self):
        self.pg_connection = psycopg2.connect(CONNECTION)
        self.column_name = RASTER_COLUMN
        self.table_name  = RASTER_TABLE
        self._make_table()
        self._import_raster(OSS_RASTER)

    def poll_query(self, query: str):
        """Run a SELECT and return the first column of the first row."""
        pg_cursor = self.pg_connection.cursor()
        pg_cursor.execute(query)
        record = pg_cursor.fetchone()
        self.pg_connection.commit()
        pg_cursor.close()
        if record is not None:
            return record[0]

    def poll_command(self, query: str):
        """Run a DML statement (INSERT, UPDATE, CREATE)."""
        pg_cursor = self.pg_connection.cursor()
        pg_cursor.execute(query)
        self.pg_connection.commit()
        pg_cursor.close()

    def _make_table(self):
        """Create the raster table if it does not already exist."""
        sql = (
            f"CREATE TABLE IF NOT EXISTS {self.table_name} "
            f"(ID INT PRIMARY KEY NOT NULL, name text, {self.column_name} raster);"
        )
        self.poll_command(sql)

    def _import_raster(self, raster):
        """Import the raster from OSS, build a pyramid, and compute statistics."""
        sql = (
            f"INSERT INTO {self.table_name} VALUES ("
            f"  1, '{RASTER_NAME}',"
            f"  ST_ComputeStatistics(st_buildpyramid(ST_ImportFrom('{CHUNK_TABLE}', '{raster}'), '{CHUNK_TABLE}'))"
            f") ON CONFLICT (id) DO NOTHING;"
        )
        self.poll_command(sql)
        self.identify = f"name = '{RASTER_NAME}'"

    def get_extent(self) -> list:
        """Return the image extent as [minLng, minLat, maxLng, maxLat] in WGS 84."""
        import re
        sql = (
            f"SELECT replace("
            f"  (box2d(st_transform(st_envelope({self.column_name}), 4326)))::text,"
            f"  'BOX', '')"
            f" FROM {self.table_name} WHERE {self.identify}"
        )
        result = self.poll_query(sql)
        bbox = [float(x) for x in re.split(r'\(|,|\s|\)', result) if x != '']
        return bbox

    def get_jpeg(self, bbox: list, width: int, height: int) -> bytes:
        """
        Fetch the image for a given bounding box as JPEG bytes.

        bbox   — [minLng, minLat, maxLng, maxLat] from the frontend
        width  — canvas width in pixels
        height — canvas height in pixels
        """
        bands   = "0-2"
        options = json.dumps(DEFAULT_CONFIG)

        # Convert the WGS 84 bbox array to a native box in the raster's CRS.
        # Step 1: Build two corner point geometries and assign WGS 84 (EPSG:4326).
        # Step 2: Project into the raster's native CRS using st_srid.
        # Step 3: Extract as box2d text, then strip 'BOX' and reformat separators
        #         so the string can be cast to the native box type.
        box_sql = (
            f"Replace(Replace(Replace("
            f"  box2d(st_transform(st_setsrid("
            f"    ST_Multi(ST_Union(st_point({bbox[0]},{bbox[1]}), st_point({bbox[2]},{bbox[3]}))), 4326),"
            f"    st_srid({self.column_name})"
            f"  ))::text,"
            f"  'BOX', ''), ',', ')(' ), ' ', ',')"
            f"::box"
        )

        # ST_BestPyramidLevel picks the right zoom level; ST_AsImage returns JPEG bytes.
        # encode(..., 'hex') transfers binary data efficiently via psycopg2.
        sql = (
            f"SELECT encode("
            f"  ST_AsImage({self.column_name}, {box_sql},"
            f"    ST_BestPyramidLevel({self.column_name}, {box_sql}, {width}, {height}),"
            f"    '{bands}', 'jpeg', '{options}'),"
            f"  'hex')"
            f" FROM {self.table_name} WHERE {self.identify}"
        )
        result = self.poll_query(sql)
        return binascii.a2b_hex(result)


rasterViewer = RasterViewer()
app = Flask(__name__)


@app.route('/raster/image')
def raster_image():
    """Return JPEG image data for the requested bounding box."""
    bbox   = request.args['bbox'].split(',')
    width  = int(request.args['width'])
    height = int(request.args['height'])
    return Response(
        response=rasterViewer.get_jpeg(bbox, width, height),
        mimetype="image/jpeg"
    )


@app.route('/raster/extent')
def raster_extent():
    """Return the image's geographic extent as a JSON array."""
    return Response(
        response=json.dumps(rasterViewer.get_extent()),
        mimetype="application/json",
    )


@app.route('/raster')
def raster_demo():
    """Serve the frontend HTML page."""
    return send_from_directory("./", "Raster.html")


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

The backend performs these operations on startup and per request:

The example uses encode(..., 'hex') + binascii.a2b_hex() because hexadecimal transfer is more efficient with psycopg2. If you use a different language or driver, retrieve the binary data directly.

Frontend

In the same directory as Raster.py, create Raster.html with the following code. After starting the backend, open http://localhost:5000/raster in a browser.

The frontend uses Mapbox GL JS as the map framework and Turf.js to compute the intersection of the field of view with the image extent. This avoids sending requests when the image is not visible in the current view.

<!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>
<script src="https://cdn.bootcdn.net/ajax/libs/lodash.js/4.17.20/lodash.min.js"></script>
<script src="https://cdn.bootcdn.net/ajax/libs/Turf.js/5.1.6/turf.min.js"></script>

<body>
  <div id="map" style="height: 100vh" />
  <script>

    // Initialize the Mapbox map with an empty style
    const map = new mapboxgl.Map({
      container: "map",
      style: { version: 8, layers: [], sources: {} },
    });

    // Extent wraps a GeoJSON polygon and provides format-conversion helpers
    class Extent {
      constructor(geojson) {
        this._extent = geojson;
      }

      static FromBBox(bbox) {
        return new Extent(turf.bboxPolygon(bbox));
      }

      static FromBounds(bounds) {
        const bbox = [bounds._sw.lng, bounds._sw.lat, bounds._ne.lng, bounds._ne.lat];
        return Extent.FromBBox(bbox);
      }

      intersect(another) {
        // Return null if the two extents do not overlap
        const intersect = turf.intersect(this._extent, another._extent);
        return intersect ? new Extent(intersect) : null;
      }

      toQuery() {
        // Serialize to a comma-separated bbox string for the backend API
        return turf.bbox(this._extent).join(",");
      }

      toBBox() {
        return turf.bbox(this._extent);
      }

      toMapboxCoordinates() {
        // Mapbox image sources require [topLeft, topRight, bottomRight, bottomLeft]
        const bbox = this.toBBox();
        return [
          [bbox[0], bbox[3]],
          [bbox[2], bbox[3]],
          [bbox[2], bbox[1]],
          [bbox[0], bbox[1]],
        ];
      }
    }

    map.on("load", async () => {
      map.resize();

      const location = window.location.href;

      // Build the backend image request URL from an Extent
      const getUrl = (extent) => {
        const params = {
          bbox: extent.toQuery(),
          height: map.getCanvas().height,
          width: map.getCanvas().width,
        };
        return `${location}/image?${Object.keys(params).map((key) => `${key}=${params[key]}`).join("&")}`;
      };

      // Fetch the image's geographic extent and center the map on it
      const result = await axios.get(`${location}/extent`);
      const extent = Extent.FromBBox(result.data);
      const coordinates = extent.toMapboxCoordinates();

      // Add the raster image as a Mapbox image source
      map.addSource("raster_source", {
        type: "image",
        url: getUrl(extent),
        coordinates,
      });

      // Add a raster layer to display the image
      map.addLayer({
        id: "raster_layer",
        paint: { "raster-fade-duration": 300 },
        type: "raster",
        layout: { visibility: "visible" },
        source: "raster_source",
      });

      // Zoom the map to fit the image
      map.fitBounds(extent.toBBox());

      // After the initial fit, set up dynamic updates on drag and zoom
      map.once("moveend", () => {
        const updateRaster = () => {
          const _extent = Extent.FromBounds(map.getBounds());
          let intersect;
          // Skip the request if the image is outside the current field of view
          if (!_extent || !(intersect = extent.intersect(_extent))) return;

          map.getSource("raster_source").updateImage({
            url: getUrl(intersect),
            coordinates: intersect.toMapboxCoordinates(),
          });
        };

        // Debounce to avoid excessive requests during continuous panning or zooming
        const _updateRaster = _.debounce(updateRaster, 200);
        map.on("zoomend", _updateRaster);
        map.on("moveend", _updateRaster);
      });
    });
  </script>
</body>

</html>

The Extent class handles all format conversions between the Mapbox map and the backend API:

Overview

Integrate image browsing in pgAdmin

The image browsing feature can be integrated in pgAdmin, the database client compatible with PolarDB. This lets you browse and evaluate raster data directly in the database client without a separate map application.