2

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
  • I had a similar issue previously. See question and answer here: gis.stackexchange.com/questions/178018/… I think what us happening is that you need to isolate only where an intersection occurs first. – Cushen Mar 25 '18 at 22:33
  • try set the crop option for ST_Clip to false to keep the initial rasters extend. – ThingumaBob Mar 26 '18 at 20:40

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.