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
      )
;
wtgeographer
  • 900
  • 1
  • 13
  • 29
  • 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 Apr 15 '22 at 02: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 Apr 15 '22 at 10:50
  • @ziggy wont I need to tie those points into my network? This is essentially the problem. – wtgeographer Apr 15 '22 at 14:05
  • See my answer here for an implementation using external POIs in the network. – geozelot Apr 15 '22 at 14:40
  • ...and are the geometries in fire_hydrants.geom actually POINTs? – geozelot Apr 15 '22 at 20:56
  • You are right @geozelot. fire_hydrants.geom was multipoint. I have corrected and query runs but is taking forever to complete. What does this query actually do? Is it defining all points as pnt.geom? I just need to pass it a single geom returned by nearest query (in question above). – wtgeographer Apr 18 '22 at 16:35

0 Answers0