st_union of set of polygons result in polygon with very small holes

Question

I have a problem with a polygon dataset. Due to the way it is produced (union of small Voronois) there are not always nodes at the same position in the common border between two adjacent polygons. When I do a st_union very small holes (can be less than 1 millimeter) appear. (They appear to be lines, but actually are polygons). This dataset is exported to other GIS environments (MapInfo) and they have the same problems with a geographical union.

I know that the polygons themselves are topological correct. But the constraint 100% adjacent polygons is not met. So my aim is to clean up this dataset to create a 100% adjacent polygon dataset. But alas, so far no succes.

Some remarks:

• I use PostgreSQL 12 and PostGIS 3.0 and the improved precision of PostGIS 3.0 helps (on screen they look perfect), but does not solve this issue.
• Within PostGIS I can do a st_union with a small buffer. That resolves my problems, but I make this for users with other GIS tools. So that's no solution.
• I tried the topology extension, but importing failed. due to the lack of adjacency I think based on the error messages.
• I tried st_simplify, st_snap, st_removerepeatedpoints and st_snaptogrid, and they all solve some issues, and produce other issues. So they are not robust solutions.
• I also tried to replace the geometry with the st_difference of the geometry (with a small buffer) with the surrounding geometries, and that worked for a big part, but not for a 100%. Due to the buffer mainly, but without the buffer it doesn't work.

So I am a little lost on how to proceed. My last resort is to use ArcGIS or FME, but it would be nice to solve this in PostGIS.

I would love to have a function like st_snapCommonBorder( geom1, geom2, precision ) which creates nodes in the common border of geom1 where there are nodes in the common border of geom2, within a certain precision. (st_snap doesn't create nodes, and st_difference doesn't resolve holes)

Or is there another (robust) solution?

Answer 1

I did some extensive search on the web but I think there is no 'quick fix' I think. So I made my cleanPolygon function. And that works very well.

It implements a two step solution.

Inputs:

1. geometry,
2. surrounding geometry
3. bordersnap precision
4. pointsnapprecision

Step A)
Go from vertex to vertex and check if there is a *surrounding point ‘in between’ with a given bordesnap precision (3) If so: add this point to the geometry exterior ring.

Step B)
Make a **pointdataset from the geometry AND the surrounding geometry Use ST_ClusterDBSCAN() to combine points within some given pointsnap distance (4)

*) This was considerable work because a point could be on within the precision, but also be 'around the corner'.
**) This works because the source pointsdataset is based on the original geometries, and not on the changed geometry.

And this works for me! You can play with the precision parameters to find what works best for the dataset.