I have a couple of tables (three) with polygons (landuse coverage in this case). Within each table, there is no overlap, but between tables there might be a lot of overlap.

I would like to combine the polygons from the tables into a single table with polygons with no overlap. To resolve situations where the source tables overlap, I would like to put a priority on each layer, so that polygons from the layer with highest priority is used for the overlap.

One way to visualize what I want to do is drawing the tables on top of each other, discarding already drawn polygons where there is overlap. You could also say I want to merge the three tables into one. (Please inform me if there is some more appropriate terminology to describe what I want to do.)

A big issue in this case is performance: each of the tables contain millions of polygons.

1 Answer 1


You can use ST_Intersects to detect when there is overlap between the geometries of two tables. For instance, the set of polygons returned by this query would be the set you want to insert into a new, "combined" table, assuming that polygons1 is the "higher-priority" table:

SELECT p1.geom
  FROM polygons1 p1, polygons2 p2
 WHERE ST_Intersects(p1.geom, p2.geom);

To insert the remaining polygons of the low-priority polygons2 table, simply take the difference. I use DISTINCT here because you will get back every geometry in polygons2 for each geometry in polygons1 that they do not intersect:

  FROM polygons1 p1, polygons2 p2
 WHERE NOT ST_Intersects(p1.geom, p2.geom);

So, you can either use the first query to identify overlapping low-priority polgyons to delete or use it to find overlapping high-priority polygons to save and insert the difference from the second query.

  • Fair enough, I'm looking through the documentation and I have a question: in order for the overlap to return true, does it have to overlap completely? The dma table would not overlap cleanly with the states table.
    – Minh
    Jul 28, 2015 at 19:54
  • They do not have to overlap completely. From the docs, ST_Intersects "Returns TRUE if the Geometries/Geography "spatially intersect in 2D" - (share any portion of space)." It goes on to say, "For geography -- tolerance is 0.00001 meters (so any points that close are considered to intersect)."
    – Arthur
    Jul 30, 2015 at 12:41

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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