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