Identifying polygons that intersect with another layer using QGIS

Question

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).

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

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.

Answer 1

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.

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.

---

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

---

References:

• Chapter 8. PostGIS Reference | 8.5. Geometry Accessors
• Counting attributes from line intersections with polygons on QGIS?
• Understanding Join Attributes by Location in QGIS?

Answer 2

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/

Answer 3

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.