<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<meta content="text/html;charset=ISO-8859-1" http-equiv="Content-Type">
</head>
<body bgcolor="#ffffff" text="#000000">
It sounds like you are after performing an overlay operation on two
polygonal datasets.<br>
<br>
I would do something like:<br>
- extract all lines from your polygons<br>
- node the linework<br>
- re-polygonize the noded lines<br>
- transfer attributes from the original dataset to the newly formed
polygons.<br>
<br>
<a class="moz-txt-link-freetext" href="http://postgis.refractions.net/support/wiki/index.php?ExamplesOverlayTables">http://postgis.refractions.net/support/wiki/index.php?ExamplesOverlayTables</a><br>
<br>
Hope this helps,<br>
Kevin<br>
<pre class="moz-signature" cols="72">-------------
Kevin Neufeld
Software Developer
Refractions Research Inc.
300-1207 Douglas St.
Victoria, B.C., V8W 2E7
Phone: (250) 383-3022
Email: <a class="moz-txt-link-abbreviated" href="mailto:kneufeld@refractions.net">kneufeld@refractions.net</a></pre>
<br>
<br>
RAVI KUMAR wrote:
<blockquote cite="mid:290266.69461.qm@web60516.mail.yahoo.com"
type="cite">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" <a class="moz-txt-link-rfc2396E" href="mailto:robe.dnd@cityofboston.gov"><robe.dnd@cityofboston.gov></a></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; ">
<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"></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"></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
moz-do-not-send="true" href="http://newtb.info/" target="_blank"><span
id="lw_1190034122_1"
style="background: transparent none repeat scroll 0% 50%; -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 moz-do-not-send="true"
href="http://b.info/" target="_blank"><span id="lw_1190034122_2">b</span>.info</a></span></font></font></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 moz-do-not-send="true"
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
moz-do-not-send="true" 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>
<a class="moz-txt-link-abbreviated" href="mailto:postgis-users-bounces@postgis.refractions.net">postgis-users-bounces@postgis.refractions.net</a>
[<a class="moz-txt-link-freetext" href="mailto:postgis-users-bounces@postgis.refractions.net">mailto:postgis-users-bounces@postgis.refractions.net</a>] <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>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"></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"></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
moz-do-not-send="true" href="http://newtb.info/" target="_blank"><span
id="lw_1190034122_1"
style="background: transparent none repeat scroll 0% 50%; -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 moz-do-not-send="true"
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 moz-do-not-send="true"
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
moz-do-not-send="true" 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 moz-do-not-send="true"
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% 50%; -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> </div>
<div><font face="Verdana" size="1">Ravi Kumar</font></div>
<div> </div>
<hr size="1"> Catch up on <a moz-do-not-send="true"
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!
<hr size="1">
<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>
<a class="moz-txt-link-abbreviated" href="mailto:postgis-users@postgis.refractions.net">postgis-users@postgis.refractions.net</a><br>
<a class="moz-txt-link-freetext" href="http://postgis.refractions.net/mailman/listinfo/postgis-users">http://postgis.refractions.net/mailman/listinfo/postgis-users</a><br>
</blockquote>
<br>
<p> </p>
<hr size="1">Need a vacation? <a moz-do-not-send="true"
href="http://us.rd.yahoo.com/evt=48256/*http://travel.yahoo.com/;_ylc=X3oDMTFhN2hucjlpBF9TAzk3NDA3NTg5BHBvcwM1BHNlYwNncm91cHMEc2xrA2VtYWlsLW5jbQ--">Get
great deals to amazing places </a>on Yahoo! Travel.
<pre wrap="">
<hr size="4" width="90%">
_______________________________________________
postgis-users mailing list
<a class="moz-txt-link-abbreviated" href="mailto:postgis-users@postgis.refractions.net">postgis-users@postgis.refractions.net</a>
<a class="moz-txt-link-freetext" href="http://postgis.refractions.net/mailman/listinfo/postgis-users">http://postgis.refractions.net/mailman/listinfo/postgis-users</a>
</pre>
</blockquote>
</body>
</html>