[postgis-users] Summary Statistics (vector, raster)

[Andreas Forø Tollefsen]

Hi Pierre,

Thank you so much.
Are the SummarystatsAgg and AreaWeightedSummaryStats included in the
latest trunk or should i install from plpgsql?

best,
Andreas

2012/3/6 Pierre Racine <Pierre.Racine at sbf.ulaval.ca>:
> In raster space, on a tiled raster coverage (equivalent to your second query but a bit more simple):
>
> SELECT gt. id, (ST_SummaryStatsAgg(ST_Clip(rt.rast, gt. geom, true)).*
> FROM rasttable rt, geomtable gt
> WHERE ST_Intersects(rt.rast, gt.the_geom)
> GROUP BY gt.id
>
> In raster space, on a non-tiled raster coverage:
>
> SELECT gt. id, (ST_SummaryStats(ST_Clip(rt.rast, gt. geom, true)).*
> FROM rasttable rt, geomtable gt
>
> In vector space (more precise, works better with big pixels and small intersecting areas and points and lines, has to be done on a tiled raster coverage):
>
> SELECT (ST_AreaWeightedSummaryStats(gv)).*
> 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
>
> Pierre
>
>> -----Original Message-----
>> From: postgis-users-bounces at postgis.refractions.net [mailto:postgis-users-
>> bounces at postgis.refractions.net] On Behalf Of Andreas Forø Tollefsen
>> Sent: Tuesday, March 06, 2012 4:12 AM
>> To: PostGIS Users Discussion
>> Subject: [postgis-users] Summary Statistics (vector, raster)
>>
>> Hi all,
>>
>> I have used a number of different queries to find the mean raster value within a
>> polygon.
>> However, this is beginning to be outdated i guess since functions have been
>> changing.
>> What is the quickest way to summarize mean raster values within polygons?
>>
>> I used these queries before where lspop is the raster and groupclusters is the
>> polygon:
>>
>> SELECT a.clusterid,
>> (ST_SummaryStats((ST_Union(ST_MapAlgebraExpr(ST_AsRaster(a.the_geom,
>> b.rast, '32BF'), b.rast, 'rast2', '32BF','INTERSECTION','0','0',0))),
>> true)).* FROM groupclusters a LEFT JOIN lspop b ON ST_Intersects(a.the_geom,
>> b.rast) WHERE a.clusterid = 4422 GROUP BY a.clusterid ORDER BY a.clusterid;
>>
>> A different query:
>>
>> SELECT clusterid, (ss).* FROM (
>> SELECT clusterid, ST_SummaryStatsAgg(gv) ss FROM ( SELECT gt.clusterid,
>> ST_Clip(rt.rast, gt.the_geom) gv FROM lspop rt, groupclusters gt WHERE
>> ST_Intersects(rt.rast, gt.the_geom) AND
>> (gt.type=1 OR gt.type=3 OR gt.type=6) AND gt.endyear>=1990
>>               ) foo
>>               WHERE clusterid = 4422
>>  GROUP BY clusterid
>>               ) foo2;
>>
>> Are there any other ways of doing this?
>> What is the PostGIS official raster summary statistics query for finding raster
>> values within polygons?
>>
>> Best regards,
>>
>> Andreas
>> _______________________________________________
>> postgis-users mailing list
>> postgis-users at postgis.refractions.net
>> http://postgis.refractions.net/mailman/listinfo/postgis-users
> _______________________________________________
> postgis-users mailing list
> postgis-users at postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-users