7

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

Everything seems correct in my (favorite) GIS: result

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.

Here are the datas

I've read those articles and used them partially or fully to solve (not totally) my problem:

présentation foss4g

une question sur gis stackeexchange

un post de bostongis

article sur geoafrica

geozelot
  • 30,050
  • 4
  • 32
  • 56
Brindavoine
  • 91
  • 2

1 Answers1

1

There are too many questions in this post, but let me address the most prominent issue:

You are not getting the correct results (at all!) because you are using the wrong vertice identifiers; all functions in the withPoints family require the points to have negative pids.

As it stands, your query will not use any point from your exo.sources table, but those from exo.troncon_vertices_pgr with corresponding ids; look closely at your results above!

Rather, force the exo.sources.gid_pt values to be negative, e.g. here

(SELECT ARRAY_AGG(-gid_pt ORDER BY gid_pt ASC) FROM exo.source)

and also, since you specified details := true, include the points in the result set, e.g. like so

[...] AS pgr
LEFT JOIN (
  SELECT id, geom FROM exo.troncon_vertices_pgr
  UNION ALL
  SELECT -gid_pt AS id, geom FROM exo.source
) AS tvp
  ON tvp.id = pgr.node
;

And also the easiest of your secondary issues:

It's easy to include the actual distance from your exo.source.geom to their nearest exo.troncon.geom into the calculations; just filter the pgr.agg_cost for values greater than 200 - exo.source.dist, e.g. like so

[...]
  ON  tvp.id = pgr.node
WHERE pgr.agg_cost < (SELECT 200-dist FROM exo.source WHERE -gid_pt = pgr.start_vid)
;

There are likely better ways to do this, but it works just fine.

About the remaining issue:

Getting the partial edges for the remaining distance after the last reachable node is more involved and likely due for a separate question (if you can't find a similar one on the board), right after you sorted this one out.

geozelot
  • 30,050
  • 4
  • 32
  • 56