[postgis-users] Using PostGIS to replicate ESRI Union Output

[Paul Ramsey]

http://s3.opengeo.org/postgis-power.pdf

On Thu, Dec 8, 2011 at 6:13 AM, Carpenter, Craig
<Craig.Carpenter at dec.wa.gov.au> 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: postgis-users-bounces at postgis.refractions.net [postgis-users-bounces at postgis.refractions.net] On Behalf Of Phil James [borntopedal at yahoo.co.uk]
> 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" <Craig.Carpenter at dec.wa.gov.au>
> To: "postgis-users at postgis.refractions.net" <postgis-users at postgis.refractions.net>
> 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
> postgis-users at postgis.refractions.net<mailto:postgis-users at postgis.refractions.net>
> 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
> postgis-users at postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-users
>