Pregunta de GTFS para listr todos los times de salida y llegada entre dos nombres de parada

Estoy trabajando con la estructura GTFS por primera vez y tengo problemas con las consultas. Tengo los datos de tránsito en tablas mysql, y puedo consultarlos, pero siento que estoy haciendo demasiadas consultas y que los loops obtienen la información más simple.

Lo que me gustaría get en una sola consulta son todas las horas de salida y llegada entre dos paradas conocidas, posiblemente identificadas por su nombre.

Esto es lo que tengo hasta ahora, que implica una consulta, seguido de un bucle a través de cada trip_id para encontrar la información de la estación de salida y llegada + veces.

Consulta 1 :

Muestra todos los horarios de salida desde una estación de origen particular, yendo en una dirección particular. El resultado dará times de salida y 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; 

Resultado

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

Consulta 2 :

Dame todas las paradas en un viaje en particular junto con sus horas de llegada, usa trip_id de la última consulta:

 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; 

Resultado

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

Lo que realmente me gustaría es una list de horarios de salida y times de llegada entre dos paradas, como este resultado teorizado:

 +---------+----------------+----------------+-----------+---------------+--------------+ | 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... 

simplemente uniendo datos juntos, uniéndose a stops dos veces para inicio y finalización y uniéndose a stop_times dos veces para inicio y final stop_times,
lo único de lo que no estoy seguro es de dónde viene direction_id .
testing la siguiente consulta.
Al final de la consulta, puede especificar start_s.stop_id y end_s.stop_id que representan las dos paradas sobre las que está consultando los datos.

 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 

Intenté search el ejemplo de estructura de GTFS desde este enlace y no pude encontrar nada en direction_id

Para especificar nombres de detención en lugar de AND start_s.stop_id = 42 AND end_s.stop_id = 1
solo use AND start_s.stop_name = 'Garrison' AND end_s.stop_name = 'Grand Central'

    Intereting Posts