I've been fighting this for quite a long time and everytime I think I got it, I got it wrong actually.

What's the trouble

See picture below. As a result of line buffers I got overlaps that need to be removed on buffer ends. I'm looking for a way to tell PostGIS which overlaps should be differed from which buffer. I ended up with topological incorrect polygons several times.

If you wish, grab the table dump.

enter image description here

I get all the overlaps with this query:

        DISTINCT (ST_Dump(ST_Intersection(a.geom, ST_Union(b.geom)))).geom geom
        FROM test a, test b
        WHERE a.geom && b.geom
            AND ST_Intersects(a.geom, b.geom)
            AND NOT ST_Equals(a.geom, b.geom)
        GROUP BY a.geom

No idea how to proceed.

UPDATE I need to remove overlaps so buffers face each other just fine with no white space. E.g. if two buffers overlap, the overlapping portion of space should be removed just from the one of those two.

  • Are you wanting to remove all overlaps leaving white space?
    – artwork21
    Mar 16, 2015 at 13:02
  • @artwork21 question updated Mar 16, 2015 at 13:06
  • Have you looked at using the v.clean Grass method via QGIS Processing (menu)>Commander (grass.osgeo.org/grass65/manuals/v.clean.html)?
    – artwork21
    Mar 16, 2015 at 14:09
  • I have. It gives erroneous results and invalid geometries in some cases and cannot be considered robust. Mar 16, 2015 at 14:14

1 Answer 1


My dirty brute force solution

DO $$
DECLARE r input;
   FOR r IN
      SELECT * FROM input

         IF EXISTS (SELECT 1 FROM output WHERE output.wkb_geometry && r.wkb_geometry)
            r.wkb_geometry := ST_Multi(ST_CollectionExtract(ST_Difference(r.wkb_geometry, ST_Union(output.wkb_geometry)), 3))
            FROM output
            WHERE output.wkb_geometry && r.wkb_geometry;
         END IF;

         INSERT INTO output values(r.*);
      END LOOP;
END $$


An there is a link https://boundlessgeo.com/2014/10/postgis-training-creating-overlays/ for pretty solution by Paul Ramsey.


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.