QGIS create vectorlayer based on project axis and data table

Question

I have a project where I need to mark ongoing and planed projects along a river on a map. I have a list of the projects in form of a table with multiple attributes including the start and end point along the project axis (Centerline of the river). Using the "line substring" algorithm I can create a polyline for each project and then manually add the attributes from the table and merge the output layers. The list of projects is quite long and will change in the future so I would like to create a custom processing algorithm that takes the table and my project axis (As a Line layer) as an Input and output one vectorlayer with all the projects as features with the attributes from the table.

I wanted to use the graphical modeller but I could not figure out how to loop throught the rows of the table. Is there a way to do this with the graphical modeller or does this require to write a pyhton script?

Here you can find some sample data

• J.R: What you are trying to do is called "linear referencing" (en.wikipedia.org/wiki/Linear_referencing), QGIS has some plugin (LinearReferencing (github.com/Ludwig-K/QGisLinearReference) , LRS (blazek.github.io/lrs), LRS-Editor (github.com/Holenstein-Ingenieure-AG/lrs-editor) and maybe other) already made to deal with your problematic, it could be wise to check them to see if they fulfill your need rather than trying to recreate an equivalent from scratch.

Answer 1

This might load faster with Python or more intuitive to change around in the Graphic Modeller, but if you would consider a Virtual Layer option, after loading both source layers in QGIS, you can use this query in Layer > Add Virtual Layer:

SELECT 
 pl.*, 
 ST_Line_Substring(
 pa.geometry, --replace with reverse(pa.geometry) to start from other end
 "start-km" / ST_Length(pa.geometry), 
 "end-km" / ST_Length(pa.geometry)
 ) AS geometry 
FROM 
 project_list pl, --replace project_list with relevant layer name
 project_axis pa --replace project_axis with relevant layer name

---

Result showing QueryLayer (virtual layer) with 5 features from supplied project_list layer, and corresponding line substring geometry split off from project_axis layer:

enter image description here

---

If the project_axis layer has more than one feature, it needs a unique field that has can be joined to matching values with a field in the project_list layer. In the example below, each river axis has a unique name, and each project has a river name.

SELECT 
 pl.*, 
 ST_Line_Substring(
 pa.geometry, 
 "start-km" / ST_Length(pa.geometry), 
 "end-km" / ST_Length(pa.geometry)
 ) AS geometry 
FROM 
 project_list pl 
 JOIN project_axis pa ON pl.name = pa.name

file externally uploaded

---

• The Virtual Layer will dynamically update based on your input in those layers. It is read-only but you can save the output to a desired format for further editing/processing/sharing.

• To reverse the direction that the distances are measured from, replace pa.geometry in above query with reverse(pa.geometry).

• Markus Lang: Thanks a lot this does exactly what i was looking for. Never played around with virtual layers before but this looks much simpler than anything I could have done with python. I was planing to make a separate project list and project axis for each river, you mentioned that the query could be adjusted to work with multiple features could I name the river-axes in one layer and add a column to the project list?
• she_weeds: Sure, if you have a field that joins the axis layer to the projects layer with matching values. It would require a layer join rather than the current code which simply tacks on the axis layer geometry. I've added the extra code to the answer. And an image
• Markus Lang: Thanks a lot, works perfectly