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…
- Determine closest fire hydrant to parcel
- Determine origin and destination points on network line.
- 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
)
;
a.ogc_fid, b2.ogc_fidare your origin and destination then just make a simple function that takes those as inputs and runs dijksta – ziggy Apr 15 '22 at 02:10fire_hydrants.geomactuallyPOINTs? – geozelot Apr 15 '22 at 20:56