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
    Commented Nov 24, 2016 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
    Commented Nov 25, 2016 at 9:22


Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Browse other questions tagged or ask your own question.