1

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.

enter image description here

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) enter image description here 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?

  • 1
    What exactly is your idea of an 'event table'? – ThingumaBob Jan 2 at 10:48
  • A tabular table that descries an asset/instance of a feature that is related to the base geometry. An "event" driven relation. Feature x relate to base geometry N from 10m, to 35m etc – geogrow Jan 2 at 11:18
  • Hm, I see. Well, that would all be easy enough...if only those lines would match to the network. "[...] I need to snap the line to the network somehow." - that somehow is going to be your final boss to beat. You can either try proximity based matching (Nearest Neighbor, Fréchet Distance, or similar) with a guaranteed margin of error, or go the proper routing way (Map Matching) with plenty of implementing... – ThingumaBob Jan 2 at 11:32
  • The lines will probably never match the base network. They will generally be road assets so would never be on the center line. The start and end node will need to be snapped to the closest line instead of closest node on the network. I have a function in postGIS that already does it fine. Now I am trying to implement it for lines. I have been thinking of routing. But that would not work as the asset does not necessarily follow shortest path of the base netwrok. Python might be the way to go but I am hoping for an easier implementation. – geogrow Jan 2 at 11:42
  • Python is no relief where algorithmic ambiguity is the issue. Routing would be the way to go; you would need to run Map Matching procedures (Markov Chains/Viterby, e.g. via OSRM) on the line vertices. The Nearest Neighbor approach you are trying to implement can be achieved in the same way you are probably already doing with the start/end vertices: ST_DumpPoints the lines, find the nearest network segment ((K)NN search; <->) for each, and then ST_LineLocatePoint. – ThingumaBob Jan 2 at 11:49
2

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.

  • That was super helpful! It look similar to what I used for my simple point layer, but definitely more advanced. I will post my progress as an answer to my question if you mind sharing your opinion, this was definitely a leap forward! – geogrow Jan 3 at 8:14

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.