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
[email protected]
http://postgis.refractions.net/mailman/listinfo/postgis-users
_______________________________________________
postgis-users mailing list
[email protected]
http://postgis.refractions.net/mailman/listinfo/postgis-users
