Unanswered: Determining Indirect Bus routes: SQL problem statement
Am trying to write a query that will find out a bus route from Place A to Place B even if place A and B are not directly connected by a single route.
Bus Stop (E.g. Trafalgar Square, Conventry, Park Street)
Route Number (E.g. A1, A2, 45, 345)
1 Bus Stop can have 1 or many bus routes.
1 Route will have 1 or many stops
So, if route A1 had both Traf Sq and Coventry, then a query such as
Select number from RouteNumber, BS_RN where RouteNumber.number=BS_RN.number and BS_RN.BusStop = "Coventry" and BS_RN.BusStop = "Traf square"
correct? or no?
Problem if, what if One wants to go from Traf Square to Park Street and there is no direct route?
Say, A1 has Coventry and Traf Square on its route and A2 has Coventry and Park Street on its route.
How would this query be?
Should one run more than 1 query one back to back and then work out a difference?
SELECT DISTINCT Table1.Route_No
FROM BusStop_RouteNumber AS Table1
INNER JOIN BusStop_RouteNumber AS Table2
ON Table1.Route_No = Table2.Route_No
WHERE Table1.Bus_Stop = 'From Stop'
AND Table2.Bus_Stop = 'To Stop'
This should work.
If you want most direct bus route you might want to change table design.
I would rather have only one table...
Bus_Stop_No (1, 2, 3... etc to determine which no this bus stop is on this route)
Now if you want to find shortest route from 'Convent Garden' To 'Leceister Square' Following query will work
FROM Bus_Route AS TableFrom
INNER JOIN Bus_Route AS TableTo
ON TableFrom.Route_No = TableTo.Route_No
WHERE TableFrom.Bus_Stop_Name = 'Convent Garden'
AND TableTo.Bus_Stop_Name = 'Leceister Square'
ORDER BY SQR((TableTo.Bus_Stop_No - TableFrom.Bus_Stop_No) * (TableTo.Bus_Stop_No - TableFrom.Bus_Stop_No))
1. (TableTo.Bus_Stop_No - TableFrom.Bus_Stop_No) might give you negative no
2. SQR = square root function ... I am doing square of the (TableTo.Bus_Stop_No - TableFrom.Bus_Stop_No)
and then finding the square root to make it positive no... you can use other functions depending on database you are using
hey, thanks for the input,
but i dont think the main part of my query is answered in your response.
When there is a direct route, i.e. both starting and destination are on a particular route, then we can find that out,
what if the starting and destination stops are not connected by any route directly?
say, one has to get down midway at stop X and catch a bus route 4 that touches the destination but starts somewhere obscure?
To go deeper, sometimes, one may not have even an indirect connection at all, but may need to walk 10 steps from 1 bus stop to another to catch a connector. How do we figure that out?
Then, there is a case where there may be a connector, but one has to go around the world for that, where you could get down at a particular stop and walk 10 steps to another stop to get a connector.
I am preparing the full problem statement and DB and grabbing available information from the Bus Agency's website and populatiing the database.
once all is done, feel free to grab the data from a not-yet-decided location and trying your luck on it. it's one interesting project. atleast for me.
give me 3-4 days atleast.