MySQL - Getting information from bus routes database

Question

Hi,

The problem I'm having is with fetching data from the following tables where a bus route contains 1 change or more. I am still relatively new to SQL.

I have a database containing 2 tables (bus_route and bus_stop). bus_route contains the following columns:

route - the number of the bus
run - the direction of the bus (1 or 2)
sequence - the position of the stop along the route
stop_code - unique code for that stop
stop_name
longitude
latitude

bus_stop contains the following columns:

stop_code
stop_name
latitude
longitude
stop_area - 3 to 8 buses per stop area

For each bus there are between 20 and 70 rows in bus_route depending on the number of stops and 1 row per stop in bus_stop.

I have written this SQL to fetch rows where we have a direct route between 2 locations:

SELECT distinct route
from bus_route
where
SQRT(POW((69.1 * (latitude - {startLocationLatitude})) , 2 ) + 
POW((53 * (longitude - {startLocationLongitude})), 2)) < 0.3 
and route in (SELECT distinct route
from bus_route
where
SQRT(POW((69.1 * (latitude - {endLocationLatitude})) , 2 ) + 
POW((53 * (longitude - {endLocationLongitude})), 2)) < 0.3)

It works well in returning rows where the the bus_stop is within 0.3 miles of the start/end locations.

I have also written the below SQL for finding routes with 1 change where the 2nd bus leaves from the same stop as where you depart the 1st bus:

select t1.route, t1.run, t1.sequence, t1.stop_code, t2.route, t2.run, t2.sequence
from bus_route t1 
inner join bus_route t2 on (t2.stop_code=t1.stop_code) 
where t1.route in (SELECT distinct route
from bus_route
where
SQRT(POW((69.1 * (latitude - {startLocationLatitude})) , 2 ) + 
POW((53 * (longitude - {startLocationLongitude})), 2)) < 0.3)
and t2.route in (SELECT distinct route
from bus_route
where
SQRT(POW((69.1 * (latitude - {endLocationLatitude})) , 2 ) + 
POW((53 * (longitude - {endLocationLongitude})), 2)) < 0.3))

Both statements work well but I am having trouble incorporating stop_area into the statement to find routes with 1 change where the 2nd bus leaves from another stop in the same stop_area.

Any advise on the above queries or how I could use stop_area would be greatly appreciated.

I should also mention that the following is not mine (I found it online) :

SQRT(POW((69.1 * (latitude - {endLocationLatitude})) , 2 ) + 
POW((53 * (longitude - {endLocationLongitude})), 2)) < 0.3))

Answer 1

There's a 1:1 relation between bus_route and bus_stop. So to go from route1, to stop1, to all matching stops in the same area, to all matching routes with the same area:

route1 -> stop1 => stop2 -> route2

One way to do that would be changing:

from bus_route t1 
inner join bus_route t2 on t2.stop_code = t1.stop_code

To:

from bus_route t1 
inner join bus_stop s1 on s1.stop_code = t1.stop_code
inner join bus_stop s2 on s2.stop_area = s1.stop_area
inner join bus_route t2 on t2.stop_code = s2.stop_code