3

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.

Routes with start and end measures

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

Excel file with 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):

QGIS with events file loaded

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;

Virtual Layer

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:

Event layer

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?

  • If I change /*:pointm:31370*/ to /*:point:31370*/, the geometry column is recognized as point but does not show up on the map. – thibautg May 28 '16 at 11:52
  • I think I found the problem: it's because ST_LocateAlong implementation in Spatialite returns NULL when the searched m_value is not on a specified vertex (search LocateAlongMeasure on gaia-gis.it/gaia-sins/spatialite-sql-4.3.0.html ). This is different than PostGIS's implementation which interpolates the measure between the two vertices around it. – thibautg May 28 '16 at 13:14
2

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;

Located events

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.

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.