[postgis-users] Can you rebuild a multipolygon from rings?

[Ben Madin]

G'day all,

We have a problem with erroneous geometries that we can't edit using QGIS - the geometries are too big, and the application just hangs.

The geometries are multipolygons, and they contain invalid rings. To get at them using postgis seems to require two levels of dumping. First, st_dump to split the Multipolygons into polygons, then st_dumprings to get at the bad ring.

But rebuilding the multipolygons is not so simple (for me) - I need to re-aggregate the remaining rings into a geometry dump to reinsert into the other dumped polygons to rebuild the multipolygon.

I'm not sure how to express this more articulately. sorry.

I have done :

create table phl(id serial unique, country varchar not null default 'tmp', the_geom geometry_dump);

insert into phl (the_geom) select ((st_dump(the_geom))) from summ.ctybnda where year=2005 and ccode like 'PHL';


Then I find the bad geometry :

select id, ((the_geom).path), st_isvalid((the_geom).geom) from phl where st_isvalid((the_geom).geom) is false;   

create table phl2(id serial unique, country varchar not null default 'tmp',  the_geom geometry_dump);

insert into phl2 (the_geom) select st_dumprings(((the_geom).geom)) from phl where id = 2601;

select id, ((the_geom).path), st_isvalid((the_geom).geom) from phl2 where st_isvalid((the_geom).geom) is false;


And Delete it 

delete from phl2 where id = 58;


Now is where I'm stuck - I need to congeal the remaining rings back into the first table, replacing the polygon that was unhealthy, but I have a geometry_dump, and need to aggregate it into another geometry dump to fit into the table...

I can happily aggregate geometry dumps into geometries, but I don't seem to be able to collect my rings back into a geometry dump.

This works to rebuild the original multipolygon from the dumped polygons table :

update summ.ctybnda c set the_geom = (select st_multi(st_collect(n.the_geom)) from (select country, ((p.the_geom).geom) as the_geom from phl p) as n) where c.year = 2005 and c.ccode = 'PHL';


but I can't get my repaired polygon back into the dumped polygons table. Any idea?

cheers

Ben

-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20111021/4abd1385/attachment.html>