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

[Matt Doughty]

Hi Mark,

I'm trying to do a geometric union (I'm more used to doing these things
in ArcGIS where it'd be a union: A topological overlay of two or more
polygon spatial datasets that preserves the features that fall within
the spatial extent of either input dataset; that is, all features from
both datasets are retained and extracted into a new polygon dataset
(ESRI website))

In this case I'm not bothered about what happens to attributes, it's the
polygons that I want.

The input tables are as follows:

	1. Table1
		name character varying(70),
  		the_geom geometry
	2. Table 2
		name character varying(70),
  		the_geom geometry
Cheers,

Matt
 
 
Matt Doughty
 
GEOGRAMA S.L.
Tel.:  +34 945 13 13 72    652 77 14 15
Fax: +34 945 23 03 40 
www.geograma.com
 
 
-----Mensaje original-----
De: postgis-users-bounces at postgis.refractions.net
[mailto:postgis-users-bounces at postgis.refractions.net] En nombre de Mark
Cave-Ayland
Enviado el: viernes, 01 de junio de 2007 11:14
Para: PostGIS Users Discussion
Asunto: Re: [postgis-users] Problem with slow GeomUnion (geom, geom)

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


_______________________________________________
postgis-users mailing list
postgis-users at postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users