http://s3.opengeo.org/postgis-power.pdf
On Thu, Dec 8, 2011 at 6:13 AM, Carpenter, Craig <[email protected]> wrote: > Phil > > Ive just had a look at your suggestion. > > The output table is structured correctly, in that the geometry is unioned on > a single row with the attribution from the 2 input datasets. > > However the geometry isnt split correctly where overlaps occur, i.e. a new > geometry record isnt created for the overlapping segments. In the case of the > diagram attached the input has 3 records from 2x datasets and the final > output from your suggestion only has 3 records but now in 1x dataset. The > desired outcome would be to have 5 records (2x additional records created for > the overlapping segments) in 1x dataset. > > So in summary you have helped solve a piece of the puzzle and it has brought > me a lil closer to my answer of trying to replicate the ESRI Union Analysis. > > Cheers Phil > > Regards > Craig > > > ________________________________________ > From: [email protected] > [[email protected]] On Behalf Of Phil James > [[email protected]] > Sent: Thursday, 8 December 2011 2:34 PM > To: PostGIS Users Discussion > Subject: Re: [postgis-users] Using PostGIS to replicate ESRI Union Output > > How About > > CREATE TABLE newtable AS( > SELECT a.col1, a.col2, b.col1, b.col2, ST_UNION(b.the_geom, b.the_geom) AS > the_geom > FROM > table1 a, table2 b > WHERE > > ST_INTERSECTS(a.the_geom, b.the_geom)) > > changing cols to the columns you want. > > Phil > > ________________________________ > From: "Carpenter, Craig" <[email protected]> > To: "[email protected]" > <[email protected]> > Sent: Thursday, 8 December 2011, 5:51 > Subject: [postgis-users] Using PostGIS to replicate ESRI Union Output > > Hello All > > I'm a new user to PostGIS and am trying to get my head around it. I come from > an ESRI background and am currently trying to replicate the ESRI Union > Analysis function, unfortunately I'm not having much luck. > > I've read alot of proposed solutions but nothing comes close to providing the > output that mimics the ESRI Union output. > > Essentially what I'm trying to do is overlay 2x or more vector datasets over > the top of each other and create new geometry where they overlap and cut each > other, while combining the attributes of all input spatial datasets. The > attached picture demonstrates what I'm trying to do. Its important that were > overlap occurs a new polygon is created which combines the attribution of the > overlapping layers in a single record / row. > > "Union All" doesnt work very well as I have many differing columns. > > I have managed to get the geometry overlapped and new polygons created where > overlaps occur, however I havent managed to get the inner rings sorted. > However the attributes from varying input datasets are split over seperate > rows. > > The process that I've currently worked on is summarised below: > 1) Combine input datasets into a single table using "Union All" and null > spacers > 2) Dump multi polygon to single polygon > 3) Convert to exterior ring, union, and polygonize > 4) Do pointonsurface analysis to create point layer centroids of new > polygonized polygons > 5) Create join table where pointonsurface point layer intersects with > original data to extract attribution > 6) Join attributes from the join table to the pointonsurface point layer > 7) Create new table combining the polygonized geometry with the attribution > of the point layer where intersects > > > Any help or advice would be appreciated, I've almost pulled all my hair > out.... > Regards > Craig > > > This email, together with any attachments, is intended for the > addressee only. It may contain confidential or privileged information. > If you are not the intended recipient of this email, please notify > the sender, delete the email and attachments from your system and > destroy any copies you may have taken of the email and its attachments. > Duplication or further distribution by hardcopy, by electronic means > or verbally is not permitted without permission. > > _______________________________________________ > postgis-users mailing list > [email protected]<mailto:[email protected]> > http://postgis.refractions.net/mailman/listinfo/postgis-users > > > > This email, together with any attachments, is intended for the > addressee only. It may contain confidential or privileged information. > If you are not the intended recipient of this email, please notify > the sender, delete the email and attachments from your system and > destroy any copies you may have taken of the email and its attachments. > Duplication or further distribution by hardcopy, by electronic means > or verbally is not permitted without permission. > > _______________________________________________ > postgis-users mailing list > [email protected] > http://postgis.refractions.net/mailman/listinfo/postgis-users > _______________________________________________ postgis-users mailing list [email protected] http://postgis.refractions.net/mailman/listinfo/postgis-users
