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

[Andreas Forø Tollefsen]

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

[Pierre Racine]

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

[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...@sbf.ulaval.ca>:

[Pierre Racine]

> Are the SummarystatsAgg and AreaWeightedSummaryStats included in the latest
> trunk or should i install from plpgsql?

That's what I forgot: They are not part of rtpostgis.sql... They are in scripts/plpgsql.

Pierre

[Andreas Forø Tollefsen]

Ok. Thank you.

Andreas

2012/3/6 Pierre Racine <Pierre...@sbf.ulaval.ca>:

[Lucas Ferreira Mation]

POSTGIS begginer here: 

I think I'm having a similar problem as described in this post. I tryed following Pierre's sucgestion (second post), but I'm not able to find or install the 

SummarystatsAgg

 and 

AreaWeightedSummaryStats

functions. 

how do I do that?

tks

Lucas

[Pierre Racine]

Run the whole postgis_addons.sql like a sql command in pgAdmin.

> postgi...@postgis.refractions.net <javascript:>
> http://postgis.refractions.net/mailman/listinfo/postgis-users

> <http://postgis.refractions.net/mailman/listinfo/postgis-users>
>
>
>

_______________________________________________
postgis-users mailing list

postgi...@lists.osgeo.org
http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users