3

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?

3

1 Answer 1

3

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

input

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

result

4
  • this goes perfectly in the right direction, i do not get an output for my data structure though.. i m looking into it, this looks very promising!
    – sn1ks
    Sep 16, 2020 at 12:45
  • 1
    Check layers' names, are they specified in the right way? What does your output look like?
    – Taras
    Sep 16, 2020 at 12:46
  • unfortunately no pic uploads possible in the answer section. i double checked the layer names ("lease" with colum "id" and some more, unrelevant ones and "Land_Use" with the column "LU_type"). My output virtual layer does not have the column "info". Both layers are in the same EPSG (intersect works with the QGIS onboard tool) and no features (althogh i have one feature in the "Lease" table - i will digg into it tomorrow, maybe it is because of the encoding of the files..or because of the "unrelevant" columns of my "Lease" table or..
    – sn1ks
    Sep 16, 2020 at 13:54
  • Column "info" is created automatically, which is an attribute you require. Let's handle it today, should not be that difficult.
    – Taras
    Sep 16, 2020 at 14:00

Your Answer

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

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