Creating linear event table from geometry using QGIS Linear Referencing

Question

I am trying to find a way to dynamically create an event table from a linear feature.

Imagine the example below. Registration of a sidewalk/fence or similar. The colours represent different routes. So the sidewalk is following 3 different routes. How do you create an event table based on this situation? I have a couple of ideas to explore, but how is it usually done?

It must be fairly common situation in LRS but I have struggled to find many examples.Most examples are on point features which are much easier to deal with. I would prefer to implement a solution using postgis and/or QGIS.

I am thinking that I need to snap the line to the network somehow. Then split the line based on topology nodes on the base network and then create the event table to:from for each created segment and store the segments in a relational table so i can join the segments back to a line later for visualisation purpose, now following the centerline. Not sure how I can practically do it though. Best solution would be to do it dynamically using PostGIS or a python script perhaps.

Any ideas?

EDIT: I modified the query @ThingumaBob wrote as help with a nearest neighbour approach. This edit is a comment to his post.

I modified to query a bit to visually see the result, which is pretty close to what I am looking for as a final product. There is probably more clever ways of doing it, but this is my approach.

Modified the query to get the point geom on the base network and turned it into a view.

CREATE OR replace view asset_ref as
  WITH
    pts AS (
      SELECT <id>,
             dmp.path[1] AS seq,
             dmp.geom
      FROM   <lines> AS ln,
             LATERAL ST_DumpPoints(ln.geom) AS dmp
    )      
  SELECT pts.<id>,
         pts.seq,
         nw.<id>,
         ST_LineLocatePoint(nw.geom, pts.geom) AS frac,
         ST_Line_Interpolate_Point(nw.geometri_spa, ST_LineLocatePoint(nw.geometri_spa, pts.geom)) AS point_on_line
  FROM   pts
  CROSS JOIN LATERAL (
    SELECT <id>,
           geom
    FROM   <network>
    ORDER BY
           geom <-> pts.geom
    LIMIT  1
  ) AS nw;

From the view group by id, make array of points and create a line based on them

select 
    id,
    ST_MakeLine(ARRAY_AGG(point_on_line))
from asset_ref
group by id

This is a visual representation of the result (Black line) This is close to what I am looking for. My question is. Is this "as good as I can get" or can i expect more? I dont want to clutter the database with too many sub queries. If I use the created nodes in a routing analysis then I expect it to work how I want, would that be a sensible approach?

Answer 1

A little help for the Nearest Neighbor approach:

WITH
  pts AS (
    SELECT <id>,
           dmp.path[1] AS seq,
           dmp.geom
    FROM   <lines> AS ln,
           LATERAL ST_DumpPoints(ln.geom) AS dmp
  )

SELECT pts.<id>,
       pts.seq,
       nw.<id>,
       ST_LineLocatePoint(nw.geom, pts.geom) AS frac
FROM   pts
CROSS JOIN LATERAL (
  SELECT <id>,
         geom
  FROM   <network>
  ORDER BY
         geom <-> pts.geom
  LIMIT  1
) AS nw
;

For each vertex in your linear features, this returns:

• the id of the linear feature (pts.<id>)

• the sequential id of the vertex (pts.seq, increasing from start to end)

• the id of the network segment it was matched to (nw.<id>, its Nearest Neighbor)

• the feaction of line length (frac) of the network segment it was closest to (measured from its starting point); change the function to ST_LocatePoint to get the interpolated M value for a projected point closest to the vertex

You can alter the outer SELECT to return whatever format you need (or post a comment if you need help).

Note that you absolutely want an index on <network>.geom!

---

Be prepared to encounter inconsistencies and edge cases where this fails; the closest network edge is often not the one you'd expect.