[postgis-users] ST_Union (8.4) and TopologyException

[Simon Greener]

Regina,

> Did you try separating out the timings between the Union and the
> differencing.  I'm wondering if the time is being lost in unioning or the
> differencing part or combination of both.  Something also very stupid may be
> going on if postgresql choosed not to materialize that subselect for some
> reason.  Thats why for this type of thing, I like using CTEs instead since a
> CTE will force a temp table creation (materialization of the subselect)
> where as a subselect is not guaranteed to and is finicky based on costing
> parameters (which as we have discovered are kind of poor for PostGIS because
> we haven't put in any costs).

I'll investigate

> The downside of CTE (well at least in PostgreSQL 8.4) is that it always
> seems to force a temp table creation of some sort so for large numbers of
> rows in result subselect you don't want materialized, may perform slower
> than subselect way.
>
> So I would write your below as
>
> create table simon.si_result
> as
> WITH i AS
> (select si_grouping_f_id, ST_Union(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);
>
> I probably made a typo but you get the idea.

It was fine. Result is

Query returned successfully with no result in 5455968 ms.
About an hour and a half. The ST_Union should be done against the result of ST_Accum instead.

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