2

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
      )
;
6
  • 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
    – ziggy
    Commented Apr 15, 2022 at 2:10
  • 1
    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).
    – geozelot
    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.
    – geozelot
    Commented Apr 15, 2022 at 14:40
  • ...and are the geometries in fire_hydrants.geom actually POINTs?
    – geozelot
    Commented Apr 15, 2022 at 20:56

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Browse other questions tagged or ask your own question.