PostGIS equivalent of ArcMap Union

Question

What is the equivalent in PostGIS / PostgreSQL of the "Union" operation in ArcMap?

Say you have two shapefiles with two features each. (PostGIS equivalent: two tables with two rows with polygon geometries)

then the result would be 1 shapefile with 7 features. (PostGIS equivalent: Table with 7 rows with geometries)

I've looked at ST_Intersect, ST_Union and ST_Collect but can't find the right combination. Your help is much appreciated.

Have a look at this answer on gis.se: https://gis.stackexchange.com/questions/83/separate-polygons-based-on-intersection-using-postgis — thibautg, Nov 13 '18 at 21:56

score 1 · Answer 1 · answered Nov 13 '18 at 22:05

Here is a working query based on this answer from gis.stackexchange:

Read it from a) to d):

-- d) Extract the path number and the geom from the geometry dump
SELECT
  (dump).path[1] id,
  (dump).geom
FROM
(
  -- c) Polygonize the unioned rings (returns a GEOMETRYCOLLECTION)
  --    Dump them to return individual geometries
  SELECT
    ST_Dump(ST_Polygonize(geom)) dump
  FROM
  (
    -- b) Union all rings in one big geometry
    SELECT
      ST_Union(geom) geom
    FROM
    (
      -- a) First get the exterior ring from all geoms
      SELECT
        ST_ExteriorRing(geom) geom
      FROM
        rectangles
    ) a
  ) b
) c

Result:

Note that `ST_ExteriorRing` drops any holes. `ST_Boundary` will preserve the interior rings, but it will also create a polygon inside them. — jpmc26, Jan 18 '19 at 18:41

score 0 · Answer 2 · answered Nov 15 '18 at 11:45

Many thanks to Michael Entin

-- input data
with polys1 AS (
  SELECT 1 df1, ST_GeogFromText('Polygon((0 0, 2 0, 2 2, 0 2, 0 0))') g
  UNION ALL
  SELECT 2, ST_GeogFromText('Polygon((2 2, 4 2, 4 4, 2 4, 2 2))')
),
polys2 AS (
  SELECT 1 df2, ST_GeogFromText('Polygon((1 1, 3 1, 3 3, 1 3, 1 1))') g
  UNION ALL
  SELECT 2, ST_GeogFromText('Polygon((3 3, 5 3, 5 5, 3 5, 3 3))')
),
-- left and right unions
union1 AS (
  SELECT ST_UNION_AGG(g) FROM polys1
),
union2 AS (
  SELECT ST_UNION_AGG(g) FROM polys2
),
-- various combinations of intersections
pairs AS (
  SELECT df1, df2, ST_INTERSECTION(a.g, b.g) g FROM polys1 a, polys2 b WHERE ST_INTERSECTS(a.g, b.g)
  UNION ALL
  SELECT df1, NULL, ST_DIFFERENCE(g, (SELECT * FROM union2)) g FROM polys1
  UNION ALL 
  SELECT NULL, df2, ST_DIFFERENCE(g, (SELECT * FROM union1)) g FROM polys2
)
SELECT * FROM pairs WHERE NOT ST_IsEmpty(g)

PostGIS equivalent of ArcMap Union

2 Answers2

Linked