ST_Statistics-AnalyticDB(AnalyticDB)-阿里云帮助中心

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 band number, starting from 0.
geom	The geometry object. If the geometry has M values, the M values represent the corresponding weight information.
stats_range	Describes the statistics ranges, which represent the ranges of original pixel values. A range can consist of one or more numeric values separated by commas (,). The start and end of a range support open and closed interval relationships. Examples: `[300,400,500)` and `(300,400,500]` . The boundary symbols are described as follows: `(` means greater than. `)` means less than. `[` means greater than or equal to. `]` means less than or equal to. Default value: `(]` , which indicates an empty interval.
rast_coord	Specifies whether the coordinates are pixel coordinates.
name	The range name. All pixels: full. Interval range: `(300, 400]` .
min	The minimum value.
max	The maximum value.
mean	The mean value.
sum	The sum.
count	The count.
std	The standard deviation.
median	The median.
mode	The mode.

Description

Syntax 1: Obtains the statistics of a band of a raster object in the JSON format. If no statistics exist, an empty value is returned.
Syntax 2: Obtains the corresponding statistics based on a geometry. If the geometry has M values, the M values represent the corresponding weight information.

Example

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)