So I have a list of source points and a list of destination points. I also have a street network and I have created connection lines using ST_ShortestLine()
between source/destination points and the street network:
with unioned_ways as (
select ST_UNION(geom) as geom from my_ways
)
--(Same for source points)
select
ST_ShortestLine(adr.geom, way.geom)
from
my_destinations adr,
unioned_ways way
Now I would like to ST_Split()
my road network at each connection line's endpoint, so I have my final road network for my shortest path analysis. This routing should then identify the shortest route from each source point to any destination point. However, by just cutting the road network I don't know which nodes I should select as pgrouting's source and target nodes. I usually used this script for my routing and pointed to some specific nodes I am interested at:
SELECT seq, id1 AS node, id2 AS edge, cost, linestring
FROM pgr_dijkstra(
'SELECT id, source, target, st_length(linestring) as cost FROM my_ways',
17, --source node
10, --target node
false,
false
) as di
JOIN my_ways pt
ON di.id2 = pt.id ;
How can I get a list/array of my source and target nodes from my splitting procedure above?