Take the 2-minute tour ×
Geographic Information Systems Stack Exchange is a question and answer site for cartographers, geographers and GIS professionals. It's 100% free, no registration required.

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.

share|improve this question

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.

share|improve this answer
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 Mai Jul 28 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 at 12:41

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

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