1

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') 
4
  • 2
    either the line layer does not have M values, or the equation computing the line fraction returns an invalid value (i.e. not between 0 and 1). To debug it, you can display the computed fraction and/or each component of the equation. Let's note that the doc specifies that a NULL geometry is returned for invalid arguments – JGH Aug 11 '20 at 8:23
  • Could be wrong but (e.'LE_FROM' - ST_M(ST_StartPoint(r.geometry))) / (ST_M(ST_EndPoint(r.geometry)) - ST_M(ST_StartPoint(r.geometry))) within ST_Line_Interpolate_Point should always be between 0 and 1 and I'm convinced it's not always the case, hence your null values in the geometry (with cast or not) – ThomasG77 Aug 11 '20 at 10:03
  • I managed to change the formula to fit what I wanted. Long story short, I had no M value. Thanks for pointing that out. – Kantan Aug 11 '20 at 11:28
  • Please do not post an answer in the Question. This breaks our Q&A model. Instead post your own Answer. – Vince Aug 19 '20 at 8:25
1

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.

2
  • It seems to do something, as the layer is now correctly labeled as a point layer. However, when I try to zoom in a feature, I get the message "No extent could be determined". Any Idea? Also if I try to compute the ratio and display it in the table, I get NULL values, even with casting. – Kantan Aug 10 '20 at 14:22
  • I made it work based on your advice, thanks! (see the code in the edited question) – Kantan Aug 11 '20 at 11:31

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.