I have osm data imported into PostgreSQL with osm2po tool (which makes the data routable). Also I have imported data relationed with nodes and node_tags with osmosis tool.

I need to obtain a set of nearest nodes from a given location (maybe 30 or 40). Then I need to calculate the shortest path from the given node from each others. It means to execute shortest_path query multiple times in a row, which takes about 2 seconds (1 query 2 seconds: 30 queries 60 -> too long).

SELECT * FROM shortest_path('
   SELECT id AS id,
          source::int4 AS source,
          target::int4 AS target,
          cost::float8 AS cost
   FROM tt_2po_4pgr',

What would be the best way to do this in a reasonable time?

Please excuse my English


In accordance to dkastl answer, I tried with wrapper functions and effectively I obtain better results 2 or 3 times better, but isn't acceptable for me yet.

With the following query:

explain analyze SELECT *
    FROM dijkstra_sp_delta('dr_2po_4pgr', 278174, 289786, 0.3);

I obtain this results: explain

  • 1
    2 seconds per query is quite long if you're not doing long distance routing that selects lots of network data. Have you checked for indices? – dkastl Nov 21 '12 at 13:36
  • Yes, I have index in both columns (source, target), but there are more than 2 million rows. – Iñigo Nov 22 '12 at 9:48

Referring to your comment it seems to me, that you select the whole network with each query, right?

You can speedup queries if you select only the area you expect to be relevant. The easiest way to do this is to select data within a BBOX that contains start and end point of your route: http://workshop.pgrouting.org/chapters/shortest_path.html#wrapper

This will increase speed drastically for short distance queries.


Remember that pgrouting is not really optimised for query speed, you should use some other tools if you need real-time performance. In principle it might be possible to modify internal implementations the algorithms to calculate many routes from single point, but I suggest to check this out from pgrouting mailing list.

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.