How to count pixels in a raster below a given value in PostGIS

Question

The code below works in getting the basic statistics of a clipped raster. I would like to add a metric that is the percent of pixels that are below a given value. I am already calculating the total number of valid pixels, but I am missing the number of pixels below a given value. I have tried using ST_ValueCount, however this seems to only give the count of unique values in a band and not the count of all pixels below a given value. Does anyone know of a solution to my problem?

WITH
--  Select Features
    feat AS (SELECT DISTINCT fid AS building_id, wkb_geometry AS geom FROM inside_raster
        WHERE fid = 'osgb1000005284805'
    ),
--  Clip band 1 of raster image to the boundaries of buildings
--  then get statistics for these clipped regions
    b_stats AS
    (SELECT  building_id, (stats).*
        FROM (SELECT building_id, ST_SummaryStats(ST_Clip(a.rast,1,geom,-9999,true)) AS stats
            FROM height_raster a
                INNER JOIN feat
            ON ST_Intersects(feat.geom,rast)
        ) AS foo
    )
--  Summarise statistics
    SELECT building_id, count As pixel_count, 
        min AS min_pixel_val,
        max AS max_pixel_val,
        mean AS avg_pixel_val,
        stddev AS pixel_stddev
    FROM b_stats
    WHERE count > 0;

Answer 1

This may be a rather crude solution, but you can dump all the values using ST_DumpAsPolygons (and then keeping just the values) and count them afterwards :

WITH values as (
        SELECT (ST_DumpAsPolygons(rast)).val AS val
          FROM rast
     )
SELECT Count(*)
  FROM values
 WHERE val < threshold

I'm not sure where that would go within your code in particular, but it should help.

Answer 2

I would first ST_ReClass() all undesired values to NULL and then use ST_SummaryStats(). That should be much faster than ST_DumpAsPolygons() all values.