Как составить список всех остановок, связанных с маршрутом, с помощью GTFS?

Как вы заметили, в GTFS нет прямой зависимости между маршрутами и остановками. Вместо этого остановки связаны с поездками, где каждая поездка представляет собой один «пробег» транспортного средства по определенному маршруту. Это отражает тот факт, что маршрут не обязательно обслуживает каждую из своих остановок постоянно — например, по выходным он может пропускать остановки за пределами средней школы.

Таким образом, получение списка всех остановок, обслуживаемых маршрутом, требует объединения нескольких моделей:

• routes.txt дает вам идентификатор маршрута для интересующего вас маршрута.
• trips.txt дает вам набор идентификаторов поездок для этого маршрута.
• stop_times.txt дает вам набор идентификаторов остановок для остановок, обслуживаемых в каждой из этих поездок.
• stops.txt дает вам информацию о каждой из этих остановок.

Предполагая, что вы используете базу данных SQL для хранения данных GTFS, вы можете использовать такой запрос (после получения идентификатора маршрута):

SELECT stop_id, stop_name FROM stops WHERE stop_id IN (
  SELECT DISTINCT stop_id FROM stop_times WHERE trip_id IN (
    SELECT trip_id FROM trips WHERE route_id = <route_id>));

Помните, однако, что это выведет запись для каждой остановки, которая когда-либо обслуживалась маршрутом. Если вы создаете информацию о расписании для пассажира, вы, вероятно, захотите ограничить запрос только рейсами, выполняемыми сегодня, и только временем остановок с отправлениями, скажем, в следующие тридцать минут.

Обновление: я написал приведенный выше SQL-запрос так, как я это сделал, поскольку я чувствовал, что он наиболее просто иллюстрирует взаимосвязь между моделями GTFS, но btse прав (в своем ответе ниже), что такой запрос никогда не будет реально использовать в производстве. Это слишком медленно. Вместо этого вы должны использовать соединения таблиц и индексы, чтобы поддерживать разумное время запроса.

Вот эквивалентный запрос, написанный таким образом, чтобы его можно было скопировать и вставить в реальное приложение:

SELECT DISTINCT stops.stop_id, stops.stop_name
  FROM trips
  INNER JOIN stop_times ON stop_times.trip_id = trips.trip_id
  INNER JOIN stops ON stops.stop_id = stop_times.stop_id
  WHERE route_id = <route_id>;

Обычно вы также создаете индекс для каждого столбца, используемого в предложении JOIN или WHERE, что в данном случае будет означать:

CREATE INDEX stop_times_trip_id_index ON stop_times(trip_id);

CREATE INDEX trips_route_id_index ON trips(route_id);

(Обратите внимание, что РСУБД обычно автоматически индексируют каждую таблицу по ее первичному ключу, поэтому нет необходимости явно создавать индекс для stops.stop_id.)

Возможны многие дальнейшие оптимизации в зависимости от конкретной используемой СУБД и вашей готовности пожертвовать дисковым пространством ради производительности. Но эти команды обеспечат хорошую производительность практически на любой СУБД без излишнего ущерба для ясности.

Я наткнулся на этот пост в своем поиске в Google и решил обновить его, добавив лучший ответ на случай, если кто-то еще наткнется на него. Ответ, который дал Саймон, на 100% правильный, однако предоставленный им запрос довольно медленный для больших фидов GTFS. Вот запрос, который делает то же самое, но работает значительно быстрее.

Просто чтобы дать вам некоторые неподтвержденные данные, для канала GTFS размером около 50 МБ запрос Саймона выполнялся где-то от 10 до 25 секунд. Приведенный ниже оператор последовательно занимает ‹ 0,2 секунды.

SELECT T3.stop_id, T3.stop_name 
FROM trips AS T1
JOIN
stop_times AS T2
ON T1.trip_id=T2.trip_id AND route_id = <routeid>
JOIN stops AS T3
ON T2.stop_id=T3.stop_id
GROUP BY T3.stop_id, T3.stop_name

ОБНОВЛЕНИЕ:

Я понял, что не упоминал об этом раньше, но, конечно, вы захотите иметь индексы, в которых соединяются все таблицы.

Если вы GROUP BY shape_id выбираете из trips, вы можете сделать запрос еще быстрее.

Использование запроса @btse для получения уникальных остановок для двух маршрутов занимает 1,147 с.

Мой эквивалентный запрос занимает 0,4 с.

SELECT unique_stops.route_id, unique_stops.stop_id, stop_name, stop_desc, stop_lat, stop_lon
FROM
  stops,
  (SELECT stop_id, route_id
   FROM
     stop_times,
     (SELECT trip_id, route_id
      FROM trips
      WHERE route_id IN (801, 803)
      GROUP BY shape_id
     ) AS unique_trips
   WHERE stop_times.trip_id = unique_trips.trip_id
   GROUP BY stop_id) AS unique_stops
WHERE stops.stop_id = unique_stops.stop_id

Если вы работаете в R, вы можете сделать это, чтобы найти маршруты, которые останавливаются в вашем целевом пункте назначения X:

require(dplyr)

routesX <- routes %>%
  left_join(trips %>% select(trip_id, route_id, shape_id)) %>%
  left_join(stop_times %>% select(trip_id, stop_id)) %>%
  semi_join(stops %>% filter(grepl('X', stop_name, ignore.case = T)), by = c('stop_id' = 'stop_code')) %>%
  select(names(routes), shape_id) %>%
  unique 

Если необходимо направление остановки, следует изменить ответ Лукмана:

SELECT unique_stops.route_id, unique_stops.stop_id, stop_name, stop_desc, stop_lat, stop_lon, unique_stops.direction_id
FROM
  stops,
  (SELECT stop_id, route_id, direction_id
   FROM
     stop_times,
     (SELECT trip_id, route_id, direction_id
      FROM trips
      WHERE route_id IN (801, 803)
      GROUP BY direction_id
     ) AS unique_trips
   WHERE stop_times.trip_id = unique_trips.id
   GROUP BY stop_id, direction_id) AS unique_stops
WHERE stops.stop_id = unique_stops.stop_id

Если вы также добавите stop_times.stop_sequence таким же образом и упорядочите по направлению и последовательности остановок, остановки будут отсортированы так, как они есть в поездке.

Если вы используете onebusaway, есть быстрый способ сделать это, не касаясь GTFS.

Допустим, вы хотите узнать автобусные остановки для автобусного маршрута «M1» на Манхэттене, Нью-Йорк.

http://bustime.mta.info/api/where/stops-for-route/MTA%20NYCT_M1.json?key=yourapikey&includePolylines=false&version=2

предоставит вам json-канал, после чего вы сможете извлечь автобусные остановки в обоих направлениях по маршруту M1.