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:
- imported all shapefiles as simple geometries instead of multi geometries.
- created source and target columns in line network (streams)
- created topology: SELECT pgr_createTopology('streams',0.000001,'geom','gid');
- 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.
Best Answer
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:
generate all pairs of source and destination points
find closest network vertices for each point pair
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.