1

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?

  • ST_Union() in PostGIS is like the 'Dissolve' tool in QGIS. What you need to use is something like 'select ST_Intersection(a.geom, b.geom) where ST_Intersects(a.geom, b.geom) in order to return the area where both A and B overlap. – pdavis Mar 21 at 16:13
  • 1
    It's best practice to use the JOIN operator instead of a list in the FROM clause, so you don't forget to specify the join rule in the WHERE clause. – Vince Mar 21 at 18:11
3

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.

  • o.k. I see, it has to be used and structured differently, it works! Thank you! I wonder where to put the serial number as I tried in my script and I would like to take over some attributes from table 1 (nature reserve) and table 2 (wet soil) in to separat columns. Is it possible in one step or is a following join needed? – oppia04 Mar 22 at 7:38
  • @oppia04, I've updated my answer and hopefully it answers your question better. – pdavis Mar 22 at 16:57
  • your the best! Thank you very much! Greatings to Montana! – oppia04 Mar 22 at 18:04
0

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;

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.