[postgis-users] ESRI union (analysis) in Postgis

[Ralf Suhr]

Hi Teresa,

you can enhance the following construct to fit your needs.

CREATE VIEW fakeunion AS
  -- poly1 only
  SELECT a, b, NULL AS c, NULL AS d, geom
  FROM poly1
  WHERE gid NOT IN
    (
      SELECT p1.gid
      FROM poly1 p1
        INNER JOIN
        poly2 p2 ON (ST_Intersects(p1.geom,p2.geom))
    )
  -- poly2 only
  UNION
  SELECT NULL AS a, NULL AS b, c, d, geom
  FROM poly2
  WHERE gid NOT IN
    (
      SELECT p2.gid
      FROM poly2 p2
        INNER JOIN
        poly1 p1 ON (ST_Intersects(p1.geom,p2.geom))
    )
  -- poly1 & poly2
  UNION
  SELECT a, b, c, d, ST_Intersection(p1.geom, p2.geom) AS geom
  FROM poly2 p2
    INNER JOIN
    poly1 p1 ON (ST_Intersects(p1.geom,p2.geom))
  -- poly1 - poly2
  UNION
  SELECT a, b, NULL AS c, NULL AS d, ST_Difference(p1.geom, p2.geom) AS geom
  FROM poly2 p2
    INNER JOIN
    poly1 p1 ON (ST_Intersects(p1.geom,p2.geom))
  --  poly2 - poly1
  UNION
  SELECT NULL AS a, NULL AS b, c, d, ST_Difference(p2.geom, p1.geom) AS geom
  FROM poly2 p2
    INNER JOIN
    poly1 p1 ON (ST_Intersects(p1.geom,p2.geom))
;

SELECT *
FROM fakeunion
WHERE ST_GeometryType(geom) = 'ST_LineString'

Gr
Ralf

Am Mittwoch 07 Juli 2010, 09:31:07 schrieb Teresa Fazio:
> I would like to perform in PostGIS a processing like that performed by
> the union(analysis) tool of ArcGIS.
> See here reference
> http://webhelp.esri.com/arcgisdesktop/9.2/index.cfm?TopicName=Union_(Analys
> is)
> <http://webhelp.esri.com/arcgisdesktop/9.2/index.cfm?TopicName=Union_%28An
> alysis%29>
> 
> 
> It is very difficult to add some words to what it does, so I've attached
> two layers before and after the union.
> 
> This tool repeats the new created geometries as many times as the number
> of the geometries which partecipate to the intersection, populating the
> attribute table with the relative generating geometry values.
> So, after its execution, it allows you to perform a sum on a column
> attribute grouping by geometry.
> 
> Maybe such an operation could be generated using aotmic PostGIS operators.
> 
> Thank you very much