PostgreSQL - Array of overlapping Polygon Ids

Question

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.

You should probably start by validating your virtual table query. Does it return what you're expecting? — Vince
– Vince, Commented Jun 2, 2019 at 15:56

Jay Laura · Accepted Answer · 2019-06-02 18:08:40Z

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.

answered Jun 2, 2019 at 18:08

Jay Laura

4,5031 gold badge25 silver badges45 bronze badges

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
– John Powell

2019-06-03 09:13:09 +00:00
Commented Jun 3, 2019 at 9:13

Add a comment |

Stack Exchange Network

PostgreSQL - Array of overlapping Polygon Ids

1 Answer 1

Your Answer

Hot Network Questions

PostgreSQL - Array of overlapping Polygon Ids

1 Answer 1

Your Answer

Sign up or log in

Post as a guest

Related

Hot Network Questions