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