10

Using QGIS, I have two polygon layers, a vegetation layer and a treatment layer. I want to add a field to the attribute layer of the vegetation layer to indicate if any part of the polygon has been treated (overlaps with a treatment layer).

layer example

For the above example - I want an attribute table that ends up looking like

attribute table example

I do not want to intersect the two layers - I need to keep the full polygons in the vegetation layer, I just want to know if they've been treated. There are too many polygons to do this manually.

I have looked through the vector tools to try identify one that does this, this was not successful. I've tried to find a workflow via rasters instead of polygons, but cannot figure out what would be needed for this to work. I have searched online but not been able to find anything that answers this question, likely because I do not know what this is called so do not know what terms to search for.

The only solutions I have found are manual, but there are several hundred polygons so I would like to avoid that approach unless there is no other option.

0

3 Answers 3

15

There is a possibility using a "Virtual Layer" through Layer > Add Layer > Add/Edit Virtual Layer...

Let's assume we have three features in 'vegetation' (green) and four in 'treatment' (red) accordingly, see image below.

Example

With the following Query, it is possible to add a field to the attribute layer of the vegetation layer to indicate if any part of the polygon has been treated (overlaps with a treatment layer).

SELECT vegetation.*,
(CASE
    WHEN vegetation.id IN
        (SELECT vegetation.id
         FROM vegetation, treatment
         WHERE st_intersection(vegetation.geometry, treatment.geometry) IS NOT NULL)
    THEN '1'
    ELSE '0'
    END) AS Is_Treated
FROM vegetation

The output Virtual Layer will maintain initial attributes and geometries and add an additional field representing overlaps.

Result_1


Additionally, you may extend your output layer as was earlier suggested by @spatialthoughts with several lines

SELECT vegetation.*,
(CASE
    WHEN vegetation.id IN
        (SELECT vegetation.id
         FROM vegetation, treatment
         WHERE st_intersection(vegetation.geometry, treatment.geometry) IS NOT NULL)
    THEN '1'
    ELSE '0'
    END) AS Is_Treated,
SUM(st_intersection(vegetation.geometry, treatment.geometry) IS NOT NULL) AS Intersections
FROM vegetation, treatment
GROUP BY vegetation.id

Now, the output Virtual Layer will look as following

Result_2


References:

6
  • 1
    Nice! Works on QGIS 3.4 too. Always good to learn different ways of accomplishing the same. Would be interesting to see which approach scales better in layers with lots of polygons. Apr 16, 2019 at 9:08
  • Definitely. I am also interested in that "comparison". Let's figure it out ))
    – Taras
    Apr 16, 2019 at 9:19
  • @spatialthoughts, thank you for testing on QGIS 3.4. I will complete my answer with this note
    – Taras
    Apr 16, 2019 at 9:19
  • 1
    Yes. @Esme_ please do test both the solutions and let is know which one runs faster. Apr 16, 2019 at 9:22
  • 1
    @spatialthoughts and Taras apologies for slowness to reply (it's holiday time here) - I tested the select option above and it worked perfectly (once I fixed a few problems with the layer). I'll test the other option as well. Is there a way to get system time for how long things take - otherwise I'll just guestimate via the clock? I'll test both options and report the outcomes.
    – Esme_
    Apr 25, 2019 at 23:31
8

You can do this using Aggregate function. Add a new field "isTreated" in the 'vegetation' layer with an expression like below:

if(aggregate(layer:='treatment',
             aggregate:='count',
             expression:=fid,
             filter:=intersects($geometry, geometry(@parent))
             ) > 0,
1, 0)

The aggregate function returns number of features from the 'treatment' layer that are intersecting. As you are only interested whether they intersect at least 1 feature, you can add the if condition to assign 0 or 1.

See my post about aggregate functions in QGIS to learn more https://spatialthoughts.com/2019/04/12/summary-aggregation-qgis/

4

A possible performance improvement for large amounts of features, and a slight improvement in readability:

SELECT  DISTINCT
        a.*,
        CASE WHEN b.id
          THEN 1
          ELSE 0
        END AS "isTreated"          -- but, better to avoid camelCase as column names
FROM    vegetation AS a
LEFT JOIN
        treatment AS b
  ON    ST_Intersects(a.geometry, b.geometry)
;

The LEFT JOIN will select all rows in the left hand table for the join, matching the condition or not; b.id will be NULL if a row has no match in the right hand table, and the CASE filters accordingly. The DISTINCT makes sure there will only be one row per match.

2
  • @Taras I thought about simply editing into your answer, or suggest per comment, since your answer got it covered and you added that nice extra. If you want to add this in, I'm happy to delete my answer, too. The LEFT JOIN should make things a bit faster as sub-selections in the CASE and I reckon it's good to know.
    – geozelot
    Apr 16, 2019 at 10:35
  • yes, please. It would be a pleasure if you can contribute to my answer
    – Taras
    Apr 16, 2019 at 10:39

Your Answer

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

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