Following on this question.
I have a set of reference lines and a non geometrical table of events. This table contains the mileage of the event along the reference lines, amongst other attributes.
I upscaled my application to use real world data. It works, but the display is slow. I have read about spatial indexes, but I am not sure about how to make it work with a non spatial table.
My virtual layer query is the following :
SELECT e.fid /*:int*/, e.'LE_idRiver' AS RouteID /*:int*/, r.'LB_idRiver' AS RivName /*:text*/, e.'LE_Value' AS val /*:int*/, ST_Line_Substring(r.geometry, e.'LE_FROM' / st_length(r.geometry), e.'LE_TO' / st_length(r.geometry)) as geom /*:linestring:2056*/ FROM "Linear_events"as e Join "base_lines" as r ON (e.'LE_idRiver' = r.'fid')
The baselines are quite long (up to 50km) and some events are short (~10m), so there are quite a few of them. I was thinking about splitting the baselines every km and use the processes described here but I would rather have a single geometry if possible.
Moreover, my hypothesis is that the whole layer is redrawn even when I zoom in, meaning that this approach would lead to an improvement at a lower scale, but the gains would vanish at a larger scale, leaving the problem unsolved.
Is there a way to improve the display speed of my layer at all zoom levels in this use case?