Closed hbruch closed 1 year ago
hbruch
commented
4 years ago Antwort von DELFI e.V. am 6.4.:
Die DB Fernverkehrsdaten sind in der „Agency DB-Nahverkehr“ enthalten, da sie nicht separat von der DB bereitgestellt werden. Wir werden die Metadatenbeschreibung an dieser Stelle um einen Hinweis ergänzen.
hbruch
commented
4 years ago Auch mit den Hinweisen des DELFI e.V. können wir im Datensatz vom 9.4.2020 keine ICE-Verbindung finden:
SELECT * FROM gtfs.routes WHERE route_short_name like '%ICE%';
feed_index | route_id | agency_id | route_short_name | route_long_name | route_desc | route_type | route_url | route_color | route_text_color | route_sort_order
(0 rows)
Auch die laut Fahrplan der DB um 9:04 in Stuttgart ankommende, um 9:10 abfahrende ICE-Verbindung ist nicht auffindbar:
SELECT DISTINCT r.route_id, r.route_short_name, r.route_type, t.trip_id, st.stop_id, st.stop_sequence, st.departure_time, st.arrival_time FROM gtfs.routes r
JOIN gtfs.trips t ON r.route_id=t.route_id
JOIN gtfs.stop_times st ON t.trip_id= st.trip_id
WHERE st.stop_id like 'de:08111:6115%'
AND (st.arrival_time ='09:04:00' OR st.departure_time='09:10:00');
route_id | route_short_name | route_type | trip_id | stop_id | stop_sequence | departure_time | arrival_time
----------+------------------+------------+-----------+---------------+---------------+----------------+--------------
162326_2 | 83 | 2 | 921797234 | de:08111:6115 | 3 | 09:10:00 | 09:00:00
162326_2 | 83 | 2 | 921797248 | de:08111:6115 | 3 | 09:10:00 | 09:00:00
162326_2 | 83 | 2 | 921797251 | de:08111:6115 | 3 | 09:10:00 | 09:00:00
162326_2 | 83 | 2 | 921797265 | de:08111:6115 | 0 | 09:10:00 | 09:10:00
derhuerst
commented
2 years ago DELFI-Datensatz vom 21.03.2022:
SELECT route_id, route_short_name, route_long_name, agency_name
FROM routes
LEFT JOIN agency ON agency.agency_id = routes.agency_id
WHERE agency_name LIKE 'DB %' AND route_short_name like '%ICE%'| route_id | route_short_name | route_long_name | agency_name |
|---|---|---|---|
| 162337_101 | ICE | DB Fernverkehr AG |
derhuerst
commented
1 year ago Laut dem DELFI-Datensatz vom 20.03.2023 fahren heute lediglich 3 ICEs.
Mit gtfs-via-postgres 4.5.1 abgefragt:
SELECT DISTINCT ON(trip_id)
*
FROM arrivals_departures
WHERE route_short_name LIKE '%ICE%'
AND date = '2023-03-21'| arrival_departure_id | route_id | route_short_name | route_long_name | route_type | trip_id | direction_id | trip_headsign | service_id | shape_id | date | stop_sequence | stop_headsign | pickup_type | drop_off_type | shape_dist_traveled | timepoint | tz | arrival_time | t_arrival | departure_time | t_departure | stop_id | stop_name | station_id | station_name | frequencies_row | frequencies_it |
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| MjA3NzY1MTQwNg==:MjAyMy0wMy0yMQ==:MA==:LTE=:LTE= | 162244_101 | ICE | NULL | 101 | 2077651406 | 0 | Interlaken Ost | 11198 | 19360 | 2023-03-21 00:00:00 | 0 | NULL | regular | regular | NULL | NULL | Europe/Berlin | 19:56:00 | 2023-03-21 19:56:00+01 | 19:56:00 | 2023-03-21 19:56:00+01 | ch:23005:300 | Basel SBB | NULL | NULL | -1 | -1 |
| MjA3NzY1MTQyMA==:MjAyMy0wMy0yMQ==:Mg==:LTE=:LTE= | 162244_101 | ICE | NULL | 101 | 2077651420 | 0 | Basel SBB | 11198 | 19362 | 2023-03-21 00:00:00 | 2 | NULL | regular | regular | NULL | NULL | Europe/Berlin | 06:21:00 | 2023-03-21 06:21:00+01 | 06:22:00 | 2023-03-21 06:22:00+01 | 000008507483 | Spiez | NULL | NULL | -1 | -1 |
| MjA3NzY1MTQ0MQ==:MjAyMy0wMy0yMQ==:Mw==:LTE=:LTE= | 162244_101 | ICE | NULL | 101 | 2077651441 | 0 | Basel SBB | 11198 | 19362 | 2023-03-21 00:00:00 | 3 | NULL | regular | regular | NULL | NULL | Europe/Berlin | 10:32:00 | 2023-03-21 10:32:00+01 | 10:33:00 | 2023-03-21 10:33:00+01 | 000008507100 | Thun | NULL | NULL | -1 | -1 |
| MjA3NzY1MTQ1Nw==:MjAyMy0wMy0yMQ==:Ng==:LTE=:LTE= | 162244_101 | ICE | NULL | 101 | 2077651457 | 0 | Interlaken Ost | 11198 | 19360 | 2023-03-21 00:00:00 | 6 | NULL | regular | regular | NULL | NULL | Europe/Berlin | 19:52:00 | 2023-03-21 19:52:00+01 | 19:53:00 | 2023-03-21 19:53:00+01 | 000008507493 | Interlaken West | NULL | NULL | -1 | -1 |
| MjA3NzY1MTQ3Ng==:MjAyMy0wMy0yMQ==:Nw==:LTE=:LTE= | 162244_101 | ICE | NULL | 101 | 2077651476 | 0 | Interlaken Ost | 3564 | 19360 | 2023-03-21 00:00:00 | 7 | NULL | regular | regular | NULL | NULL | Europe/Berlin | 13:58:00 | 2023-03-21 13:58:00+01 | 13:58:00 | 2023-03-21 13:58:00+01 | 000008507492 | Interlaken Ost | NULL | NULL | -1 | -1 |
| MjA3NzY1MTUwNw==:MjAyMy0wMy0yMQ==:MQ==:LTE=:LTE= | 162244_101 | ICE | NULL | 101 | 2077651507 | 0 | Basel SBB | 1285 | 19358 | 2023-03-21 00:00:00 | 1 | NULL | regular | regular | NULL | NULL | Europe/Berlin | 17:53:00 | 2023-03-21 17:53:00+01 | 17:53:00 | 2023-03-21 17:53:00+01 | ch:23005:300 | Basel SBB | NULL | NULL | -1 | -1 |
| MjA3NzY1MTUzMQ==:MjAyMy0wMy0yMQ==:Mw==:LTE=:LTE= | 162244_101 | ICE | NULL | 101 | 2077651531 | 0 | Chur | 11207 | 19366 | 2023-03-21 00:00:00 | 3 | NULL | regular | regular | NULL | NULL | Europe/Berlin | 11:12:00 | 2023-03-21 11:12:00+01 | 11:13:00 | 2023-03-21 11:13:00+01 | 000008509002 | Landquart | NULL | NULL | -1 | -1 |
| MjA3NzY5NjE5OQ==:MjAyMy0wMy0yMQ==:Mg==:LTE=:LTE= | 162252_101 | ICE | NULL | 101 | 2077696199 | 1 | Amsterdam Centraal | 1392 | 19381 | 2023-03-21 00:00:00 | 2 | NULL | regular | regular | NULL | NULL | Europe/Berlin | 20:28:00 | 2023-03-21 20:28:00+01 | 20:28:00 | 2023-03-21 20:28:00+01 | 000008400058 | Amsterdam Centraal | NULL | NULL | -1 | -1 |
| MjA3NzY5NjIwNA==:MjAyMy0wMy0yMQ==:MA==:LTE=:LTE= | 162252_101 | ICE | NULL | 101 | 2077696204 | 1 | Amsterdam Centraal | 11490 | 19381 | 2023-03-21 00:00:00 | 0 | NULL | regular | regular | NULL | NULL | Europe/Berlin | 22:29:00 | 2023-03-21 22:29:00+01 | 22:29:00 | 2023-03-21 22:29:00+01 | NL:S:ah | Arnhem Centraal | NULL | NULL | -1 | -1 |
| MjA3NzY5NjIxMA==:MjAyMy0wMy0yMQ==:Mg==:LTE=:LTE= | 162252_101 | ICE | NULL | 101 | 2077696210 | 1 | Amsterdam Centraal | 1392 | 19381 | 2023-03-21 00:00:00 | 2 | NULL | regular | regular | NULL | NULL | Europe/Berlin | 09:29:00 | 2023-03-21 09:29:00+01 | 09:29:00 | 2023-03-21 09:29:00+01 | 000008400058 | Amsterdam Centraal | NULL | NULL | -1 | -1 |
| MjA3NzY5NjIxOA==:MjAyMy0wMy0yMQ==:MQ==:LTE=:LTE= | 162252_101 | ICE | NULL | 101 | 2077696218 | 1 | Amsterdam Centraal | 5432 | 19381 | 2023-03-21 00:00:00 | 1 | NULL | regular | regular | NULL | NULL | Europe/Berlin | 10:59:00 | 2023-03-21 10:59:00+01 | 11:02:00 | 2023-03-21 11:02:00+01 | 000008400621 | Utrecht Centraal | NULL | NULL | -1 | -1 |
| MjA3NzY5NjIyNw==:MjAyMy0wMy0yMQ==:MQ==:LTE=:LTE= | 162252_101 | ICE | NULL | 101 | 2077696227 | 1 | Amsterdam Centraal | 1392 | 19381 | 2023-03-21 00:00:00 | 1 | NULL | regular | regular | NULL | NULL | Europe/Berlin | 12:59:00 | 2023-03-21 12:59:00+01 | 13:02:00 | 2023-03-21 13:02:00+01 | 000008400621 | Utrecht Centraal | NULL | NULL | -1 | -1 |
| MjA3NzY5NjIzMA==:MjAyMy0wMy0yMQ==:Mg==:LTE=:LTE= | 162252_101 | ICE | NULL | 101 | 2077696230 | 1 | Amsterdam Centraal | 1392 | 19381 | 2023-03-21 00:00:00 | 2 | NULL | regular | regular | NULL | NULL | Europe/Berlin | 15:29:00 | 2023-03-21 15:29:00+01 | 15:29:00 | 2023-03-21 15:29:00+01 | 000008400058 | Amsterdam Centraal | NULL | NULL | -1 | -1 |
| MjA3NzY5NjIzNg==:MjAyMy0wMy0yMQ==:MA==:LTE=:LTE= | 162252_101 | ICE | NULL | 101 | 2077696236 | 1 | Amsterdam Centraal | 11489 | 19381 | 2023-03-21 00:00:00 | 0 | NULL | regular | regular | NULL | NULL | Europe/Berlin | 16:29:00 | 2023-03-21 16:29:00+01 | 16:29:00 | 2023-03-21 16:29:00+01 | NL:S:ah | Arnhem Centraal | NULL | NULL | -1 | -1 |
| MjA3NzY5NjIzNw==:MjAyMy0wMy0yMQ==:MA==:LTE=:LTE= | 162252_101 | ICE | NULL | 101 | 2077696237 | 1 | Amsterdam Centraal | 5691 | 19381 | 2023-03-21 00:00:00 | 0 | NULL | regular | regular | NULL | NULL | Europe/Berlin | 20:59:00 | 2023-03-21 20:59:00+01 | 20:59:00 | 2023-03-21 20:59:00+01 | NL:S:ah | Arnhem Centraal | NULL | NULL | -1 | -1 |
| MjA3NzY5NjMwNg==:MjAyMy0wMy0yMQ==:MA==:LTE=:LTE= | 162252_101 | ICE | NULL | 101 | 2077696306 | 0 | Arnhem Centraal | 133 | 19378 | 2023-03-21 00:00:00 | 0 | NULL | regular | regular | NULL | NULL | Europe/Berlin | 06:38:00 | 2023-03-21 06:38:00+01 | 06:38:00 | 2023-03-21 06:38:00+01 | 000008400058 | Amsterdam Centraal | NULL | NULL | -1 | -1 |
| MjA3NzY5NjMxMQ==:MjAyMy0wMy0yMQ==:MA==:LTE=:LTE= | 162252_101 | ICE | NULL | 101 | 2077696311 | 0 | Arnhem Centraal | 9873 | 19378 | 2023-03-21 00:00:00 | 0 | NULL | regular | regular | NULL | NULL | Europe/Berlin | 18:38:00 | 2023-03-21 18:38:00+01 | 18:38:00 | 2023-03-21 18:38:00+01 | 000008400058 | Amsterdam Centraal | NULL | NULL | -1 | -1 |
| MjA3NzY5NjMxNw==:MjAyMy0wMy0yMQ==:MA==:LTE=:LTE= | 162252_101 | ICE | NULL | 101 | 2077696317 | 0 | Arnhem Centraal | 133 | 19378 | 2023-03-21 00:00:00 | 0 | NULL | regular | regular | NULL | NULL | Europe/Berlin | 16:38:00 | 2023-03-21 16:38:00+01 | 16:38:00 | 2023-03-21 16:38:00+01 | 000008400058 | Amsterdam Centraal | NULL | NULL | -1 | -1 |
| MjA3NzY5NjMxOQ==:MjAyMy0wMy0yMQ==:Mg==:LTE=:LTE= | 162252_101 | ICE | NULL | 101 | 2077696319 | 0 | Arnhem Centraal | 133 | 19378 | 2023-03-21 00:00:00 | 2 | NULL | regular | regular | NULL | NULL | Europe/Berlin | 09:05:00 | 2023-03-21 09:05:00+01 | 09:05:00 | 2023-03-21 09:05:00+01 | NL:S:ah | Arnhem Centraal | NULL | NULL | -1 | -1 |
| MjA3NzY5NjMyMQ==:MjAyMy0wMy0yMQ==:MA==:LTE=:LTE= | 162252_101 | ICE | NULL | 101 | 2077696321 | 0 | Arnhem Centraal | 133 | 19378 | 2023-03-21 00:00:00 | 0 | NULL | regular | regular | NULL | NULL | Europe/Berlin | 14:38:00 | 2023-03-21 14:38:00+01 | 14:38:00 | 2023-03-21 14:38:00+01 | 000008400058 | Amsterdam Centraal | NULL | NULL | -1 | -1 |
| MjA3NzY5NjMyNA==:MjAyMy0wMy0yMQ==:MQ==:LTE=:LTE= | 162252_101 | ICE | NULL | 101 | 2077696324 | 0 | Arnhem Centraal | 11483 | 19378 | 2023-03-21 00:00:00 | 1 | NULL | regular | regular | NULL | NULL | Europe/Berlin | 13:01:00 | 2023-03-21 13:01:00+01 | 13:04:00 | 2023-03-21 13:04:00+01 | 000008400621 | Utrecht Centraal | NULL | NULL | -1 | -1 |
| MjA3NzY5NjMyOA==:MjAyMy0wMy0yMQ==:MQ==:LTE=:LTE= | 162252_101 | ICE | NULL | 101 | 2077696328 | 0 | Arnhem Centraal | 133 | 19378 | 2023-03-21 00:00:00 | 1 | NULL | regular | regular | NULL | NULL | Europe/Berlin | 11:01:00 | 2023-03-21 11:01:00+01 | 11:04:00 | 2023-03-21 11:04:00+01 | 000008400621 | Utrecht Centraal | NULL | NULL | -1 | -1 |
| MjA3Nzg2ODc3MA==:MjAyMy0wMy0yMQ==:MQ==:LTE=:LTE= | 162271_101 | ICE | NULL | 101 | 2077868770 | 1 | Passau Hbf | 5390 | 19601 | 2023-03-21 00:00:00 | 1 | NULL | regular | regular | NULL | NULL | Europe/Berlin | 06:54:00 | 2023-03-21 06:54:00+01 | 06:56:00 | 2023-03-21 06:56:00+01 | 000008100514 | Wien Meidling | NULL | NULL | -1 | -1 |
| MjA3Nzg2ODc5OA==:MjAyMy0wMy0yMQ==:Mg==:LTE=:LTE= | 162271_101 | ICE | NULL | 101 | 2077868798 | 1 | Passau Hbf | 2341 | 19600 | 2023-03-21 00:00:00 | 2 | NULL | regular | regular | NULL | NULL | Europe/Berlin | 09:44:00 | 2023-03-21 09:44:00+01 | 09:46:00 | 2023-03-21 09:46:00+01 | 000008100008 | St.Pölten Hbf | NULL | NULL | -1 | -1 |
| MjA3Nzg2ODgwNw==:MjAyMy0wMy0yMQ==:Mw==:LTE=:LTE= | 162271_101 | ICE | NULL | 101 | 2077868807 | 1 | Passau Hbf | 9688 | 19600 | 2023-03-21 00:00:00 | 3 | NULL | regular | regular | NULL | NULL | Europe/Berlin | 12:32:00 | 2023-03-21 12:32:00+01 | 12:34:00 | 2023-03-21 12:34:00+01 | 000008100013 | Linz Hbf | NULL | NULL | -1 | -1 |
| MjA3Nzg2ODg0Mg==:MjAyMy0wMy0yMQ==:Mg==:LTE=:LTE= | 162271_101 | ICE | NULL | 101 | 2077868842 | 1 | Passau Hbf | 1748 | 19600 | 2023-03-21 00:00:00 | 2 | NULL | regular | regular | NULL | NULL | Europe/Berlin | 15:44:00 | 2023-03-21 15:44:00+01 | 15:46:00 | 2023-03-21 15:46:00+01 | 000008100008 | St.Pölten Hbf | NULL | NULL | -1 | -1 |
| MjA3Nzg2ODg4Mg==:MjAyMy0wMy0yMQ==:Mw==:LTE=:LTE= | 162271_101 | ICE | NULL | 101 | 2077868882 | 1 | NULL | 1090 | 19600 | 2023-03-21 00:00:00 | 3 | NULL | regular | regular | NULL | NULL | Europe/Berlin | 11:32:00 | 2023-03-21 11:32:00+01 | 11:34:00 | 2023-03-21 11:34:00+01 | 000008100013 | Linz Hbf | NULL | NULL | -1 | -1 |
| MjA3Nzg2ODg5Mg==:MjAyMy0wMy0yMQ==:Mg==:LTE=:LTE= | 162271_101 | ICE | NULL | 101 | 2077868892 | 1 | NULL | 11444 | 19600 | 2023-03-21 00:00:00 | 2 | NULL | regular | regular | NULL | NULL | Europe/Berlin | 13:44:00 | 2023-03-21 13:44:00+01 | 13:46:00 | 2023-03-21 13:46:00+01 | 000008100008 | St.Pölten Hbf | NULL | NULL | -1 | -1 |
| MjA3Nzg2ODg5OA==:MjAyMy0wMy0yMQ==:MQ==:LTE=:LTE= | 162271_101 | ICE | NULL | 101 | 2077868898 | 1 | NULL | 9706 | 19599 | 2023-03-21 00:00:00 | 1 | NULL | regular | regular | NULL | NULL | Europe/Berlin | 17:18:00 | 2023-03-21 17:18:00+01 | 17:20:00 | 2023-03-21 17:20:00+01 | 000008100514 | Wien Meidling | NULL | NULL | -1 | -1 |
| MjA3Nzg2ODk1NQ==:MjAyMy0wMy0yMQ==:NA==:LTE=:LTE= | 162271_101 | ICE | NULL | 101 | 2077868955 | 0 | Wien Hbf | 2341 | 19592 | 2023-03-21 00:00:00 | 4 | NULL | regular | regular | NULL | NULL | Europe/Berlin | 20:47:00 | 2023-03-21 20:47:00+01 | 20:47:00 | 2023-03-21 20:47:00+01 | 000008103000 | Wien Hbf | NULL | NULL | -1 | -1 |
| MjA3Nzg2ODk4Mw==:MjAyMy0wMy0yMQ==:MQ==:LTE=:LTE= | 162271_101 | ICE | NULL | 101 | 2077868983 | 0 | Wien Hbf | 1452 | 19592 | 2023-03-21 00:00:00 | 1 | NULL | regular | regular | NULL | NULL | Europe/Berlin | 17:26:00 | 2023-03-21 17:26:00+01 | 17:28:00 | 2023-03-21 17:28:00+01 | 000008100013 | Linz Hbf | NULL | NULL | -1 | -1 |
| MjA3Nzg2ODk5Nw==:MjAyMy0wMy0yMQ==:MA==:LTE=:LTE= | 162271_101 | ICE | NULL | 101 | 2077868997 | 0 | Wien Hbf | 9677 | 19592 | 2023-03-21 00:00:00 | 0 | NULL | regular | regular | NULL | NULL | Europe/Berlin | 12:29:00 | 2023-03-21 12:29:00+01 | 12:29:00 | 2023-03-21 12:29:00+01 | de:09262:156 | Passau Hbf | NULL | NULL | -1 | -1 |
| MjA3Nzg2OTAxMQ==:MjAyMy0wMy0yMQ==:MA==:LTE=:LTE= | 162271_101 | ICE | NULL | 101 | 2077869011 | 0 | Wien Hbf | 4161 | 19592 | 2023-03-21 00:00:00 | 0 | NULL | regular | regular | NULL | NULL | Europe/Berlin | 15:29:00 | 2023-03-21 15:29:00+01 | 15:29:00 | 2023-03-21 15:29:00+01 | de:09262:156 | Passau Hbf | NULL | NULL | -1 | -1 |
| MjA3Nzg2OTAxOA==:MjAyMy0wMy0yMQ==:MA==:LTE=:LTE= | 162271_101 | ICE | NULL | 101 | 2077869018 | 0 | Wien Hbf | 1090 | 19592 | 2023-03-21 00:00:00 | 0 | NULL | regular | regular | NULL | NULL | Europe/Berlin | 14:29:00 | 2023-03-21 14:29:00+01 | 14:29:00 | 2023-03-21 14:29:00+01 | de:09262:156 | Passau Hbf | NULL | NULL | -1 | -1 |
| MjA3Nzg2OTAyNg==:MjAyMy0wMy0yMQ==:MA==:LTE=:LTE= | 162271_101 | ICE | NULL | 101 | 2077869026 | 0 | Wien Hbf | 5390 | 19593 | 2023-03-21 00:00:00 | 0 | NULL | regular | regular | NULL | NULL | Europe/Berlin | 20:31:00 | 2023-03-21 20:31:00+01 | 20:31:00 | 2023-03-21 20:31:00+01 | de:09262:156 | Passau Hbf | NULL | NULL | -1 | -1 |
| MjA3Nzg2OTA0MA==:MjAyMy0wMy0yMQ==:Mw==:LTE=:LTE= | 162271_101 | ICE | NULL | 101 | 2077869040 | 0 | Wien Hbf | 9680 | 19591 | 2023-03-21 00:00:00 | 3 | NULL | regular | regular | NULL | NULL | Europe/Berlin | 12:14:00 | 2023-03-21 12:14:00+01 | 12:16:00 | 2023-03-21 12:16:00+01 | 000008100008 | St.Pölten Hbf | NULL | NULL | -1 | -1 |
traines-source
commented
1 year ago Also zumindest aktuell sind die ICEs, ECs, ICs jeweils ohne Zuggattungs-Prefix enthalten, d.h. der route_short_name enthält nur die Liniennummer (vgl. #41 ). Dann findet man sie natürlich nicht, wenn man nach ICE sucht. Stattdessen kann man nach route_type 101 (ICE) und 102 (IC/EC) suchen (vgl. z.B. hier).
Auch zu beachten: Fast alle (alle?) Fernverkehrszüge halten nicht an den richtigen Gleisen, sondern an der Top-Level-Station mit Postfix _G (vgl. #115 ). Zum Beispiel sind alle Züge in Frankfurt(Main) Hbf bei de:06412:10_G eingetragen.
Laut Meldung auf der Homepage des DELFI sei der Schienen-Fernverkehr in den GTFS-Daten des DELFI enthalten.
Aus unserer Sicht fehlen derzeit die meisten Fernverbindungen der Deutschen Bahn.
Aktualisierungszeitpunkt der GTFS-Daten:
Downloadlink der GTFS-Daten: https://cms.opendata-oepnv.de/fileadmin/datasets/delfi/20200403_fahrplaene_gesamtdeutschland_gtfs.zip