Use ST_Transform to transform the coordinates of a raster object to a new spatial reference system - ApsaraDB RDS

ST_Transform reprojects a raster object from one spatial reference system (SRS) to another by transforming its underlying coordinates and resampling pixel values.

Syntax

raster ST_Transform(raster rast,
           integer outSrid,
           cstring processExpr default '',
           cstring storageOption default '')

Parameters

Parameter	Description
`rast`	The source raster object to reproject.
`outSrid`	The SRID of the target spatial reference system. Must be a valid SRID from the `spatial_ref_sys` table.
`processExpr`	A JSON string that controls pixel resampling and nodata handling.
`storageOption`	A JSON string that controls how the output raster is stored.

processExpr fields

Field	Type	Default	Description
`resample`	Text	`Near`	Resampling algorithm. Valid values: `Near`, `Average`, `Cubic`, `Bilinear`.
`nodata`	Boolean	`false`	Specifies whether nodata values in the source raster are treated as valid. If `true`, pixels with nodata values are not resampled. If `false`, pixels with nodata values are resampled.
`nodataValue`	float8 or float8[]	`NULL`	The nodata value(s) to assign to the output raster's bands. Specify a single value to apply it to all bands, or an array with one value per band.

Warning

Set nodata and nodataValue carefully. If the source raster has no pixels with nodata values, set nodata to false and omit nodataValue. Otherwise, image artifacts may occur.

storageOption fields

Field	Type	Default	Description
`chunking`	Boolean	Same as source	Specifies whether to store the output raster as chunks.
`chunkdim`	String	Same as source	Chunk dimensions. Takes effect only when `chunking` is `true`.
`chunktable`	String	`''` (empty string)	Name of the chunk table for permanent storage. If empty or `NULL`, a temporary chunk table with a random name is created and deleted at the end of the session. To persist the output raster, specify a table name.
`compression`	String	Same as source	Compression format. Valid values: `None`, `JPEG`, `Zlib`, `PNG`, `LZO`, `LZ4`.
`quality`	Integer	Same as source	Image quality. Takes effect only in JPEG format.
`interleaving`	String	Same as source	Interleaving type. Valid values: `bip` (band interleaved by pixel, BIP), `bil` (band interleaved by line, BIL), `bsq` (band sequential, BSQ).
`endian`	String	Same as source	Byte order. `NDR` for little endian; `XDR` for big endian.

Examples

The following examples use a GeoTIFF file loaded into a source table.

CREATE TABLE IF NOT EXISTS datasource_table (id integer, rast raster);
INSERT INTO datasource_table VALUES (
  1,
  ST_ImportFrom('rbt', '$(RAST_DATA_DIR)/512_512_1_bsq_8u_geo.tif', '{}')
);

Persist the output to a permanent chunk table

Specify a chunktable name to save the reprojected raster across sessions.

Without nodata:

CREATE TABLE rat_transform_result (id integer, rast raster);

INSERT INTO rat_transform_result (id, rast)
SELECT
  10,
  ST_Transform(
    rast,
    32652,
    '{"resample":"Near","nodata":false}',
    '{"chunking":true,"chunkdim":"(256,256,1)","compression":"none","interleaving":"bsq","chunktable":"result_rbt"}'
  )
FROM datasource_table
WHERE id = 1;

With a single nodata value (pixels with nodata values are resampled):

INSERT INTO rat_transform_result (id, rast)
SELECT
  11,
  ST_Transform(
    rast,
    32652,
    '{"resample":"Near","nodata":true,"nodatavalue":255}',
    '{"chunking":true,"chunkdim":"(256,256,1)","compression":"none","interleaving":"bsq","chunktable":"result_rbt"}'
  )
FROM datasource_table
WHERE id = 1;

With per-band nodata values:

INSERT INTO rat_transform_result (id, rast)
SELECT
  12,
  ST_Transform(
    rast,
    32652,
    '{"resample":"Near","nodata":false,"nodatavalue":[255,255,255]}',
    '{"chunking":true,"chunkdim":"(256,256,1)","compression":"none","interleaving":"bsq","chunktable":"result_rbt"}'
  )
FROM datasource_table
WHERE id = 1;

Store the output in a temporary chunk table

Omit chunktable to write the output to a session-scoped temporary table. The table is deleted when the session ends, so use this pattern for intermediate computations only.

CREATE TEMP TABLE rat_transform_result_temp (id integer, rast raster);

INSERT INTO rat_transform_result_temp (id, rast)
SELECT
  1,
  ST_Transform(
    rast,
    32652,
    '{"resample":"Near","nodata":false,"nodataValue":[255,255,255]}',
    '{"chunking":true,"chunkdim":"(256,256,1)","compression":"none","interleaving":"bsq"}'
  )
FROM datasource_table
WHERE id = 1;