Mdthbs

I have a database table (overlay) that has columns, id::int, intersections::int array, geom::polygon. I have another table (images) that has id and geom columns. I am trying to populate the intersections array with the ids of the rows in the images table that intersect (and have an area greater than 0.00001). For example, if intersection A (overlay table) intersects B1, B2, and B3 (images table), I am trying to set the intersection column in the overlay table to {B1, B2, B3}. The query below is what I have come up with to accomplish this goal,

UPDATE public.overlay

SET intersections = array_append(intersections, i.id)

FROM (SELECT geom, id FROM images WHERE images.geom IS NOT NULL) i

WHERE ST_AREA(ST_INTERSECTION(ST_MAKEVALID(overlay.geom), i.geom)) > 0.000001

AND NOT ST_TOUCHES(ST_MAKEVALID(overlay.geom), i.geom)

Right now, the above query is only ever adding a single entry to the intersections column and I am not sure why. I am going to walk through what I think is occurring in the hopes that my mistake is quite evident:

• 
  UPDATE public.overlay <- The table that needs to be updated


• 
  SET intersections = array_append(intersections, i.id) <- use the Postgres array_append function to update the intersections column. This could also be written as SET intersections = intersections || i.id, but that does not result in the desired output.


• 
  FROM (SELECT geom, id FROM images WHERE images.geom IS NOT NULL) i <- The images table has a few entries with NULL geometries, so skip those. The results of the query are pushed into the i variable.


• 
  WHERE ST_AREA(ST_INTERSECTION(ST_MAKEVALID(overlay.geom), i.geom)) > 0.000001 <- Start of the where clause. Here compute the intersection of the overlay geometry and compute the area so slivers can be omitted.


• 
  AND NOT ST_TOUCHES(ST_MAKEVALID(overlay.geom), i.geom) <- The second half of the where clause that omits bordering (touching) polygons. This is redundant, I believe with the area check, but I want to make sure that neighbors are omitted.

Going off the suggestion of @Vince, I was able to get this working using the following:

UPDATE overlay

SET intersections = imgs.iid

FROM (  

        SELECT overlay.id, array_agg(images.id) as iid

        FROM overlay, images

        WHERE images.geom is NOT NULL AND

        ST_AREA(ST_INTERSECTION(overlay.geom, images.geom)) > 0.000001

        GROUP BY overlay.id

    ) AS imgs

WHERE imgs.id = overlay.id;

Since the selection is all at once, an aggregation was the correct approach. The append might be useful in the future if I want to update existing overlaps, but for now, this is a 'one-and-done' operation.