What does the ST_Union of the geometry column of two tables produce?

Question

I'm working in Postgres 9.5. I have two tables, each with a column geometry(MultiPolygon,4326).

I want to creat a single MultiPolygon that is the union of every polygon in the two tables.

I have tried running ST_Union, but it isn't doing quite what I expected. If I do this:

SELECT
ST_GeometryType(ST_Union(table1.geom, table2.geom))
FROM table1, table2;

This produces lots of MultiPolygon results, instead of a single one, which suggests to me that the result of a simple ST_Union is not a single polygon.

What is ST_Union(table1.geom, table2.geom) actually giving me, conceptually? Why are there multiple polygons?

What do I need to do to get a single multipolygon - something like ST_Union(ST_Union(table1.geom), ST_Union(table2.geom))?

Answer 1

St_Union comes in two flavors, aggregate and simple.

In your query you are using the simple one that is creating the union of two single geometries.

What you are doing is cross joining your two tables, and make union of every combination of the geometries of your two tables, resulting in n*m multipolygons, where n is the row count of your first table and m the count of the second one. This is called a cartesian product.

To accomplish what you want, you need to use the aggregate version, that will behave like the sql-standard sum function. Something like :

SELECT st_union(geom) FROM (SELECT geom FROM table1 UNION SELECT geom FROM table2) as foo