0

I've imported the latest North America OSM extract into postgres using osm2po so that I can use the pgr_drivingDistance function of pgRouting to create a drive-time polygon. I'm at the final step, but the function is returning no results even though it definitely should.

I use a bounding box to narrow down the possible results inside the query consumed by `pgr_drivingdistance. My first debug step was to see what that is returning.
My inner query (altered temporarily to include the geom):

SELECT rn.id AS id,
rn.source::int4 AS source,
rn.target::int4 AS target,
rn.cost::float8 as cost,
rn2.geom_way
FROM public.na_2po_4pgr rn
left join public.na_2po_4pgr rn2 on rn2.id = rn.id
where ST_Intersects(rn.geom_way, st_geomfromtext('POLYGON((-79.5670159782642 36.3965968758439,-79.5670159782642 36.6695646217025,-79.2319617622083 36.6695646217025,-79.2319617622083 36.3965968758439,-79.5670159782642 36.3965968758439))', 4326))

This query returns 10230 rows, and looks like: enter image description here

That's correct, and is the correct region, and obviously, plenty of nodes.

I've got a start node, and picked it's ID, which I've verified is present in the returned rows: 14232131 enter image description here

So finally, I put it all together, and run pgr_drivingDistance with the following:

SELECT drv.*, rn.geom_way, rn.x1, rn.y1 FROM pgr_drivingdistance('
  SELECT rn.id AS id,
  rn.source::int4 AS source,
  rn.target::int4 AS target,
  rn.cost::float8 as COST
  FROM public.na_2po_4pgr rn
  where ST_Intersects(rn.geom_way, st_geomfromtext(''POLYGON((-79.5670159782642 36.3965968758439,-79.5670159782642 36.6695646217025,-79.2319617622083 36.6695646217025,-79.2319617622083 36.3965968758439,-79.5670159782642 36.3965968758439))'', 4326))',
  14232131,
  25,
  false,
  false) as drv
  left join public.na_2po_4pgr rn on drv.id1 = rn.id;

But I receive no results at all. At first I thought perhaps the start ID needed to be either the source or the target id, and not the id of the record (though I'd expect it's looking for the id specified by the id column in the inner query). I tried providing the source (and target) value of the starting record as the input, and interestingly, that DID return records, but in a completely different part of the country, and it included records that aren't even in the sub query, so I have no idea how that would even work.

1

Ok, so I figured it out. It was a two part problem.

First, using the source of the start node IS the correct way to produce a matchup.

Second, my left join at the end was joining to id1 instead of id2 produced by the pgr_drivingDistance function. That caused it to relate to the wrong entities, producing the results in a different part of the country. As soon as I changed it to left join public.na_2po_4pgr rn on drv.id2 = rn.id; I got the expected join, and my results are what I would expect.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.