1

I want to search route from one place to one another.

The places are referenced in a SQLite Database.

I think I should make loop in the SQL query but I don't know how to perform this.

puchka
  • 13
  • 4
  • puchka, cna you refine a little bit the question? What is bus_stop_id1 and bus_stop_id2? I suppose input (call :bus_stop_id1, :bus_stop_id2). What means "search for 3 or more bus lines to join the 2 bus_stop with possible intermediary bus_stop". Are you trying to build a path like busline1 (stop1) -> busline2 -> busline3 (stop2) – venergiac Jan 19 '14 at 08:48
  • Yes, bus_stop_id1 and bus_stop_id2 are inputs. Yes, I try to make a path. – puchka Jan 19 '14 at 08:51

1 Answers1

0

puchka,

if I understood the question, you need a routing algorithm or graph search algorithm; in other words you cannot iterate simply on the table because you could find infinite sub-optimal solutions and you need the sortest; see Dijkastra and the related java implementation or http://www.cs.nyu.edu/~vs667/development/~DijkstraAlgorithm/ or related .

See also these SO question on

  1. The best shortest path algorithm.
  2. k-shortest (alternative) path algorithm, java implementations
Community
  • 1
  • 1
venergiac
  • 7,469
  • 2
  • 48
  • 70
  • So have I to transform my database to a graph? – puchka Jan 19 '14 at 09:27
  • Unfortunatly yes; any different way will be not optimal. If the databse is not big you can simply search for 2 or 3 consecutive lines and no more – venergiac Jan 19 '14 at 09:34
  • @puchka Constructing all the possible routes for a bounded number of changes (e.g., 2 or 3) should leave the data relatively manageable, but it is something that you'd want to compute once and reuse rather than building on each query. (That is, you'd be denormalizing the data to improve search performance. A classic tradeoff.) – Donal Fellows Jan 19 '14 at 12:30
  • That's true but it's a mobile application so there is a constraint in the memory too. – puchka Jan 22 '14 at 07:45
  • @puchka you got any solution? – Pranita Nov 18 '21 at 03:32
  • @Pranita Kind of. I created a 3rd database with the intermediary steps between every pair of places and a query checking if there is one itinerary between them. It's not optimal as I don't have the distance information but it had the merit of working. – puchka Apr 16 '23 at 07:50