[postgis-users] UNION

Thu Sep 13 05:30:28 PDT 2007

Ravi,

Andreas - this may help you out too.  I apologize if I misunderstood
before.
------------------------------------------------------------------------
------------
So I take it you want a set of records with non-duplicating A & B where
the geom1, geom2  is the union of all intersecting sets

So you would want output to be

A1       B1   geom1
A2    null    geom2
A3    B3     geom3
A4    B4    geom4

and you don't want this
A1   B1   geom1
A1   B1   geom2

If you don't want dupes in your A code and B Info, then you will
probably want to do something like this

SELECT newtb.code,  newtb.info, geomunion(newtb.cgeom) as thenewgeom
FROM 
(  SELECT tb1.code, tb2.info, geomunion(tb1.the_geom, tb2.the_geom) as
cgeom
    FROM tb1 
        INNER JOIN tb2 ON tb1.the_geom && tb2.the_geom AND
intersects(tb1.the_geom, tb2.the_geom)
    UNION ALL
    SELECT tb1.code, null As info, tb1.the_geom as cgeom 
    FROM tb1 
        LEFT JOIN tb2 ON tb1.the_geom && tb2.the_geom AND
intersects(tb1.the_geom, tb2.the_geom)
        WHERE tb2.the_geom IS NULL
    UNION ALL
    SELECT null as code, tb2.info, tb2.the_geom as cgeom
            FROM tb2 LEFT JOIN tb1 ON tb1.the_geom && tb2.the_geom AND
intersects(tb1.the_geom, tb2.the_geom)
           WHERE tb1.the_geom IS NULL
)  AS newtb
GROUP BY newtb.code, newtb.info

----NOTE YOU CAN Probably write the above with a few more key strokes by
grouping each dataset separately instead of writing as a subselect and
then grouping.   I'm not sure which is most efficient processor wise.
Let me know if you want me to give an example of the second way.

Another note -  it may be more efficient and just as good to replace
geomunion(newtb.cgeom)   

with     

buffer(collect(newtb.cgeom), 0.0)

Hope that helps,
Regina

________________________________

From: postgis-users-bounces at postgis.refractions.net
[mailto:postgis-users-bounces at postgis.refractions.net] On Behalf Of RAVI
KUMAR
Sent: Wednesday, September 12, 2007 9:11 AM
To: PostGIS Users Discussion
Subject: RE: [postgis-users] UNION

Hi Regina,
I am trying to have a UNION of two shapefiles.
shape A has code as attribute
shape B has info as attribute

I wish to have a result where
1. Both the shapes intersected portions will have both the attributes
and
2. The portions which donot intersect have their portion represented as
well,
    with zero as value for the attribute information that is missing.

So the resultant shape AB will have 2 attributes Code and Info and
values as per intersection, or zero values for the attribute that
doesnot intersect.

The resultant will also have corresponding geometries of intersected
portions as polygons gernerated, and unintersected portions also
modified as seperate polygons.

Please give me some lead to find proper SQL query.

Cheers
Ravi Kumar

"Obe, Regina" <robe.dnd at cityofboston.gov> wrote:

	Ravi,

	Still not quite clear what question you are trying to answer.

	I am guessing that you are getting duplicates because you have a
one to many going on here.  But I'm not sure which is the one and which
is the many.

	Also you don't need coalesce here since that is only useful if
you expect one of your fields to be null.  In this case since you are
doing an inner join, neither will be NULL.  Or perhaps you don't want to
do an inner join.

	It might be that you really want to use the aggregate form of
geomunion

	Something like

	SELECT strubuf.struclass, geomunion(geof.geometry) AS geofac 
	FROM geof INNER JOIN strubuf ON (geof.geometry &&
strubuf.geometry AND
	 intersects(strubuf.geometry, geof.geometry))
	GROUP BY strubuf.struclass

	Just a guess.  But perhaps that doesn't answer the question you
were trying to ask.

________________________________

	From: postgis-users-bounces at postgis.refractions.net
[mailto:postgis-users-bounces at postgis.refractions.net] On Behalf Of RAVI
KUMAR
	Sent: Tuesday, September 11, 2007 6:26 AM
	To: jump-users at lists.jump-project.org
	Cc: postgis
	Subject: [postgis-users] UNION

	Hi,
	I have prepared a course material for training in Jump for
Geoscientists.
	Added a minerological theme in which the final output results in
prospectivity map.

	I have used Post-GIS where ever complex bullion operators are
needed. This was done smoothly.

	But Iam now held-up in UNION and Intersection. Jump gives an
OVERLAY which equals intersection. But the problem needs both
	Intersected portion and
	Non intersecting portion together.

	Followed the advise given on this list to get the result. But
the result
	OPENJUMP
	1.Overlay Layers A, B
	2. Make union of the result (to use as a mask)
	3. A-Union and B-union and combine theses two layers
	4. Add Overlay to this.

	This does the work but you have duplicate geometries which are
filled with different attributes in different instances.
	POSTGIS:
	Followinf the suggestions on the Postgis list I have tried this
query.
	SELECT strubuf.struclass, COALESCE(geomunion(strubuf.geometry,
	 geof.geometry), geof.geometry) AS geofac FROM geof INNER JOIN
strubuf ON (geof.geometry && strubuf.geometry AND
	 intersects(strubuf.geometry, geof.geometry));

	But this doesnt result in a table geofac

	This is where I am..
	But for this additional exercise every thing else is ready.
	However Proof of Concept for Geoscientists lies in THIS.

	Cheers
	Ravi Kumar 
________________________________

	Be a better Heartthrob. Get better relationship answers
<http://us.rd.yahoo.com/evt=48255/*http://answers.yahoo.com/dir/_ylc=X3o
DMTI5MGx2aThyBF9TAzIxMTU1MDAzNTIEX3MDMzk2NTQ1MTAzBHNlYwNCQUJwaWxsYXJfTkl
fMzYwBHNsawNQcm9kdWN0X3F1ZXN0aW9uX3BhZ2U-?link=list&sid=396545433> from
someone who knows.
	Yahoo! Answers - Check it out. 
________________________________

	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

________________________________

Take the Internet to Go: Yahoo!Go puts the Internet in your pocket:
<http://us.rd.yahoo.com/evt=48253/*http://mobile.yahoo.com/go?refer=1GNX
IC>  mail, news, photos & more. 
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20070913/72caa559/attachment.html>