ST_AsDatasetFile Raster-to-BYTEA Conversion Reference - PolarDB

Extracts a spatial subset of a raster object and returns the result as a BYTEA file.

Syntax

setof record ST_AsDatasetFile(raster raster_obj,
                              box extent,
                              integer pyramidLevel default 0,
                              cstring bands default '',
                              cstring format default 'GTiff',
                              cstring create_option default '{}',
                              cstring process_option default '{}',
                              out ext cstring,
                              out data bytea);

Parameters

Parameter	Description
`raster_obj`	The raster object to extract data from.
`extent`	The spatial extent of the data to extract. By default, coordinates are interpreted as geographic coordinate system (GCS) values. To use pixel coordinates instead, set `rast_coord` to `true` in `process_option`.
`pyramidLevel`	The pyramid level to extract data from. Valid values start from `0`. Default: `0`.
`bands`	The band IDs to extract. Band IDs start from `0`. Accepts a range (`'0-2'`) or a comma-separated list (`'1,2,3'`). Default: `''` (all bands).
`format`	The output file format. Must be a format whose `can_asfile` flag is `true` in ST_RasterDrivers. Default: `GTiff`.
`create_option`	Creation options for the output dataset, as a JSON string. Valid values come from the `create_options` field in ST_RasterDrivers. Default: `'{}'`.
`process_option`	Processing options for the extraction, as a JSON string. Default: `'{}'`. Set `rast_coord` to `true` to interpret `extent` as pixel coordinates, where the x-coordinate is the column number and the y-coordinate is the row number (both starting from `0`).
`ext` (OUT)	The format of the output file. Supported values: `TIF` and `XML`.
`data` (OUT)	The extracted file content as BYTEA.

Usage notes

The output format specified in format must correspond to a Ganos driver with can_asfile set to true. To check which drivers are supported, query ST_RasterDrivers.
The default cache size for extracted data is 100 MB. To adjust the limit, set the ganos.raster.clip_max_buffer_size parameter.

Examples

All examples query raster_table for the row with id = 1. Each example builds on the previous one by adding parameters.

Extract by extent

SELECT ST_AsDatasetFile(raster_obj,
                        '(-180,-90), (0,0)'::Box)
FROM raster_table
WHERE id = 1;

Extract from a specific pyramid level

SELECT ST_AsDatasetFile(raster_obj,
                        '(-180,-90), (0,0)'::Box,
                        1)
FROM raster_table
WHERE id = 1;

Extract specific bands

SELECT ST_AsDatasetFile(raster_obj,
                        '(-180,-90), (0,0)'::Box,
                        1,
                        '0-2')
FROM raster_table
WHERE id = 1;

Save as GIF

SELECT ST_AsDatasetFile(raster_obj,
                        '(-180,-90), (0,0)'::Box,
                        1,
                        '0-2',
                        'GIF')
FROM raster_table
WHERE id = 1;

Apply creation options (GTiff with DEFLATE compression)

SELECT ST_AsDatasetFile(raster_obj,
                        '(-180,-90), (0,0)'::Box,
                        1,
                        '0-2',
                        'GTiff',
                        '{"blockxsize":256, "blockysize":256, "compress": "DEFLATE"}')
FROM raster_table
WHERE id = 1;

Use pixel coordinates for the extent

-- rast_coord:true interprets extent as pixel coordinates (column number, row number), both starting from 0.
SELECT ST_AsDatasetFile(raster_obj,
                        '(0,0), (100,100)'::Box,
                        1,
                        '0-2',
                        'GTiff',
                        '{}',
                        '{"rast_coord":"true"}')
FROM raster_table
WHERE id = 1;