PostGIS

ST_ValueCount
Prev	12.9. Raster Band Statistics and Analytics	Next

ST_ValueCount

12.9. Raster Band Statistics and Analytics

Name

ST_ValueCount — Returns a set of records containing a pixel band value and count of the number of pixels in a given band of a raster (or a raster coverage) that have a given set of values. If no band is specified defaults to band 1. By default nodata value pixels are not counted. and all other values in the pixel are output and pixel band values are rounded to the nearest integer.

Synopsis

record +`*`+ST_ValueCount*(`setof raster `rast, integer nband=1, boolean exclude_nodata_value=true, double precision[] searchvalues=NULL, double precision roundto=0, double precision OUT value, integer OUT count`)`;

record +`*`+ST_ValueCount*(`setof raster `rast, integer nband, double precision[] searchvalues, double precision roundto=0, double precision OUT value, integer OUT count`)`;

record +`*`+ST_ValueCount*(`setof raster `rast, double precision[] searchvalues, double precision roundto=0, double precision OUT value, integer OUT count`)`;

bigint +`*`+ST_ValueCount*(`setof raster `rast, double precision searchvalue, double precision roundto=0`)`;

bigint +`*`+ST_ValueCount*(`setof raster `rast, integer nband, boolean exclude_nodata_value, double precision searchvalue, double precision roundto=0`)`;

bigint +`*`+ST_ValueCount*(`setof raster `rast, integer nband, double precision searchvalue, double precision roundto=0`)`;

setof record +`*`+ST_ValueCount*(`text `rastertable, text rastercolumn, integer nband=1, boolean exclude_nodata_value=true, double precision[] searchvalues=NULL, double precision roundto=0, double precision OUT value, integer OUT count`)`;

setof record +`*`+ST_ValueCount*(`text `rastertable, text rastercolumn, double precision[] searchvalues, double precision roundto=0, double precision OUT value, integer OUT count`)`;

setof record +`*`+ST_ValueCount*(`text `rastertable, text rastercolumn, integer nband, double precision[] searchvalues, double precision roundto=0, double precision OUT value, integer OUT count`)`;

bigintST_ValueCount(`text `rastertable, text rastercolumn, integer nband, boolean exclude_nodata_value, double precision searchvalue, double precision roundto=0`)`;

bigint +`*`+ST_ValueCount*(`text `rastertable, text rastercolumn, double precision searchvalue, double precision roundto=0`)`;

bigint +`*`+ST_ValueCount*(`text `rastertable, text rastercolumn, integer nband, double precision searchvalue, double precision roundto=0`)`;

Description

Returns a set of records with columns value count which contain the pixel band value and count of pixels in the raster tile or raster coverage of selected band.

If no band is specified nband defaults to 1. If no searchvalues are specified, will return all pixel values found in the raster or raster coverage. If one searchvalue is given, will return an integer instead of records denoting the count of pixels having that pixel band value

If exclude_nodata_value is set to false, will also count pixels with no data.

Availability: 2.0.0

Examples

UPDATE dummy_rast SET rast = ST_SetBandNoDataValue(rast,249) WHERE rid=2;
--Example will count only pixels of band 1 that are not 249. --

SELECT (pvc).*
FROM (SELECT ST_ValueCount(rast) As pvc
    FROM dummy_rast WHERE rid=2) As foo
    ORDER BY (pvc).value;

 value | count
-------+-------
   250 |     2
   251 |     1
   252 |     2
   253 |     6
   254 |    12

-- Example will coount all pixels of band 1 including 249 --
SELECT (pvc).*
FROM (SELECT ST_ValueCount(rast,1,false) As pvc
    FROM dummy_rast WHERE rid=2) As foo
    ORDER BY (pvc).value;

 value | count
-------+-------
   249 |     2
   250 |     2
   251 |     1
   252 |     2
   253 |     6
   254 |    12

-- Example will count only non-nodata value pixels of band 2
SELECT (pvc).*
FROM (SELECT ST_ValueCount(rast,2) As pvc
    FROM dummy_rast WHERE rid=2) As foo
    ORDER BY (pvc).value;
 value | count
-------+-------
    78 |     1
    79 |     1
    88 |     1
    89 |     1
    96 |     1
    97 |     1
    98 |     1
    99 |     2
   112 |     2
:

--real live example.  Count all the pixels in an aerial raster tile band 2 intersecting a geometry
-- and return only the pixel band values that have a count > 500
SELECT (pvc).value, SUM((pvc).count) As total
FROM (SELECT ST_ValueCount(rast,2) As pvc
    FROM o_4_boston
        WHERE ST_Intersects(rast,
            ST_GeomFromText('POLYGON((224486 892151,224486 892200,224706 892200,224706 892151,224486 892151))',26986)
             )
        ) As foo
    GROUP BY (pvc).value
    HAVING SUM((pvc).count) > 500
    ORDER BY (pvc).value;

 value | total
-------+-----
    51 | 502
    54 | 521

-- Just return count of pixels in each raster tile that have value of 100 of tiles that intersect  a specific geometry --
SELECT rid, ST_ValueCount(rast,2,100) As count
    FROM o_4_boston
        WHERE ST_Intersects(rast,
            ST_GeomFromText('POLYGON((224486 892151,224486 892200,224706 892200,224706 892151,224486 892151))',26986)
             ) ;

 rid | count
-----+-------
   1 |    56
   2 |    95
  14 |    37
  15 |    64

PostGIS

Name

Synopsis

Description

Examples

See Also