mysql - GTFS 查询以列出两个站点名称之间的所有出发和到达时间

Question

我是第一次使用 GTFS 结构，在查询时遇到了问题。我已将传输数据放入 mysql 表中，并可以自由查询它们，但我觉得我进行了太多的查询和循环以获取最简单的信息。

我想在单个查询中获得的是两个已知站点之间的所有出发时间和到达时间，可能由名称标识。

这是我目前所做的，它涉及一个查询，然后循环遍历每个 trip_id 以查找出发站和到达站信息 + 时间。

查询 1:

显示从特定起点站沿特定方向行驶的所有出发时间。结果将给出出发时间和 trip_id。

SELECT t.trip_id, trip_headsign, departure_time, direction_id, s.stop_name 
FROM stops s, routes r, stop_times st, calendar c, trips t 
WHERE departure_time > "00:00:00" and departure_time < "23:59:59" 
AND r.route_id=1 and s.stop_id = 42 
AND s.stop_id = st.stop_id 
AND st.trip_id = t.trip_id   
AND c.service_id = t.service_id   
AND c.monday=1 and direction_id=1;

结果

+---------+---------------+----------------+--------------+-----------+
| trip_id | trip_headsign | departure_time | direction_id | stop_name |
+---------+---------------+----------------+--------------+-----------+
| 5671498 | Grand Central | 04:43:00       |            1 | Garrison  |
| 5671501 | Grand Central | 05:13:00       |            1 | Garrison  |
| 5671504 | Grand Central | 05:43:00       |            1 | Garrison  |
| 5671506 | Grand Central | 06:08:00       |            1 | Garrison  |
| 5671507 | Grand Central | 06:32:00       |            1 | Garrison  |
| 5671513 | Grand Central | 06:53:00       |            1 | Garrison  |
| 5671516 | Grand Central | 07:18:00       |            1 | Garrison  |
| 5671519 | Grand Central | 07:40:00       |            1 | Garrison  |
| 5671521 | Grand Central | 08:03:00       |            1 | Garrison  |
| 5671523 | Grand Central | 08:32:00       |            1 | Garrison  |
| 5671525 | Grand Central | 08:58:00       |            1 | Garrison  |
| 5671526 | Grand Central | 09:27:00       |            1 | Garrison  |
| 5671529 | Grand Central | 10:24:00       |            1 | Garrison  |
| 5671532 | Grand Central | 11:24:00       |            1 | Garrison  |
| 5671535 | Grand Central | 12:24:00       |            1 | Garrison  |
| 5671537 | Grand Central | 13:24:00       |            1 | Garrison  |
| 5671540 | Grand Central | 14:24:00       |            1 | Garrison  |
| 5671542 | Grand Central | 15:24:00       |            1 | Garrison  |
| 5671543 | Grand Central | 16:22:00       |            1 | Garrison  |
| 5671547 | Grand Central | 17:24:00       |            1 | Garrison  |
| 5671550 | Grand Central | 18:24:00       |            1 | Garrison  |
| 5671552 | Grand Central | 19:26:00       |            1 | Garrison  |
| 5671554 | Grand Central | 20:24:00       |            1 | Garrison  |
| 5671556 | Grand Central | 21:24:00       |            1 | Garrison  |
| 5671557 | Grand Central | 22:24:00       |            1 | Garrison  |
| 5671559 | Croton-Harmon | 23:24:00       |            1 | Garrison  |
+---------+---------------+----------------+--------------+-----------+

查询 2:

给我特定行程的所有停靠点及其到达时间，使用上次查询的 trip_id:

SELECT s.stop_id,stop_lat, stop_lon, stop_name, arrival_time, stop_sequence 
FROM stop_times st JOIN stops s ON s.stop_id=st.stop_id 
WHERE trip_id=5671521;

结果

+---------+-----------+------------+------------------+--------------+---------------+
| stop_id | stop_lat  | stop_lon   | stop_name        | arrival_time | stop_sequence |
+---------+-----------+------------+------------------+--------------+---------------+
|      51 | 41.705839 | -73.937946 | Poughkeepsie     | 07:31:00     |             1 |
|      49 | 41.587448 | -73.947226 | New Hamburg      | 07:41:00     |             2 |
|      46 | 41.504007 | -73.984528 | Beacon           | 07:50:00     |             3 |
|      43 | 41.415283 | -73.958090 | Cold Spring      | 07:58:00     |             4 |
|      42 | 41.381780 | -73.947202 | Garrison         | 08:03:00     |             5 |
|      40 | 41.332601 | -73.970426 | Manitou          | 08:08:00     |             6 |
|      39 | 41.285962 | -73.930420 | Peekskill        | 08:17:00     |             7 |
|      37 | 41.246259 | -73.921884 | Cortlandt        | 08:22:00     |             8 |
|      33 | 41.189903 | -73.882394 | Croton-Harmon    | 08:32:00     |             9 |
|       4 | 40.805157 | -73.939149 | Harlem-125th St. | 09:09:00     |            10 |
|       1 | 40.752998 | -73.977056 | Grand Central    | 09:22:00     |            11 |
+---------+-----------+------------+------------------+--------------+---------------+

我真正想要的是两站之间的出发时间和到达时间列表，例如这个理论结果:

+---------+----------------+----------------+-----------+---------------+--------------+
| trip_id | departure_stop | departure_time | direction | arrival_stop  | arrival_time |
+---------+----------------+----------------+-----------+---------------+--------------+
| 5671521 | Garrison       | 08:03:00       |         1 | Grand Central | 09:22:00     |
| 5671522 | Garrison       | 08:32:00       |         1 | Grand Central | 09:51:00     |    
...etc...

Answer 1

简单地将数据连接在一起，连接到 stops 两次用于开始和结束 stop 并连接到 stop_times 两次用于开始和结束 stop_times，
我唯一不确定的是 direction_id 来自哪里。
试试下面的查询。
在查询的最后，您可以指定 start_s.stop_id 和 end_s.stop_id，它们代表您正在查询数据的两个站点。

SELECT t.trip_id,
       start_s.stop_name as departure_stop,
       start_st.departure_time,
       direction_id as direction,
       end_s.stop_name as arrival_stop,
       end_st.arrival_time
FROM
trips t INNER JOIN calendar c ON t.service_id = c.service_id
        INNER JOIN routes r ON t.route_id = r.route_id
        INNER JOIN stop_times start_st ON t.trip_id = start_st.trip_id
        INNER JOIN stops start_s ON start_st.stop_id = start_s.stop_id
        INNER JOIN stop_times end_st ON t.trip_id = end_st.trip_id
        INNER JOIN stops end_s ON end_st.stop_id = end_s.stop_id
WHERE c.monday = 1 
  AND direction_id = 1
  AND start_st.departure_time > "00:00:00" AND start_st.departure_time < "23:59:59" 
  AND r.route_id = 1
  AND start_s.stop_id = 42
  AND end_s.stop_id = 1

我尝试从 this link 中查找 GTFS 结构示例我在 direction_id

上找不到任何内容

指定停止名称而不是 AND start_s.stop_id = 42 AND end_s.stop_id = 1
只需使用 AND start_s.stop_name = 'Garrison' AND end_s.stop_name = 'Grand Central'