I downloaded a shapefile where admin(country) and province of the world are located (http://www.naturalearthdata.com/downloads/10m-cultural-vectors/10m-admin-1-states-provinces/).

For each country, you can see the provinces as an extra polygon (Brazil yellow highlighted): before

Based on this I want to create a shapefile that only contains the countries. Since its a pretty big shapefile, I want to do this with postgis. *Note that I tried doing this in QGIS which caused the programme to crash.

SELECT c.admin, ST_Union(geom) AS geom 
FROM contry_province_natural_earth_world_data c
GROUP BY c.admin;

That query results this (here you see the resulting polygon Brasil): after

Obviously the geometry of the original shapefile is not an exact topolgy. I thought about using ST_ExteriorRing to get only the outline. I am not sure what the result would look like. I tried, but using ST_ExteriorRing() results in: ExteriorRing: geom is not a polygon when I try it on one geometry. Fixing the input geometry I could try to snap neighbouring polygonlines, but how to do this for every polygon? ST_Snap() only works on two input geometries (as far as I understand the documentation). I somehow need to say "Snap every polygon to each other that is within a distance of XY".

  • 1
    Instead of saying to yourself, "This is a large file, I should use a database", maybe you should be thinking, "This is a complex geoprocessing task; I should be using GIS software." It's unlikely that a Dissolve utility wouldn't have a tolerance value, and it's unlikely that data which fits in a shapefile would exceed the capability of GIS software for processing. There are many potential paths to achieve your goal (fix input, different software, edit result), but you'll need to choose one and include your efforts before we can help you complete the task. – Vince Jan 12 '17 at 12:37
  • 2
    @Vince I tried using QGIS, caused it to crash. I want to do it in PostGis. The problem I am having is, that I cannot describe the problem in words, but rather in screenshots as shown here. That makes it rather hard to search for a solution. Thats why I do not have any attempts to show how to solve this. – Stophface Jan 12 '17 at 12:51
  • 2
    I sympathise with your question, I think it is valid (not too broad, at least after you edited it). I myself spent almost one day once for generating a shapefile from state boundaries to country boundaries, having to manually remove such holes. Having a nice answer here using a PostGIS solution (or other types of solution) would be nice. – Andre Silva Jan 12 '17 at 13:50
  • 3
    In my experience this is due to tiny mismatches between the intersecting lines of neighbouring polygons. One way to fix it is to do a union of the buffer of the individual (mutli) polygons and then to a negative buffer of the result (by the same amount), which means the outer border will be the correct, but the tiny gaps you are seeing will vanish. You could also try st_snaptogrid. – John Powell Jan 12 '17 at 15:01

The comment of @John Barca worked. Make a ST_Union() of the ST_Buffer and then of the resulting geometry a "negative" ST_Buffer with the same value the first buffer had.


SELECT c.a, c.b, ST_Union(ST_Buffer(geom, 0.2)) AS geom  -- units are degrees
FROM countries c
GROUP BY c.a, c.b;

SELECT t.a, t.b, ST_Buffer(geom, -0.2) AS Geom
FROM myTable t;

Good thinking of using the ST_ExteriorRing. Unfortunately the immediate result of the ST_Union, n your case is a Multipolygon. See that wee islands on the top?

What you want is the exterior ring of each of the polygons that comprise the multi polygon.

Your Answer

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

Not the answer you're looking for? Browse other questions tagged or ask your own question.