[postgis-users] Raster pixel value

[Tom van Tilburg]

Pierre,

This is great you're working on this. It is exactly what I think that 
pgraster is going to make a great tool.

Last days I didn't really have time to check out things, hopefully this 
week.

Cheers,
  Tom

On 30-11-2011 19:19, Pierre Racine wrote:
> 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
>
> _______________________________________________
> postgis-users mailing list
> postgis-users at postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-users