3

I have two line layers in QGIS, of which one layer (layer 2 - yellow in the image) is a geometrical subset of the other (layer 1- blue in the image). The two layers also differ in their values of a column. I want to sum the values of layer 2 (yellow) to the values of layer 1 (blue), where their geometries are equal.

enter image description here

I created a virtual layer like this:

select
    a.DTV + b.DTV as DTV,
    a.geometry
from layer1 a
left join layer2 b
on st_equals(a.geometry, b.geometry)

However, this returns a layer with a column 'DTV' which has the sum of the values in the two layers where their geometries are equal, but everywhere else it is NULL.

What I want is a column which has everywhere else the values of layer 1.

In other words, I want to create a 'copy' of layer 1 but change the values where its geometry is overlapping with layer 2. How do I need to adjust the SQL accordingly?

Improve this question
1
  • @Taras I'm not very familiar with SQL. Where woud I need to to include group by? I tried already some conditions (where...else...) but nothing worked as I hoped for.
    – i.i.k.
    Commented Jul 15 at 13:52

2 Answers 2

Reset to default
5

b.DTV will be NULL when there are no duplicate geometries so a.DTV+NULL=NULL.

With coalesce you can replace NULL with 0, whenever b.DTV is NULL:

select
    a.DTV + coalesce(b.DTV, 0) as DTV,
    a.geometry
from layer1 a
left join layer2 b
on st_equals(a.geometry, b.geometry)

Example:

I have the layer firstlayer with three lines, all with DTV values. In a second layer secondlayer I have two lines, the line with id 1 have an identical geometry as line with id 3 in the first layer:

enter image description here

With a left join only the blue line with id 3 will be join to the first layer. So all DTV values in the DTV column for layer 2 will be NULL in the join.

select  a.id as a_id, a.DTV as a_DTV, 
        b.id as b_id, b.DTV as b_DTV,
        a.DTV+b.DTV as sum_DTV,
        coalesce(b.DTV, 0) as coalesce_test,
        a.DTV + coalesce(b.DTV, 0) as sum_DTV_with_coalesce
from firstlayer as a
left join secondlayer as b
on st_equals(a.geometry, b.geometry)

enter image description here

Improve this answer
1
  • 1
    Thank you, it worked! Do you mind to elaborate a bit on this solution, not just for me, but for other readers as well? That would be awesome!
    – i.i.k.
    Commented Jul 15 at 14:27
1

The solution demonstrated by @Bera is correct for cases where there is a 1 to 1 equality of geometries from two layers. If the second layer has two or more features with geometries equal to features in the first layer, it gonna duplicate features in the first layer (see the proof below achieved with @Bera's query), and therefore a bit different approach should be applied.

proof

Let's assume there are two point layers with surveying results i.e. 'Survey1' (greenish) and 'Survey2' (orangish), see the image below:

input

My suggestion utilizes the IFNULL function, and performs a grouping on a level of the second layer and looks like the following:

SELECT
    a.id, a.type, a.geometry,
    IFNULL(a.dtv, 0) + IFNULL(b.dtv, 0) AS dtv
FROM "Survey1" AS a
LEFT JOIN (
    SELECT
        SUM(dtv) AS dtv,
        geometry AS geometry
    FROM "Survey2"
    GROUP BY geometry) AS b
ON ST_Equals(a.geometry, b.geometry)

Notice, that the SUM function is an aggregate function that returns the sum of the non-NULL values or only the distinct values in a group.

The output point layer (greenish) with its attribute table will look like:

result

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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