[postgis-users] ST_SummaryStats

[Simon Greener]

Silly me, i can st_union the rows before input into st_summarystats.
Simon

⁣39 Cliff View Drive
Allens Rivulet Tas 7150
(W) 0418 396 391​

On 3 Jul 2023, 10:29, at 10:29, Simon SPDBA Greener <simon at spdba.com.au> wrote:
>Following the documentation I have crafted some GDAL to load, and SQL
>to 
>query, some rasters.
>
>I note that :
>
>1. If, when loading using GDAL, I don't tile the rasters I get a table 
>with a single row (toddriver_2019_2p5m)
>
>2. If, when loading using GDAL, I specify a tile size (eg -t 256*256) I
>
>get a table with multiple rows (toddriver_2019_3p0m)
>
>Now if I execute ST_SummaryStats against 1 I get a single row which 
>looks correct.:
>
>WITH data AS (
>   SELECT '1' as band,
>          'SRID=4283;POLYGON ((133.84747311077376 -23.74668364533433, 
>133.84747311077376 -23.7409001580403, 133.85607301067603 
>-23.7409001580403, 133.85607301067603 -23.746683645334333, 
>133.84747311077376 -23.74668364533433))'::geometry as geom
>)
>SELECT (stats).sum   as sum,
>        (stats).mean  as mean,
>        (stats).stddev as stddev,
>        (stats).min   as min,
>        (stats).max   as max,
>        (stats).count as count
>    FROM (SELECT 
>ST_SummaryStats(ST_Clip(ST_Band(raster,d.band),geom,true)) As stats
>            FROM gis.toddriver_2019_2p5m
>                 INNER JOIN data as d
>                 ON ST_Intersects(d.geom,ST_Band(raster,d.band))
>         ) As foo;
>
>If I execute the above against 2) I get multiple summary statistics 
>rows, one for each of the tiles in the table.
>
>To return the summary stats for 2 across all rows I did the following:
>
>WITH data AS (
>   SELECT '1' as band,
>          'SRID=4283;POLYGON ((133.84747311077376 -23.74668364533433, 
>133.84747311077376 -23.7409001580403, 133.85607301067603 
>-23.7409001580403, 133.85607301067603 -23.746683645334333, 
>133.84747311077376 -23.74668364533433))'::geometry as geom
>)
>SELECT sum((stats).sum)   as sum,
>        avg((stats).mean)  as mean,
>        sqrt(sum((stats).stddev*(stats).stddev)/min(groups)) as stddev,
>        min((stats).min)   as min,
>        max((stats).max)   as max,
>        sum((stats).count) as count
>   FROM (SELECT row_number() over () as groups,
>ST_SummaryStats(ST_Clip(ST_Band(raster,d.band),geom,true)) As stats
>           FROM gis.toddriver_2019_3p0m as t
>                INNER JOIN data as d
>                ON ST_Intersects(d.geom,ST_Band(raster,d.band))
>        ) As f;
>
>I have tested this SQL against tiled and untiled versions of the same
>raster and the result looks correct.
>Even so, I could like confirmed that this approach is correct for tiled
>rasters?
>
>regards
>Simon
>
>-- 
>Simon Greener
>39 Cliff View Drive, Allens Rivulet, 7150, Tasmania, Australia
>(m) +61 418 396 391
>(w)www.spdba.com.au
>(m)simon at spdba.com.au	
>
>
>------------------------------------------------------------------------
>
>_______________________________________________
>postgis-users mailing list
>postgis-users at lists.osgeo.org
>https://lists.osgeo.org/mailman/listinfo/postgis-users