I have a (time-varying) shapefile with country polygons from which I want to extract borders between neighboring countries as a separate shapefile, using PostGIS. I managed to do this for most countries, but it did not work for all of them:
I did the following:
Transformed the polygons to lines:
CREATE TABLE t.test AS SELECT a.id, a.name, a."start", a."end", ST_boundary(wkb_geometry) AS geom from t.testfile;
Extracted the shared borders:
CREATE TABLE t.test2 AS SELECT a.id, a."name",a."start", a."end", b.id, b."name", b."start", b."end", ST_Intersection(a.geom, b.geom) FROM t.test AS a, t.test AS b WHERE ST_Intersects(a.geom, b.geom) AND a.id != b.id AND (a.start, a.end) OVERLAPS (b.start, b.end);
The resulting file looks like this (purple lines):
Some borders are complete, others are interrupted (e.g. the border between Germany and the Netherlands) and many other borders are missing.
I think that some of the original polygons did not align perfectly and tried to resolve this using ST_Snap (see below). However this did not change the result:
CREATE TABLE t.snap AS SELECT a.id, a."name", a."start", a."end", ST_Snap(a.geom, b.geom, ST_Distance(a.geom,b.geom)*1.01) as geom FROM t.test As a, t.test As b WHERE ST_Intersects(a.geom, b.geom) AND a.id!=b.id AND (a."start",a."end") OVERLAPS (b."start", b."end");
Does someone have an how to fix this?
P.S. It seems that the "integrate" tool in ArcMap does a good job fixing this issue, but I was hoping to find a solution in PostGIS or R.