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..

David Hanimann
  • 128
  • 9
  • 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 Jul 10 '17 at 08: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 Jul 10 '17 at 08: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 Jul 10 '17 at 09:17
  • The geometries are all Polygons and valid. I checked using ST_GeometryType and ST_IsValid. – David Hanimann Jul 10 '17 at 10:02
  • Is the line the 180 meridian? – JGH Jul 10 '17 at 12:05
  • No it's completely random coordinates. The error occurs in many places. – David Hanimann Jul 10 '17 at 12:23
  • It's something I would be happy to look into, but you will have to supply the data. I have used ST_DumpAsPolygons a lot, and dealt with lines across polygons like this before, but it can be a bit fiddly, so rahter than speculate, it would be good to have some real data. – John Powell Jul 10 '17 at 12:51
  • Sure! How can I share the data? There's no built-in functionality in StackExchange as far as I see? – David Hanimann Jul 10 '17 at 14:27
  • Dropbox, Google drive, github, etc. – John Powell Jul 10 '17 at 18:13
  • https://drive.google.com/drive/folders/0Bw8nSsBdx33fZEN4R0lwY1hlakE?usp=sharing – David Hanimann Jul 11 '17 at 08:12
  • @DavidHanimann. Hi David, I am very busy at the moment, but I promise I will give this a look as soon as I can. – John Powell Jul 11 '17 at 09:10
  • Problem solved! @John Powell aka Barca: You're suggestion was right. My fault was to 'unbuffer' the dataset before dumping it. A working example looks like this: select st_buffer((st_dump(st_union(st_buffer(geom, 0.0000000001)))).geom, -0.0000000001) from table; Unbuffering must be done After dumping (not after union). Thanks! – David Hanimann Jul 17 '17 at 14:11
  • Shall I write it up, so we can close the question, as it might help others in the future? Sorry, I was planning to come back to this, but have had an absolutely horrific few days. I am glad my initial suggestion worked. – John Powell Jul 17 '17 at 15:04
  • Yes, I leave it to you to answer the question. Just another input on the issue you may include in your answer: The problem probably originates from the raster to polygon conversion in postgis. Raster tiles are not always touching when looking at the very least digits of the coordinates. While in QGIS this can't be seen, the dissolve operation fails due to this. Thank you again for your help! – David Hanimann Jul 18 '17 at 07:11

1 Answers1

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!

David Hanimann
  • 128
  • 9