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

Mon Jun 4 07:46:19 PDT 2007

Hi Regina,

I've tried out your script and it works! Although it threw up the following notice:

NOTICE:  LWGEOM_gist_joinsel called with incorrect join type

Thanks very much,

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 Obe, Regina
Enviado el: lunes, 04 de junio de 2007 14:14
Para: PostGIS Users Discussion
Asunto: RE: [postgis-users] Problem with slow GeomUnion (geom, geom)

I would think if you wanted to do a falls in either spatial extent dataset and still keep both sets of data as a single row, then you would want to do a full outer join.  This is just a guess since I'm not too familiar with ArcGIS. Unfortunately I tried that but doesn't seem to work with PostGIS functions gives error

ERROR: FULL JOIN is only supported with merge-joinable join conditions
SQL state: 0A000

- but if such a thing were to work with PostGIS functions - it would look something like - it's a shame it doesn't or am I missing something here?

As a workaround try

AS A Work around for the fact that the above doesn't work - I guess you could do it as 2 left joins unioned

	SELECT DISTINCT
		Table1.name as table1_name, Table2.name as table2_name, 
		CASE WHEN Table2.the_geom IS NULL THEN Table1.the_geom ELSE GeomUnion(Table1.the_geom, Table2.the_geom) END as new_geom
	FROM 	
		Table1 LEFT JOIN Table2 ON Table1.the_geom && Table2.the_geom
	UNION
	SELECT  DISTINCT
		Table1.name as table1_name, Table2.name as table2_name, 
		CASE WHEN Table1.the_geom IS NULL THEN Table2.the_geom ELSE GeomUnion(Table1.the_geom, Table2.the_geom) END as new_geom
	FROM 
		Table2 LEFT JOIN Table1 ON Table1.the_geom && Table2.the_geom;

Note the Case statements.  The reason for the case statement is that if you try to union a geometry with null, you get null.  Also note you make need to add a within, overlaps or intersects operator as part of the join depending on what you are trying to do.  The above will join where the spatial extents of each geometry(bounding box) overlap and if they don't it will return null for that field.

The more standard FULL OUTER JOIN approach which doesn't work with Postgis functions looks like the below

So something like

SELECT Table1.name as table1_name, Table2.name as table2_name, CASE WHEN Table1.the_geom IS NULL THEN Table2.the_geom WHEN Table2.the_geom IS NULL THEN Table1.the_geom ELSE GeomUnion(Table1.the_geom, Table2.the_geom) END as new_geom
FROM
	Table1 full outer join Table2 ON (Table1.the_geom && Table2.the_geom)

Well that would give you the spatial extent of each if I take ESRI's definition literally which doesn't entirely make sense to me.  Would seem to make more sense

SELECT Table1.name as table1_name, CASE WHEN Table1.the_geom IS NULL THEN Table2.the_geom WHEN Table2.the_geom IS NULL THEN Table1.the_geom ELSE GeomUnion(Table1.the_geom, Table2.the_geom) END as new_geom
FROM
	Table1 full outer join Table2 ON (Table1.the_geom && Table2.the_geom) AND distance(Table1.the_geom, Table2.the_geom) = 0

Hope that helps,
Regina

-----Original Message-----
From: postgis-users-bounces at postgis.refractions.net [mailto:postgis-users-bounces at postgis.refractions.net] On Behalf Of Matt Doughty
Sent: Monday, June 04, 2007 5:48 AM
To: PostGIS Users Discussion
Subject: RE: [postgis-users] Problem with slow GeomUnion (geom, geom)

Hi,

I managed to union the two tables through a simple union in the end:

CREATE TABLE newtable AS
	SELECT 
		Table1.name,
		Table1.the_geom
	FROM 	
		Table1
	UNION
	SELECT 
		Table2.name,
		Table2.the_geom
	FROM 
		Table2;

This didn't quite work as I wanted, as you can only set it to output the attributes of one of the tables, but serves its purpose for now. 

I don't think the Intersection(geometry,geometry) would've been relevant as this leaves only the geometries of the polygons where they overlap (or intersect).

Thanks for you inputs,

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 Håvard Tveite
Enviado el: viernes, 01 de junio de 2007 16:17
Para: PostGIS Users Discussion
Asunto: Re: [postgis-users] Problem with slow GeomUnion (geom, geom)

This is probably a map overlay, and not a traditional spatial join.
See:

<URL: http://en.wikipedia.org/wiki/Geographic_information_system#Map_overlay >

The PostGIS Intersection(geometry,geometry) is a relevant
operation.

Håvard

Mark Cave-Ayland wrote:
> On Fri, 2007-06-01 at 12:00 +0200, Matt Doughty wrote:
>> 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
> 
> Okay, this is getting a little out of my comfort zone so someone else
> may need to help out here - is basically what you want to return all the
> polygons that overlap each other from your two tables?
> 
> 
> 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
> 

-- 
Håvard Tveite
Department of Mathematical Sciences and Technology, UMB
Drøbakveien 31, POBox 5003, N-1432 Ås, NORWAY
Phone: +47 64965483 Fax: +47 64965401 http://www.umb.no/imt/
_______________________________________________
postgis-users mailing list
postgis-users at postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users
_______________________________________________
postgis-users mailing list
postgis-users at postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users
-----------------------------------------
The substance of this message, including any attachments, may be
confidential, legally privileged and/or exempt from disclosure
pursuant to Massachusetts law. It is intended
solely for the addressee. If you received this in error, please
contact the sender and delete the material from any computer.
_______________________________________________
postgis-users mailing list
postgis-users at postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users