mysql - GTFS SQL查询间接路由

我需要编写查询以查找GTFS数据结构中两个站点之间的路线/路程，我已将所有csv数据迁移到mysql表。这是stop_times.txt的样本数据

trip_id,arrival_time,departure_time,stop_id,stop_sequence,stop_headsign,shape_dist_traveled
170309010001S10,12:24:00,12:24:00,10000871,1,MRT SG BULOH - HOSPITAL SG BULOH,0.00688
170309010001S10,12:28:32,12:28:32,10000872,2,MRT SG BULOH - HOSPITAL SG BULOH,1.249023
170309010001S10,12:29:57,12:29:57,10002386,3,MRT SG BULOH - HOSPITAL SG BULOH,1.595181
170309010001S10,12:31:58,12:31:58,10000874,4,MRT SG BULOH - HOSPITAL SG BULOH,2.113874
170309010001S10,12:33:45,12:33:45,10000875,5,MRT SG BULOH - HOSPITAL SG BULOH,2.56561

我无法进行查询以返回所有间接路线的旅程，我们将不胜感激。

最佳答案

如果只希望Trip.Id和Route.Id在两个站点之间。

select StopTimes.TripId , Trips.RouteId
from StopTimes
join Trips on (Trips.Id = StopTimes.TripId)
Where TripId in
(
    select StopTimes.TripId
    from StopTimes
    Where StopTimes.StopId = FirstStopId
)
And StopTimes.StopId = SecondStopId

请记住，要在StopTimes.TripId上创建索引。

关于mysql - GTFS SQL查询间接路由，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/44325493/