sql - MySQL - 从巴士路线数据库获取信息

Question

我遇到的问题是从下表中获取数据，其中公交路线包含 1 个或多个更改。我对 SQL 还是比较陌生。

我有一个包含 2 个表(bus_route 和 bus_stop)的数据库。 bus_route 包含以下列:

route - 巴士的编号
run - 公共(public)汽车的方向(1 或 2)
sequence - 停靠点沿路线的位置
stop_code - 该站点的唯一代码
停止名称
经度
纬度

bus_stop 包含以下列:

停止代码
停止名称
纬度
经度
stop_area - 每个停靠区 3 到 8 辆公共(public)汽车

对于每辆公交车，bus_route 中有 20 到 70 行，具体取决于停靠站的数量，bus_stop 中每个停靠站有 1 行。

我编写了这个 SQL 来获取我们在 2 个位置之间有直接路由的行:

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)

它适用于返回 bus_stop 距离开始/结束位置 0.3 英里以内的行。

我还编写了以下 SQL，用于查找具有 1 个变化的路线，其中第二辆公共(public)汽车从您离开第一辆公共(public)汽车的同一站出发:

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

这两个语句都运行良好，但我无法将 stop_area 合并到语句中以查找具有 1 个变化的路线，其中第二辆公共(public)汽车从同一 stop_area 中的另一个站点出发。

如有任何关于上述查询或如何使用 stop_area 的建议，我们将不胜感激。

我还应该指出以下不是我的(我在网上找到的):

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

Answer 1

bus_route 和 bus_stop 之间存在 1:1 的关系。所以从 route1 到 stop1，到同一区域中的所有匹配站点，到具有相同区域的所有匹配路线:

route1 -> stop1 => stop2 -> route2

一种方法是改变:

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

收件人:

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