Snap points to route and calculate relative length from start

Question

Input:

1. Points layer points spread around some route
2. Route layer route where each route link is feature of type Multi Linestring. However it's possible to merge it to single feature layer of type Multi Linestring using Qgis GUI Merge Lines tool (I haven't succeed to merge to single feature of type Linestring-this is important later on)

What I want to calculate:

Snap each point to the route with a length field indicating length distance along the route from the beginning of the route (and not beginning of the nearest route link etc.).

What I tried:

1. Using PostGis I tried

   ST_Line_Locate_Point(ST_MakeLine(route.geom), points.geom)

I need ST_MakeLine to form single LineString (as I said it's a Multi Linestring), The problem is that ST_Line_Locate_Point still returns the error "first argument isn't Line", so I don't know whats going on here.

2. Searched in all Qgis tool box for appropriate methods - haven't found one.
3. Tried LRS plugin - seems not relevant for this use case.

Answer 1

ST_Line_Locate_point only works with Linestrings, not MultiLinestrings. This makes sense, as the return value is the percentage distance along the line that the point occurs, which wouldn't make sense with a MultiLinestring, as you wouldn't know which of the constituent Linestrings was being referred to. You can fix this by using ST_Dump.

WITH lines (geom) AS 
  (SELECT (ST_Dump(multiline)).geom 
     FROM ST_GeomFromText(
             'MULTILINESTRING((0 0, 10 0), (10 10, 20 20))') AS multiline
 )
 SELECT ST_LineLocatePoint(geom, ST_MakePoint(5,0)) FROM lines;

which returns:

  0.5
    0

However, you need to be a bit careful here, as the 2nd Linesting does not intersect the point at all, and 0, simply means the closest point, ie, the start point. You could exclude the 2nd line by using ST_DWithin with a small tolerance, eg, with a tolerance of 1,

WITH 
  lines (geom) AS 
     (SELECT (ST_Dump(multiline)).geom 
        FROM ST_GeomFromText(
          'MULTILINESTRING((0 0, 10 0),(10 10, 20 20))') as multiline), 
  point (geom) AS 
    (SELECT ST_MakePoint(5,0) as geom)
 SELECT 
    ST_LineLocatePoint(lines.geom, point.geom) 
   FROM lines, point 
  WHERE ST_DWithin(lines.geom, point.geom, 1);

which now only returns

0.5

ST_DWithin works better than ST_Intersects, as it is a radius search and interesting a point with a linestring will often fail due to precision issues.

If you need to know which of the Linestrings is being referenced, you can either use the path part of the set ((geom, path)) returned by ST_Dump, which will give you a 1-based index for each linestring in the dumped MultiLinestring or you could return the linestring itself and feed this to another function -- but this, probably, goes beyond what you asked.