GTFS查询以列出两个站点名称之间的所有出发和到达时间 [英] GTFS Query to list all departure and arrival times between two stop names
问题描述
我第一次使用GTFS结构，但查询遇到麻烦.我已经将运输数据存储到mysql表中，可以自由查询它们，但是我感觉我在进行太多的查询和for循环以获取最简单的信息.
I'm working with GTFS structure for the first time and am having trouble with the queries. I've got the transit data into mysql tables, and am free to query them, but I feel like I'm doing far too many queries and for loops to get the simplest info.
我想在一个查询中得到的是两个已知站点之间的所有出发时间和到达时间，可能用名称来标识.
What I'd like to get in a single query is all the departure times and arrival times between two known stops, possibly identified by name.
这是我到目前为止的内容，其中涉及一个查询，然后循环遍历每个trip_id以查找出发和到达站的信息+时间.
Here is what I have so far, which involves a query, followed by looping through each trip_id to find the departure and arrival station info + times.
查询1 :
显示从特定始发站到特定方向的所有出发时间.结果将给出出发时间和trip_ids.
Show all departure times from a particular origin station, going in a particular direction. Result will give departure times and trip_ids.
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:
Give me all the stops on a particular trip along with their arrival times, use trip_id from last query:
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 |
+---------+-----------+------------+------------------+--------------+---------------+
我真正想要的是两站之间的出发时间和到达时间的列表，例如理论上的结果:
What I'd really like is a list of departure times and arrival times between two stops, such as this theorized result:
+---------+----------------+----------------+-----------+---------------+--------------+
| 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...
推荐答案
简单地将数据连接在一起，两次连接到stops进行开始和结束停止，两次连接到stop_times进行开始和结束stop_times，
我唯一不确定的是direction_id的来源.
请尝试以下查询.
在查询的最后，您可以指定start_s.stop_id和end_s.stop_id，它们代表您要查询数据的两个停靠点.
simply joining data together, joining to stops twice for start and end stop and joining to stop_times twice for start and end stop_times,
the only thing i am unsure about is where direction_id comes from.
try the below query.
At the very end of the query, you can specify start_s.stop_id and end_s.stop_id which represents the two stops you're querying data about.
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
我尝试从此链接和i中查找GTFS结构示例在direction_id
I tried looking up GTFS structure example from this link and i couldn't find anything on 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'
To specify stop names instead of AND start_s.stop_id = 42 AND end_s.stop_id = 1
just use AND start_s.stop_name = 'Garrison' AND end_s.stop_name = 'Grand Central'
这篇关于GTFS查询以列出两个站点名称之间的所有出发和到达时间的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
