PostGIS align borders of countries

Question

New to PostGIS. I have USA & Canada vector data. The borders have small gaps or overlaps. I want to use PostGIS to align one border to another border. We are free to add or remove vertices.

I have tried ArcGIS integrate, align features, etc, they do not work well and we also want to programmatically align the borders

Any idea?

Welcome to GIS SE! We're a little different from other sites; this isn't a discussion forum but a Q&A site. Your questions should as much as possible describe not just what you want to do, but precisely what you have tried and where you are stuck trying that. Please check out our short [tour] for more about how the site works — Ian Turton, Jul 13 '21 at 16:45
These things you mention are topology related (topology is about relationships between spatial objects, roughly); there's a topology extension in PostGIS which I haven't used, when I have polygons such as those you describe, I use mapshaper.org or topojson (javascript), GRASS also does a great job cleaning that stuff, pretty much at import — Elio Diaz, Jul 13 '21 at 17:24
With PostGIS I would suggest using st_snap: https://postgis.net/docs/ST_Snap.html. You will have to play around with tolerances and check the topology for errors. — Konan Pruiksma, Jul 13 '21 at 17:53
ArcGIS Map/Pro has a topology tool on any license Standard and higher, (maybe Basic, cannot remember). It can, more-or-less, fill and cut any overlaps or gaps without much oversight, just approve the change assuming you can tolerate the boundary not being survey quality. — RomaH, Jul 13 '21 at 17:53
Thanks, everyone and I really appreciate it. It seems this might be helpful as well: https://gis.stackexchange.com/questions/11004/removing-small-spaces-slivers-between-polygons — Tony, Jul 13 '21 at 18:14

score 0 · Answer 1 · edited Jul 15 '21 at 19:04

Two options for myself

QGIS Snap geometries to layer works great!
ST_Snap works too we need to use st_maxdistance, not st_distance (st_distance is the minimum distance, for us, it is zero most of the time since the borders always touch somewhere); actually, we can use a fixed distance like 0.01 (decimal degrees)

WITH snap_table AS (
  SELECT st_multi(st_setsrid(st_snap(a.geom, b.geom, 0.01), 4326)) as polysnapped 
  FROM partial_can_usa a, 
       partial_can_usa b 
  WHERE a.id = 2069343 AND b.id = 2027962
) 
UPDATE partial_can_usa c 
SET  geom = polysnapped 
FROM snap_table 
WHERE c.id = 2069343;

PostGIS align borders of countries

1 Answers1