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
Query
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
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
ST_Clip
to false to keep the initial rasters extend. – geozelot Mar 26 '18 at 20:40