[postgis-users] UNION

[RAVI KUMAR]

Hi,
a and b, are multiple tables derived from -2- shape files saved as POST-GIS data.
Slide 1:
'a' this data as in the slide show has attributes, This is a square with polygons.
'b' as in the slide show is made up of 2 polygons with attributes which overlap 'a'.

Slide 2: a and b are in  UNION with the syntax below. But this is not the desired result. There are polygons overlapping other polygons, one below the other.

Slide 3:
'abc' is a union of a and b, with desired  result  

My further analysis with post GIS awaits HELP.

Cheers
Ravi Kumar

"Obe, Regina" <robe.dnd at cityofboston.gov> wrote:      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, multi(buffer(geomunion(distinct newtb.cgeom), 0.0)) as  thenewgeom
 FROM 
 (  SELECT a.code, 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, 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


  
  
 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, geomunion(newtb.cgeom) as  thenewgeom
 FROM 
 (  SELECT a.code, 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, 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


  
 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 
  
 Ravi Kumar
  

---------------------------------
 Catch up on fall's  hot new shows 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. 
_______________________________________________
postgis-users mailing list
postgis-users at postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users


       
---------------------------------
Need a vacation? Get great deals to amazing places on Yahoo! Travel. 
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20070918/fe11d49e/attachment.html>