TransWikia.com

Linear referencing using virtual layers in QGIS

Geographic Information Systems Asked by Kantan on September 4, 2020

I am trying to replicate the behaviour explained on this page.

There are two initial layers :

  • One network layer (line geometry), called base_lines (fields : LB_idRiver)
  • One event layer (non-geometrical table), called linear_events and containing information about the beginning (eFrom) and the end of the event (eTo) (fields : fid, LE_from, LE_value)

From there, I need to, using virtual layers in QGIS :

  • Create two point layers showing the position of the beginning and the end of the events along the base_line layer
  • Create a new line layer extracting the parts of the base_line layer between the points.

So far, my query for the first task looks like this :

SELECT
    e.fid,
    e.'LE_FROM' AS Debut, 
    e.'LE_idRiver' AS RouteID, 
    e.'LE_Value' AS val,
    r.'LB_idRiver',
    ST_Line_Interpolate_Point(r.geometry, (e.'LE_FROM' - ST_M(ST_StartPoint(r.geometry))) / (ST_M(ST_EndPoint(r.geometry)) - ST_M(ST_StartPoint(r.geometry)))) as geom
FROM "Linear_events" as e
Join "base_lines" as r ON (e.'LE_idRiver' = r.'fid')

The table is created, but the geometry creation fails (NULL gets returned for each part of the geom alias).

What changes have to be made to the query to make it work as intended?
Once this query works, how is it possible to get the second one working?

(On a side-note, I’d be very interested in advice concerning virtual layer creation, as the interface is not really comfortable in QGIS)


I finally made it work based on the advice here :

For the first query :

SELECT
    e.fid AS Event_ID/*:int*/,
    e.'LE_FROM' AS Debut /*:real*/, 
    e.'LE_idRiver' AS RouteID /*:int*/, 
    e.'LE_Value' AS val /*:int*/,
    r.'LB_idRiver',
    ST_Line_Interpolate_Point(r.geometry, e.'LE_FROM' /st_length(r.geometry)) as geom /*:point:2056*/
FROM  "Linear_events"as e
Join    "base_lines" as r ON (e.'LE_idRiver' = r.'fid')

And the second query :

SELECT
    e.fid /*:int*/,
    e.'LE_idRiver' AS RouteID /*:int*/, 
    r.'LB_idRiver' AS RivName /*:text*/,
    e.'LE_FROM' AS Debut /*:real*/, 
    e.'LE_TO' AS Fin /*:real*/, 
    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') 

One Answer

This answer is not a complete answer as I don't have the same exact environment. You need to cast to get the "right result". The content should be more or less like the following

SELECT id /*:int*/, ST_Line_Interpolate_Point(geometry, 0.3) AS geom /*:point:4326*/ FROM demo_pts

FIY, when I tried with "in memory" layer, the previous code was not working.

Correct answer by ThomasG77 on September 4, 2020

Add your own answers!

Ask a Question

Get help from others!

© 2024 TransWikia.com. All rights reserved. Sites we Love: PCI Database, UKBizDB, Menu Kuliner, Sharing RPP