Hi,<br>a and b, are multiple tables derived from -2- shape files saved as POST-GIS data.<br>Slide 1:<br>'a' this data as in the slide show has attributes, This is a square with polygons.<br>'b' as in the slide show is made up of 2 polygons with attributes which overlap 'a'.<br><br>Slide 2: a and b are in UNION with the syntax below. But <span style="background-color: rgb(255, 255, 128);">this is not the desired result.</span> There are polygons overlapping other polygons, one below the other.<br><br>Slide 3:<br>'abc' is a union of a and b,<span style="background-color: rgb(255, 255, 128);"> with desired result </span><br><br>My further analysis with post GIS awaits HELP.<br><br>Cheers<br>Ravi Kumar<br><br><b><i>"Obe, Regina" <robe.dnd@cityofboston.gov></i></b> wrote:<blockquote class="replbq" style="border-left: 2px solid rgb(16, 16, 255); margin-left: 5px; padding-left: 5px;"> <meta http-equiv="Content-Type" content="text/html; charset=us-ascii"> <meta
content="MSHTML 6.00.6000.16481" name="GENERATOR"> <div dir="ltr" align="left"> <div dir="ltr" align="left"><span class="913305812-17092007"><font color="#0000ff" face="Arial" size="2">Ravi,</font></span></div> <div dir="ltr" align="left"><span class="913305812-17092007"><font color="#0000ff" face="Arial" size="2"></font></span> </div> <div dir="ltr" align="left"><span class="913305812-17092007"><font color="#0000ff" face="Arial" size="2"><span class="947490613-17092007">Is the graphic one record or multiple records? If multiple records - I'm afraid you may be asking 2 conflicting questions depending on your dataset so I'm not sure there is anything that can remedy that aside from treating them as two separate questions.</span></font></span></div> <div dir="ltr" align="left"><span class="913305812-17092007"><font face="Arial"><font color="#0000ff"><font size="2"></font></font></font></span> </div> <div dir="ltr" align="left"><span
class="913305812-17092007"><font face="Arial"><font size="2"><font color="#0000ff">I'm <span class="947490613-17092007">also </span>afraid you are getting into territory I'm<span class="947490613-17092007"> very </span><span class="947490613-17092007">weak</span>. You may want to investigate use of st_boundary<span class="947490613-17092007"> (which will give you a multilinestring of a polygon/multipolygon boundary and then apply buildarea to that).</span></font></font></font></span></div> <div dir="ltr" align="left"><span class="913305812-17092007"><font face="Arial"><font color="#0000ff"><font size="2"><span class="947490613-17092007"></span></font></font></font></span> </div> <div dir="ltr" align="left"><span class="913305812-17092007"><font face="Arial"><font color="#0000ff"><font size="2"><span class="947490613-17092007">The below should get rid of some redundant overlapping polygons, but probably won't completely satisfy what
you want to do. I'm also thinking you may want to be using intersection instead of geomunion for the inner part. But again depends what you are trying to answer.</span></font></font></font></span></div> <div dir="ltr" align="left"><span class="913305812-17092007"><font face="Arial"><font color="#0000ff"><font size="2"><span class="947490613-17092007"></span></font></font></font></span> </div> <div dir="ltr" align="left"><span class="913305812-17092007"><font face="Arial"><font color="#0000ff"><font size="2"><span class="947490613-17092007"></span></font></font></font></span> </div> <div dir="ltr" align="left"><span class="913305812-17092007"><span class="947490613-17092007"> <div dir="ltr" align="left"><span class="691344412-13092007"><font color="#0000ff" face="Arial" 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 color="#0000ff"><font face="Arial" size="2">SELECT newtb.code, </font><a href="http://newtb.info/" target="_blank"><span id="lw_1190034122_1" style="background: transparent none repeat scroll 0%; -moz-background-clip: -moz-initial; -moz-background-origin: -moz-initial; -moz-background-inline-policy: -moz-initial;"><font color="#003399" face="Arial" size="2">newtb.info</font></span></a><font face="Arial" size="2">, <span class="947490613-17092007">multi(</span><span class="947490613-17092007">buffer(</span>geomunion(<span class="947490613-17092007">distinct </span>newtb.cgeom)<span class="947490613-17092007">, 0.0))</span> as thenewgeom</font></font></span></div> <div dir="ltr" align="left"><span class="217440812-13092007"><font color="#0000ff" face="Arial" size="2">FROM </font></span></div> <div dir="ltr" align="left"><span class="217440812-13092007"><font face="Arial"><font color="#0000ff"><font size="2">(
SELECT <span class="691344412-13092007">a</span>.code, <span class="691344412-13092007"><a href="http://b.info/" target="_blank"><span id="lw_1190034122_2">b</span>.info</a></span><a href="http://b.info/" target="_blank"></a></font><a href="http://b.info/" target="_blank"></a></font><a href="http://b.info/" target="_blank"></a></font><font color="#0000ff" face="Arial" size="2">, <span class="947490613-17092007"><strong>intersection</strong></span>(<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 color="#0000ff"><font face="Arial"><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
color="#0000ff" face="Arial" 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"><font size="2"> <font color="#0000ff">UNION ALL</font></font></font></span></div> <div dir="ltr" align="left"><span class="217440812-13092007"><font face="Arial" size="2"> <font color="#0000ff">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> </font> <div dir="ltr" align="left"><span class="217440812-13092007"><font color="#0000ff" face="Arial" size="2"> FROM <span class="691344412-13092007">a</span> </font></span></div> <div dir="ltr" align="left"><span class="217440812-13092007"><font color="#0000ff" face="Arial" 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 color="#0000ff" face="Arial" 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"><font size="2"> <font color="#0000ff">UNION ALL</font></font></font></span></div> <div dir="ltr" align="left"><span class="217440812-13092007"><font face="Arial"><font size="2"> <font color="#0000ff">SELECT null as code, <span class="691344412-13092007"><a href="http://b.info/" target="_blank"><font color="#003399"><span id="lw_1190034122_3">b</span>.info</font></a></span>, <span class="691344412-13092007">b</span>.geom<span class="691344412-13092007">etry</span> as cgeom</font></font></font></span></div> <div dir="ltr" align="left"><span class="217440812-13092007"><font color="#0000ff"
face="Arial" 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 color="#0000ff" face="Arial" 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 color="#0000ff" face="Arial" size="2">) AS newtb</font></span></div> <div dir="ltr" align="left"><span class="217440812-13092007"><font color="#0000ff"><font face="Arial" size="2">GROUP BY newtb.code, </font><a href="http://newtb.info/" target="_blank"><span id="lw_1190034122_4"><font color="#003399" face="Arial" size="2">newtb.info</font></span></a></font></span></div></span></div></span></span></div> <div dir="ltr" align="left"><span class="913305812-17092007"><font face="Arial"><font color="#0000ff"><font size="2"><span class="947490613-17092007"> </span></font></font></font></span></div> <div dir="ltr" align="left"><span class="913305812-17092007"><font face="Arial"><font color="#0000ff"><font size="2"><span class="947490613-17092007"></span></font></font></font></span> </div> <div dir="ltr" align="left"><span class="913305812-17092007"><font face="Arial"><font color="#0000ff"><font size="2"><span
class="947490613-17092007">Hope that helps,</span></font></font></font></span></div> <div dir="ltr" align="left"><span class="913305812-17092007"><font face="Arial"><font color="#0000ff"><font size="2"><span class="947490613-17092007">Regina</span></font></font></font></span></div></div><br> <div class="OutlookMessageHeader" dir="ltr" align="left" lang="en-us"> <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> Monday, September 17, 2007 9:04 AM<br><b>To:</b> postgis<br><b>Subject:</b> [postgis-users] UNION<br></font><br></div> <div></div> <div>Hi Regina,</div> <div>giving the link to show actual how the UNION of polygons is not working as per expectation.</div> <div>Please see the clips in the link.</div> <div> </div> <div> <div dir="ltr" align="left"><span class="691344412-13092007"><span
class="691344412-13092007"><font color="#0000ff" face="Arial" 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 color="#0000ff" face="Arial" 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 color="#0000ff" face="Arial" size="2"></font></span> </div> <div dir="ltr" align="left"><span class="691344412-13092007"><font color="#0000ff" face="Arial" size="2">--The insert</font></span></div> <div dir="ltr" align="left"><span class="691344412-13092007"><font color="#0000ff" face="Arial" size="2"></font></span> </div> <div dir="ltr" align="left"><span class="691344412-13092007"><font color="#0000ff" face="Arial" 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 color="#0000ff" face="Arial" size="2">SELECT newtb.code, <a href="http://newtb.info/" target="_blank"><span id="lw_1190034122_1" style="background: transparent none repeat scroll 0%; -moz-background-clip: -moz-initial; -moz-background-origin: -moz-initial; -moz-background-inline-policy: -moz-initial;"><font color="#003399">newtb.info</font></span></a>, geomunion(newtb.cgeom) as thenewgeom</font></span></div> <div dir="ltr" align="left"><span class="217440812-13092007"><font color="#0000ff" face="Arial" size="2">FROM </font></span></div> <div dir="ltr" align="left"><span class="217440812-13092007"><font color="#0000ff" face="Arial" size="2">( SELECT <span class="691344412-13092007">a</span>.code, <span class="691344412-13092007"><a href="http://b.info/" target="_blank"><font color="#003399"><span
id="lw_1190034122_2">b</span>.info</font></a></span>, 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 color="#0000ff" face="Arial" 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 color="#0000ff" face="Arial" size="2">UNION ALL</font></span></div> <div dir="ltr" align="left"><span class="217440812-13092007"> <font color="#0000ff" face="Arial" 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 color="#0000ff" face="Arial" size="2"> FROM <span class="691344412-13092007">a</span> </font></span></div> <div dir="ltr" align="left"><span class="217440812-13092007"><font color="#0000ff" face="Arial" 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 color="#0000ff" face="Arial" 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 color="#0000ff" face="Arial" size="2">UNION ALL</font></span></div> <div dir="ltr" align="left"><span
class="217440812-13092007"> <font color="#0000ff" face="Arial" size="2">SELECT null as code, <span class="691344412-13092007"><a href="http://b.info/" target="_blank"><font color="#003399"><span id="lw_1190034122_3">b</span>.info</font></a></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 color="#0000ff" face="Arial" 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 color="#0000ff" face="Arial" 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 color="#0000ff" face="Arial" size="2">) AS newtb</font></span></div> <div dir="ltr" align="left"><span class="217440812-13092007"><font color="#0000ff" face="Arial" size="2">GROUP BY newtb.code, <a href="http://newtb.info/" target="_blank"><span id="lw_1190034122_4"><font color="#003399">newtb.info</font></span></a></font></span></div></span></div></div> <div> </div> <div>May be I should be using different syntax for such a result.</div>
<div> </div> <div> </div> <div><a href="http://www.kodakgallery.com/I.jsp?c=cjns10h3.8ovw4dsn&x=0&y=w3kbw0" target="_blank" rel="nofollow"><span id="lw_1190033901_2" style="background: rgb(220, 238, 255) none repeat scroll 0%; -moz-background-clip: -moz-initial; -moz-background-origin: -moz-initial; -moz-background-inline-policy: -moz-initial;"><font color="#003399" face="Verdana" size="1">http://www.kodakgallery.com/I.jsp?c=cjns10h3.8ovw4dsn&x=0&y=w3kbw0</font></span></a><font face="Verdana" size="1"> </font></div> <div><font face="Verdana" size="1"></font> </div> <div><font face="Verdana" size="1">Ravi Kumar</font></div> <div> </div><hr size="1"> Catch up on <a href="http://us.rd.yahoo.com/tv/mail/tagline/falltv/evt=47093/*http://tv.yahoo.com/collections/3658%20">fall's hot new shows</a> on Yahoo! TV. Watch previews, get listings, and more! <div></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></blockquote><br><p>
<hr size=1>Need a vacation? <a href="http://us.rd.yahoo.com/evt=48256/*http://travel.yahoo.com/;_ylc=X3oDMTFhN2hucjlpBF9TAzk3NDA3NTg5BHBvcwM1BHNlYwNncm91cHMEc2xrA2VtYWlsLW5jbQ--">Get great deals
to amazing places </a>on Yahoo! Travel.