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.

  • I think you just need, for each point, to find the closest corresponding point of your stream, for exemple using nearest search (take a look at postgis.net/workshops/postgis-intro/knn.html if you don't know how to do it) Apr 1, 2022 at 7:42
  • That is exactly what I am looking for. However, I am so lost with aligning my ESRI source and target points with the network data. I have books, but I am so new to this that I need a little help.
    – MJM
    Apr 1, 2022 at 17:22

1 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;
  • Ahhh. Thank you. Much appreciated.
    – MJM
    Apr 5, 2022 at 15:18
  • Ok. I ran it and it worked, but it is a directed stream network, so I need to tweeze out all the upstream locations. I see directed =>false, to set directed => true I will need to add the field 'fldir', where a value of 32 signifies the flow direction is the same as direction of digitization, 33 is opposite, 20 not identified, 34 is not applicable. I think for a value of 20 I will set is as flow is both directions and 34 as same direction as digitization. I still need to figure out how to send query results to new table and then view those results in qgis.
    – MJM
    Apr 7, 2022 at 16:13
  • Glad to hear it worked. Sounds like you have ideas for how to set cost and reverse_cost for your directed case. The results return a sequence of route legs, including edge identifiers. You can use those to join the segment geometries. For a quick visualization of your route result, without writing them to a permanent table, run the route search query in the QGIS DB Manager SQL Window and then load the result as a query layer (once you've rejoined the geometries).
    – hgb
    Apr 7, 2022 at 16:59
  • I have updated the question to include the revised code and results, which I am having problems in aggregating the costs to get a total cost.
    – MJM
    Apr 8, 2022 at 19:54
  • This latest update probably should be a different question rather than interleave one answer with new questions. If you'd like, create a new question and link it here. I'll take a look.
    – hgb
    Apr 8, 2022 at 21:20

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.