[postgis-users] Problem with slow GeomUnion (geom, geom)

[Mark Cave-Ayland]

On Fri, 2007-06-01 at 10:06 +0200, Matt Doughty wrote:
> Hi,
> 
>  
> 
> I’m having some problems with the GeomUnion (geometry, geometry)
> function- basically that it takes forever and eventually crashes out
> for lack of memory. 
> 
>  
> 
> I’m trying to perform a union on two polygon tables (of 9,000 and 200
> rows respectively), which are indexed on the geometry column, and
> vacuumed too. The command I’m trying to run is: 
> 
>  
> 
> SELECT 
> 
>             table1.name AS t1_name,
> 
>             table2.name AS t2_name,
> 
>             GeomUnion(table1.the_geom, table2.the_geom) 
> 
> INTO
> 
>             table_union
> 
> FROM 
> 
>             table1, table2;
> 
>  
> 
> From what I can gather, it shouldn’t be that demanding. In Kosmo the
> same operation takes less than 30 seconds (although it only lets you
> maintain the attributes from one of the tables). Is it just that the
> function takes a very long time or should I perform other functions
> before carry out the GeomUnion??
> 
>  
> 
>  
> 
> All help greatly appreciated!!
> 
>  
> 
> Matt


Hi Matt,

What sort of union are you trying to do? Combine two tables into one, or
perform a geometric union? It would help if you gave the schemas of your
input tables (\d in psql), and an example of the output you are aiming
to achieve.

The reason the SQL above takes so much memory and time is because you've
specified an SQL cross-join with your syntax; hence your SELECT is
effectively running on a 2 million row table.


Kind regards,

Mark.

-- 
ILande - Open Source Consultancy
http://www.ilande.co.uk