Measuring line length in polygons with virtual layer qgis 3

Question

What seemed to be an easy task turned out to be not that easy.

I want to measure line length in separate polygons.

My steps so far:

1. polygons to lines (because intersecting lines with polygons ends up in lineends out of the polygons get deleted, same behaviour like clip btw. not logic for me)
2. split lines with lines (polygon lines with actual line layer)
3. save intersection (because you cant apply a virtual layer on a temporary layer)
4. calculate line lengths (lines splitted need new line lengths)
5. Run the virtual layer with st_within OR select by location with "within", select splitted line layer with polygons one by one.

And here strange things happen: The lines, cutted with the lines from the polygon, still dont get correctly selected at the boarders. I get one half, but not both of the cutted lines.

Like, one part of the splitted line isnt within the polygon. But i cutted them with the boarders of the polygon and didnt move them.

Answer 1

If I understand your question correctly, you want to achieve something like this:

where the black dashed line is the original line layer and you want the length of the red lines. (dont mind my label, my length here is actually in degree, I just labeled it as meters)

You can use this query:

Select 
ST_Length(ST_Intersection(l.geometry,p.geometry)) as line_length, 
ST_Intersection(l.geometry,p.geometry) 
from lines as l, polygons as p 
where ST_Length(ST_Intersection(l.geometry,p.geometry)) is not null

PS: virtual layers do work on temporary layers.