4

I need to append the shapefiles to a PostgreSQL database. Once the data gets appended to PostgreSQL, some topology errors like gaps & overlaps can be seen.

By using the v.clean tools the topology errors can be removed. However, as a result, it gives a separate shapefile ("cleaned").

So how can I remove the topology errors in the given table (test1) in PostgreSQL database?

QGIS_window_view

4
  • Do you have access to PostGIS as well?
    – Taras
    Jan 10, 2019 at 8:01
  • yes. I can access the PostGIS. Jan 10, 2019 at 9:14
  • 1
    That's a very interesting question for me as well. From my experience, I could say that such errors can be eliminated only within some interactive program tool. Because no one can guarantee that some automatic procedure does what you want.
    – Vadym
    Jan 10, 2019 at 9:26
  • 1
    I think you may confuse geometries errors (self-intersections etc.) which can be solved with ST_MakeValid() in PostGIS, and logical errors like gaps and overlaps as seen on your image, for which there is no magical function.
    – MaxPlank
    Jan 10, 2019 at 10:25

3 Answers 3

1

You can fix the invalid geometries in PostGIS like that:

UPDATE test1 SET geom = ST_MakeValid(geom) 
WHERE ST_IsValid(geom) = FALSE;

Note that ST_MakeValid might result in some unwanted lower-dimension geometries ( for example your layer is of type MultiPolygon and you get some resulting Point geometries ), in which case your new geometry column type will GeometryCollection, so you might want to force the return of only one type; maybe MultiPolygon in your case:

update test1 set geom = st_multi(st_collectionextract(st_makevalid(geom),3))
where st_isvalid(geom) = false;

PS: Overlaps and gaps/holes are not invalid geometries, you need to decide yourself what to do with them. You can for instance fill or delete holes between polygons, and for overlaps, you can dissolve them, but this depends entirely on your data and purpose of the analysis.

0

As with Quantum GIS, adding a 0-sized buffer on a polygon often suppresses topology error, although this will not fix holes.

UPDATE test1 SET geom = ST_BUFFER(geom,0) 
0

This method uses PostGIS and can be carried out in three steps. The code below is based on a polygon table is called polygon_table with a geometry column geom and a unique integer columns called ìd. An example initial input geometries is shown below:

Initial Situation

Step 1: Remove overlapping geometries

WITH overlap_removal AS
(
         SELECT   a.id,
                  St_union(b.geom) AS geom
         FROM     polygon_table a,
                  polygon_table b
         WHERE    St_intersects(a.geom, b.geom)
         AND      b.id > a.id
         GROUP BY a.id)
UPDATE polygon_table
SET    geom =
       CASE
              WHEN overlap_removal.id IS NOT NULL THEN St_collectionextract(St_difference(polygon_table.geom, overlap_removal.geom), 3)
              ELSE polygon_table.geom
       END
FROM   overlap_removal
WHERE  polygon_table.id = overlap_removal.id

Result: Result of above step

Step 2: Create missing (enclosed) hole polygons and merge with the neighboring geometry sharing the longest touching border

 WITH poly_union AS
(
       SELECT st_buffer(st_buffer((St_dump(St_union(polygon_table.geom))).geom, 1, 'join=mitre'), -1, 'join=mitre') AS geom
       FROM   polygon_table ) , rings AS
(
       SELECT st_dumprings(poly_union.geom) AS DUMP
       FROM   poly_union ), inners AS
(
       SELECT row_number() OVER () AS id,
              (DUMP).geom          AS geom
       FROM   rings
       WHERE  (
                     DUMP).path[1] > 0) , best_match AS
(
                SELECT DISTINCT
                ON (
                                                inners.id) inners.id inner_id,
                                polygon_table.id poly_id,
                                st_length(st_intersection(inners.geom, polygon_table.geom)) AS len,
                                inners.geom
                FROM            inners,
                                polygon_table
                WHERE           st_intersects(inners.geom, polygon_table.geom)
                ORDER BY        inners.id,
                                len DESC ), inner_addition AS
(
         SELECT   poly_id        AS id,
                  st_union(geom) AS geom
         FROM     best_match
         GROUP BY poly_id )
UPDATE polygon_table
SET    geom =
       CASE
              WHEN inner_addition.id IS NOT NULL THEN st_union(polygon_table.geom, inner_addition.geom)
              ELSE polygon_table.geom
       END
FROM   inner_addition
WHERE  polygon_table.id = inner_addition.id 

Result: Result of fill step above

Lastly, in case of geometry imperfections or artifacts along the fill joins, the geometry is tided using a positive and negative buffer.

UPDATE polygon_table
SET    geom = st_buffer(st_buffer(geom, 1, 'join=mitre'), -1, 'join=mitre')

Result: Final result after cleaning

This may result in the creation of MultiPolygon repaired geometries. It has a distinct advantage over other solutions to this problem (snapping, buffering) in that as as much of the original geometry is retained as possible.

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.