I'd like to calculate a propagation from sources using my road graph. In extenso, I'd like to answer the following question: where are the limits of my isochrones for every sources I got. My sources (point) are not on the graph but outside.
No problem to create my graph.
Here's how I create a table with the sources and elements attaching them to my graph:
create table exo.psource as (with poi as ( select p.gid as gid_pt, p."q (m3/h)" as debit, p.volume as volume, p.geom from exo.epi p ) SELECT poi.gid_pt::int4, poi.volume::int8, poi.debit::int4, closest_tr.gid::int4, closest_tr.dist::double precision, st_linelocatepoint(closest_tr.geom, poi.geom)::double precision as fraction FROM poi LEFT JOIN LATERAL (SELECT t.gid, ST_Distance(t.geom, poi.geom) as dist, t.geom FROM exo.troncon t ORDER BY poi.geom <-> t.geom LIMIT 1 ) AS closest_tr ON TRUE )
After that I run the following query to get the vertices at 200m or less from my sources:
select pgr.*, t vp.the_geom from pgr_withPointsDD( 'select gid AS id, nsource::int4 AS source, ncible::int4 AS target, st_length(geom)::float8 AS cost, st_length(geom)::float8 as reverse_cost from exo.troncon'::text, 'select gid_pt as pid, gid as edge_id, fraction from exo.source'::text, ( select array_agg(exo.source.gid_pt order by gid_pt asc) from exo.source ) , -- un array de tous les id de mes sources 200 , details := true ) pgr left join exo.troncon_vertices_pgr tvp on tvp.id = pgr.node
Except: for some sources, the closest edge hasn't been browsed (see source 2 to vertex 142 by example)
What did I miss?
No vertex is exactly at 200m from a source, how do I calculate the remaining part of edges to get 200m exactly? My sources are distants of the edge, how do I integrate this distance into the calculation?
I'm quite sure the solution to this probleme may benefit a lot of people.
I orignally post this problem on a french forum.
I've read those articles and used them partially or fully to solve (not totally) my problem: