[postgis-users] UNION

Andreas Laggner andreas.laggner at fal.de
Thu Sep 13 02:37:56 PDT 2007


Hi Ravi, Hi Regina,

it seems to me that Ravi wants to perform the same GIS-Operation as i 
want - Ravi, perhaps look at the mails with "Union of 7 datasets" from 
end of august.
Point 2 works good with a left join, but part 1 i did not get so far.

cheers      Andreas


RAVI KUMAR schrieb:
> Hi Regina,
> I am trying to have a UNION of two shapefiles.
> shape A has code as attribute
> shape B has info as attribute
>  
> I wish to have a result where
> 1. Both the shapes intersected portions will have both the attributes and
> 2. The portions which donot intersect have their portion represented 
> as well,
>     with zero as value for the attribute information that is missing.
>  
> So the resultant shape AB will have 2 attributes Code and Info and
> values as per intersection, or zero values for the attribute that 
> doesnot intersect.
>  
> The resultant will also have corresponding geometries of intersected 
> portions as polygons gernerated, and unintersected portions also 
> modified as seperate polygons.
>  
> Please give me some lead to find proper SQL query.
>  
> Cheers
> Ravi Kumar
>
>
> */"Obe, Regina" <robe.dnd at cityofboston.gov>/* wrote:
>
>     Ravi,
>      
>     Still not quite clear what question you are trying to answer.
>      
>     I am guessing that you are getting duplicates because you have a
>     one to many going on here.  But I'm not sure which is the one and
>     which is the many.
>      
>     Also you don't need coalesce here since that is only useful if you
>     expect one of your fields to be null.  In this case since you are
>     doing an inner join, neither will be NULL.  Or perhaps you don't
>     want to do an inner join.
>      
>     It might be that you really want to use the aggregate form of
>     geomunion
>      
>     Something like
>      
>     SELECT strubuf.struclass, geomunion(geof.geometry) AS geofac
>     FROM geof INNER JOIN strubuf ON (geof.geometry && strubuf.geometry AND
>      intersects(strubuf.geometry, geof.geometry))
>     GROUP BY strubuf.struclass
>      
>     Just a guess.  But perhaps that doesn't answer the question you
>     were trying to ask.
>
>     ------------------------------------------------------------------------
>     *From:* postgis-users-bounces at postgis.refractions.net
>     [mailto:postgis-users-bounces at postgis.refractions.net] *On Behalf
>     Of *RAVI KUMAR
>     *Sent:* Tuesday, September 11, 2007 6:26 AM
>     *To:* jump-users at lists.jump-project.org
>     *Cc:* postgis
>     *Subject:* [postgis-users] UNION
>
>     Hi,
>     I have prepared a course material for training in Jump for
>     Geoscientists.
>     Added a minerological theme in which the final output results in
>     prospectivity map.
>
>     I have used Post-GIS where ever complex bullion operators are
>     needed. This was done smoothly.
>
>     But Iam now held-up in UNION and Intersection. Jump gives an
>     OVERLAY which equals intersection. But the problem needs both
>     Intersected portion and
>     Non intersecting portion together.
>
>     Followed the advise given on this list to get the result. But the
>     result
>     OPENJUMP
>     1.Overlay Layers A, B
>     2. Make union of the result (to use as a mask)
>     3. A-Union and B-union and combine theses two layers
>     4. Add Overlay to this.
>
>     This does the work but you have duplicate geometries which are
>     filled with different attributes in different instances.
>     POSTGIS:
>     Followinf the suggestions on the Postgis list I have tried this query.
>     SELECT strubuf.struclass, COALESCE(geomunion(strubuf.geometry,
>      geof.geometry), geof.geometry) AS geofac FROM geof INNER JOIN
>     strubuf ON (geof.geometry && strubuf.geometry AND
>      intersects(strubuf.geometry, geof.geometry));
>
>     But this doesnt result in a table geofac
>
>
>
>     This is where I am..
>     But for this additional exercise every thing else is ready.
>     However Proof of Concept for Geoscientists lies in THIS.
>
>
>     Cheers
>     Ravi Kumar
>     ------------------------------------------------------------------------
>     Be a better Heartthrob. Get better relationship answers
>     <http://us.rd.yahoo.com/evt=48255/*http://answers.yahoo.com/dir/_ylc=X3oDMTI5MGx2aThyBF9TAzIxMTU1MDAzNTIEX3MDMzk2NTQ1MTAzBHNlYwNCQUJwaWxsYXJfTklfMzYwBHNsawNQcm9kdWN0X3F1ZXN0aW9uX3BhZ2U-?link=list&sid=396545433>from
>     someone who knows.
>     Yahoo! Answers - Check it out.
>     ------------------------------------------------------------------------
>     *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
>
>
> ------------------------------------------------------------------------
> Take the Internet to Go: Yahoo!Go puts the Internet in your pocket: 
> <http://us.rd.yahoo.com/evt=48253/*http://mobile.yahoo.com/go?refer=1GNXIC> 
> mail, news, photos & more.
> ------------------------------------------------------------------------
>
> _______________________________________________
> postgis-users mailing list
> postgis-users at postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-users
>   


-- 
Dipl. Geoökologe Andreas Laggner
Institut für Ländliche Räume (LR)
Bundesforschungsanstalt für Landwirtschaft (FAL)

Institute of Rural Studies
Federal Agricultural Research Centre (FAL)

Bundesallee 50
D-38116 Braunschweig

Tel.: (+49) (0)531 596 5515
Fax: (+49) (0)531 596 5599
E-mail: andreas.laggner at fal.de
Homepage: http://www.lr.fal.de/ 




More information about the postgis-users mailing list