I have put a first version of ST_Clip in script/plpgsql Pierre
> -----Original Message----- > From: postgis-users-boun...@postgis.refractions.net [mailto:postgis-users- > boun...@postgis.refractions.net] On Behalf Of Tom van Tilburg > Sent: Thursday, December 01, 2011 7:14 AM > To: postgis-users@postgis.refractions.net > Subject: Re: [postgis-users] Raster pixel value > > 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 _______________________________________________ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
