<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML><HEAD>
<META http-equiv=Content-Type content="text/html; charset=us-ascii">
<META content="MSHTML 6.00.6000.16481" name=GENERATOR></HEAD>
<BODY>
<DIV dir=ltr align=left><SPAN class=217440812-13092007><FONT face=Arial
color=#0000ff size=2>Ravi,</FONT></SPAN></DIV>
<DIV dir=ltr align=left><SPAN class=217440812-13092007><FONT face=Arial
color=#0000ff size=2></FONT></SPAN> </DIV>
<DIV dir=ltr align=left><SPAN class=217440812-13092007><FONT face=Arial
color=#0000ff size=2>Andreas - this may help you out too. I apologize if I
misunderstood before.</FONT></SPAN></DIV>
<DIV dir=ltr align=left><SPAN class=217440812-13092007><FONT face=Arial
color=#0000ff
size=2>------------------------------------------------------------------------------------</FONT></SPAN></DIV>
<DIV dir=ltr align=left><SPAN class=217440812-13092007><FONT face=Arial
color=#0000ff size=2>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</FONT></SPAN></DIV>
<DIV dir=ltr align=left><SPAN class=217440812-13092007><FONT face=Arial
color=#0000ff size=2></FONT></SPAN> </DIV>
<DIV dir=ltr align=left><SPAN class=217440812-13092007><FONT face=Arial
color=#0000ff size=2>So you would want output to be</FONT></SPAN></DIV>
<DIV dir=ltr align=left><SPAN class=217440812-13092007><FONT face=Arial
color=#0000ff size=2></FONT></SPAN> </DIV>
<DIV dir=ltr align=left><SPAN class=217440812-13092007><FONT face=Arial
color=#0000ff size=2>A1 B1
geom1</FONT></SPAN></DIV>
<DIV dir=ltr align=left><SPAN class=217440812-13092007><FONT face=Arial
color=#0000ff size=2>A2 null
geom2</FONT></SPAN></DIV>
<DIV dir=ltr align=left><SPAN class=217440812-13092007><FONT face=Arial
color=#0000ff size=2>A3
B3 geom3</FONT></SPAN></DIV>
<DIV dir=ltr align=left><SPAN class=217440812-13092007><FONT face=Arial
color=#0000ff size=2>A4 B4
geom4</FONT></SPAN></DIV>
<DIV dir=ltr align=left><SPAN class=217440812-13092007><FONT face=Arial
color=#0000ff size=2></FONT></SPAN> </DIV>
<DIV dir=ltr align=left><SPAN class=217440812-13092007><FONT face=Arial
color=#0000ff size=2>and you don't want this</FONT></SPAN></DIV>
<DIV dir=ltr align=left><SPAN class=217440812-13092007><FONT face=Arial
color=#0000ff size=2>A1 B1 geom1</FONT></SPAN></DIV>
<DIV dir=ltr align=left><SPAN class=217440812-13092007><FONT face=Arial
color=#0000ff size=2>A1
B1 geom2</FONT></SPAN></DIV>
<DIV dir=ltr align=left><SPAN class=217440812-13092007><FONT face=Arial
color=#0000ff size=2></FONT></SPAN> </DIV>
<DIV dir=ltr align=left><SPAN class=217440812-13092007><FONT face=Arial
color=#0000ff size=2>If you don't want dupes in your A code and B Info, then you
will probably want to do something like this</FONT></SPAN></DIV>
<DIV dir=ltr align=left><SPAN class=217440812-13092007><FONT face=Arial
color=#0000ff size=2></FONT></SPAN> </DIV>
<DIV dir=ltr align=left><SPAN class=217440812-13092007><FONT face=Arial
color=#0000ff size=2>SELECT newtb.code, newtb.info, geomunion(newtb.cgeom)
as thenewgeom</FONT></SPAN></DIV>
<DIV dir=ltr align=left><SPAN class=217440812-13092007><FONT face=Arial
color=#0000ff size=2>FROM </FONT></SPAN></DIV>
<DIV dir=ltr align=left><SPAN class=217440812-13092007><FONT face=Arial
color=#0000ff size=2>( SELECT tb1.code, tb2.info, geomunion(tb1.the_geom,
tb2.the_geom) as cgeom</FONT></SPAN></DIV>
<DIV dir=ltr align=left><SPAN class=217440812-13092007><FONT face=Arial
color=#0000ff size=2> FROM tb1 </FONT></SPAN></DIV>
<DIV dir=ltr align=left><SPAN class=217440812-13092007><FONT face=Arial
color=#0000ff size=2> INNER JOIN tb2
ON tb1.the_geom && tb2.the_geom AND intersects(tb1.the_geom,
tb2.the_geom)</FONT></SPAN></DIV>
<DIV dir=ltr align=left><SPAN class=217440812-13092007> <FONT
face=Arial color=#0000ff size=2>UNION ALL</FONT></SPAN></DIV>
<DIV dir=ltr align=left><SPAN class=217440812-13092007> <FONT
face=Arial color=#0000ff size=2>SELECT tb1.code, null As info, tb1.the_geom as
cgeom</FONT>
<DIV dir=ltr align=left><SPAN class=217440812-13092007><FONT face=Arial
color=#0000ff size=2> FROM tb1 </FONT></SPAN></DIV>
<DIV dir=ltr align=left><SPAN class=217440812-13092007><FONT face=Arial
color=#0000ff size=2> LEFT JOIN
tb2 ON tb1.the_geom && tb2.the_geom AND intersects(tb1.the_geom,
tb2.the_geom)</FONT></SPAN></DIV></SPAN></DIV>
<DIV dir=ltr align=left><SPAN class=217440812-13092007><FONT face=Arial
color=#0000ff size=2> WHERE
tb2.the_geom IS NULL</FONT></SPAN></DIV>
<DIV dir=ltr align=left><SPAN class=217440812-13092007> <FONT
face=Arial color=#0000ff size=2>UNION ALL</FONT></SPAN></DIV>
<DIV dir=ltr align=left><SPAN class=217440812-13092007> <FONT
face=Arial color=#0000ff size=2>SELECT null as code, tb2.info, tb2.the_geom as
cgeom</FONT></SPAN></DIV>
<DIV dir=ltr align=left><SPAN class=217440812-13092007><FONT face=Arial
color=#0000ff
size=2> FROM
tb2 LEFT JOIN tb1 ON tb1.the_geom && tb2.the_geom AND
intersects(tb1.the_geom, tb2.the_geom)</FONT></SPAN></DIV>
<DIV dir=ltr align=left><SPAN class=217440812-13092007><FONT face=Arial
color=#0000ff
size=2> WHERE
tb1.the_geom IS NULL</FONT></SPAN></DIV>
<DIV dir=ltr align=left><SPAN class=217440812-13092007><FONT face=Arial
color=#0000ff size=2>) AS newtb</FONT></SPAN></DIV>
<DIV dir=ltr align=left><SPAN class=217440812-13092007><FONT face=Arial
color=#0000ff size=2>GROUP BY newtb.code, newtb.info</FONT></SPAN></DIV>
<DIV dir=ltr align=left><SPAN class=217440812-13092007><FONT face=Arial
color=#0000ff size=2></FONT></SPAN> </DIV>
<DIV dir=ltr align=left><SPAN class=217440812-13092007><FONT face=Arial
color=#0000ff size=2>----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.</FONT></SPAN></DIV>
<DIV dir=ltr align=left><SPAN class=217440812-13092007><FONT face=Arial
color=#0000ff size=2></FONT></SPAN> </DIV>
<DIV dir=ltr align=left><SPAN class=217440812-13092007><FONT face=Arial
color=#0000ff size=2>Another note - it may be more efficient and just as
good to replace</FONT></SPAN></DIV>
<DIV dir=ltr align=left><SPAN class=217440812-13092007><FONT face=Arial
color=#0000ff size=2>geomunion(newtb.cgeom) </FONT></SPAN></DIV>
<DIV dir=ltr align=left><SPAN class=217440812-13092007><FONT face=Arial
color=#0000ff size=2></FONT></SPAN> </DIV>
<DIV dir=ltr align=left><SPAN class=217440812-13092007><FONT face=Arial
color=#0000ff size=2>with </FONT></SPAN></DIV>
<DIV dir=ltr align=left><SPAN class=217440812-13092007><FONT face=Arial
color=#0000ff size=2></FONT></SPAN> </DIV>
<DIV dir=ltr align=left><SPAN class=217440812-13092007><FONT face=Arial
color=#0000ff size=2>buffer(collect(newtb.cgeom), 0.0)</FONT></SPAN></DIV>
<DIV dir=ltr align=left><SPAN class=217440812-13092007><FONT face=Arial
color=#0000ff size=2></FONT></SPAN> </DIV>
<DIV dir=ltr align=left><SPAN class=217440812-13092007><FONT face=Arial
color=#0000ff size=2>Hope that helps,</FONT></SPAN></DIV>
<DIV dir=ltr align=left><SPAN class=217440812-13092007><FONT face=Arial
color=#0000ff size=2>Regina</FONT></SPAN></DIV>
<DIV dir=ltr align=left><SPAN class=217440812-13092007><FONT face=Arial
color=#0000ff size=2></FONT></SPAN> </DIV>
<DIV dir=ltr align=left><SPAN class=217440812-13092007><FONT face=Arial
color=#0000ff size=2></FONT></SPAN><SPAN class=217440812-13092007><FONT
face=Arial color=#0000ff size=2></FONT></SPAN> </DIV>
<DIV dir=ltr align=left><SPAN class=217440812-13092007><FONT face=Arial
color=#0000ff size=2></FONT></SPAN> </DIV><BR>
<DIV class=OutlookMessageHeader lang=en-us dir=ltr align=left>
<HR tabIndex=-1>
<FONT face=Tahoma size=2><B>From:</B>
postgis-users-bounces@postgis.refractions.net
[mailto:postgis-users-bounces@postgis.refractions.net] <B>On Behalf Of </B>RAVI
KUMAR<BR><B>Sent:</B> Wednesday, September 12, 2007 9:11 AM<BR><B>To:</B>
PostGIS Users Discussion<BR><B>Subject:</B> RE: [postgis-users]
UNION<BR></FONT><BR></DIV>
<DIV></DIV>
<DIV>Hi Regina,</DIV>
<DIV>I am trying to have a UNION of two shapefiles.</DIV>
<DIV>shape A has code as attribute</DIV>
<DIV>shape B has info as attribute</DIV>
<DIV> </DIV>
<DIV>I wish to have a result where</DIV>
<DIV>1. Both the shapes intersected portions will have both the attributes
and</DIV>
<DIV>2. The portions which donot intersect have their portion represented as
well,</DIV>
<DIV> with zero as value for the attribute information that is
missing.</DIV>
<DIV> </DIV>
<DIV>So the resultant shape AB will have 2 attributes Code and Info and</DIV>
<DIV>values as per intersection, or zero values for the attribute that doesnot
intersect.</DIV>
<DIV> </DIV>
<DIV>The resultant will also have corresponding geometries of intersected
portions as polygons gernerated, and unintersected portions also modified as
seperate polygons.</DIV>
<DIV> </DIV>
<DIV>Please give me some lead to find proper SQL query.</DIV>
<DIV> </DIV>
<DIV>Cheers</DIV>
<DIV>Ravi Kumar</DIV>
<DIV><BR><BR><B><I>"Obe, Regina" <robe.dnd@cityofboston.gov></I></B>
wrote:</DIV>
<BLOCKQUOTE class=replbq
style="PADDING-LEFT: 5px; MARGIN-LEFT: 5px; BORDER-LEFT: #1010ff 2px solid">
<META content="MSHTML 6.00.6000.16481" name=GENERATOR>
<DIV dir=ltr align=left><SPAN class=430184215-11092007><FONT face=Arial
color=#0000ff size=2>Ravi,</FONT></SPAN></DIV>
<DIV dir=ltr align=left><SPAN class=430184215-11092007><FONT face=Arial
color=#0000ff size=2></FONT></SPAN> </DIV>
<DIV dir=ltr align=left><SPAN class=430184215-11092007><FONT face=Arial
color=#0000ff size=2>Still not quite clear what question you are trying to
answer.</FONT></SPAN></DIV>
<DIV dir=ltr align=left><SPAN class=430184215-11092007><FONT face=Arial
color=#0000ff size=2></FONT></SPAN> </DIV>
<DIV dir=ltr align=left><SPAN class=430184215-11092007><FONT face=Arial
color=#0000ff size=2>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.</FONT></SPAN></DIV>
<DIV dir=ltr align=left><SPAN class=430184215-11092007><FONT face=Arial
color=#0000ff size=2></FONT></SPAN> </DIV>
<DIV dir=ltr align=left><SPAN class=430184215-11092007><FONT face=Arial
color=#0000ff size=2>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.</FONT></SPAN></DIV>
<DIV dir=ltr align=left><SPAN class=430184215-11092007><FONT face=Arial
color=#0000ff size=2></FONT></SPAN> </DIV>
<DIV dir=ltr align=left><SPAN class=430184215-11092007><FONT face=Arial
color=#0000ff size=2>It might be that you really want to use the aggregate
form of geomunion</FONT></SPAN></DIV>
<DIV dir=ltr align=left><SPAN class=430184215-11092007><FONT face=Arial
color=#0000ff size=2></FONT></SPAN> </DIV>
<DIV dir=ltr align=left><SPAN class=430184215-11092007><FONT face=Arial
color=#0000ff size=2>Something like</FONT></SPAN></DIV>
<DIV dir=ltr align=left><SPAN class=430184215-11092007><FONT face=Arial
color=#0000ff size=2></FONT></SPAN> </DIV>
<DIV dir=ltr align=left><SPAN class=430184215-11092007>SELECT
strubuf.struclass, geomunion(geof.geometry) AS geofac </SPAN></DIV>
<DIV dir=ltr align=left><SPAN class=430184215-11092007>FROM geof INNER JOIN
strubuf ON (geof.geometry && strubuf.geometry
AND<BR> intersects(strubuf.geometry, geof.geometry))</SPAN></DIV>
<DIV dir=ltr align=left><SPAN class=430184215-11092007>GROUP BY
strubuf.struclass</SPAN></DIV>
<DIV dir=ltr align=left><SPAN class=430184215-11092007></SPAN> </DIV>
<DIV dir=ltr align=left><SPAN class=430184215-11092007><FONT face=Arial
color=#0000ff size=2>Just a guess. But perhaps that doesn't answer the
question you were trying to ask.</FONT></SPAN></DIV><BR>
<DIV class=OutlookMessageHeader lang=en-us dir=ltr align=left>
<HR tabIndex=-1>
<FONT face=Tahoma size=2><B>From:</B>
postgis-users-bounces@postgis.refractions.net
[mailto:postgis-users-bounces@postgis.refractions.net] <B>On Behalf Of
</B>RAVI KUMAR<BR><B>Sent:</B> Tuesday, September 11, 2007 6:26
AM<BR><B>To:</B> jump-users@lists.jump-project.org<BR><B>Cc:</B>
postgis<BR><B>Subject:</B> [postgis-users] UNION<BR></FONT><BR></DIV>
<DIV></DIV>Hi,<BR>I have prepared a course material for training in Jump for
Geoscientists.<BR>Added a minerological theme in which the final output
results in prospectivity map.<BR><BR>I have used Post-GIS where ever complex
bullion operators are needed. This was done smoothly.<BR><BR>But Iam now
held-up in UNION and Intersection. Jump gives an OVERLAY which equals
intersection. But the problem needs both<BR>Intersected portion and<BR>Non
intersecting portion together.<BR><BR>Followed the advise given on this list
to get the result. But the result<BR>OPENJUMP<BR>1.Overlay Layers A, B<BR>2.
Make union of the result (to use as a mask)<BR>3. A-Union and B-union and
combine theses two layers<BR>4. Add Overlay to this.<BR><BR>This does the work
but you have duplicate geometries which are filled with different attributes
in different instances.<BR>POSTGIS:<BR>Followinf the suggestions on the
Postgis list I have tried this query.<BR>SELECT strubuf.struclass,
COALESCE(geomunion(strubuf.geometry,<BR> geof.geometry), geof.geometry)
AS geofac FROM geof INNER JOIN strubuf ON (geof.geometry &&
strubuf.geometry AND<BR> intersects(strubuf.geometry,
geof.geometry));<BR><BR>But this doesnt result in a table
geofac<BR><BR><BR><BR>This is where I am..<BR>But for this additional exercise
every thing else is ready.<BR>However Proof of Concept for Geoscientists lies
in THIS.<BR><BR><BR>Cheers<BR>Ravi Kumar
<DIV>
<HR SIZE=1>
Be a better Heartthrob. <A
href="http://us.rd.yahoo.com/evt=48255/*http://answers.yahoo.com/dir/_ylc=X3oDMTI5MGx2aThyBF9TAzIxMTU1MDAzNTIEX3MDMzk2NTQ1MTAzBHNlYwNCQUJwaWxsYXJfTklfMzYwBHNsawNQcm9kdWN0X3F1ZXN0aW9uX3BhZ2U-?link=list&sid=396545433">Get
better relationship answers </A>from someone who knows.<BR>Yahoo! Answers -
Check it out.
<DIV>
<HR SIZE=1>
<DIV></DIV>
<DIV><STRONG>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.
</STRONG></DIV>_______________________________________________<BR>postgis-users
mailing
list<BR>postgis-users@postgis.refractions.net<BR>http://postgis.refractions.net/mailman/listinfo/postgis-users<BR></DIV></DIV></BLOCKQUOTE><BR>
<P>
<HR SIZE=1>
Take the Internet to Go: Yahoo!Go puts the <A
href="http://us.rd.yahoo.com/evt=48253/*http://mobile.yahoo.com/go?refer=1GNXIC">Internet
in your pocket:</A> mail, news, photos & more. </BODY></HTML>