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;