[postgis-users] Raster pixel value

[Pierre Racine]

Andreas, Tom,

I have put two new functions you might be interested by in

http://trac.osgeo.org/postgis/browser/trunk/raster/scripts/plpgsql

The first one, st_areaweightedsummarystats.sql, is usefull when you want to compute summary stats (like the weighted mean) of values coming from a raster table for a series of polygons and that you use ST_Intersection(raster, geometry) to compute the intersection. You normally use it this way:

SELECT gt.id,
       (aws).count, 
       (aws).distinctcount,
       (aws).geom, 
       (aws).totalarea, 
       (aws).meanarea, 
       (aws).totalperimeter, 
       (aws).meanperimeter, 
       (aws).weightedsum, 
       (aws).weightedmean, 
       (aws).maxareavalue, 
       (aws).minareavalue, 
       (aws).maxcombinedareavalue, 
       (aws).mincombinedareavalue, 
       (aws).sum, 
       (aws).mean, 
       (aws).max, 
       (aws).min
FROM (SELECT ST_AreaWeightedSummaryStats(gv) aws
      FROM (SELECT ST_Intersection(rt.rast, gt.geom) gv
            FROM rasttable rt, geomtable gt
            WHERE ST_Intersects(rt.rast, gt.geom)
           ) foo
      GROUP BY gt.id
     ) foo2

The second one, st_summarystatsagg.sql, serve the same purpose but when you are using ST_MapAlgebra() (soon ST_Clip()) to compute the intersection. you normally use it like this:

SELECT (ss).count, 
       (ss).sum, 
       (ss).mean, 
       (ss).min, 
       (ss).max
FROM (SELECT ST_SummaryStatsAgg(gv) ss
      FROM (SELECT ST_Clip(rt.rast, gt.geom) gv
            FROM rasttable rt, geomtable gt
            WHERE ST_Intersects(rt.rast, gt.geom)
           ) foo
      GROUP BY gt.id
     ) foo2

This is very equivalent to what you were doing. The ST_Clip() replace the ST_MapAlgebra() part and the ST_SummaryStatsAgg() replace the ST_SummaryStats(ST_Union())  part. This should be faster since it does a ST_Union() less.

I'm working on a plpgsql version of ST_Clip(). I will post it very soon.

Pierre