How to identify pgrouting nodes I am interested after splitting road network in for many to many routing?

Question

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?

Answer 1

Two out-of-the-box options:

• use the pgr_withPoints family of functions to dynamically add nodes/edges to an existing network, based on a pre-computed set of nearest edges; this is elegantly solving non-network integrated routing in a highly dynamic setting where source and target points vary per call.
  See my answer to a similar task for more details on the general usage.
• reading your other question it may be useful to actually integrate edges representing trenches from the road network to the houses; your (extended) network is likely rather static, and working with actual network edges may eventually be somewhat more intuitive.
  The easiest way would be to simply INSERT your ST_ShortestLines into the actual network table and run pgr_nodeNetwork and then pgr_createTopolgy on it to recompute connectivity.

Update:

pgr_createTopology will recompute the source and target field values of your extended <edge_table>, and create an <edge_table>vertices_pgr table holding all vertices and their location as GEOMETRY; you can then

• visually find the vertices ids in question
• SELECT the target value of the inserted edges by their edge_id
• run a spatial selection (e.g. (K)NN) of closest vertices of the <edge_table>_vertices_pgr table to the geometries of your houses/POIs

---

Note that, with that linked other question in mind, you are likely looking for working with the pgr_TSP family of functions (not 100% applicable, though), which would require the full integration of those connections into the network as per the second option.