PolarDB:Raster model

Raster data model

A raster is a matrix of cells organized into rows and columns. Each cell stores a numeric value — making a raster fundamentally a georeferenced numeric matrix, not just an image. This means you can run statistical and algebraic operations on raster data directly in the database, alongside your other relational data.

GanosBase Raster organizes raster data into the following elements:

In the database, each raster image is stored as a single raster object. That object is logically divided into bands, physically stored and managed by tiles, and described by metadata (spatial extent, data type, projection information, and coordinate numbers). If the raster uses a pyramid structure, each band has its own pyramid.

Storage model

GanosBase Raster keeps raster metadata in the database while storing raster attribute data on OSS (Object Storage Service). This separation keeps large raster datasets manageable at low cost. Each raster record can be up to 1 TB.

Spatial reference system

The spatial reference system (SRS) defines how a raster object maps to a location on Earth. GanosBase uses a Spatial Reference Identifier (SRID) to link each raster object to its SRS definition.

For more information, see Spatial reference.

raster_columns view

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

Query all raster columns in the current database:

SELECT * FROM raster_columns;

Indexes

A spatial index eliminates full sequential scans by organizing spatial data in a search tree for fast traversal.

GanosBase Raster supports two index types:

Prerequisites

Before you begin, ensure that you have:

• A PolarDB for PostgreSQL cluster

• An OSS bucket containing raster files

• Your OSS access key ID and access key secret

Install the extension

Install ganos_raster without specifying a schema:

CREATE EXTENSION ganos_raster CASCADE;

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

Use ST_ImportFrom to load a raster file from OSS. Replace <ak>, <ak_secret>, and the bucket path with your actual values:

INSERT INTO raster_table
VALUES (
  1,
  ST_ImportFrom(
    'chunk_table',
    'OSS://<ak>:<ak_secret>@oss-cn-internal.aliyuncs.com/bucket/data/image.tif'
  )
);

Access OSS through the correct OSS domain names. For path format details, see Object storage service paths.

Query raster attributes

Get the height and width of the imported raster:

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

Expected output:

-----------
  1241
(1 rows)

Get statistics for band 0:

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

Expected output:

{"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

Pyramids speed up rendering at lower resolutions by pre-computing reduced-resolution versions of the raster. Build the pyramid before running viewport-based queries:

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

Expected output: UPDATE 1

Find the optimal pyramid level for an 800 x 600 viewport over a bounding box:

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

Expected output: 3

Clip and export

Query the pixel matrix within a bounding box at band 0:

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

Get the pixel coordinate boundaries of a clipped area at pyramid level 2:

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

Expected output: '((600, 720),(200, 300))'

Clip to a polygon geometry and export to Cloud Optimized GeoTIFF (COG) format:

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),
    0
  ),
  'COG',
  'OSS://<ak>:<ak_secret>@oss-cn.aliyuncs.com/mybucket/data/image_clip.tif'
);

Expected output: t (1 row)

Drop the extension

DROP EXTENSION ganos_raster CASCADE;