Allocating line values that intersect with polygons

Question

I am trying to figure out how to allocate the values I have in a lines layer to polygons based on how much of each line segment falls within each polygon.

I have a roads lines layer that has a different "traffic activity" value for each segment similar to the map shown here (for illustrative purposes):

Image source: https://www.researchgate.net/figure/A-screenshot-of-a-map-of-typical-traffic-congestion-levels-at-6-PM-on-Wednesday-in-the_fig3_330031302

Now, I have an underlying county block groups polygons layer. I want to find the sum of traffic activity for each block group, based on 1) the traffic activity value assigned to each road segment, and 2) how much of each road segment falls within each polygon.

To summarize, I want the total traffic activity for each block group by summing the traffic activity values of the road segments that fall within each block group polygon, proportional to how much of a road segment falls within each polygon.

I am thinking of using the "Intersection" tool in QGIS to intersect the roads lines layer and the block group polygons layer to delineate the roads layer by the brock groups, and then counting the traffic activity values in each block group, though I am not sure how the traffic activity values from the roads lines will get distributed between the block groups they fall within. If the values of a road segment are evenly divided between the polygons they fall within, that is not what I want. I want the values to be allocated to the block groups based on the portion of the lines each block group contains.

For example: A line segment of 10 miles contains a traffic activity value of 100 and falls within block groups A, B, and C. Block group A contains 2 of those miles of road, Block group B contains 3 of those miles of road, and block group C contains 5 of those miles of road. Accordingly, after the intersection, Block group A should have a traffic activity value of 20, Block group B should have a traffic activity value of 30, and block group C should have a traffic activity value of 50.

How this might be accomplished in QGIS?

Answer 1

This can be achieved using a "Virtual Layer".

Go the the menu layer / add layer / add-edit virtual layer and enter the following query. You would have to change the layer name and field name(s) to suit your needs.

The output is a dynamic table, which you can then join back to your polygon layer.

Since the line length is involved, it assumes the data is projected in a CRS suitable for distance computation.

select p.id, 
 sum(l.val * st_length(
   st_intersection(p.geometry,l.geometry))/st_length(l.geometry)) distributed_val
from p
join l
on st_intersects(p.geometry,l.geometry)
group by p.id

let's break it down:
There is the polygon p layer and the line l layer.
For each polygon id (group by p.id), the query computes the intersection between the lines and the polygon. For each intersection, its length is divided by the original line length and is multiplied by the original line value (l.val). At last, for each polygon, the sum of the aforementioned line percentage is computed (should multiple lines intersects with a polygon)