3

I want to dissolve a polygon data set in POSTGIS. So far I've read about some different approaches, e. g. Is there a dissolve function in PostGIS other than st_union? When I run one of the following queries: 

SELECT (ST_DUMP(ST_UNARYUNION(ST_COLLECT(geom)))).geom from myTable;
SELECT (ST_DUMP(ST_UNION(geom))).geom from myTable;

most, but not all features are dissolved. Here an example how it looks like before dissolving: enter image description here

And after dissolving:enter image description here

On the left hand side, dissolving was successful, on the right it wasn't.
Does anybody have an idea why this might happen? I want to dissolve all neighboring polygons.

EDIT: The problem becomes awkward. When I Buffer the Polygons before dissolving, the problematic polygons are still not dissolved: enter image description here

I really can't explain this. The Polygons stem from a ST_DumpAsPolygons operation on a raster..

Improve this question
14
  • Did this data come from a shp file? I have seen this a lot where there are these vertical or horizonatal lines running through polygons, which interferes with unioning. One solution I have tried that might work is to buffer by a small amount (this will obviously depend on the CRS you are using), run ST_Union, and then apply a negative buffer of the same amount. Once those line features have been dissolved, there is no way for a negative buffering to bring them back.
    – John Powell
    Commented Jul 10, 2017 at 8:13
  • Thanks, I will try to buffer! The data is polygonized from a raster in POSTGIS using ST_DumpAsPolygons. The lines run through the tiles of the raster.
    – David Hanimann
    Commented Jul 10, 2017 at 8:18
  • It might be that not all of the polygons are properly closed, and you have some line features (or worse, geometry collections) on the edges. Have a look at ST_GeometryType of the input geometries. You might have to eliminate any non-polygons before proceeding.
    – John Powell
    Commented Jul 10, 2017 at 9:17
  • The geometries are all Polygons and valid. I checked using ST_GeometryType and ST_IsValid.
    – David Hanimann
    Commented Jul 10, 2017 at 10:02
  • Is the line the 180 meridian?
    – JGH
    Commented Jul 10, 2017 at 12:05

1 Answer 1

Reset to default
3

Here my answer in order to complete this question:

I tried to polygonize a raster data set consisting of many tiles. When doing so, it is necessary to dissolve the resulting polygons, since the ST_DumpAsPolygon function dissolves tile by tile, which leads to cut polygons when overlapping tiles.
Now why does the dissolving not work properly? Probably this is due to the nature of raster vs vector data. Raster geometry is stored implicitly (pixel size), where vectors have explicit coordinates assigned. We all agree that the earth is not flat and hence a regular grid with evenly sized quadratic pixels can only approximate earth surface. Raster tiles do not always really touch each other. There can be an offset of some 0.0...001 degrees. So although you might not see this offset in a GIS like QGIS, the Dissolve algorithm detects this offset and doesn't regard such polygons as touching.
The simple solution to this problem is to buffer and unbuffer the polygons when dissolving. Here a working example: 

select st_buffer(
         (st_dump(
           st_union(
             st_buffer(geom, 0.0000000001)))).geom, -0.0000000001) 
from table;

Thank you @John Powell aka Barça!

Just be aware, that this solution also dissolves polygons that touch in only one vertex!

Improve this answer

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.