2

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;
1
  • I think you can probably use ST_Histogram for this. There are various forms, but you get the count and the min/max of each bin back, so it should be possible to count all values below a certain number. Having looked at ST_ValueCount again, I can't see why you can't summarize over all the returned values less than something in another subquery? Jul 20, 2018 at 14:10

2 Answers 2

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.

0

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.