ST_Union in PostgreSQL and QGIS

Question

I have a problem with understanding of the ST_Union (PostgreSQL) and the union function in QGIS. Basically, I have as an example two layers A and B that overlap. The result when using union in QGIS is one single layer with three rows: A without B, A & B, B without A. This is in principal the result I would like to have. Because the tables are quite big, I would like to select some columns and attributes; therefore I created a query in PostgreSQL. Following my approach:

SELECT row_number() over (order by a.id desc) as gid, a.nsg as nsg, b.ffh as ffh, 
       ST_Union(a.geom,b.geom) as geom, st_area(St_union(a.geom,b.geom)) as area
FROM   p_gaiac.shape1 as a, p_gaiac.shape2 as b 

The result is a single layer with only one row (attributes A & B). I am not able to get separate rows with separate attributes like in QGIS. What is wrong?

Answer 1

Now that I have a couple minutes, I can expand on my previous comment. ST_Union() in PostGIS and the Union tool in QGIS do different things, despite the similar names. ST_Union() is similar to the Dissolve tool in QGIS, in that it will eliminate boundaries of adjacent features and/or group single features with the same attribute values into Multi* features.

I believe what you are looking for is the ST_Intersection() of the overlapping geometries.

To make things more confusing, I think you will want to use the PostgreSQL operator UNION, which will append your selected geometries and other attributes from your two tables into the same column.

So I think the answer you're looking for is something like this (updated to more accurately answer your original question and your follow-up comment):

WITH cte as (
SELECT nsg, NULL as ffh, ST_Area(geom) as area, geom 
FROM p_gaiac.shape1
UNION
SELECT NULL as nsg, ffh, ST_Area(geom) as area, geom 
FROM p_gaiac.shape2
UNION
SELECT nsg, ffh, area, geom 
FROM (SELECT nsg, ffh, ST_Area(ST_Intersection(a.geom, b.geom)) as area, 
      ST_Intersection(a.geom, b.geom) as geom
      FROM p_gaiac.shape1 as a 
      JOIN p_gaiac.shape2 as b ON ST_Intersects(a.geom, b.geom)) c
)

SELECT ROW_NUMBER () OVER () as new_id, nsg, ffh, area, geom
from cte; 

By placing the main query within a CTE, you can then assign a new unique ID to the whole set, which will allow you to bring it into QGIS to view. By placing NULL placeholders in columns for which the original table does not have, you are able then to populate both the columns in the third select statement, which you will want for the overlapping areas. You can expand on this to include more columns from your original tables as necessary, by using the NULL placeholder.

Answer 2

The code above resulted in two overlapping areas. In the following I added a St_difference to remove the overlapping areas from the basic shapes. The result is now comparable to the union in qgis.

WITH cte as (
SELECT nsg, NULL as ffh, St_difference(a.geom,b.geom) as geom, round(St_area(St_difference(a.geom,b.geom))/10000) as area
FROM p_gaiac.shape1 as a, p_gaiac.shape2 as b 
UNION
SELECT Null as nsg, ffh, St_difference(b.geom,a.geom) as geom, round(St_area(St_difference(b.geom,a.geom))/10000) as area
FROM p_gaiac.shape1 as a, p_gaiac.shape2 as b 
UNION
SELECT nsg, ffh, geom, area
FROM (SELECT nsg, ffh, round(ST_Area(ST_Intersection(a.geom, b.geom))/10000) as area, 
      ST_Intersection(a.geom, b.geom) as geom
      FROM p_gaiac.shape1 as a 
      JOIN p_gaiac.shape2 as b ON ST_Intersects(a.geom, b.geom)) c
)

SELECT ROW_NUMBER () OVER () as new_id, nsg, ffh, geom, area
from cte;