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

Background

When managing the growing remote sensing data, you may ask the following questions for displaying an image on the map:

• Is it worth going through the traditional tiling and publishing process for images that may only be used a few times? Where are the tiles stored? How is the tile data managed subsequently?

• Can the real-time tiling solution meet the response requirements without pre-tiling? How much is real-time tiling impacted when processing particularly large images?

However, when you use PolarDB for PostgreSQL and the GanosBase Raster extension, you can efficiently display images from the database on the map using only SQL statements without relying on third-party tools.

Best practice

Prerequisites

Import image data

1. Install the ganos_raster extension.

   CREATE EXTENSION ganos_raster CASCADE;

2. Create a test table named raster_table with a raster column.

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

3. Import an image from OSS as test data. Use the ST_ImportFrom function to import image data into the chunk table chunk_table. For more information, see ST_ImportFrom.

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

Create a pyramid

A pyramid is the basis for quickly browsing image data. For newly imported data, we recommend that you create a pyramid first. GanosBase provides the ST_BuildPyramid function to create pyramids. For more information, see ST_BuildPyramid.

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

Statement analysis

After creating a pyramid, you can use the ST_AsImage function provided by GanosBase to get images of a specified range from the database. See the following basic syntax of the ST_AsImage function. For detailed information, see ST_AsImage.

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

The parameters of the ST_AsImage function are divided into two categories: static parameters and dynamic parameters.

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

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

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

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

Use case

Raster data can usually be browsed by publishing services through Geoserver. GanosBase also supports raster and vector service publishing based on Geoserver. For details, see Map services. In this example, a simpler low-code method is introduced, which does not rely on any GIS Server tools. Using minimal Python code, you can quickly build a map application that dynamically updates the display of the specified image data when the user drags and zooms the map. This application can be easily integrated with your business systems.

Architecture

Backend code

For code simplicity and logic description, Python is used as the backend language. The Flask framework for Python is used as the web framework. Psycopg2 developed based on Python is used as the database connection framework. You can install Psycopg2 by running the pip install psycopg2 command. In this example, a simple map service is created on the backend. It can automatically build pyramids and respond to frontend requests by returning image data within a specified range. Save the following code as a file named Raster.py and execute the command python Raster.py to start the service.

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

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

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

## Image address
OSS_RASTER = "oss://<access_id>:<secret_key>@<Endpoint>/<bucket>/path_to/file.tif"

## Chunk table name
RASTER_NAME = "xxxx image"

## Chunk table name
CHUNK_TABLE = "chunk_table"

## Primary table name
RASTER_TABLE = "raster_table"

## Field name
RASTER_COLUMN = "rast"

## Default 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):
        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:
        """Get image range"""
        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)

        # Convert to a form easily recognized by the frontend
        bbox = [float(x) for x in re.split(
                '\(|,|\s|\)', result) if x != '']
        return bbox

    def get_jpeg(self, bbox: list, width: int, height: int) -> bytes:
        """
        Get image for a specified location
        :param bbox: Bounding box of the specified location
        :param width: Width of the field of view control
        :param height: Height of the field of view control
        """

        # Specify bands and rendering parameters
        bands = "0-2"
        options = json.dumps(DEFAULT_CONFIG)

        # Obtain range
        boxSQl = f"Replace(Replace(Replace(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, 'BOX', ''), ',', '),('),' ',',')::box"
        sql = f"select encode(ST_AsImage({self.column_name},{boxSQl} ,ST_BestPyramidLevel({self.column_name},{boxSQl},{width},{height}),'{bands}','jpeg','{options}'),'hex')  from {self.table_name} where {self.identify}"
        result = self.poll_query(sql)
        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():
    """Proxy frontend page"""
    return send_from_directory("./", "Raster.html")


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

For the image data, the backend code mainly performs the following operations:

• Initialize the data including creating a test table, importing data, building a pyramid, and collecting statistics.

• Locate the image to help the frontend map quickly jump to the location of the image.

• Retrieve the image in a picture format.

  The code gets the metadata of the current image before retrieving the image and understands that this image consists of 3 bands. The number of bands can also be dynamically obtained by using the ST_NumBands function.

  The code determines the image range based on the range information returned by the frontend and the pixel width and height of the field of view control.

  When using the psycopg2 library, it is more efficient to transmit hexadecimal data. If other frameworks or languages are used, use binary data directly.

Python is used in this example. If other languages are used, develop the service with the same logic.

Frontend code

