Creating aggregate attribute from two polygon layers

Question

I have two layers: one underlying land use information layer (with columns 'land_use_type' and 'area') and one for leased land.
To organize the leasing of the land it would be important that when drawing a polygon for an area I want to lease to have columns which tell me how much area of the drawn polygon is covered by land-use type (e.g. wood, farmland etc..).
I tried a bit around with the aggregate function and a virtual field, but to no avail:
aggregate(layer:='Land_Use',aggregate:='sum',expression:="area",filter:=contains(geometry(@parent), $geometry),order_by:="land_use_type")

How can I achieve that?

Taras · Answer

As a working solution I can suggest using a "Virtual Layer" through Layer > Add Layer > Add/Edit Virtual Layer....
Let's assume there are two polygon layers called "land_use_info" (dark red) and "leased_land" (yellow).

With the following Query, it is possible to have columns which tell me how much area of the drawn polygon is covered by land-use type.
SELECT ll.*, GROUP_CONCAT(lui."land_type" || ':' || round(st_area(st_intersection(ll.geometry, lui.geometry)),4)) AS "info"
FROM "leased_land" AS ll
JOIN "land_use_info" AS lui ON st_intersects(ll.geometry, lui.geometry)
GROUP BY ll.id

The output polygon layer with its attribute table will look like

Creating aggregate attribute from two polygon layers

One Answer

Add your own answers!

Ask a Question