[postgis-users] UNION

[Obe, Regina]

Ravi,
 
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.
 
I'm also afraid you are getting into territory I'm very weak.  You may
want to investigate use of st_boundary (which will give you a
multilinestring  of a polygon/multipolygon boundary and then apply
buildarea to that).
 
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.
 
 
INSERT INTO abc(code, info, the_geom)
SELECT newtb.code,  newtb.info <http://newtb.info/> ,
multi(buffer(geomunion(distinct newtb.cgeom), 0.0)) as thenewgeom
FROM 
(  SELECT a.code, b.info <http://b.info/> , intersection(a.geometry,
b.geometry) as cgeom
    FROM a
        INNER JOIN b ON a.geometry && b.geometry AND
intersects(a.geometry, b.geometry)
    UNION ALL
    SELECT a.code, null As info, a.geometry as cgeom 
    FROM a 
        LEFT JOIN b ON a.geometry && b.geometry AND
intersects(a.geometry, b.geometry)
        WHERE b.geometry IS NULL
    UNION ALL
    SELECT null as code, b.info <http://b.info/> , b.geometry as cgeom
            FROM b LEFT JOIN a ON a.geometry && b.geometry AND
intersects(a.geometry, b.geometry)
           WHERE a.geometry IS NULL
)  AS newtb
GROUP BY newtb.code, newtb.info <http://newtb.info/> 
 
 
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: Monday, September 17, 2007 9:04 AM
To: postgis
Subject: [postgis-users] UNION


Hi Regina,
giving the link to show actual how the UNION of polygons is not working
as per expectation.
Please see the clips in the link.
 
CREATE TABLE abc(code smallint, info smallint) with oids;
SELECT AddGeometryColumn('public', 'abc', 'the_geom', 4326,
'MULTIPOLYGON', 2);
 
--The insert
 
INSERT INTO abc(code, info, the_geom)
SELECT newtb.code,  newtb.info <http://newtb.info/> ,
geomunion(newtb.cgeom) as thenewgeom
FROM 
(  SELECT a.code, b.info <http://b.info/> , geomunion(a.geometry,
b.geometry) as cgeom
    FROM a
        INNER JOIN b ON a.geometry && b.geometry AND
intersects(a.geometry, b.geometry)
    UNION ALL
    SELECT a.code, null As info, a.geometry as cgeom 
    FROM a 
        LEFT JOIN b ON a.geometry && b.geometry AND
intersects(a.geometry, b.geometry)
        WHERE b.geometry IS NULL
    UNION ALL
    SELECT null as code, b.info <http://b.info/> , b.geometry as cgeom
            FROM b LEFT JOIN a ON a.geometry && b.geometry AND
intersects(a.geometry, b.geometry)
           WHERE a.geometry IS NULL
)  AS newtb
GROUP BY newtb.code, newtb.info <http://newtb.info/> 
 
May be I should be using different syntax for such a result.
 
 
http://www.kodakgallery.com/I.jsp?c=cjns10h3.8ovw4dsn&x=0&y=w3kbw0
<http://www.kodakgallery.com/I.jsp?c=cjns10h3.8ovw4dsn&x=0&y=w3kbw0>  
 
Ravi Kumar

________________________________

Catch up on fall's hot new shows
<http://us.rd.yahoo.com/tv/mail/tagline/falltv/evt=47093/*http://tv.yaho
o.com/collections/3658>  on Yahoo! TV. Watch previews, get listings, and
more! 



-----------------------------------------
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.
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20070917/f94df5ad/attachment.html>