<!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=691344412-13092007><FONT face=Arial
color=#0000ff size=2>First I would call your field something other than geometry
since that is confusing and could get you into trouble since its a psuedo
keyword</FONT></SPAN></DIV>
<DIV dir=ltr align=left><SPAN class=691344412-13092007><FONT face=Arial
color=#0000ff size=2></FONT></SPAN> </DIV>
<DIV dir=ltr align=left><SPAN class=691344412-13092007><FONT face=Arial
color=#0000ff size=2>So would be something like</FONT></SPAN></DIV>
<DIV dir=ltr align=left><SPAN class=691344412-13092007><FONT face=Arial
color=#0000ff size=2></FONT></SPAN> </DIV>
<DIV dir=ltr align=left><SPAN class=691344412-13092007><FONT face=Arial
color=#0000ff size=2>DROP TABLE abc</FONT></SPAN></DIV>
<DIV dir=ltr align=left><SPAN class=691344412-13092007><FONT face=Arial
color=#0000ff size=2>CREATE TABLE abc(code smallint, info smallint,
the_geom geometry) with oids</FONT></SPAN></DIV>
<DIV dir=ltr align=left><SPAN class=691344412-13092007><FONT face=Arial
color=#0000ff size=2></FONT></SPAN> </DIV>
<DIV dir=ltr align=left><SPAN class=691344412-13092007><FONT face=Arial
color=#0000ff size=2>(you might want to use addgeomtrycolumn to create the_geom
field so geometry_columns table is updated, but I'm not sure what you are
expecting for a geometry so not sure what parameters to feed you but would be
something like this.</FONT></SPAN></DIV>
<DIV dir=ltr align=left><SPAN class=691344412-13092007><FONT face=Arial
color=#0000ff size=2></FONT></SPAN> </DIV>
<DIV dir=ltr align=left><SPAN class=691344412-13092007><SPAN
class=691344412-13092007><FONT face=Arial color=#0000ff size=2>CREATE TABLE
abc(code smallint, info smallint) with oids;</FONT></SPAN></SPAN></DIV>
<DIV dir=ltr align=left><SPAN class=691344412-13092007><SPAN
class=691344412-13092007><FONT face=Arial color=#0000ff size=2>SELECT
AddGeometryColumn('public', 'abc', 'the_geom', 4326, 'MULTIPOLYGON',
2);</FONT></SPAN></SPAN></DIV>
<DIV dir=ltr align=left><SPAN class=691344412-13092007><FONT face=Arial
color=#0000ff size=2></FONT></SPAN> </DIV>
<DIV dir=ltr align=left><SPAN class=691344412-13092007><FONT face=Arial
color=#0000ff size=2>--The insert</FONT></SPAN></DIV>
<DIV dir=ltr align=left><SPAN class=691344412-13092007><FONT face=Arial
color=#0000ff size=2></FONT></SPAN> </DIV>
<DIV dir=ltr align=left><SPAN class=691344412-13092007><FONT face=Arial
color=#0000ff size=2>INSERT INTO abc(code, info,
the_geom)</FONT></SPAN></DIV>
<DIV dir=ltr align=left><SPAN class=691344412-13092007>
<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 <SPAN
class=691344412-13092007>a</SPAN>.code, <SPAN
class=691344412-13092007>b</SPAN>.info, geomunion(<SPAN
class=691344412-13092007>a</SPAN>.geom<SPAN
class=691344412-13092007>etry</SPAN>, <SPAN
class=691344412-13092007>b</SPAN>.geom<SPAN
class=691344412-13092007>etry</SPAN>) as cgeom</FONT></SPAN></DIV>
<DIV dir=ltr align=left><SPAN class=217440812-13092007><FONT face=Arial><FONT
color=#0000ff><FONT size=2> FROM <SPAN
class=691344412-13092007>a</SPAN></FONT></FONT></FONT></SPAN></DIV>
<DIV dir=ltr align=left><SPAN class=217440812-13092007><FONT face=Arial
color=#0000ff size=2> INNER
JOIN <SPAN class=691344412-13092007>b</SPAN> ON <SPAN
class=691344412-13092007>a</SPAN>.geom<SPAN class=691344412-13092007>etry</SPAN>
&& <SPAN class=691344412-13092007>b</SPAN>.geom<SPAN
class=691344412-13092007>etry</SPAN> AND intersects(<SPAN
class=691344412-13092007>a</SPAN>.geom<SPAN
class=691344412-13092007>etry</SPAN>, <SPAN
class=691344412-13092007>b</SPAN>.geom<SPAN
class=691344412-13092007>etry</SPAN>)</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 <SPAN
class=691344412-13092007>a</SPAN>.code, null As info, <SPAN
class=691344412-13092007>a</SPAN>.geom<SPAN class=691344412-13092007>etry</SPAN>
as cgeom</FONT>
<DIV dir=ltr align=left><SPAN class=217440812-13092007><FONT face=Arial
color=#0000ff size=2> FROM <SPAN
class=691344412-13092007>a</SPAN> </FONT></SPAN></DIV>
<DIV dir=ltr align=left><SPAN class=217440812-13092007><FONT face=Arial
color=#0000ff size=2> LEFT
JOIN <SPAN class=691344412-13092007>b</SPAN> ON <SPAN
class=691344412-13092007>a</SPAN>.geom<SPAN class=691344412-13092007>etry</SPAN>
&& <SPAN class=691344412-13092007>b</SPAN>.geom<SPAN
class=691344412-13092007>etry</SPAN> AND intersects(<SPAN
class=691344412-13092007>a</SPAN>.geom<SPAN
class=691344412-13092007>etry</SPAN>, <SPAN
class=691344412-13092007>b</SPAN>.geom<SPAN
class=691344412-13092007>etry</SPAN>)</FONT></SPAN></DIV></SPAN></DIV>
<DIV dir=ltr align=left><SPAN class=217440812-13092007><FONT face=Arial
color=#0000ff size=2> WHERE <SPAN
class=691344412-13092007>b</SPAN>.geom<SPAN class=691344412-13092007>etry</SPAN>
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, <SPAN
class=691344412-13092007>b</SPAN>.info, <SPAN
class=691344412-13092007>b</SPAN>.geom<SPAN class=691344412-13092007>etry</SPAN>
as cgeom</FONT></SPAN></DIV>
<DIV dir=ltr align=left><SPAN class=217440812-13092007><FONT face=Arial
color=#0000ff
size=2>
FROM <SPAN class=691344412-13092007>b</SPAN> LEFT JOIN <SPAN
class=691344412-13092007>a</SPAN> ON <SPAN
class=691344412-13092007>a</SPAN>.geom<SPAN class=691344412-13092007>etry</SPAN>
&& <SPAN class=691344412-13092007>b</SPAN>.geom<SPAN
class=691344412-13092007>etry</SPAN> AND intersects(<SPAN
class=691344412-13092007>a</SPAN>.geom<SPAN
class=691344412-13092007>etry</SPAN>, <SPAN
class=691344412-13092007>b</SPAN>.geom<SPAN
class=691344412-13092007>etry</SPAN>)</FONT></SPAN></DIV>
<DIV dir=ltr align=left><SPAN class=217440812-13092007><FONT face=Arial
color=#0000ff
size=2>
WHERE <SPAN class=691344412-13092007>a</SPAN>.geom<SPAN
class=691344412-13092007>etry</SPAN> 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></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> Thursday, September 13, 2007 8:17 AM<BR><B>To:</B>
postgis<BR><B>Subject:</B> [postgis-users] Join variation<BR></FONT><BR></DIV>
<DIV></DIV>Hi Andreas and Regina,<BR>I have used the following to get a result
which will have polygons that do not overlap.<BR>But failed. <BR>Pl help me and
correct the following query.<BR>All I want is that after UNION of two layers,
the polygons must split and have both the attributes of source layers. (NO PLace
for overlapping polygons)<BR><BR>drop table abc;<BR>create table abc (code
smallint, info smallint, geometry geometry) with oids;<BR><BR>insert into abc
(select a.code, b.info,(geomunion(a.geometry,<BR> b.geometry)) AS abc FROM
b inner JOIN a ON (a.geometry && b.geometry AND
intersects(a.geometry, b.geometry)));<BR><BR>I can actually send two small shape
files 'a' and 'b' If U want to private mail Ids..<BR>Along with the result I
wish to get..<BR><BR>Cheers<BR>Ravi Kumar<BR>
<P>
<HR SIZE=1>
Got a little couch potato? <BR>Check out fun <A
href="http://us.rd.yahoo.com/evt=48248/*http://search.yahoo.com/search?fr=oni_on_mail&p=summer+activities+for+kids&cs=bz">summer
activities for kids.</A></BODY></HTML>
<HTML><BODY><P><hr size=1></P>
<P><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></P></BODY></HTML>