How to use GanosBase Raster (PolarDB for PostgreSQL (Compatible with Oracle)) - PolarDB

This topic describes the details and usage of the raster model.

Introduction

Overview

A raster consists of a matrix of cells (or pixels) organized into rows and columns (or grids). Each cell contains a value that represents information, such as temperature. Raster models are ideal for storing and calculating raster data, including aerial photographs, satellite imagery, digital pictures, or scanned maps.

GanosBase Raster is a spatial-temporal extension for PolarDB for PostgreSQL (Compatible with Oracle). It enables the database to store and manage raster data effectively. It supports the integration and analysis of raster data from different sources, including remote sensing, photogrammetry, and thematic mapping. In addition, GanosBase Raster offers a GeoServer plug-in to allow users to publish raster data as OGC-compliant services, such as WMS or WMTS services. GanosBase Raster is ideal for fields that rely on raster data for analysis and decision-making, including meteorology, environmental monitoring, geological exploration, natural resource management, national defense, emergency response, telecommunications, media, transportation, urban planning, and homeland security.

Features

Supports a versatile range of input and output formats, including TIFF, IMG, HDF, NetCDF, and GRIB.
Allows you to query a raster object's basic attributes, band statistics, and pixel value statistics, and for obtaining the pixel matrix in multiple ways.
Allows you to perform various operations on raster objects, such as projection conversion, cropping, band operation, mosaic, and color balancing.
Offers dynamic raster tiling for on-demand visualization for raster objects.

Scenarios

GanosBase Raster is well-suited for storing and analyzing raster datasets in the following scenarios:

Smart agriculture
GanosBase Raster can efficiently store and retrieve massive volumes of remote sensing satellite data. GanosBase Raster can aid in agricultural production analysis by utilizing diverse spatial thematic data, remote sensing imagery data, band calculations, and spatial statistics. When used with DataV, a data visualization tool, it graphically displays data through maps and statistical graphs.
Meteorological and hydrological forecasting
GanosBase Raster can import and query data in standard meteorological and hydrological formats, such as HDF, NetCDF, and GRIB. Leveraging the spatial interpolation and contour/isosurface extraction capabilities, it can rapidly acquire critical meteorological and hydrological information, providing essential technical support for disaster prevention.
Agricultural finance and insurance
GanosBase Raster is useful for financial institutions to provide agricultural financial and insurance services. With the band calculation and classification technologies, GanosBase Raster allows users to conduct rapid statistical analysis of crop types, coverage, and growth under different temporal and spatial conditions. It can be combined with specialized risk control models to estimate crop yields and values based on self-declarations from farmers, farmland transfer records, climates, geographical locations, and agricultural market momentum. This approach helps determine reasonable credit limits and repayment cycles for farmers.

Components

Raster elements

The raster data model consists of the following elements:

Raster: a raster dataset, such as a remote sensing image or a TIFF file.
Tile: a block of pixels. A tile is the basic storage unit of a raster object. Each tile has 256 x 256 pixels by default.
Band: a single 2D layer in a raster dataset. Each band comprises multiple tiles, with each tile having its own coordinate numbers.
Cell: a pixel in a tile. Cells can be of different data types, such as byte, short, int, or double.
Pyramid: a series of reduced-resolution versions of a raster, used to speed up the display of raster data. A pyramid has different levels, with each level corresponding to a layer. Level 0 refers to the raw data.
Metadata: raster metadata, including the spatial extent, projection type, and pixel type.

As shown in the preceding figure, GanosBase Raster uses a simple but efficient raster model to manage thematic data and remote sensing imagery data. In the database system, a raster image is stored as a raster object. A raster object is logically divided into multiple bands, each holding the raw image data retrieved during the import process. A raster object is stored and managed by tiles. The size of a tile defaults to 256 x 256 pixels, but it can be changed as required. Each tile contains one or more bands. A tile consists of cells that represent pixels. Each raster object has its own metadata, such as the spatial extent, data type, projection information, and coordinate numbers. If a raster is organized in a pyramid structure, each band will have its own pyramid.

Spatial reference system

