All Products
Search
Document Center

ApsaraDB RDS:ST_Statistics

Last Updated:Aug 02, 2023

This topic describes the ST_Statistics function. This function returns the statistics on a raster object at a band.

Syntax

  • Syntax 1

    TEXT ST_Statistics(raster raster_obj, integer band);
  • Syntax 2

    SETOF RECORD ST_Statistics(raster raster_object,
        geometry geom,
        integer band default 0,
        cstring statics_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

Parameter

Description

raster_obj

The raster object.

band

The sequence number of the band, which starts from 0.

geom

The geometry object. If an m value exists, it specifies the weight of the raster.

stats_range

The range of the statistics. The statistics range specifies the original pixel value range and consists of one or more numeric values that are separated by commas (,).

You can specify an open or closed range at the start and end of a range. For example, you can specify a range of [300,400,500) or (300,400,500]. The following list describes the boundary symbols:

  • ( indicates greater than.

  • ) indicates less than.

  • [indicates greater than or equal to.

  • ] indicates less than or equal to.

Default value: (], which indicates an empty range.

rast_coord

Specifies whether the pixel coordinate is used.

name

The name of the range.

  • full: all pixels.

  • Range: (300, 400].

min

The minimum value.

max

The maximum value.

mean

The average value.

sum

The sum.

count

The total number.

std

The standard deviation.

median

The median value.

mode

The mode.

Description

  • Syntax 1: This function returns the statistics on a raster object at a band. The value is in the JSON format. If no statistics exist, the function returns null.

  • Syntax 2: This function returns the statistics on a raster based on a geometry object. If an m value exists for the geometry object, the value specifies the weight of the raster.

Examples

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

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


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

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

-- with range
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;

    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

-- Polygon
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;

 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)