pgRouting: How to start path search based on source and target locations in other layers?

Question

I have a line network and two ESRI point shapefiles, where one is the source locations (from_pts) and the other is the target locations(to_pts). I have done the following to prepare my network, but I am confused about how to associate the points for the source and target columns:

1. imported all shapefiles as simple geometries instead of multi geometries.
2. created source and target columns in line network (streams)
3. created topology: SELECT pgr_createTopology('streams',0.000001,'geom','gid');
4. spatial indexed points CREATE INDEX idx_from_pts_geom ON from_pts USING GIST(geom); & CREATE INDEX idx_from_pts_geom ON from_pts USING GIST(geom);

How do I assign the points in my source and target point shapefile to the closest node in my newly created 'streams' network table?

What I am trying to do is assess the closest downstream target points from each one of my source points. There are 12 source points and a couple hundred target points.

Answer 1

As @robin loche suggested, you don't assign the points in your from and to point lists to the network vertices. You use the locations of your from and to points to find the closest start and destination vertices in the network for each search.

The following nested queries do this in stages:

1. generate all pairs of source and destination points

2. find closest network vertices for each point pair

3. run the routing algorithm

The results of pgr_Dijkstra are returned here as a sequence of rows for each from, to point pair with each row containing a step in the route sequence in a composite type. You may want to nest this to unpack those results a bit more. Being a stream application, you probably also have a directed network.

This should get you started.

ALTER TABLE streams ADD COLUMN length float8; -- add length field for pgr costs
UPDATE streams SET length = ST_Length(geom::geography);

WITH all_pairs AS (
  -- all pairs of start and end geometries with IDs
  -- that get carried through so your routing results
  -- match with the pt IDs you know.
  SELECT f.gid AS fid, f.geom as fgeom,
         t.gid as tid, t.geom as tgeom
    FROM from_pts AS f,
         to_pts AS t
), vertices AS (
SELECT fid, tid,
       (SELECT id -- proximity search for closest from vertex
          FROM streams_vertices_pgr
         ORDER BY the_geom <-> fgeom
         LIMIT 1) as fv,
       (SELECT id -- proximity search for closest to vertex
          FROM streams_vertices_pgr
         ORDER BY the_geom <-> tgeom
         LIMIT 1) as tv
  FROM all_pairs
)
SELECT fid, tid, pgr_Dijkstra(
  'SELECT gid AS id, source, target, length AS cost FROM streams',
  fv, tv,
  directed =>false
) from vertices;