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

[Sandro Santilli]

On Fri, Oct 21, 2011 at 03:54:28PM +0800, Ben Madin wrote:
> Thanks strk,
> 
> but I'm still confused. If I don't select the .geom component, I can't dumprings... when I do, I end up with 90 rows in a table of geometry dumps.
> 
> # select id, (the_geom).path, st_astext((the_geom).geom) from phl2 where id = 58;
>  id | path |                                                  st_astext                                                   
> ----+------+--------------------------------------------------------------------------------------------------------------
>  58 | {57} | POLYGON((126.023966 8.568675,126.023895 8.56901,126.013809 8.570289,126.023912 8.56893,126.023966 8.568675))
> (1 row)
> 
> # select id, ((the_geom).path), st_isvalidreason((the_geom).geom) from phl2 where st_isvalid((the_geom).geom) is false;
> NOTICE:  Self-intersection at or near point 126.024 8.56893
>  id | path |                  st_isvalidreason                   
> ----+------+-----------------------------------------------------
>  58 | {57} | Self-intersection[126.02391195391 8.56893000619979]
> 
> 
> which is the polygon ring I want to delete
> 
> I can delete it, but I now want to insert this polygon (the remaining
> 89 rows) back into my table of polgons (the dump from the first step),
> and then turn them back into a multipolygon. In fact, I can do the last
> step. But I can't get the geometry dump back into the polygons table in
> geometry dump format.

Something like this has a probably higher cost but is simpler
(and doesn't require you to save the path):

=> with validrings as ( select * from allrings where st_isvalid(geom) )
   select st_buildarea(st_collect(geom)) from validrings;

Btw, maybe you'd want to fix the invalidity rather than throw the invalid
ring away. There's an ST_MakeValid function in PostGIS trunk (upcoming 2.0).

--strk;

  ()   Free GIS & Flash consultant/developer
  /\   http://strk.keybit.net/services.html