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?
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.
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)
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:
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:
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:
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:
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.