This example uses Mapbox as the frontend map framework and introduces the frontend spatial library Turf to calculate the intersection of the current field of view and the image after the user drags or zooms the map. A clearer image of the area is then requested from the server to update the image with map operations. In the same file directory as the backend code, create a file named Raster.html and write the following code in the file. After the backend service is started, you can access the map by visiting http://localhost:5000/raster.

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

    class Extent {
      constructor(geojson) {
        // Use the geojson format by default
        this._extent = geojson;
      }

      static FromBBox(bbox) {
        // Generate the Extent object from the bbox format object
        return new Extent(turf.bboxPolygon(bbox));
      }

      static FromBounds(bounds) {
        // Generate the extent object from Mapbox bounds
        const bbox = [
          bounds._sw.lng,
          bounds._sw.lat,
          bounds._ne.lng,
          bounds._ne.lat,
        ];
        return Extent.FromBBox(bbox);
      }

      intersect(another) {
        // Determine the intersection area
        const intersect = turf.intersect(this._extent, another._extent);
        return intersect ? new Extent(intersect) : null;
      }

      toQuery() {
        // Convert to the query format
        return turf.bbox(this._extent).join(",");
      }

      toBBox() {
        // Convert to the bbox format
        return turf.bbox(this._extent);
      }

      toMapboxCoordinates() {
        // Convert to the Mapbox coordinate format
        const bbox = this.toBBox();
        const coordinates = [
          [bbox[0], bbox[3]],
          [bbox[2], bbox[3]],
          [bbox[2], bbox[1]],
          [bbox[0], bbox[1]],
        ];
        return coordinates;
      }
    }

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

      const location = window.location.href;

      // Construct query statement
      const getUrl = (extent) => {
        const params = {
          bbox: extent.toQuery(),
          height: map.getCanvas().height,
          width: map.getCanvas().width,
        };
        // Join request
        const url = `${location}/image?${Object.keys(params)
          .map((key) => `${key}=${params[key]}`)
          .join("&")}`;
        return url;
      };

      // Query image range
      const result = await axios.get(`${location}/extent`);
      const extent = Extent.FromBBox(result.data);
      const coordinates = extent.toMapboxCoordinates();

      // Add data source
      map.addSource("raster_source", {
        type: "image",
        url: getUrl(extent),
        coordinates,
      });

      // Add layer
      // The image type layer of Mapbox is used to attach the image to the specified location to display it on the map
      map.addLayer({
        id: "raster_layer",
        paint: { "raster-fade-duration": 300 },
        type: "raster",
        layout: { visibility: "visible" },
        source: "raster_source",
      });

      // Jump to image location
      map.fitBounds(extent.toBBox());

      // Bind refresh method
      map.once("moveend", () => {
        const updateRaster = () => {
          const _extent = Extent.FromBounds(map.getBounds());
          let intersect;
          // Do not request again if no graphic exists in the field of view
          if (!_extent || !(intersect = extent.intersect(_extent))) return;

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

        // Add debounce to reduce invalid requests
        const _updateRaster = _.debounce(updateRaster, 200);
        map.on("zoomend", _updateRaster);
        map.on("moveend", _updateRaster);
      });
    });
  </script>
</body>

</html>

Because the current operation for image retrieval is not a standard map protocol operation, you must manually implement features related to image updates. The key question is: How to determine the image range within the user's field of view. The basic logic is:

• Get the spatial range of the image.

• Get the spatial range of the user's field of view.

• Get the intersection of the two spatial ranges, which is the expected image range.

To achieve spatial identification on the frontend, the Turf framework is introduced to perform simple calculations on the frontend, reducing unnecessary requests. To facilitate spatial identification and format conversion, an auxiliary class Extent that provides the following features is implemented:

• Format conversion:

  • BBox <=> Geojson

  • Mapbox Coordinate <=> Geojson

  • Geojson => Query

  • Bounds => Geojson

• Encapsulation of the spatial identification methods provided by Turf.

Results

Overview

Integrate image browsing in pgAdmin

The image browsing feature can be integrated in the database client pgAdmin which is compatible with PolarDB. It allows you to quickly browse and evaluate image data in the database, providing a better data management experience.

Conclusion

Using GanosBase Raster functions, you can retrieve image data from the database, and implement a map application that can interactively browse remote sensing images through a low-code method. Use GanosBase to manage the remote sensing images to help you reduce management costs. With the GanosBase Raster extension and a small amount of code, you can browse image data in the database without relying on complex third-party tools. This greatly improves the data management experience.