[postgis-users] ST_Union (8.4) and TopologyException

[Simon Greener]

Regina,

> This is a little troubling.
>
> Regardless of if you are using the faster cascade approach, all the geometry
> aggs should be using the faster accumulation approach.
>
> The ST_Union you are now using is the ST_Union(geometry[]) instead of the
> ST_Union aggregate.
>
> How does replacing
>
> ST_Union(ST_Accum(...))
>
> With
> ST_Union(array_agg(...))
>
> Perform?

create table simon.si_result
as
WITH i AS
(select si_grouping_f_id, ST_Union(array_agg(a.geom)) as geom
          from simon.si_grouping_f_store_f a
         where geom is not null 
         group by si_grouping_f_id
        )
select i.si_grouping_f_id, ST_Difference(s.geom,i.geom) as geom
  from simon.si_grouping_f s
       inner join i
        on (s.id = i.si_grouping_f_id);

4421484 ms = 1hr 13mins 41seconds.

> Also did you get a chance to do just the timing on the ST_Union part?

select si_grouping_f_id, ST_Union(array_agg(a.geom)) as geom
          from simon.si_grouping_f_store_f a
         where geom is not null 
         group by si_grouping_f_id

Total query runtime: 4016078 ms. = 1 hr 7 mins
2 rows retrieved.

Simon
-- 
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)
NAC:W80CK 7SWP3