# Pgrouting: problem with pgr_withpointsDD

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: 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

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 `pid`s.

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.