Query raster object statistics with ST_Statistics - ApsaraDB RDS

Returns band-level statistics for a raster band as a JSON object (syntax 1), or as a set of records scoped to a geometry (syntax 2).

Syntax

Syntax 1 — returns statistics for a single band as JSON:

TEXT ST_Statistics(raster raster_obj, integer band);

Syntax 2 — returns per-range statistics for pixels that intersect a geometry:

SETOF RECORD ST_Statistics(
    raster  raster_object,
    geometry geom,
    integer  band          DEFAULT 0,
    cstring  stats_range   DEFAULT '',
    boolean  rast_coord    DEFAULT true,
    OUT cstring  name,
    OUT integer  band,
    OUT float8   min,
    OUT float8   max,
    OUT float8   mean,
    OUT float8   sum,
    OUT float8   count,
    OUT float8   std,
    OUT float8   median,
    OUT float8   mode
);

Parameters

Input parameters

Parameter	Type	Default	Description
`raster_obj` / `raster_object`	`raster`	—	The raster to analyze.
`band`	`integer`	`0`	The band number, starting from `0`.
`geom`	`geometry`	—	The geometry that defines the region of interest. If the geometry includes an M value, that value is used as the pixel weight.
`stats_range`	`cstring`	`''`	The pixel value ranges for which to compute statistics. Specify one or more boundary values separated by commas. An empty string means no range filter—only the `full` row (all pixels) is returned. See stats_range syntax for details.
`rast_coord`	`boolean`	`true`	When `true`, interprets the geometry coordinates as pixel coordinates. When `false`, uses the raster's spatial reference system.

Output fields (syntax 2 only)

Field	Type	Description
`name`	`cstring`	The range label. `full` covers all pixels; named ranges use boundary notation, such as `(0-10]`.
`band`	`integer`	The band number.
`min`	`float8`	Minimum pixel value in the range.
`max`	`float8`	Maximum pixel value in the range.
`mean`	`float8`	Mean pixel value in the range.
`sum`	`float8`	Sum of pixel values in the range.
`count`	`float8`	Number of pixels in the range.
`std`	`float8`	Standard deviation of pixel values in the range.
`median`	`float8`	Median pixel value in the range.
`mode`	`float8`	Most frequent pixel value in the range.

Description

Syntax 1 computes statistics for a single band and returns them as a JSON object with the keys min, max, mean, std, and approx. Returns NULL if no statistics are available for the band.

Syntax 2 computes statistics for the pixels of a raster that fall within the specified geometry. If the geometry has an M value, it is used as a weighting factor per pixel. The function always returns a full row covering all matched pixels, plus one row per range defined in stats_range.

A range defined in stats_range with no matching pixels still appears in the output with empty values.

stats_range syntax

stats_range accepts a comma-separated list of boundary values with open/closed interval notation at the start and end of the string:

Symbol	Meaning
`(`	Greater than (exclusive lower bound)
`)`	Less than (exclusive upper bound)
`[`	Greater than or equal to (inclusive lower bound)
`]`	Less than or equal to (inclusive upper bound)

For example, (0, 10, 20, 100, 1000] defines the ranges (0-10], (10-20], (20-100], and (100-1000].

The default value '' (empty string) disables range filtering. The function returns only the full row, which covers all pixels in the geometry.

Examples

Get band statistics as JSON

Returns statistics for band 0 of the raster with id=1.

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

Output:

'{"min" : 0.00, "max" : 255.00, "mean" : 125.00, "std" : 23.123, "approx" : false}'

Get statistics for pixels intersecting a MultiPoint geometry

Returns statistics for band 0 of pixels intersecting the three points. The M values (10, 50, 100) specify the pixel weights.

SELECT (ST_Statistics(
    raster_obj,
    'MultiPoint((0 0 10), (100 100 50), (199 199 100))'::geometry,
    0
)).*
FROM raster_table
WHERE id = 1;

Output:

 name | band | min | max |   mean   |  sum  | count |       std        | median | mode
------+------+-----+-----+----------+-------+-------+------------------+--------+------
 full |    2 |  47 | 196 | 140.3125 | 22450 |   160 | 71.8955133770529 |     47 |  196

Get per-range statistics for pixels intersecting a MultiPoint geometry

Returns statistics broken down by the four ranges defined in stats_range. The full row always covers all matched pixels.

SELECT (ST_Statistics(
    raster_obj,
    'MultiPoint((0 0 10), (100 100 50), (199 199 100))'::geometry,
    0,
    '(0, 10, 20, 100, 1000]'
)).*
FROM raster_table
WHERE id = 1;

Output:

    name    | band | min | max | mean |  sum  | count |       std        | median | mode
------------+------+-----+-----+------+-------+-------+------------------+--------+------
 full       |    0 |   1 | 202 |   82 |   246 |     3 | 86.5678924313166 |    202 |    1
 (0-10]     |    0 |   1 |   1 |    1 |     1 |     1 |                0 |      1 |    1
 (10-20]    |    0 |     |     |      |       |       |                  |        |
 (20-100]   |    0 |  43 |  43 |   43 |    43 |     1 |                0 |     43 |   43
 (100-1000] |    0 | 202 | 202 |  202 |   202 |     1 |                0 |    202 |  202

Get per-range statistics for pixels inside a polygon

Returns per-range statistics for pixels of rast inside the specified polygon, using band 1.

SELECT id, (ST_Statistics(
    rast,
    ST_geomfromtext('POLYGON((50 50, 55 50, 55 55, 50 55, 50 50))'),
    1,
    '(0, 10, 20, 100, 1000]'
)).*
FROM raster_table
WHERE id = 1;

Output:

 id |    name    | band | min | max |       mean       |  sum | count |       std        | median | mode
----+------------+------+-----+-----+------------------+------+-------+------------------+--------+------
  3 | full       |    0 |  48 | 103 | 78.1020408163265 | 3827 |    49 | 21.5815916437107 |     97 |   97
  3 | (0-10]     |    0 |     |     |                  |      |       |                  |        |
  3 | (10-20]    |    0 |     |     |                  |      |       |                  |        |
  3 | (20-100]   |    0 |  48 |  97 | 76.4782608695652 | 3518 |    46 | 21.2855729161028 |     97 |   97
  3 | (100-1000] |    0 | 103 | 103 |              103 |  309 |     3 |                0 |    103 |  103
(5 rows)