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:

  1. 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;
  2. 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):

enter image description here

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.

  • 1
    You used ST_Distance - minimal distance betweeen geometries... since they have to intersect this always will be 0. Maybe you meant ST_MaxDistance? I don't think this will fix all problems, but for sure it'll be better – Jendrusk Nov 24 '16 at 23:02
  • Excellent! Thanks for pointing this out. I'm quite new to PostGIS so I didn't see where the problem lies. Everything worked out fine after I used ST_MaxDistance – guyus Nov 25 '16 at 9:22

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.