In PostGIS: How to delete completely overlapping polygons?

Question

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.

Answer 1

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;