ST_Statistics Overview: Raster Band Analysis in PolarDB - PolarDB

Returns band statistics for a raster object. Two variants are available: one that returns compact JSON statistics for a single band, and one that returns per-range statistics filtered by a geometry area.

Syntax

Syntax 1 — returns JSON statistics for a single band:

TEXT ST_Statistics(raster raster_obj, integer band);

Syntax 2 — returns a set of records with per-range statistics, filtered by a geometry object:

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 object.
`band`	integer	0	The band index. Band numbering starts at 0.
`geom`	geometry	—	The geometry object used to filter the statistics area. If the geometry has an M value, that value specifies the weight of each raster pixel in the calculation.
`stats_range`	cstring	`''`	The pixel value ranges to compute statistics for. Specify one or more numeric breakpoints separated by commas. Use `(` / `)` for exclusive bounds and `[` / `]` for inclusive bounds. The default `''` produces no range segmentation — only the `full` row is returned. For example, `(0, 10, 20, 100]` defines three ranges: `(0–10]`, `(10–20]`, and `(20–100]`. See stats_range syntax for details.
`rast_coord`	boolean	`true`	Specifies whether to interpret the geometry coordinates as pixel coordinates.

Output fields (Syntax 2)

Field	Type	Description
`name`	cstring	The range label. `full` covers all pixels; named ranges use the boundary notation, for example `(300, 400]`.
`band`	integer	The band index.
`min`	float8	The minimum pixel value.
`max`	float8	The maximum pixel value.
`mean`	float8	The mean (arithmetic average) pixel value.
`sum`	float8	The sum of all pixel values.
`count`	float8	The number of pixels counted.
`std`	float8	The standard deviation of pixel values.
`median`	float8	The median pixel value.
`mode`	float8	The mode (most frequent pixel value).

Description

Syntax 1 returns band statistics as a JSON object. If no statistics have been computed for the band, the function returns null.

Syntax 2 returns one record per range defined in stats_range, plus one full record covering all pixels. Use a geometry object to restrict the calculation to a specific area. If the geometry has an M value, that value acts as a per-pixel weight in the statistical calculations.

stats_range syntax

Specify numeric breakpoints separated by commas. Each adjacent pair of breakpoints defines one range. Use ( or ) for exclusive bounds (greater than / less than) and [ or ] for inclusive bounds (greater than or equal to / less than or equal to).

Symbol	Meaning
`(`	Greater than
`)`	Less than
`[`	Greater than or equal to
`]`	Less than or equal to

For example, (0, 10, 20, 100] produces three ranges: (0–10], (10–20], and (20–100]. The result includes one row per range plus a full row for all pixels.

Examples

Get JSON statistics for band 0

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 a MultiPoint geometry — no range segmentation

All three points use pixel coordinates. The M values (10, 50, 100) specify per-point 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 statistics for a MultiPoint geometry — with range segmentation

Specifying stats_range produces one row per range in addition to the full row. Ranges with no matching pixels show empty values.

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 statistics for a polygon area — with range segmentation

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)