[postgis-users] Union a very big Multipolygon table with self intersections

[Simon Greener]

How about something like this:

select id, ST_Union(ST_Accum(case when ST_IsValid(a.the_geom) = 't' then a.the_geom else st_buffer(a.the_geom,0) end) as the_geom
   from (select id, the_geom
           from <<table>>
           where the_geom is not null
             and geometrytype(the_geom) != 'GEOMETRYCOLLECTION'
         union all
         SELECT gid, (ST_Dump(the_geom)).geom as the_geom
           from <<table>>
           where the_geom is not null
             and geometrytype(the_geom) = 'GEOMETRYCOLLECTION'
         ) as a;

The split based on GEOMETRYCOLLECTION is that when I first tested the STR indexed ST_Union I found that
performance was affectedif the union set includes an mpoly.

regards
SImon
On Fri, 05 Feb 2010 19:44:51 +1100, ibrahim saricicek <ibrahimsaricicek at gmail.com> wrote:

> Hi;
>
> create a new geometry column
>
> use update table set new_column=st_buffer(the_geom,0). IsValid will return
> true. Then try the union operation..
>
> IBO..
>
> On Fri, Feb 5, 2010 at 12:46 AM, Javier de la Torre
> <jatorre at vizzuality.com>wrote:
>
>> Hi all,
>>
>> I have a table with a MULTIPOLYGON field with around 100k records. Some of
>> these records, 2K have self intersections and other problems that make
>> ST_IsValid return false. The polygons overlap a lot and I wanted to generate
>> another table that will be the union of all polygons.
>>
>> The table looks like
>> id, the_geom
>>
>> What would be the best way to union all of the geometries into a new table
>> where there is only POLYGONS that do not overlap?
>>
>> Thanks in advance.
>>
>> Javier.
>>
>>
>> _______________________________________________
>> postgis-users mailing list
>> postgis-users at postgis.refractions.net
>> http://postgis.refractions.net/mailman/listinfo/postgis-users
>>
>


-- 
SpatialDB Advice and Design, Solutions Architecture and Programming,
Oracle Database 10g Administrator Certified Associate; Oracle Database 10g SQL Certified Professional
Oracle Spatial, SQL Server, PostGIS, MySQL, ArcSDE, Manifold GIS, FME, Radius Topology and Studio Specialist.
39 Cliff View Drive, Allens Rivulet, 7150, Tasmania, Australia.
Website: www.spatialdbadvisor.com
   Email: simon at spatialdbadvisor.com
   Voice: +61 362 396397
Mobile: +61 418 396391
Skype: sggreener
Longitude: 147.20515 (147° 12' 18" E)
Latitude: -43.01530 (43° 00' 55" S)
GeoHash: r22em9r98wg
NAC:W80CK 7SWP3