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 '15 at 13:02
  • @artwork21 question updated – Michal Zimmermann Mar 16 '15 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 '15 at 14:09
  • I have. It gives erroneous results and invalid geometries in some cases and cannot be considered robust. – Michal Zimmermann Mar 16 '15 at 14:14

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, privacy policy and cookie policy

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