The spatial reference system (SRS) defines how to associate an Sfmesh object to a specific location on the earth's surface.

GanosBase uses SRID to link a raster object to its respective SRS definition.

Fore more information, see Spatial reference.

Data column view

In GanosBase, the raster_columns view is a catalog of all the columns in your database that are of type raster. Its structure is as follows:

Column	Type	Description
r_table_catalog	varchar(256)	The database that the table is in, which is generally fixed to 'postgres'.
r_table_schema	varchar(256)	The database schema that the raster table belongs to.
r_table_name	varchar(256)	The name of the raster table.
r_raster_column	varchar(256)	The name of a raster column in the table.

You can execute the following statement to query all geometric data columns in the current database:

SELECT * FROM raster_columns;

Index

A spatial index greatly enhances the efficiency of querying a large spatial dataset by eliminating the need of a full sequential scan of the database. It achieves this by organizing spatial data in a search tree, which allows for rapid traversal and retrieval of records.

GanosBase raster provides two indexing methods for raster data:

Index	Description	Characteristics
B-tree index	The B-tree index is well-suited to queries by the names of raster files.	It is the most commonly used indexing method in database systems and can accelerate a wide range of query operations.
GIN index	The Generalized Inverted Index (GIN) index is ideal for full-text searching, rapidly locating tuples that contain specified keywords. You can create GIN indexes on raster columns.	As a rule of thumb, GIN indexes are best for static data because lookups are faster. However, GIN indexes are moderately slower to update than GiST indexes.

Advantages

GanosBase Raster outperforms PostGIS Raster in the following aspects: business adaptation, storage savings, and calculation capabilities.

Better business adaptation
While PostGIS Raster fully rasterizes data, GanosBase Raster offers an object-oriented storage structure. Each raster, whether an image or a DEM file, is stored as a single database record, forming a one-to-one mapping. Each of these records can contain a raster up to 1 TB. What is more, GanosBase Raster does not allow for direct operations on tiles, ensuring the integrity of metadata. In addition, it is highly correlated with time series data. In a word, GanosBase Raster enables you to better integrate raster data into your business processes.
Greater storage savings
GanosBase Raster adopts an architecture that preserves raster metadata in the database and raster attribute data on low-cost OSS (Object Storage Service). This is beneficial for use cases which require large storage capacity, such as the analysis of massive numbers of images. This way, you can still perform spatial analysis of raster with significantly lower storage costs.
More spatial-temporal operators
In addition to assessing spatial relationships, processing images, and performing standard operations on pyramids, pixel values, and attributes, GanosBase Raster provides a variety of unique statistical and algebraic operations, as well as specialized image color balancing algorithms and accelerated rendering for overview maps, even when handling large-scale raster datasets.

Quick start

Overview

This section describes how to use the GanosBase Raster extension, such as installing the extension, creating a table, importing raster data, performing queries, creating a pyramid, calculating a clipped area, and exporting the result.

Syntax

Install the extension:
```
CREATE extension ganos_raster cascade;
```
Note
Install the extension into the public schema to avoid potential 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','OSS://<ak>:<ak_secret>@oss-cn-internal.aliyuncs.com/bucket/data/image.tif'))
```
Note
- You need to access OSS through OSS domain names. For more information, see Object storage service paths.
- Replace <ak> and <ak_secret> with the actual access key and access secret.

Query information about a raster object:

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

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

Query statistics on a specified band:

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

---------------------
{"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}
(1 row)

Create a pyramid:

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

Calculate the optimal pyramid level based on the world coordinate range, width, and height of a viewport:

SELECT ST_BestPyramidLevel(rast, '((128.0, 30.0),(128.5, 30.5))', 800, 600) FROM raster_table WHERE id = 10;

---------------------
3

Query the pixel matrix within the specified bounding box:

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

Calculate the coordinate boundaries of a clipped area:

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

-------------------------
'((600, 720),(200, 300))'

Clip and export:

Clip a portion of the raster object with the specified geometry and export the result to the 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'
);

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

Drop the extension (optional):
```
DROP extension ganos_raster cascade;
```

SQL statements

For more information, see Raster SQL reference.