This GTFS-RT Routes Visualiser was designed for end users to input a url for a GTFS-RT feed (e.g., gtfs_rt_url from Translink) and a stable download url for the most recent corresponding GTFS file (e.g., corresponding_gtfs_url found by clicking on “Download Latest” at TransitFeeds or the download icon at Transitland). Alternatively, the end user can query TPRN’s GTFS Catalogue by a recent date and some coordinates within the bounding box to receive a file name (e.g., http://115.146.86.165:7062/gtfs_catalogue_query_json?day=22&month=October&year=2021&lat=153.0260&lon=-27.4705) and then append the received file name to the end of the GTFS Catalogue’s download url and input the resultant url as the corresponding_download_url for the GTFS Visualiser Service (e.g., http://115.146.86.165:7062/gtfs/nsw_trainlink_f-r6-nswtrainliyinterlinebus_20170803_20171101.zip). Note: this service was designed for pairing with GTFS-RT with the corresponding GTFS and so it will output data without receiving a corresponding pair of GTFS and GTFS-RT download urls.

Still to do:

  • convert to Shiny App

  • perhaps capture mouse clicks to filter out stops and routes

  • or create figure popups that can be downloaded

Data Models

Fig x. Primary Key Candidates from GTFS (blue) and GTFS-RT (green)

Fig x. Foreign Key Candidates from GTFS (blue) and GTFS-RT (green)

Fig x. The Final Data Model from GTFS (blue) and GTFS and GTFS-RT Tables Squashed for Spatial Attributes (purple)

The Visualization without updates and the Shiny GUI

Appendix A: Identifying Candidates Suitable for Primary Keys

Table x. Primary key candidates for agency
columns candidate why
agency_name TRUE
agency_url TRUE
agency_timezone TRUE
agency_lang TRUE
agency_phone TRUE
Table x. Primary key candidates for calendar
columns candidate why
service_id TRUE
monday FALSE has duplicate values: 0 (87), 1 (43)
tuesday FALSE has duplicate values: 0 (86), 1 (44)
wednesday FALSE has duplicate values: 0 (86), 1 (44)
thursday FALSE has duplicate values: 0 (83), 1 (47)
friday FALSE has duplicate values: 0 (84), 1 (46)
saturday FALSE has duplicate values: 0 (101), 1 (29)
sunday FALSE has duplicate values: 0 (97), 1 (33)
start_date FALSE has duplicate values: 20211026 (31), 20211030 (21), 20211031 (16), 20211101 (11), 20211029 (10), …
end_date FALSE has duplicate values: 20211130 (32), 20211127 (18), 20211128 (17), 20211028 (7), 20211029 (7), …
Table x. Primary key candidates for calendar_dates
columns candidate why
service_id FALSE has duplicate values: QR 21_22-QRMTP_WD-M-Tu-W-Th-01 (7), ATS 21_22-NG_SH-Weekend-01 (5), ATS 21_22-NG_SH-Weekend-01-0000010 (5)
date FALSE has duplicate values: 20211029 (11), 20211031 (2), 20211106 (2), 20211107 (2), 20211113 (2), 20211114 (2)
exception_type FALSE has duplicate values: 2 (24), 1 (6)
Table x. Primary key candidates for feed_info
columns candidate why
feed_publisher_name TRUE
feed_publisher_url TRUE
feed_lang TRUE
feed_start_date TRUE
feed_end_date TRUE
Table x. Primary key candidates for gtfs_rt
columns candidate why
trip_id TRUE
vehicle_id TRUE
route_id FALSE has duplicate values: 150-2063 (26), 100-2063 (17), 130-2063 (17), 66-2063 (16), 700-2034 (16), …
lat FALSE has duplicate values: -27.47048 (13), -27.44140 (5), -27.50975 (3), -27.49792 (3), -27.49286 (3), …
lon FALSE has duplicate values: 153.0248 (13), 153.0381 (6), 153.0176 (4), 153.0224 (4), 153.0236 (4), …
current_status FALSE has duplicate values: 2 (919), 1 (453)
timestamp FALSE has duplicate values: 2021-10-26 16:06:38 (76), 2021-10-26 16:06:43 (67), 2021-10-26 16:06:39 (60), 2021-10-26 16:06:29 (50), 2021-10-26 16:06:36 (48), …
updated_at FALSE has duplicate values: 16:06:38 (76), 16:06:43 (67), 16:06:39 (60), 16:06:29 (50), 16:06:36 (48), …
Table x. Primary key candidates for routes
columns candidate why
route_id TRUE
route_short_name FALSE has duplicate values: BDVL (16), BNBR (16), BNFG (16), BRBD (16), BRBN (16), …
route_long_name FALSE has duplicate values: Airport line - Varsity Lakes line (16), Beenleigh line - Brisbane City (16), Beenleigh line - Ferny Grove line (16), Brisbane City - Airport line (16), Brisbane City - Beenleigh line (16), …
route_desc FALSE has 1453 missing values
route_type FALSE has duplicate values: 2 (726), 3 (717), 4 (8), 0 (2)
route_url FALSE has 109 missing values, and duplicate values: https://jp.translink.com.au/travel-information/network-information/trains/T/ipswich-rosewood-line (140), https://jp.translink.com.au/travel-information/network-information/trains/T/caboolture-line (50), https://jp.translink.com.au/travel-information/network-information/trains/T/springfield-line (47), https://jp.translink.com.au/travel-information/network-information/trains/T/gold-coast-line (47), https://jp.translink.com.au/travel-information/network-information/trains/T/sunshine-coast-line (46), https://jp.translink.com.au/travel-information/network-information/trains/T/cleveland-line (NA)
route_color FALSE has duplicate values: 71BF5B (707), 00871C (271), 1578BE (105), FFC425 (104), 00467F (103), …
route_text_color FALSE has duplicate values: 000000 (808), FFFFFF (645)
Table x. Primary key candidates for shapes
columns candidate why
shape_id FALSE has duplicate values: GYBR0010 (1649), GYBR0250 (1649), GYBR0018 (1648), GYBR0236 (1648), BRGY0250 (1646), …
shape_pt_lat FALSE has duplicate values: -27.46603 (1031), -27.45539 (921), -27.45711 (920), -27.46399 (917), -27.46252 (916), …
shape_pt_lon FALSE has duplicate values: 153.0403 (1397), 153.0256 (1031), 153.0366 (958), 153.0243 (957), 153.0368 (956), …
shape_pt_sequence FALSE has duplicate values: 10001 (2792), 10002 (2792), 20001 (2779), 20002 (2779), 10003 (2778), …
Table x. Primary key candidates for stop_times
columns candidate why
trip_id FALSE has duplicate values: 18764953-LCBS 21_22-LCBS_SUN-Sunday-03 (94), 18764956-LCBS 21_22-LCBS_SUN-Sunday-03 (94), 18764957-LCBS 21_22-LCBS_SUN-Sunday-03 (94), 18764977-LCBS 21_22-LCBS_SUN-Sunday-03 (94), 18764997-LCBS 21_22-LCBS_SUN-Sunday-03 (94), …
arrival_time FALSE has 28151 missing values, and duplicate values: 16:30:00 (3695), 17:15:00 (3695), 17:20:00 (3689), 16:42:00 (3673), 17:30:00 (3653), 16:45:00 (NA)
departure_time FALSE has 28165 missing values, and duplicate values: 17:15:00 (3709), 16:30:00 (3685), 17:20:00 (3678), 17:30:00 (3673), 16:42:00 (3672), 16:45:00 (NA)
stop_id FALSE has duplicate values: 10802 (19198), 11168 (15440), 19064 (11820), 19063 (11651), 19052 (11500), …
stop_sequence FALSE has duplicate values: 1 (100815), 2 (100815), 3 (99846), 4 (99787), 5 (99248), …
pickup_type FALSE has duplicate values: 0 (2687668), 1 (110088)
drop_off_type FALSE has duplicate values: 0 (2785633), 1 (12123)
Table x. stops
columns candidate why
stop_id TRUE
stop_code TRUE
stop_name FALSE has duplicate values: Broadbeach North station (4), Broadwater Parklands station (4), Florida Gardens station (4), Main Beach station (4), Mount Cotton Rd near Killarney Cr (4), …
stop_desc FALSE has 12705 missing values
stop_lat FALSE has duplicate values: -27.49212 (3), -28.14148 (2), -28.10138 (2), -28.08521 (2), -28.07508 (2), …
stop_lon FALSE has duplicate values: 152.9800 (3), 152.9807 (3), 152.7773 (2), 152.8443 (2), 152.9168 (2), …
zone_id FALSE has duplicate values: 2 (4795), 3 (2549), 1 (2347), 5 (1224), 6 (764), …
stop_url FALSE has 44 missing values
location_type FALSE has duplicate values: 0 (12705)
parent_station FALSE has 12060 missing values, and duplicate values: place_QSBS (14), place_INTGCY (11), place_INTCAR (9), place_cabsta (9), place_INTCAN (8), place_INTCHE (NA)
platform_code FALSE has 12217 missing values, and duplicate values: 1 (188), 2 (50), 3 (27), 4 (5), 5 (3), 6 (NA)
Table x. trips
columns candidate why
trip_id TRUE
route_id FALSE has duplicate values: 60-2063 (1088), 700-2034 (1016), 199-2063 (955), 60-2143 (938), 412-2063 (887), …
service_id FALSE has duplicate values: BT 21_22-SEP_FUL-Weekday-01 (10173), BT 21_22-SEP_HUNG-Weekday-01 (10149), BT 21_22-NOV_HUNG-Weekday-01 (10144), BT 21_22-NOV_HUN-Weekday-01 (9826), BT 21_22-NOV_FUL-Saturday-01 (5570), …
trip_headsign FALSE has duplicate values: Queen Street station (6766), Garden City interchange (3341), Roma Street station (2691), Cultural Centre, Grey St (2679), West End Ferry (1912), …
direction_id FALSE has duplicate values: 0 (51173), 1 (49642)
block_id FALSE has 100815 missing values
shape_id FALSE has duplicate values: 600001 (933), 1990002 (879), 4120053 (812), 1990003 (801), 4120044 (785), …

Appendix B: Identifying Candidates Suitable for Foreign Keys

Table x. Foreign key candidates for trips to routes
columns candidate why
route_id TRUE
service_id FALSE values of trips$service_id not in routes$route_id: BT 21_22-SEP_FUL-Weekday-01 (10173), BT 21_22-SEP_HUNG-Weekday-01 (10149), BT 21_22-NOV_HUNG-Weekday-01 (10144), BT 21_22-NOV_HUN-Weekday-01 (9826), BT 21_22-NOV_FUL-Saturday-01 (5570), …
trip_id FALSE values of trips$trip_id not in routes$route_id: 17323011-BBL 21_22-BBL_WD-Weekday-01 (1), 17323012-BBL 21_22-BBL_WD-Weekday-01 (1), 17323013-BBL 21_22-BBL_WD-Weekday-01 (1), 17323014-BBL 21_22-BBL_WD-Weekday-01 (1), 17647126-QR 21_22-QRMTP_Sa-Saturday-00-1502 (1), …
trip_headsign FALSE values of trips$trip_headsign not in routes$route_id: Queen Street station (6766), Garden City interchange (3341), Roma Street station (2691), Cultural Centre, Grey St (2679), West End Ferry (1912), …
direction_id FALSE Can’t combine value1 <double> and value1 <character>.
block_id FALSE Can’t combine value1 <logical> and value1 <character>.
shape_id FALSE values of trips$shape_id not in routes$route_id: 600001 (933), 1990002 (879), 4120053 (812), 1990003 (801), 4120044 (785), …
Table x. Foreign key candidates for shapes to trips
columns candidate why
shape_id FALSE values of shapes$shape_id not in trips$trip_id: GYBR0010 (1649), GYBR0250 (1649), GYBR0018 (1648), GYBR0236 (1648), BRGY0250 (1646), …
shape_pt_lat FALSE Can’t combine value1 <double> and value1 <character>.
shape_pt_lon FALSE Can’t combine value1 <double> and value1 <character>.
shape_pt_sequence FALSE Can’t combine value1 <double> and value1 <character>.
Table x. Foreign key candidates for stop_times to stops
columns candidate why
stop_id TRUE
trip_id FALSE values of stop_times$trip_id not in stops$stop_id: 18764953-LCBS 21_22-LCBS_SUN-Sunday-03 (94), 18764956-LCBS 21_22-LCBS_SUN-Sunday-03 (94), 18764957-LCBS 21_22-LCBS_SUN-Sunday-03 (94), 18764977-LCBS 21_22-LCBS_SUN-Sunday-03 (94), 18764997-LCBS 21_22-LCBS_SUN-Sunday-03 (94), …
arrival_time FALSE Can’t combine value1 <time> and value1 <character>.
departure_time FALSE Can’t combine value1 <time> and value1 <character>.
stop_sequence FALSE Can’t combine value1 <double> and value1 <character>.
pickup_type FALSE Can’t combine value1 <double> and value1 <character>.
drop_off_type FALSE Can’t combine value1 <double> and value1 <character>.
Table x. Foreign key candidates for calendar_dates to calendar
columns candidate why
service_id TRUE
date FALSE Can’t combine value1 <double> and value1 <character>.
exception_type FALSE Can’t combine value1 <double> and value1 <character>.
Table x. Foreign key candidates for trips to calendar
columns candidate why
service_id TRUE
route_id FALSE values of trips$route_id not in calendar$service_id: 60-2063 (1088), 700-2034 (1016), 199-2063 (955), 60-2143 (938), 412-2063 (887), …
trip_id FALSE values of trips$trip_id not in calendar$service_id: 17323011-BBL 21_22-BBL_WD-Weekday-01 (1), 17323012-BBL 21_22-BBL_WD-Weekday-01 (1), 17323013-BBL 21_22-BBL_WD-Weekday-01 (1), 17323014-BBL 21_22-BBL_WD-Weekday-01 (1), 17647126-QR 21_22-QRMTP_Sa-Saturday-00-1502 (1), …
trip_headsign FALSE values of trips$trip_headsign not in calendar$service_id: Queen Street station (6766), Garden City interchange (3341), Roma Street station (2691), Cultural Centre, Grey St (2679), West End Ferry (1912), …
direction_id FALSE Can’t combine value1 <double> and value1 <character>.
block_id FALSE Can’t combine value1 <logical> and value1 <character>.
shape_id FALSE values of trips$shape_id not in calendar$service_id: 600001 (933), 1990002 (879), 4120053 (812), 1990003 (801), 4120044 (785), …
Table x. Foreign key candidates for stop_times to calendar
columns candidate why
trip_id TRUE
arrival_time FALSE Can’t combine value1 <time> and value1 <character>.
departure_time FALSE Can’t combine value1 <time> and value1 <character>.
stop_id FALSE values of stop_times$stop_id not in trips$trip_id: 10802 (19198), 11168 (15440), 19064 (11820), 19063 (11651), 19052 (11500), …
stop_sequence FALSE Can’t combine value1 <double> and value1 <character>.
pickup_type FALSE Can’t combine value1 <double> and value1 <character>.
drop_off_type FALSE Can’t combine value1 <double> and value1 <character>.
Table x. Foreign key candidates for trips to calendar
columns candidate why
route_id FALSE values of trips$route_id not in gtfs_rt$vehicle_id: 60-2063 (1088), 700-2034 (1016), 199-2063 (955), 60-2143 (938), 412-2063 (887), …
service_id FALSE values of trips$service_id not in gtfs_rt$vehicle_id: BT 21_22-SEP_FUL-Weekday-01 (10173), BT 21_22-SEP_HUNG-Weekday-01 (10149), BT 21_22-NOV_HUNG-Weekday-01 (10144), BT 21_22-NOV_HUN-Weekday-01 (9826), BT 21_22-NOV_FUL-Saturday-01 (5570), …
trip_id FALSE values of trips$trip_id not in gtfs_rt$vehicle_id: 17323011-BBL 21_22-BBL_WD-Weekday-01 (1), 17323012-BBL 21_22-BBL_WD-Weekday-01 (1), 17323013-BBL 21_22-BBL_WD-Weekday-01 (1), 17323014-BBL 21_22-BBL_WD-Weekday-01 (1), 17647126-QR 21_22-QRMTP_Sa-Saturday-00-1502 (1), …
trip_headsign FALSE values of trips$trip_headsign not in gtfs_rt$vehicle_id: Queen Street station (6766), Garden City interchange (3341), Roma Street station (2691), Cultural Centre, Grey St (2679), West End Ferry (1912), …
direction_id FALSE Can’t combine value1 <double> and value1 <character>.
block_id FALSE Can’t combine value1 <logical> and value1 <character>.
shape_id FALSE values of trips$shape_id not in gtfs_rt$vehicle_id: 600001 (933), 1990002 (879), 4120053 (812), 1990003 (801), 4120044 (785), …