Results 1 to 4 of 4
  1. #1
    Join Date
    Feb 2005
    Posts
    8

    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.

    Tables
    Bus Stop (E.g. Trafalgar Square, Conventry, Park Street)
    Route Number (E.g. A1, A2, 45, 345)


    Rules:
    1 Bus Stop can have 1 or many bus routes.
    1 Route will have 1 or many stops

    Table 3:
    BusStop_RouteNumber


    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?

  2. #2
    Join Date
    Feb 2005
    Location
    London
    Posts
    19
    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_Route
    Route_No
    Bus_Stop_Name
    Bus_Stop_No (1, 2, 3... etc to determine which no this bus stop is on this route)

    Example records will look like :

    A1, Convent Garden, 1
    A1, Leceister Square, 2
    82, North Finchley, 1
    82, Tally Ho Corner, 2
    ....
    82, Convent Garden, 12
    ....
    82, Leceister Square, 15


    Now if you want to find shortest route from 'Convent Garden' To 'Leceister Square' Following query will work

    SELECT Route_No
    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))

    Note:
    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





    Hope this all will help..

  3. #3
    Join Date
    Feb 2005
    Posts
    8

    full query not resolved

    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,
    BUT
    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.
    thanks again.

  4. #4
    Join Date
    Feb 2005
    Location
    London
    Posts
    19
    okay... i think, it might help if you define hotspots (major bus stops)

    when there is no direct route then try look for nearest hotspot and have best possible route for hotspots already defined in the database. I think that's how http://www.tfl.gov.uk works..

    You can also use Deductive Database for this if you are interested in Artificial intelligence.. just a thought.

    Good Luck with your projecct. it sounds really interesting .. !

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •