Proportion of polygon classes in each multi-ring buffers

Question

I have a landcover map with many irregular polygons, with each polygon a uniform habitat type. I have a second shapefile of points, and a third shapefile with multi-ring buffers around those points. I would like to know the proportion of each habitat type within each buffer for each individual point (or even area rather than proportion), ideally as additional columns in the attribute table of the buffer shapefile.

How can I do this without repeatedly clipping the landcover map to each buffer and then manually calculating the proportions? (In my full data set I have 190 points, each with 150 buffers, so 28,500 rows, i.e. I need to automate this as much as possible).

Example, landcover polygons and points;

Concentric buffers around a single point;

So in the example above, my desired output for the first three buffers for this one point would look something like this;

Answer 1

Here my solution :

To do that, I've :

• a donut ring buffer table buffers with two fields point_id (id of the belonging point) and distance (distance of the donut buffer).
• a landcover table landcover with one field habtype with the habitat type.

In Virtual Layers :

WITH dat AS (SELECT buffers.point_id,
buffers.distance,
landcover.habtype,
ST_AREA(ST_INTERSECTION(landcover.geometry, buffers.geometry)) AS area
FROM landcover, buffers
WHERE ST_INTERSECTION(landcover.geometry, buffers.geometry) IS NOT NULL),

surf_dist AS (
SELECT dat.point_id,
dat.distance,
SUM(area) AS tot_area
FROM dat
GROUP BY dat.point_id, dat.distance),

surf_hab_dist AS (
SELECT dat.point_id,
dat.distance,
dat.habtype,
SUM(area) AS tot_hab_area
FROM dat
GROUP BY dat.point_id, dat.distance, dat.habtype),

prop_hab AS (SELECT shd.point_id,
shd.distance,
shd.habtype,
shd.tot_hab_area / sd.tot_area * 100 AS val
FROM surf_dist sd
INNER JOIN surf_hab_dist shd ON (sd.point_id = shd.point_id AND sd.distance = shd.distance)),

pivot AS (SELECT prop_hab.point_id,
prop_hab.distance,
CASE WHEN prop_hab.habtype = "Yellow" THEN prop_hab.val
ELSE 0 END AS habtype1,
CASE WHEN prop_hab.habtype = "Pale_green" THEN prop_hab.val
ELSE 0 END AS habtype2
FROM prop_hab)

SELECT pivot.point_id,
pivot.distance,
SUM(pivot.habtype1) AS habtype1,
SUM(pivot.habtype2) AS habtype2
FROM pivot
GROUP BY pivot.point_id, pivot.distance

In the pivot query part, add as many :

CASE
  WHEN prop_hab.habtype = "Pale_green"
  THEN prop_hab.val
  ELSE 0
END AS habtype2

as needed (one by type of desired habitat type) by replacing correct values, and in the last part, equivalent SUM(pivot.habtype1) AS habtype1.

Answer 2

In the end I followed this work flow;

1. Converted the land cover classes to numbers

2. Rasterised the landcover shapefile (Raster > Conversion > Rasterize)

3. Used the zonal histogram tool to calculate the number of raster pixels in each buffer ring. (Toolbox > Raster Analysis > Zonal histogram)

It took a while. @J.Monticolo's answer may be much more efficient, I didn't test it. My approach gave me exactly what I was looking for.