I am looking for a PostGIS equivalent to the ArcGIS Zonal Statistics by Table function.
I have two rasters. I would like to group the data in the second raster by the values in the first raster, and report the summary statistics (count, sum, etc.) as a table. There are questions for grouping raster data by polygons, but I don't see anything relevant to grouping by rasters.
The desired result is produced by this query, but I am working with a global 30-arc second resolution raster, and would like to avoid dumping all the values. Note that the two rasters are aligned and the rids match for intersecting tiles. (I assume that unnest preserves the order of arrays - is this correct?)
SELECT id, sum(val), count(val) from ( SELECT unnest(ST_DumpValues(r1.rast,1)) id, unnest(ST_DumpValues(r2.rast,1)) val FROM rast1 r1, rast2 r2 WHERE r1.rid=r2.rid) t1 GROUP BY id;