In a table where many of the geometries might be completely covered by other ones, is there a query that selects only geometries that are not covered by any other geometry in the same table (excluding the geometry itself of course)?

Each geometry has properties that should be returned too, so no new geometry should be created.

  • Just to clarify... do you want a) geometries that are completely uncovered by others, b) have a part not covered by any other geometries c) are not covered by only one other polygon (but could be covered by 2 or more geometries)?
    – mlinth
    May 30 '16 at 12:23
  • Said in other words, I would like to discard all geometries which are completely covered by any other polygon from the result set
    – a1an
    May 30 '16 at 12:43

I assume you have a primary id column, and a geometry column.

This could be slow if you have lots of records, or the geometries are complicated, but by using the && in the first join it should use indexes and be reasonably fast.

The logic is like this:

  • For each geometry,get the union of all the other geometries
  • For the above, get the geometries that are completely within the corresponding union. These are the ones you don't want
  • Do a left join on the above to get what you do want

    with unions as
        (select a.id, st_union(b.geometry) as geometry  from mytable a
        inner join mytable b
        on a.id <> b.id AND a.geometry && b.geometry
        group by a.id),covered as
        (select m.id from mytable m
        inner join unions u on st_covers(u.geometry,m.geometry) and m.id = u.id)
        SELECT m.* from mytable m
        LEFT JOIN covered c on c.id = m.id
        WHERE c.id is null

Note that if you have two identical geometries they will count as covering each other...

If you're only interested in geometries covered by only one other, this should be quicker...

WITH contained_ids as
(select a.id from mytable a
    inner join mytable b
    on st_covers(b.geometry,a.geometry) and a.id <> b.id)
SELECT g.* from mytable g 
LEFT JOIN contained_ids c on g.id = c.id
  • Performance is indeed slow but it gives the desired results!
    – a1an
    May 30 '16 at 13:44
  • Just be aware that that first query might give you unexpected results e.g. if a polygon is covered by two polygons rather than only one. The second query should do what you want and it will be faster...
    – mlinth
    May 30 '16 at 17:31
  • I have several polygons covered by two or more polygons and it seems to work pretty fine also in that case. I want to remove all geometries covered by at least one other which is not exactly equivalent. Uncovered duplicate geometries should either be kept or (ideally) grouped and filtered according to a numerical property such as height and only the topmost should be kept).
    – a1an
    May 31 '16 at 15:26
  • I'd get rid of the duplicates first. You can do this with e.g. SELECT DISTINCT ON (geometry) id from mytable order by geometry,id
    – mlinth
    Jun 1 '16 at 8:39
  • 1
    I had a better look at select distinct on and it seems to be the right solution, just ordering on the other numerical attribute instead of id! Also group by geometry seems to work with good performances on duplicate geometries
    – a1an
    Jun 1 '16 at 13:49

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.