5

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))?

  • 1
    Have you tried using ST_Multi and/or ST_Collect around ST_Union. ST_Union will only union intersecting polygons. You are getting multiple (multipolygons) because each row represents those that actually form an intersecting union. ST_Collect simply concatenates unions, but if you perform this after ST_Union, you should get what you want, which will be one giant multipolygon. – John Powell Apr 21 '17 at 15:12
  • Thanks! I see - I think. So perhaps I should do ST_Union(ST_Collect(t1.geom), ST_Collect(t2.geom)) instead, for efficiency? – Richard Apr 21 '17 at 15:21
  • 1
    No, sorry, I'm half asleep, do as tumasgiu says. – John Powell Apr 21 '17 at 15:26
7

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
  • Thank you so much! That does indeed return a single giant MultiPolygon. As a relative noob I'd like to understand a bit more about what I was doing wrong: was my error a SQL one, or to do with how ST_Union works? i.e. was the Cartesian product a function of the SQL query, or ST_Union? – Richard Apr 21 '17 at 15:30
  • 1
    I think it's about SQL : if the geometries were integers, ask yourself how can you sum the values of your two tables. It's the same idea. You are misunderstanding what the FROM clause of your query is doing, that is : cross joining, i.e. create a new set from the combination of the elements of two other sets. You also probably don't undestand what an aggegate function is. – tumasgiu Apr 21 '17 at 15:34
  • great answer, I was never fully able to put into the words the two ways st_union can be utilized – ziggy Apr 21 '17 at 15:40
  • Quoting Wikipedia about aggregate function : In database management an aggregate function is a function where the values of multiple rows are grouped together as input on certain criteria to form a single value of more significant meaning or measurement such as a set, a bag or a list. – tumasgiu Apr 21 '17 at 15:44

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.