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
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?