2

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:

areas with annoying lines within the area

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:

Zones 723 and 717 are null after st_exteriorring

@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 ?

1

if your problem is the table geo_a is multipolygons you need to use st_dump to return only SINGLEPART geometries. it is hard to test this code without your data but this approach will help you need to break down the multipolygons

drop table if exists eric_z;
create table eric_z as

with a as(select (st_dump(shape)).geom shape,* 
        from geo_a a
        )
select z.id,
       z.name,
       st_makepolygon(ST_ExteriorRing(st_union(a.shape))) as shape,
       count(*)
from a join geo_z z on a."ZS_id" = z.id
  group by z.id, z.name;

alter table eric_z drop column geom;

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.