I am running a simple zonal statistics query in PostGIS. My code is below
DROP TABLE IF EXISTS output_aa; CREATE TABLE output_aa AS SELECT id, geom, (ST_SummaryStats(ST_Union(ST_Clip(rast,geom)),true)).mean FROM raster_aa, polys_aa GROUP BY id, geom;
That works, although it does create a new table, rather than appending to the input polygon table.
But, if I have say 100 rasters in my database and 100 matching polygons tables, how would I best go about looping through all of them?
The raster names increment through a predefined sequence of letter pairs (e.g. AA, AB, AC, BA, BB etc) rather than numbers. The output can be either 100 output polygons tables or a single merged polygons table with all the output polygons in it. I'm still pretty new to PostGIS.
In response to the comments made, I have made an initial stab at the code and got part of the way there.
do $$ declare rst record; out_tn text; begin for rst in (select * from pg_catalog.pg_tables where tablename LIKE 'my_raster_%' ) loop out_tn := 'results_' || rst.tablename; execute format ('drop table if exists %I ',out_tn); execute format ('create table %I as ' 'select id, geom, (ST_SummaryStats(ST_Union(ST_Clip(rast,geom)),true)).mean ' 'from %I, polys ' 'group by id, geom ', out_tn, rst.tablename); end loop; end; $$
This allows me to loop through all the rasters in the database which meet the requirement but only allows me to compare them to a single polygon table.
How do I extend this so that I can match my polygons tables with my rasters?
i.e. so that polys_aa queries my_raster_aa and polys_ab queries my_raster_ab etc.
I have some huge datasets to run, so I don't think it's sensible to try and put all the polygons in a single table.
Am I coming at this from the right perspective?