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
add comment

1 Answer

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:

SELECT DISTINCT p1.geom
  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
add comment

Your Answer

 
discard

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.