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;