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.


Tested on QGIS 2.18 and QGIS 3.4

I can suggest using a "Virtual Layer" through Layer > Add Layer > Add/Edit Virtual Layer...

Let's assume we have three features in "vegetation" and four in "treatment" accordingly, see image below.


With the following Query, it is possible to achieve the result

SELECT vegetation.*,
    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.


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

SELECT vegetation.*,
    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



  • 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. – spatialthoughts Apr 16 '19 at 9:08
  • Definitely. I am also interested in that "comparison". Let's figure it out )) – Taras Apr 16 '19 at 9:19
  • @spatialthoughts, thank you for testing on QGIS 3.4. I will complete my answer with this note – Taras Apr 16 '19 at 9:19
  • 1
    Yes. @Esme_ please do test both the solutions and let is know which one runs faster. – spatialthoughts Apr 16 '19 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 '19 at 23:31

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

 layer:= 'treatment',
 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/


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

        CASE WHEN b.id
          THEN 1
          ELSE 0
        END AS "isTreated"          -- but, better to avoid camelCase as column names
FROM    vegetation AS a
        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.

  • @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. – ThingumaBob Apr 16 '19 at 10:35
  • yes, please. It would be a pleasure if you can contribute to my answer – Taras Apr 16 '19 at 10:39

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.