1

I would like to eliminate overlapping polygons from a table that is often updated with new polygons, but only if a certain attribute has a higher value. If this higher-priority polygon only partly overlaps the lower-priority polygon, only delete the part of the lower-priority polygon that intersects.

As an analogy (not actually my case), let's say we have a table that will contain both U.S. county and state polygons. If I have county (low-priority) polygons for, say, Virginia, and then acquire a state-level (higher-priority) polygon, I would add the state and delete the counties.

On the other hand, if I have a regional (low-priority) polygon, say New England, and add a New Hampshire (high) polygon, punch a hole in the New England polygon, insert New Hampshire, and leave the rest of the New England polygon intact.

0

I would try something like this assuming we're interested only in those geometries that lie inside of some other geometry - these are considered as higher valued.

UPDATE polygons
-- adjust geometry
SET wkb_geometry = new_polygon
FROM (
    SELECT
        p1.id, 
        -- take the difference of the geometries
        ST_Difference(p1.wkb_geometry, p2.wkb_geometry) new_polygon
    FROM
        polygons p1,
        polygons p2
    WHERE
        -- consider only geometries inside others
        ST_Within(p1.wkb_geometry, ST_Buffer(p2.wkb_geometry, 0.1)) AND
        -- do not account for the same items
        p1.id <> p2.id
) sub
WHERE polygons.id = sub.id;

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.