3

What is the best way to use routing table generated by osm2po? When I apply osm2po on my *.osm.pbf file, the generated table looks like this:

enter image description here

Here is my routing script:

SELECT d.seq, d.path_seq, d.edge, d.cost, h.osm_name, h.kmh, h.x1, h.y1, h.x2, h.y2, h.geom_way  FROM pgr_dijkstra('
    SELECT id,
         source,
         target,
     cost,
         reverse_cost
        FROM hh_2po_4pgr',
    (select source from hh_2po_4pgr h order by st_makepoint(h.x1, h.y1) <-> (select st_makepoint(lon, lat) from fnc_geocode('my source address')) limit 1), 
    (select target from hh_2po_4pgr h order by st_makepoint(h.x2, h.y2) <-> (select st_makepoint(lon, lat) from fnc_geocode('my target address')) limit 1), 
    directed := false) d
left join hh_2po_4pgr h on d.edge = h.id

When I display route in Geoserver target is not correct, as you can see on the picture: enter image description here

Can you tell me what I am doing wrong? I need my route to end on the blue point. I am using osm2po 5.1.0. Function fnc_geocode('xxx'). It has a function that returns coordinates of the entered address. With osm2pgrouting it works fine. Now we have more data, so this is the reason why I decided to use the osm2po tool instead.

  • 2
    Please don't vandalize your posts. – Glorfindel Mar 10 '17 at 10:55
1

The created table only contains real intersections. If I understand the picture correctly, you want to locate source and target on arbitrary positions on edges - known as perpendecular foot. If so, you need a bit more preprocessing or sth. which can handle this stuff at runtime and which does not modify the graph with each request. However, osm2po is able to do this. But not with the DB means.

  • Can you tell me how can I do this with db? At first I've tried import data by osm2pgsql, and apply on them pgr_createTopology function, but this not works, I try find route between 2 points, and nothing result .. but when I use osm2pgrouting route was found ... but unfortunately I do not use osm2pgrouting on this data – Denis Stephanov Feb 20 '17 at 19:51
1

Everything is OK - routing includes all edges of graph - you have to manually cut first and last line...

I've answered similar question here. I think this could be what you're looking for.


EDIT:

It could be issue with '<->' operator, following documenation:

"For PostgreSQL below 9.5 only gives centroid distance of bounding boxes and for PostgreSQL 9.5+, does true KNN distance search giving true distance between geometries, and distance sphere for geographies.":

Try this:

SELECT d.seq, d.path_seq, d.edge, d.cost, h.osm_name, h.kmh, h.x1, h.y1, h.x2, h.y2, h.geom_way  FROM pgr_dijkstra('
    SELECT id,
         source,
         target,
     cost,
         reverse_cost
        FROM hh_2po_4pgr',
    (select source from hh_2po_4pgr h order by st_distance(st_makepoint(h.x1, h.y1), (select st_makepoint(lon, lat) from fnc_geocode('my source address')) limit 1), 
    (select target from hh_2po_4pgr h order by st_distance(st_makepoint(h.x2, h.y2), (select st_makepoint(lon, lat) from fnc_geocode('my target address')) limit 1), 
    directed := false) d
left join hh_2po_4pgr h on d.edge = h.id

If this not help check a point returned by your geocoding function - maybe it is in some other place then this one on your picture and in fact it is nearer to this shorter line then to Cirila a Metoda?

About your line of defence - as you see your injecting into function one of vertices of nearest edge (not necessarily the nearest) so function will start and stop routing on one of crossings of your nearest street, including (or not including) all edge. Function of pg_routing doesn't even know what was your original starting point, so there is no magic here. If you need to include only a part of nearest edge you'll need some preprocessing. This is how it works...

I've made some tests and this calculations with cutting first and last edge takes few milliseconds so I considered it negligible...

  • Thanks, I'll try it and give you response ... but I have some questions: 1 is not this query too slow with a lot of caluclations? 2. Can you tell me is some oficial explanation why we need do that? Because I need defend this solution in my work. Thanks – Denis Stephanov Feb 21 '17 at 14:32
  • Sorry, I saw you change right now and I'll try it ... are you sure with this? Because on place when pgr_dijsktra required source and target id, you select id of edge from hh_2po_4pgr. I run this script and give me really bed route. – Denis Stephanov Feb 21 '17 at 20:26
  • Sorry Denis - you are right - vertice id not an edge id. I've edited my answer - hope it helps now. – Jendrusk Feb 22 '17 at 6:51
  • I afraid st_distance do not wroks too... look at this: ctrlv.cz/shots/2017/02/22/0lwo.png for now I use nominatim api to get coordinates. As you can see, coordinates x1, y1 and entered by mine, are very different :/ – Denis Stephanov Feb 22 '17 at 9:47
  • BUG in query... x1,y1 not x1,x2... Coordinates will be different but not that much... – Jendrusk Feb 22 '17 at 10:31

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.