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

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.

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.

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:

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

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

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.

• geozelot: @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.
• Taras: yes, please. It would be a pleasure if you can contribute to my answer