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]
>http://postgis.refractions.net/mailman/listinfo/postgis-users
>
>
>
_______________________________________________
postgis-users mailing list
[email protected]
http://postgis.refractions.net/mailman/listinfo/postgis-users
