I loaded into PostGIS (to be used in QGIS and web) a shapefile containing areas of a country. These areas are grouped in zones. I'm trying to create the shapes of these zones (and later an additional level).
The naive approach was:
create table eric_z as select z.id, z.name, st_multi(st_union(a.geom)) as geom, count(*) from geo_a a inner join geo_z z on a."Z_id" = z.id group by z.id, z.name;
This works but for some areas where the borders are not exactly matching, I'm getting inner points like this:
I then thought about using st_exteriorring. Since this produces outer LineString, I used st_makepolygon to get full shapes. The issue would not be fully visible in the screenshot below with just lines.
create table eric_z2 as select z.id, z.name, st_makepolygon(ST_ExteriorRing(st_union(a.geom))) as geom, count(*) from geo_a a inner join geo_z z on a."ZS_id" = z.id group by z.id, z.name
This seems to work at first but as predicted in comments by @ThingumaBob, it can be tricky because
st_exteriorring can have issues with multipolygons. The result is that some areas suddenly become null:
@Thingumabob suggested to use st_dump to get the individual lines, exteriorring them and then turn it back to polygons. However, I can't figure out how to do that.
Something that does work but that I find awfully dirty is to enlarge the polygons by a short distance and then shrink them again. This actually does what I want but I'd feel dirty committing that code into posterity... :(
st_buffer(st_buffer(st_multi(st_union(a.geom)), 0.00001), -0.00001)
Any better suggestion ?