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?