# Dynamically determine origin and destination points on network lines from nearby features using Pg Routing (pgr_dijkstra)

I am trying to determine the distance along a route from parcel to the nearest fire hydrant using pg_routing and pgr_dijkstra. I have a network table and a nodes table and the following query returns the expected results when passed origin (1) and destination (135) nodes.

``````SELECT *
FROM pgr_Dijkstra(
'SELECT ogc_fid AS id,
start_id::int4 AS source,
end_id::int4 AS target,
geom as geom,
st_length(st_transform(geom,98012))::float8 AS cost
FROM network',
1,
135,
false
);
``````

However I need a method of dynamically defining origin node as nearest point on network line from a parcel (centroid) and destination node defined as nearest point on network line from closest fire hydrant.

So I think my steps are…

1. Determine closest fire hydrant to parcel
2. Determine origin and destination points on network line.
3. Run pgr_Dijksta query.

Determine closest fire hydrant to parcel…

``````SELECT a.ogc_fid, b2.ogc_fid, st_distance(a.geom,b2.geom) dist,st_shortestline(a.geom,b2.geom) geom
FROM (SELECT * FROM parcels WHERE ogc_fid = 1) a
CROSS JOIN LATERAL
(SELECT b.ogc_fid,b.geom
FROM fire_hydrants b
ORDER BY a.geom <-> b.geom)b2
ORDER BY dist LIMIT 1;
``````

This gives me the id and geometry of the closest fire hydrant.

To solve the second step, how can I pass this to a query that determines new origin and destination points on the network line. According to this post some instruction is provided as follows but no example is provided.

Update

According to @geozelot this function may already be implemented. Am I understanding this usage correctly? Does the below intend to take me from fire hydrant #1 to #2?

I am receiving an error when trying to execute.

ERROR: line_locate_point: 2st arg isn't a point

``````SELECT *
FROM   pgr_withPoints(
'SELECT ogc_fid AS id,
start_id::int4 AS source,
end_id::int4 AS target,
geom as geom,
st_length(st_transform(geom,98012))::float8 AS cost',
'SELECT pnt.ogc_fid AS pid,
edg.edge_id,
edg.fraction
FROM fire_hydrants AS pnt
CROSS JOIN LATERAL (
SELECT ogc_fid AS edge_id,
ST_LineLocatePoint(geom, pnt.geom) AS fraction
FROM   network
ORDER BY
geom <-> pnt.geom
LIMIT  1
) AS edg',
-1, -2,
details := TRUE
)
;
``````
• if `a.ogc_fid, b2.ogc_fid` are your origin and destination then just make a simple function that takes those as inputs and runs dijksta Commented Apr 15, 2022 at 2:10
• Remember that the nearest hydrant across the network is not necessarily the one with the least cartesian distance (you may see the hydrant across a river, but the next bridge is 10km away). Commented Apr 15, 2022 at 10:50
• @ziggy wont I need to tie those points into my network? This is essentially the problem. Commented Apr 15, 2022 at 14:05
• See my answer here for an implementation using external POIs in the network. Commented Apr 15, 2022 at 14:40
• ...and are the geometries in `fire_hydrants.geom` actually `POINT`s? Commented Apr 15, 2022 at 20:56