I have a multiband population raster and geometry table in postgres. For every polygon, I'm trying to sum the population (rast) that is contained in that polygon (geom).
This is how I originally imported my raster
raster2pgsql -s 4326 -d -I -C -M -R /opt/population.tif -F -t 10x10 public.raster
SELECT gid, SUM((pop_band1).value) AS population_1, SUM((pop_band2).value) as population_2 FROM ( SELECT gid, ST_ValueCount(ST_Union(ST_Clip(rast, 1, ST_Transform(geom, ST_SRID(rast)), 0, TRUE))) as pop_band1, ST_ValueCount(ST_Union(ST_Clip(rast, 2, ST_Transform(geom, ST_SRID(rast)), 0, TRUE))) as pop_band2 FROM raster, polygons WHERE ST_Intersects(geom, rast) GROUP BY 1 ) a GROUP BY 1;
This works when I select specific polygons GIDs, but it breaks when I run it over the entire polygons table.
ERROR: rt_raster_iterator: The set of rasters provided (custom extent included, if appropriate) do not have the same alignment CONTEXT: PL/pgSQL function st_clip(raster,integer,geometry,double precision,boolean) line 8 at RETURN