2

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.
  • 1
    You should probably start by validating your virtual table query. Does it return what you're expecting? – Vince Jun 2 at 15:56
2

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.

  • You will probably get it to run much faster if you put a WHERE ST_Intersects(overlay.geom, images.geom) in the inner query, otherwise you will be calculating the intersection for all combinations of rows from both tables. – John Powell Jun 3 at 9:13

Your Answer

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

Not the answer you're looking for? Browse other questions tagged or ask your own question.