[postgis-users] Raster pixel value

[Tom van Tilburg]

Pierre,

I just ran a test to see the difference between my old method and your 
new method with ST_Clip and ST_SummaryStatsAgg and came up with the 
following:

1. The old way took 38 secs. whereas the new one 46 (100 polygons, 
usually covering part of 1 raster tile).
2. ST_Clip appears to fill up nodatavalues with zero as soon as they 
fall within the polygon. I have been staring a while at your code but 
couldn't really see what caused this. I thought it might be a wrong 
MinPossibleVal but it doesn't seem to be.

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