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
<[email protected]> 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
<[email protected]>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
[email protected]
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: [email protected]
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
_______________________________________________
postgis-users mailing list
[email protected]
http://postgis.refractions.net/mailman/listinfo/postgis-users
