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

Install the GanosBase Raster extension

CREATE EXTENSION ganos_raster CASCADE;

Create a raster table

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

Import an image from OSS

Use ST_ImportFrom to load the image from OSS into the chunk table:

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

Build a pyramid

A pyramid is required for fast image browsing. Build it immediately after importing the image using ST_BuildPyramid:

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

ST_AsImage

ST_AsImage retrieves image bytes for a specified spatial extent at a given pyramid level:

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

The parameters split into two categories based on how often they change at runtime.

Static parameters — set once in code

Dynamic parameters — computed per request

ST_BestPyramidLevel

ST_BestPyramidLevel calculates the optimal pyramid level for a given viewport, balancing image quality and transfer size:

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

Get the image bounding box

Use ST_Envelope to get the image's spatial extent, then ST_Transform to reproject it to WGS 84 (EPSG:4326) for the frontend:

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

Convert bbox to box type

ST_AsImage and ST_BestPyramidLevel use the native box type. The bounding box array returned by the frontend requires a four-step conversion:

1. Construct the frontend string into a geometry object.

2. Convert the geometry object to text.

3. Replace text to make it compatible with the box type format.

4. Cast to a native box object.

SELECT Replace(
  Replace(
    Replace(
      box2d(st_transform(
        st_setsrid(ST_Multi(ST_Union(st_point(-180, -58.077876), st_point(180, 58.077876))), 4326),
        st_srid(rast)
      ))::text,
      'BOX', ''
    ),
    ',', ')('
  ),
  ' ', ','
)::box
FROM rat_clip
WHERE name = 'xxxx image';

GanosBase 6.0 introduces direct conversion functions between the raster box type and the geometry box2d type. The nested replace chain above simplifies to:

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

Backend (Raster.py)

The backend uses Flask as the web framework and psycopg2 as the database driver. On startup, it creates the raster table, imports the image from OSS, builds the pyramid, and collects statistics. It then exposes three endpoints:

Example request to verify the service is running:

http://localhost:5000/raster/extent

Expected response: a JSON array with four coordinates, for example [-180.0, -58.077876, 180.0, 58.077876].

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

# -*- coding: utf-8 -*-
# Raster.py

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

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

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

# Labels used in the raster table
RASTER_NAME = "xxxx image"
CHUNK_TABLE = "chunk_table"
RASTER_TABLE = "raster_table"
RASTER_COLUMN = "rast"

# Default JPEG rendering parameters
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):
        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):
        pg_cursor = self.pg_connection.cursor()
        pg_cursor.execute(query)
        self.pg_connection.commit()
        pg_cursor.close()

    def _make_table(self):
        sql = f"create table if not exists {self.table_name} (ID INT PRIMARY KEY NOT NULL, name text, {self.column_name} raster);"
        self.poll_command(sql)

    def _import_raster(self, raster):
        # Import image, build pyramid, and collect statistics in a single statement
        sql = f"insert into {self.table_name} values (1, '{RASTER_NAME}', ST_ComputeStatistics(st_buildpyramid(ST_ImportFrom('{CHUNK_TABLE}','{raster}'),'{CHUNK_TABLE}'))) on conflict (id) do nothing;;"
        self.poll_command(sql)
        self.identify = f" name= '{RASTER_NAME}'"

    def get_extent(self) -> list:
        """Return the image bounding box in WGS 84 as [minX, minY, maxX, maxY]."""
        import re
        sql = f"select replace((box2d(st_transform(st_envelope({self.column_name}),4326)))::text,'BOX','') 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:
        """
        Return a JPEG image for the specified viewport.
        :param bbox: Bounding box [minX, minY, maxX, maxY] from the frontend
        :param width: Viewport width in pixels
        :param height: Viewport height in pixels
        """
        bands = "0-2"
        options = json.dumps(DEFAULT_CONFIG)

        # Convert the WGS 84 bbox array to the native box type expected by GanosBase
        boxSQL = (
            f"Replace(Replace(Replace("
            f"box2d(st_transform(st_setsrid(ST_Multi(ST_Union(st_point({bbox[0]},{bbox[1]}),st_point({bbox[2]},{bbox[3]}))),4326),st_srid({self.column_name})))::text,"
            f" 'BOX', ''), ',', ')(')"
            f",' ',',')::box"
        )
        sql = (
            f"select encode(ST_AsImage({self.column_name},{boxSQL},"
            f"ST_BestPyramidLevel({self.column_name},{boxSQL},{width},{height}),"
            f"'{bands}','jpeg','{options}'),'hex') "
            f"from {self.table_name} where {self.identify}"
        )
        result = self.poll_query(sql)
        # psycopg2 is more efficient with hexadecimal data; convert to binary before returning
        result = binascii.a2b_hex(result)
        return result


rasterViewer = RasterViewer()
app = Flask(__name__)


@app.route('/raster/image')
def raster_image():
    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 Response(
        response=json.dumps(rasterViewer.get_extent()),
        mimetype="application/json",
    )


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


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

Replace the following placeholders with your actual values:

When using psycopg2, transmit data as hexadecimal for efficiency. If you use a different language or database driver, use binary data directly.

The image metadata shows this example uses 3 bands. To determine the band count of a different image dynamically, use ST_NumBands.

Frontend (Raster.html)

The frontend uses Mapbox GL JS to render the map and Turf.js to compute the intersection between the image extent and the user's current viewport. On every pan or zoom, the map requests a fresh image from /raster/image for the visible area only—avoiding unnecessary data transfer.

The key logic on every map move:

1. Get the image's spatial extent (fetched once on load from /raster/extent).

2. Get the current viewport bounds from Mapbox.

3. Compute the intersection—this is the range to request. If the image is outside the viewport, skip the request.

An Extent helper class handles format conversions between Mapbox bounds, GeoJSON, bounding box arrays, and query strings.

In the same directory as Raster.py, create Raster.html with the following content. After starting the backend, open http://localhost:5000/raster to view the map.

<!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 map
    const map = new mapboxgl.Map({
      container: "map",
      style: { version: 8, layers: [], sources: {} },
    });

    // Extent wraps GeoJSON and provides format conversion and spatial intersection
    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) {
        const intersect = turf.intersect(this._extent, another._extent);
        return intersect ? new Extent(intersect) : null;
      }

      toQuery() {
        return turf.bbox(this._extent).join(",");
      }

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

      toMapboxCoordinates() {
        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 image request URL with the current extent and viewport size
      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 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 image as a Mapbox image source
      map.addSource("raster_source", {
        type: "image",
        url: getUrl(extent),
        coordinates,
      });

      map.addLayer({
        id: "raster_layer",
        paint: { "raster-fade-duration": 300 },
        type: "raster",
        layout: { visibility: "visible" },
        source: "raster_source",
      });

      map.fitBounds(extent.toBBox());

      // Update the image when the user pans or zooms
      map.once("moveend", () => {
        const updateRaster = () => {
          const _extent = Extent.FromBounds(map.getBounds());
          let intersect;
          // Skip the request if the image is not in the current viewport
          if (!_extent || !(intersect = extent.intersect(_extent))) return;

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

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

</html>

Map application

Integration with pgAdmin

The image browsing feature integrates with pgAdmin, which is compatible with PolarDB. This lets you browse and evaluate raster data directly from the database client without a separate application.