PolarDB:GanosBase Raster

Spatial reference system

GanosBase uses a Spatial Reference Identifier (SRID) to associate a raster object with its spatial reference system (SRS) — the coordinate framework that maps the data to a location on the earth's surface. For details, see Spatial reference.

The raster_columns view

The raster_columns view lists all raster columns in the current database:

To list all raster columns in the current database:

SELECT * FROM raster_columns;

Indexes

A spatial index eliminates full table scans by organizing data in a search tree, which speeds up queries on large raster datasets. GanosBase Raster supports two index types:

Prerequisites

Before you begin, ensure that you have:

• A running PolarDB for PostgreSQL (Compatible with Oracle) cluster

• An OSS bucket containing a raster file to import

• An Alibaba Cloud AccessKey ID and AccessKey Secret with OSS read access (for importing raster data) and write access (for exporting results)

Install the extension

You can install the extension using either of the following forms. Installing into the public schema is recommended to avoid potential permission issues.

-- Basic form
CREATE EXTENSION ganos_raster CASCADE;

-- Recommended: install into the public schema to avoid permission issues
CREATE EXTENSION ganos_raster WITH SCHEMA public CASCADE;

Create a raster table

CREATE TABLE raster_table(id integer, rast raster);

Import raster data from OSS

INSERT INTO raster_table VALUES(
  1,
  ST_ImportFrom(
    'chunk_table',
    -- Replace <ak> and <ak_secret> with your AccessKey ID and AccessKey Secret.
    -- Access OSS using an OSS domain name. See: https://www.alibabacloud.com/help/en/oss/user-guide/oss-domain-names
    -- For OSS path format details, see: https://www.alibabacloud.com/help/en/polardb/polardb-for-oracle/object-storage-service-path-1
    'OSS://<ak>:<ak_secret>@oss-cn-internal.aliyuncs.com/bucket/data/image.tif'
  )
);

Query raster properties

GanosBase Raster functions operate at two levels:

• Raster level: Return properties of the entire raster object (for example, ST_Height, ST_Width).

• Band level: Operate on a single band within the raster (for example, ST_Statistics). The band index argument specifies which band to use.

Get dimensions (width and height in pixels):

SELECT ST_Height(rast), ST_Width(rast)
FROM raster_table
WHERE id = 1;

Expected output:

 st_height | st_width
-----------+----------
      1241 |     ...
(1 row)

Get statistics for a specific band:

The second argument (0) is the band index.

SELECT ST_Statistics(rast, 0)  -- band index 0
FROM raster_table
WHERE id = 1;

Expected output (JSON):

{
  "approximate": false,
  "min": 8969.0,
  "max": 12351.0,
  "mean": 9407.330013839151,
  "std": 193.4268180351078,
  "count": 70091,
  "sum": 659369168.0,
  "mode": 9366.0,
  "median": 9392.0
}

Build a pyramid

A pyramid pre-computes reduced-resolution versions of the raster, enabling fast rendering at different zoom levels. Level 0 is the original data.

UPDATE raster_table
SET rast = ST_BuildPyramid(rast)
WHERE id = 1;
-- Output: UPDATE 1

Find the optimal pyramid level for a given viewport:

The following query returns the pyramid level that best matches a viewport defined by its world coordinate bounding box, width (800 pixels), and height (600 pixels). A higher level means a lower-resolution version of the data.

SELECT ST_BestPyramidLevel(
  rast,
  '((128.0, 30.0),(128.5, 30.5))',  -- world coordinate bounding box
  800,                               -- viewport width in pixels
  600                                -- viewport height in pixels
)
FROM raster_table
WHERE id = 10;

Expected output:

 st_bestpyramidlevel
--------------------
                  3
(1 row)

A result of 3 means pyramid level 3 fits the specified viewport most efficiently.

Clip a region

Query the pixel matrix within a bounding box (band 0):

SELECT ST_Clip(rast, 0, '((128.980, 30.0),(129.0, 30.2))', 'World')
FROM raster_table
WHERE id = 1;

Calculate the pixel coordinate boundaries of a clipped area (pyramid level 2):

SELECT ST_ClipDimension(rast, 2, '((128.0, 30.0),(128.5, 30.5))')
FROM raster_table
WHERE id = 10;

Expected output:

   st_clipdimension
------------------------
 '((600, 720),(200, 300))'
(1 row)

Clip and export

Clip a portion of the raster using a polygon geometry and export the result to COG format in OSS:

SELECT ST_ExportTo(
  ST_ClipToRast(
    rast,
    ST_GeomFromText(
      'POLYGON((128.0 30.0, 129.0 30.0, 129.0 31.0, 128.0 31.0, 128.0 30.0))',
      4326  -- SRID: WGS 84
    ),
    0  -- band index
  ),
  'COG',  -- output format: Cloud Optimized GeoTIFF
  -- Replace <ak> and <ak_secret> with your AccessKey ID and AccessKey Secret.
  'OSS://<ak>:<ak_secret>@oss-cn.aliyuncs.com/mybucket/data/image_clip.tif'
);

Expected output:

 st_exportto
-------------
 t
(1 row)

A result of t (true) confirms the export succeeded.

Drop the extension

If you no longer need GanosBase Raster, remove the extension and all associated objects:

DROP EXTENSION ganos_raster CASCADE;