Linking lines to stops
Assuming the route.txt file reports the association between bus and metro number and their lines, and stops.txt the stops with their locations, what is the table linking the lines and the stops so to have the stops by which a certain bus passes?
Thanks,
Fabrizio Bartolomucci
Följ inlägget
0
följare
Hi Fabrizio!
I hope the attached file helps You to get some understanding of the GTFS extract.
I believe that to get the buses, metros ,... for a city - do You need to know what agencies operates in the city and filter all other.
For Stockholm is the main operator SL - Angency 275.
For Uppsala is the main operator UL - Agency 251.
Best regards, Åke
Bifogad fil:
Much clearer now, thank you very much.
Yet I am not so clear what is the semantic of the trips and stop_times tables. Also I think I should elicit both the route_id and bus number from the route.text table. Finally I would like to be sure that stop numbers are unique all over Sweden (and Finland?), or just respect to their own city so forcing to also use the city when referencing them.
I am trying to load the buses from routes.txt but I see a lot of repetitions. Is that the right table for the job or am I missing something by doing so?
Also, I do not know how to assess whether a route refers to a bus, metro, boat, tram or something else. Perhaps the TLT and BLT mean something, but I am not sure what.
In fact I got just 88 buses, I think a bit little for the whole of Sweden. What was wrong?
Perhaps there are repetitions in different agencies?! Or what?
As for the filtering, in theory I would not need it, if not needed by the system in that the app just loads the stops around the position of the user and so the filtering is automatically done at the time. The problem is to know if there is some reasons in the system to do the filtering, perhaps to distinguish buses with the same number in different agencies. For that matter that would be quite a big challenge for my architecture as it would force to either consider each agency as a different city or to in some way to modify the architecture to associate the lines to the agencies. Before progressing I would like to be sure of the situation, though.
I loaded all the files on my DB but found a few quite disturbing issues. In particular I have 2.254.972 entries in the table corresponding to the stop_times.txt file and when I examined it I found, for example, a whopping number of lines passing by stop 7421667, each one with its departure and arrival times, to the point that it pushed me to think this is the list of expected transits rather than the pivot table between the stops and the lines. What is also disturbing is that in that file each row has a different trip, suggesting even the trips refer to specific cars rather then being a static resource. I could of course join all stop-times with the same stop and position, but there could be two veritable lines crossing a stop after the same number of stops from the start and so I could ditch valid entries an I would not solve the trips issue.
How may actually load the minimal trips and stop-times so to create a stable backdrop for my app?
82596768,07:07:00,07:07:00,7421667,17,,,,
82596780,23:52:00,23:52:00,7421667,17,,,,
82596781,23:37:00,23:37:00,7421667,17,,,,
82596782,24:07:00,24:07:00,7421667,17,,,,
82596798,05:38:00,05:38:00,7421667,17,,,,
82596835,23:23:00,23:23:00,7421667,17,,,,
82596867,06:08:00,06:08:00,7421667,17,,,,
82596914,22:53:00,22:53:00,7421667,17,,,,
82596919,22:38:00,22:38:00,7421667,17,,,,
82596923,22:02:00,22:02:00,7421667,17,,,,
82596924,22:26:00,22:26:00,7421667,17,,,,
82597027,19:54:00,19:54:00,7421667,17,,,,
82597029,19:44:00,19:44:00,7421667,17,,,,
82597031,19:34:00,19:34:00,7421667,17,,,,
82597035,19:26:00,19:26:00,7421667,17,,,,
82597056,21:50:00,21:50:00,7421667,17,,,,
82597089,20:15:00,20:15:00,7421667,17,,,,
82597091,21:15:00,21:15:00,7421667,17,,,,
82597092,20:39:00,20:39:00,7421667,17,,,,
82597095,21:27:00,21:27:00,7421667,17,,,,
82597096,20:27:00,20:27:00,7421667,17,,,,
82597099,20:51:00,20:51:00,7421667,17,,,,
82597104,21:03:00,21:03:00,7421667,17,,,,
82597117,06:45:00,06:45:00,7421667,17,,,,
82597302,07:18:00,07:18:00,7421667,17,,,,
82597682,21:55:00,21:55:00,7421667,16,,,,
82597776,19:32:00,19:32:00,7421667,16,,,,
82598088,06:02:00,06:02:00,7421667,16,,,,
82598130,20:31:00,20:31:00,7421667,16,,,,
82598131,21:07:00,21:07:00,7421667,16,,,,
82598132,20:43:00,20:43:00,7421667,16,,,,
82598133,20:19:00,20:19:00,7421667,16,,,,
82598134,20:07:00,20:07:00,7421667,16,,,,
82598142,07:01:00,07:01:00,7421667,16,,,,
82598156,06:18:00,06:18:00,7421667,16,,,,
82598160,05:32:00,05:32:00,7421667,16,,,,
82598253,06:51:00,06:51:00,7421667,16,,,,
82598264,06:29:00,06:29:00,7421667,16,,,,
82598307,21:18:00,21:18:00,7421667,16,,,,
82598309,21:42:00,21:42:00,7421667,16,,,,
82598338,23:39:00,23:39:00,7421667,16,,,,
82598340,24:09:00,24:09:00,7421667,16,,,,
82598341,22:39:00,22:39:00,7421667,16,,,,
82598345,23:09:00,23:09:00,7421667,16,,,,
82598348,22:54:00,22:54:00,7421667,16,,,,
82598349,23:24:00,23:24:00,7421667,16,,,,
82598367,19:44:00,19:44:00,7421667,16,,,,
82598423,19:03:00,19:03:00,7421667,16,,,,
82598424,18:53:00,18:53:00,7421667,16,,,,
82598609,22:10:00,22:10:00,7421667,16,,,,
82596771,09:38:00,09:38:00,7421667,17,,,,
82596772,10:08:00,10:08:00,7421667,17,,,,
82596773,09:26:00,09:26:00,7421667,17,,,,
82596774,09:48:00,09:48:00,7421667,17,,,,
82596775,09:58:00,09:58:00,7421667,17,,,,
82596776,10:18:00,10:18:00,7421667,17,,,,
82596778,00:22:00,00:22:00,7421667,17,,,,
82596779,00:52:00,00:52:00,7421667,17,,,,
82596805,08:54:00,08:54:00,7421667,17,,,,
82596834,07:54:00,07:54:00,7421667,17,,,,
82596838,22:08:00,22:08:00,7421667,17,,,,
82596870,09:10:00,09:10:00,7421667,17,,,,
82596903,10:29:00,10:29:00,7421667,17,,,,
82596905,10:39:00,10:39:00,7421667,17,,,,
82596913,23:08:00,23:08:00,7421667,17,,,,
82596920,22:23:00,22:23:00,7421667,17,,,,
82596922,22:53:00,22:53:00,7421667,17,,,,
82596963,17:00:00,17:00:00,7421667,17,,,,
82596985,17:10:00,17:10:00,7421667,17,,,,
82596994,08:09:00,08:09:00,7421667,17,,,,
82596995,08:24:00,08:24:00,7421667,17,,,,
82596996,08:39:00,08:39:00,7421667,17,,,,
82597005,17:20:00,17:20:00,7421667,17,,,,
82597006,18:00:00,18:00:00,7421667,17,,,,
82597007,17:50:00,17:50:00,7421667,17,,,,
82597008,18:10:00,18:10:00,7421667,17,,,,
82597009,17:30:00,17:30:00,7421667,17,,,,
82597010,17:40:00,17:40:00,7421667,17,,,,
82597016,11:21:00,11:21:00,7421667,17,,,,
82597024,19:00:00,19:00:00,7421667,17,,,,
82597026,18:30:00,18:30:00,7421667,17,,,,
82597028,18:40:00,18:40:00,7421667,17,,,,
82597030,18:20:00,18:20:00,7421667,17,,,,
82597032,18:50:00,18:50:00,7421667,17,,,,
82597033,19:10:00,19:10:00,7421667,17,,,,
82597059,21:10:00,21:10:00,7421667,17,,,,
82597062,21:53:00,21:53:00,7421667,17,,,,
82597063,21:38:00,21:38:00,7421667,17,,,,
82597064,20:58:00,20:58:00,7421667,17,,,,
82597065,21:23:00,21:23:00,7421667,17,,,,
82597087,20:11:00,20:11:00,7421667,17,,,,
82597088,19:59:00,19:59:00,7421667,17,,,,
82597090,19:39:00,19:39:00,7421667,17,,,,
82597094,20:23:00,20:23:00,7421667,17,,,,
82597097,19:29:00,19:29:00,7421667,17,,,,
82597100,20:35:00,20:35:00,7421667,17,,,,
82597101,19:49:00,19:49:00,7421667,17,,,,
82597102,19:19:00,19:19:00,7421667,17,,,,
82597105,20:47:00,20:47:00,7421667,17,,,,
82597134,06:38:00,06:38:00,7421667,17,,,,
82597137,06:53:00,06:53:00,7421667,17,,,,
82597139,06:23:00,06:23:00,7421667,17,,,,
82597141,11:09:00,11:09:00,7421667,17,,,,
82597143,10:59:00,10:59:00,7421667,17,,,,
82597146,16:20:00,16:20:00,7421667,17,,,,
82597147,16:40:00,16:40:00,7421667,17,,,,
82597148,16:50:00,16:50:00,7421667,17,,,,
82597149,16:30:00,16:30:00,7421667,17,,,,
82597151,14:51:00,14:51:00,7421667,17,,,,
82597152,15:11:00,15:11:00,7421667,17,,,,
82597153,13:41:00,13:41:00,7421667,17,,,,
82597154,14:01:00,14:01:00,7421667,17,,,,
82597155,13:01:00,13:01:00,7421667,17,,,,
82597156,13:11:00,13:11:00,7421667,17,,,,
82597157,15:31:00,15:31:00,7421667,17,,,,
82597158,15:51:00,15:51:00,7421667,17,,,,
82597159,14:31:00,14:31:00,7421667,17,,,,
82597160,13:21:00,13:21:00,7421667,17,,,,
82597161,15:01:00,15:01:00,7421667,17,,,,
82597162,16:01:00,16:01:00,7421667,17,,,,
82597163,13:31:00,13:31:00,7421667,17,,,,
82597164,16:11:00,16:11:00,7421667,17,,,,
82597165,14:11:00,14:11:00,7421667,17,,,,
82597166,13:51:00,13:51:00,7421667,17,,,,
82597167,15:41:00,15:41:00,7421667,17,,,,
82597168,15:21:00,15:21:00,7421667,17,,,,
82597169,14:41:00,14:41:00,7421667,17,,,,
82597170,14:21:00,14:21:00,7421667,17,,,,
82597178,23:38:00,23:38:00,7421667,17,,,,
82597179,23:23:00,23:23:00,7421667,17,,,,
82597181,24:08:00,24:08:00,7421667,17,,,,
82597182,23:53:00,23:53:00,7421667,17,,,,
82597217,11:51:00,11:51:00,7421667,17,,,,
82597218,11:31:00,11:31:00,7421667,17,,,,
82597219,12:41:00,12:41:00,7421667,17,,,,
82597220,12:11:00,12:11:00,7421667,17,,,,
82597221,12:31:00,12:31:00,7421667,17,,,,
82597222,12:01:00,12:01:00,7421667,17,,,,
82597223,12:51:00,12:51:00,7421667,17,,,,
82597224,11:41:00,11:41:00,7421667,17,,,,
82597225,12:21:00,12:21:00,7421667,17,,,,
82597275,07:24:00,07:24:00,7421667,17,,,,
82597277,07:09:00,07:09:00,7421667,17,,,,
82597278,07:39:00,07:39:00,7421667,17,,,,
82597337,10:49:00,10:49:00,7421667,17,,,,
82597679,17:42:00,17:42:00,7421667,16,,,,
82597680,21:30:00,21:30:00,7421667,16,,,,
82597777,18:52:00,18:52:00,7421667,16,,,,
82597778,17:32:00,17:32:00,7421667,16,,,,
82597779,10:22:00,10:22:00,7421667,16,,,,
82597780,10:32:00,10:32:00,7421667,16,,,,
82597781,17:52:00,17:52:00,7421667,16,,,,
82597782,18:12:00,18:12:00,7421667,16,,,,
82597783,18:42:00,18:42:00,7421667,16,,,,
82597784,18:22:00,18:22:00,7421667,16,,,,
82597785,18:32:00,18:32:00,7421667,16,,,,
82597786,18:02:00,18:02:00,7421667,16,,,,
82597791,11:03:00,11:03:00,7421667,16,,,,
82597821,10:00:00,10:00:00,7421667,16,,,,
82597824,17:02:00,17:02:00,7421667,16,,,,
82597825,16:52:00,16:52:00,7421667,16,,,,
82598106,23:55:00,23:55:00,7421667,16,,,,
82598109,24:10:00,24:10:00,7421667,16,,,,
82598110,22:40:00,22:40:00,7421667,16,,,,
82598112,22:55:00,22:55:00,7421667,16,,,,
82598114,22:25:00,22:25:00,7421667,16,,,,
82598115,21:42:00,21:42:00,7421667,16,,,,
82598116,23:40:00,23:40:00,7421667,16,,,,
82598117,21:55:00,21:55:00,7421667,16,,,,
82598118,22:10:00,22:10:00,7421667,16,,,,
82598119,23:10:00,23:10:00,7421667,16,,,,
82598120,23:25:00,23:25:00,7421667,16,,,,
82598186,19:22:00,19:22:00,7421667,16,,,,
82598306,09:40:00,09:40:00,7421667,16,,,,
82598310,09:50:00,09:50:00,7421667,16,,,,
82598343,00:39:00,00:39:00,7421667,16,,,,
82598346,00:24:00,00:24:00,7421667,16,,,,
82598350,00:54:00,00:54:00,7421667,16,,,,
82598361,10:43:00,10:43:00,7421667,16,,,,
82598362,10:53:00,10:53:00,7421667,16,,,,
82598364,19:12:00,19:12:00,7421667,16,,,,
82598365,19:02:00,19:02:00,7421667,16,,,,
82598401,09:03:00,09:03:00,7421667,16,,,,
82598412,19:31:00,19:31:00,7421667,16,,,,
82598413,19:43:00,19:43:00,7421667,16,,,,
82598417,17:12:00,17:12:00,7421667,16,,,,
82598418,17:22:00,17:22:00,7421667,16,,,,
82598419,08:48:00,08:48:00,7421667,16,,,,
82598420,08:33:00,08:33:00,7421667,16,,,,
82598421,08:18:00,08:18:00,7421667,16,,,,
82598430,10:11:00,10:11:00,7421667,16,,,,
82598552,16:43:00,16:43:00,7421667,16,,,,
82598553,16:33:00,16:33:00,7421667,16,,,,
82598555,06:47:00,06:47:00,7421667,16,,,,
82598556,07:17:00,07:17:00,7421667,16,,,,
82598558,07:32:00,07:32:00,7421667,16,,,,
82598559,06:32:00,06:32:00,7421667,16,,,,
82598561,06:17:00,06:17:00,7421667,16,,,,
82598564,07:02:00,07:02:00,7421667,16,,,,
82598573,11:24:00,11:24:00,7421667,16,,,,
82598574,12:24:00,12:24:00,7421667,16,,,,
82598575,12:04:00,12:04:00,7421667,16,,,,
82598576,11:54:00,11:54:00,7421667,16,,,,
82598577,12:14:00,12:14:00,7421667,16,,,,
82598578,11:34:00,11:34:00,7421667,16,,,,
82598579,11:14:00,11:14:00,7421667,16,,,,
...