1

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?

1 Answer 1

2

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.

4
  • Thanks for the input! And thanks for considering my other question :) However, I still have an issue about identifying the node endpoints for my routes once I integrated my ST_ShortestLines. What should I place into the source and target attributes of my routing algorithm?
    – JoeBe
    Nov 13, 2020 at 9:14
  • @JoeBe oh, well, pgr_createTopology creates a <edge_table>_vertices_pgr table holding all vertices identifiers (it will also update the source and target columns of your <edge_table> with new values, including those new edges). Either visualize them and select manually, SELECT the target ids of the edges you inserted by their id, or run a spatial selection (e.g. (K)NN) of vertices to your houses/POIs
    – geozelot
    Nov 13, 2020 at 9:29
  • Thanks! That makes sense! Could you add this to your answer, so I can accept it as the answer for my question?
    – JoeBe
    Nov 13, 2020 at 9:39
  • @JoeBe sure...tbh. I didn't realize that was the question ,)
    – geozelot
    Nov 13, 2020 at 9:41

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.