postgis - Dynamic Linear Referencing of events in QGIS from Excel or CSV using virtual layer

I have a PostGIS table with a geometry type LINESTRING M to represent routes. Here is the table specification:

CREATE TABLE routes
(
  fid serial PRIMARY KEY,
  r_name text NOT NULL,

  geom geometry(LineStringM,31370)
);

For simplicity in this fictional example, the start measure is 0 and the end measure is 1000 for all routes.

I also have an events.xlsx file with the position of my events:

In QGIS, how can I dynamically locate my events from my excel file without uploading it to PostGIS? (my users have only read access to the database).

I've already imported the Excel file in QGIS (with drag and drop):

I used to use the Event Layer plugin but it does only work for QGIS versions <= 2.8, so I now try to do it with a Virtual Layer (Layer -> Add Layer -> Add Virtual Layer...).

I'm first importing the routes and events layers in the virtual layer dialog, and I then write the following query:

SELECT
    r.fid,
    e."Route",
    r.r_name,
    e."Event",
    e."Measure",
    ST_LocateAlong(r.geometry, e."Measure") geometry /*:pointm:31370*/
FROM

    events e
JOIN
    routes r ON e."Route" = r.r_name;

The layer is created but the geometry is not recognized. I can verify that the join has correctly been executed because I see that the resulting attributes seem to be correct:

However, there is no geometry. The geometry column is not recognized and is always NULL. The Measures are within the bounds of my LinestringM's start and end value. I've tried to set the geometry manually in the Virtual Layer dialog, but it does not work.

Does anyone know how to make the ST_LocateAlong function work in a QGIS Virtual Layer?

Answer

It seems that Spatialite's ST_LocateAlong function only returns a value if the searched value is directly found on a vertex. It does not interpolate between vertices.

It's what's written in Spatialite's manual:

ST_LocateAlong( geom Geometry , m_value Double precision ) : Geometry

Return a derived geometry collection value with elements that match the specified measure. NULL will be returned if any error is encountered (or when no element corresponding to the given measure is found). Please note: NULL will be returned if geom doesn't contain M-dimension, or if geom contains a Polygon, or if geom is a GeometryCollection.

So I had to modify my query to use ST_Line_Interpolate_Point instead of ST_LocateAlong.

ST_Line_Interpolate_Point takes a fraction in [0, 1] as second argument to interpolate the point on the line.

Here is the modified query:

SELECT
    r.fid,
    e."Route",
    r.r_name,
    e."Event",
    e."Measure",
    -- Calculate fraction (Measure - Start)/(End - Start)
    ST_Line_Interpolate_Point(r.geometry, (e."Measure" - ST_M(ST_StartPoint(r.geometry))) / (ST_M(ST_EndPoint(r.geometry)) - ST_M(ST_StartPoint(r.geometry)))) geom
FROM
    events e

JOIN
    routes r ON e."Route" = r.r_name;

Problem: it only works if the M-values are linearly distributed along the line.

I hope that in a future version, Spatialite's ST_LocateAlong implementation will interpolate between measures.