User:EdLoach/BusRoutesOpendataComparison
Data below downloaded on 24th April from the TNDS ftp site (files timestamped 23rd April) for the TransXChange (TXC) format data and from https://data.bus-data.dft.gov.uk/timetable/download/gtfs-file/all/ for the GTFS format data. Both sets of data imported into Microsoft SQL Server and restricted to routes that have at least one route variant that stop at at least one stop in the Tendring district of Essex (based on OSM overpass query). Naptan data also imported to SQL database.
The TXC analysis tries to limit route variants to those that are running at least once within the next 30 days; the GTFS data currently only checks that there is at least one regular day (uses calendar but not calendar_dates).
Differences highlighted in bold.
| TXC | GTFS | |||||||
| OperatorCode | ShortName | LineName | RouteVariants | agency_noc | agency_name | route_short_name | RouteVariants | |
| ARHE | Arriva (in Herts and Ess | 77 | 8 | ARHE | Arriva Herts and Essex | 77 | 8 | |
| ARHE | Arriva (in Herts and Ess | 77A | 2 | ARHE | Arriva Herts and Essex | 77A | 2 | |
| CHBL | Carters Heritage Bus | 193 | 2 | CHBL | Carters Heritage Buses | 193 | 2 | |
| FESX | First Essex | 102 | 5 | FESX | First Essex | 102 | 5 | |
| FESX | First Essex | 103 | 4 | FESX | First Essex | 103 | 4 | |
| FESX | First Essex | 104 | 4 | FESX | First Essex | 104 | 4 | |
| FESX | First Essex | 62 | 7 | FESX | First Essex | 62 | 7 | |
| FESX | First Essex | 74B | 2 | FESX | First Essex | 74B | 2 | |
| FESX | First Essex | 76 | 2 | |||||
| HEDO | Hedingham | 134 | 5 | HEDO | Hedingham | 134 | 3 | |
| HEDO | Hedingham | 135 | 3 | HEDO | Hedingham | 135 | 4 | |
| HEDO | Hedingham | 136 | 7 | HEDO | Hedingham | 136 | 15 | |
| HEDO | Hedingham | 137 | 8 | HEDO | Hedingham | 137 | 13 | |
| HEDO | Hedingham | 3 | 4 | HEDO | Hedingham | 3 | 4 | |
| HEDO | Hedingham | 4 | 7 | HEDO | Hedingham | 4 | 5 | |
| HEDO | Hedingham | 5 | 3 | HEDO | Hedingham | 5 | 6 | |
| HEDO | Hedingham | 5A | 1 | HEDO | Hedingham | 5A | 2 | |
| HEDO | Hedingham | 6 | 2 | HEDO | Hedingham | 6 | 2 | |
| HEDO | Hedingham | 6A | 2 | HEDO | Hedingham | 6A | 2 | |
| HEDO | Hedingham | 6B | 2 | HEDO | Hedingham | 6B | 2 | |
| HEDO | Hedingham | 7 | 9 | HEDO | Hedingham | 7 | 6 | |
| HEDO | Hedingham | 74 | 4 | HEDO | Hedingham | 74 | 4 | |
| HEDO | Hedingham | 76 | 1 | HEDO | Hedingham | 76 | 3 | |
| HEDO | Hedingham | 97 | 3 | HEDO | Hedingham | 97 | 1 | |
| HEDO | Hedingham | 97A | 1 | HEDO | Hedingham | 97A | 2 | |
| HEDO | Hedingham | 98 | 3 | HEDO | Hedingham | 98 | 6 | |
| HEDO | Hedingham | 98A | 3 | HEDO | Hedingham | 98A | 6 | |
| HEDO | Hedingham | 99 | 2 | HEDO | Hedingham | 99 | 2 | |
| HEDO | Hedingham | X76 | 13 | HEDO | Hedingham | X76 | 15 | |
| HRWC | Harwich Connexions Trans | HH1 | 2 | |||||
| IPSW | Ipswich Buses | 2 | 2 | IPSW | Ipswich Buses | SX2 | 2 | |
| IPSW | Ipswich Buses | 2A | 2 | IPSW | Ipswich Buses | SX2a | 2 | |
| IPSW | Ipswich Buses | 92 | 6 | IPSW | Ipswich Buses | 92 | 5 | |
| IPSW | Ipswich Buses | 93 | 6 | IPSW | Ipswich Buses | 93 | 2 | |
| IPSW | Ipswich Buses | 93A | 2 | IPSW | Ipswich Buses | 93a | 1 | |
| IPSW | Ipswich Buses | X93 | 1 | IPSW | Ipswich Buses | X93 | 1 | |
| NHZN | New Horizon Travel | 78 | 2 | |||||
| PNTR | Panther Travel | 118 | 2 | PNTR | Panther Travel | 118 | 2 | |
| PNTR | Panther Travel | 18 | 4 | PNTR | Panther Travel | 18 | 4 | |
| PNTR | Panther Travel | 2 | 3 | PNTR | Panther Travel | 2 | 3 | |
| PNTR | Panther Travel | 81 | 3 | PNTR | Panther Travel | 81 | 1 | |
| PNTR | Panther Travel | 81A | 8 | PNTR | Panther Travel | 81A | 3 | |
| SESX | Stephensons of Essex | 101 | 2 | SESX | Stephensons of Essex | 101 | 2 | |
| SESX | Stephensons of Essex | 105 | 6 | SESX | Stephensons of Essex | 105 | 6 | |
| SESX | Stephensons of Essex | 107 | 3 | SESX | Stephensons of Essex | 107 | 3 | |
| SESX | Stephensons of Essex | 115 | 2 | SESX | Stephensons of Essex | 115 | 2 | |
| SESX | Stephensons of Essex | 702 | 2 | SESX | Stephensons of Essex | 702 | 2 | |
| SESX | Stephensons of Essex | 9 | 4 | SESX | Stephensons of Essex | 9 | 2 | |
| TEND | Tendring Community Trans | Colchester Hospital Hopper | 2 |
Checking the Arriva 77 route variants, they don't seem to match either, so more checking to do.
GTFS 150020315/platform_entry_only;150020317/platform;150020318/platform;150020313/platform;150033096004/platform;150033096002/platform;1500IM1359/platform;1500IM273/platform;15003303800C/platform;1500DGK221/platform;150033042010/platform;150033042008/platform;150033042005/platform;150033042004/platform;1500IM139/platform;150033032011/platform;150033032001/platform;150033031001/platform;1500IM1317/platform;150032001002/platform;150032001003/platform;150032001004/platform;1500IM1127/platform;150032001006/platform;150032001007/platform;150032001008/platform;150032001009/platform;1500IM1318C/platform;150032003004/platform;1500IM108AA/platform;1500320030Y3/platform;1500IM1420/platform;1500DGK243Y/platform;150032005Y4/platform;150032005007/platform;1500IM21Y/platform;1500IM13231/platform_exit_only 150020315/platform_entry_only;150020317/platform;150020318/platform;150020313/platform;150033096004/platform;150033096002/platform;1500IM1359/platform;1500IM273/platform;15003303800C/platform;1500DGK221/platform;150033042010/platform;150033042008/platform;150033042005/platform;150033042004/platform;1500IM139/platform;150033032011/platform;150033032001/platform;150033031001/platform;1500IM1317/platform;150032001002/platform;150032001003/platform;150032001004/platform;1500IM1127/platform;150032001006/platform;150032001007/platform;150032001008/platform;150032001009/platform;1500IM1318C/platform;150032003004/platform;1500IM108AA/platform;1500320030Y3/platform;1500IM1420/platform_exit_only 150033039009/platform_entry_only;1500IM273/platform;15003303800C/platform;1500DGK221/platform;150033042010/platform;150033042008/platform;150033042005/platform;150033042004/platform;1500IM139/platform;150033032011/platform;150033032001/platform;150033031001/platform;1500IM1317/platform;150032001002/platform;150032001003/platform;150032001004/platform;1500IM1127/platform;150032001006/platform;150032001007/platform;150032001008/platform;150032001009/platform;1500IM1318C/platform;150032003004/platform;1500IM108AA/platform;1500320030Y3/platform;1500IM1420/platform_exit_only 1500CRSTMARY/platform;150033087011/platform;150033087013/platform;150033087015/platform;150033039009/platform;1500IM273/platform;15003303800C/platform;1500DGK221/platform;150033042010/platform;150033042008/platform;150033042005/platform;150033042004/platform;1500IM139/platform;150033032011/platform;150033032001/platform;150033031001/platform;1500IM1317/platform;150032001002/platform;150032001003/platform;150032001004/platform;1500IM1127/platform;150032001006/platform;150032001007/platform;150032001008/platform;150032001009/platform;1500IM1318C/platform;150032003004/platform;1500IM108AA/platform;1500320030Y3/platform;1500IM1420/platform_exit_only 1500IM13231/platform_entry_only;150032005009/platform;1500IM21/platform;150032005006/platform;150032005964/platform;1500DGK242Y/platform;1500IM1420AA/platform;150032003003/platform;1500IM108/platform;1500320030Y4/platform;1500IM1318B/platform;150032001012/platform;1500FRATNGHL/platform;150032001014/platform;150032001015/platform;1500IM1127B/platform;150032001017/platform;150032001018/platform;150032001019/platform;1500IM1317AA/platform;150033031003/platform;1500IM2534/platform;150033032Y11/platform;150033042003/platform;150033042006/platform;150033042007/platform;150033042009/platform;150033042011/platform;1500TEMP0011/platform;15003303800D/platform;1500ADD007A/platform;150033039009/platform;150033039004/platform;150033095001/platform;150033096001/platform;150033096003/platform;150020314/platform;150020315/platform_exit_only 1500IM1420/platform_entry_only;1500IM108/platform;1500320030Y4/platform;1500IM1318B/platform;150032001012/platform;1500FRATNGHL/platform;150032001014/platform;150032001015/platform;1500IM1127B/platform;150032001017/platform;150032001018/platform;150032001019/platform;1500IM1317AA/platform;150033031003/platform;1500IM2534/platform;150033032Y11/platform;150033042003/platform;150033042006/platform;150033042007/platform;150033042009/platform;150033042011/platform;1500TEMP0011/platform;15003303800D/platform;1500ADD007A/platform;150033039009/platform;150033039004/platform;150033095001/platform;150033096001/platform;150033096003/platform;150020314/platform;150020315/platform_exit_only 1500IM1420/platform_entry_only;1500IM108/platform;1500320030Y4/platform;1500IM1318B/platform;150032001012/platform;1500FRATNGHL/platform;150032001014/platform;150032001015/platform;1500IM1127B/platform;150032001017/platform;150032001018/platform;150032001019/platform;1500IM1317AA/platform;150033031003/platform;1500IM2534/platform;150033032Y11/platform;150033042003/platform;150033042006/platform;150033042007/platform;150033042009/platform;150033042011/platform;1500TEMP0011/platform;15003303800D/platform;1500ADD007A/platform;1500CRCTYHSP/platform;150033087012/platform;150033087010/platform;1500CRLEXNWY/platform 1500IM1420/platform_entry_only;1500IM108/platform;1500320030Y4/platform;1500IM1318B/platform;150032001012/platform;1500FRATNGHL/platform;150032001014/platform;150032001015/platform;1500IM1127B/platform;150032001017/platform;150032001018/platform;150032001019/platform;1500IM1317AA/platform;150033031003/platform;1500IM2534/platform;150033032Y11/platform;150033042003/platform;150033042006/platform;150033042007/platform;150033042009/platform;150033042011/platform;1500TEMP0011/platform;15003303800D/platform_exit_only
TXC 150020315/platform_entry_only;150020317/platform;150020318/platform;150020313/platform;150033096004/platform;150033096002/platform;1500IM1359/platform;150033039001/platform;1500AA7/platform;150033039006/platform;150033039004/platform;150033041002/platform_exit_only 150020315/platform_entry_only;150020317/platform;150020318/platform;150020313/platform;150033096004/platform;150033096002/platform;1500IM1359/platform;150033039001/platform;1500AA7/platform;150033039006/platform;1500IM273/platform;15003303800C/platform;1500DGK221/platform;150033042010/platform;150033042008/platform;150033042005/platform;150033042004/platform;1500IM139/platform;150033032011/platform;150033032001/platform;150033031001/platform;1500IM1317/platform;150032001002/platform;150032001003/platform;150032001004/platform;1500IM1127/platform;150032001006/platform;150032001007/platform;150032001008/platform;150032001009/platform;1500IM1318C/platform;150032003004/platform;1500IM108AA/platform;1500320030Y3/platform;150023003994/platform;150032003993/platform;1500IM1420/platform_exit_only 150020315/platform_entry_only;150020317/platform;150020318/platform;150020313/platform;150033096004/platform;150033096002/platform;1500IM1359/platform;150033039001/platform;1500AA7/platform;150033039006/platform;1500IM273/platform;15003303800C/platform;1500DGK221/platform;150033042010/platform;150033042008/platform;150033042005/platform;150033042004/platform;1500IM139/platform;150033032011/platform;150033032001/platform;150033031001/platform;1500IM1317/platform;150032001002/platform;150032001003/platform;150032001004/platform;1500IM1127/platform;150032001006/platform;150032001007/platform;150032001008/platform;150032001009/platform;1500IM1318C/platform;150032003004/platform;1500IM108AA/platform;1500IM1420AA/platform;1500320039Y9/platform;1500320039Y4/platform;150032005Y4/platform;150032005007/platform;1500IM21Y/platform;150032005010/platform;1500IM13230/platform;1500IM13231/platform_exit_only 150033039009/platform_entry_only;1500CRMDBORO/platform;1500AA20/platform;150033039010/platform;150033095001/platform;150033096001/platform;150033096003/platform;150020314/platform;150020315/platform_exit_only 1500IM1128/platform_entry_only;1500CRSTMARY/platform;150033087011/platform;150033087013/platform;150033087015/platform;150033039007/platform;1500IM273/platform;15003303800C/platform;1500DGK221/platform;150033042010/platform;150033042008/platform;150033042005/platform;150033042004/platform;1500IM139/platform;150033032011/platform;150033032001/platform;150033031001/platform;1500IM1317/platform;150032001002/platform;150032001003/platform;150032001004/platform;1500IM1127/platform;150032001006/platform;150032001007/platform;150032001008/platform;150032001009/platform;1500IM1318C/platform;150032003004/platform;1500IM108AA/platform;1500320030Y3/platform;150023003994/platform;150032003993/platform;1500IM1420/platform_exit_only 1500IM13231/platform_entry_only;150032005009/platform;1500IM21/platform;150032005006/platform;150032005964/platform;150023003994/platform;150032003993/platform;1500IM1420/platform;150032003946/platform;1500IM108/platform;1500320030Y4/platform;1500IM1318B/platform;150032001012/platform;1500FRATNGHL/platform;150032001014/platform;150032001015/platform;1500IM1127B/platform;150032001017/platform;150032001018/platform;150032001019/platform;1500IM1317AA/platform;150033031003/platform;1500IM2534/platform;150033032Y11/platform;150033042002/platform;150033042003/platform;150033042006/platform;150033042007/platform;150033042009/platform;150033042011/platform;15003303800D/platform;1500ADD007A/platform;150033039009/platform;1500CRMDBORO/platform;1500AA20/platform;150033039010/platform;150033095001/platform;150033096001/platform;150033096003/platform;150020314/platform;150020315/platform_exit_only 1500IM1420/platform_entry_only;150032003946/platform;1500IM108/platform;1500320030Y4/platform;1500IM1318B/platform;150032001012/platform;1500FRATNGHL/platform;150032001014/platform;150032001015/platform;1500IM1127B/platform;150032001017/platform;150032001018/platform;150032001019/platform;1500IM1317AA/platform;150033031003/platform;1500IM2534/platform;150033032Y11/platform;150033042002/platform;150033042003/platform;150033042006/platform;150033042007/platform;150033042009/platform;150033042011/platform;15003303800D/platform;150033041002/platform;1500CRCTYHSP/platform;150033087012/platform;150033087010/platform;1500CRLEXNWY/platform;1500IM1128/platform_exit_only 1500IM1420/platform_entry_only;150032003946/platform;1500IM108/platform;1500320030Y4/platform;1500IM1318B/platform;150032001012/platform;1500FRATNGHL/platform;150032001014/platform;150032001015/platform;1500IM1127B/platform;150032001017/platform;150032001018/platform;150032001019/platform;1500IM1317AA/platform;150033031003/platform;1500IM2534/platform;150033032Y11/platform;150033042002/platform;150033042003/platform;150033042006/platform;150033042007/platform;150033042009/platform;150033042011/platform;15003303800D/platform;1500ADD007A/platform;150033039009/platform;1500CRMDBORO/platform;1500AA20/platform;150033039010/platform;150033095001/platform;150033096001/platform;150033096003/platform;150020314/platform;150020315/platform_exit_only
Comparing just one similar route...
GTFS has 1500CRSTMARY/platform;150033087011/platform;150033087013/platform;150033087015/platform;150033039009/platform;1500IM273/platform;15003303800C/platform;1500DGK221/platform;150033042010/platform;150033042008/platform;150033042005/platform;150033042004/platform;1500IM139/platform;150033032011/platform;150033032001/platform;150033031001/platform;1500IM1317/platform;150032001002/platform;150032001003/platform;150032001004/platform;1500IM1127/platform;150032001006/platform;150032001007/platform;150032001008/platform;150032001009/platform;1500IM1318C/platform;150032003004/platform;1500IM108AA/platform;1500320030Y3/platform;1500IM1420/platform_exit_only
TXC has 1500IM1128/platform_entry_only;1500CRSTMARY/platform;150033087011/platform;150033087013/platform;150033087015/platform;150033039007/platform;1500IM273/platform;15003303800C/platform;1500DGK221/platform;150033042010/platform;150033042008/platform;150033042005/platform;150033042004/platform;1500IM139/platform;150033032011/platform;150033032001/platform;150033031001/platform;1500IM1317/platform;150032001002/platform;150032001003/platform;150032001004/platform;1500IM1127/platform;150032001006/platform;150032001007/platform;150032001008/platform;150032001009/platform;1500IM1318C/platform;150032003004/platform;1500IM108AA/platform;1500320030Y3/platform;150023003994/platform;150032003993/platform;1500IM1420/platform_exit_only
Checking GTFS (as I imported it) it has no stop 0 for trip_id VJ37400d9da7eebb435efa0f2534be75e9c288b9e9 and the TXC data has the equivalent journey as starting at Norman Way Schools 1500IM1128 and the published timetable, which doesn't include any stop references, has as starting at Colchester County High School across the road. The TXC data also includes two extra stops in Aingers Green, 150023003994 and 150032003993 which also aren't on the published timetable. In both cases the routes in the opendata finish at Aingers Green Plough Road 1500IM1420, rather than Aingers Green Road 1500320030Y3 on the published timetable. So three extra stops for TXC and one extra for GTFS.
The published PDF timetable runs 11 April to 22 July and lists some route variants school days only and others school holidays only. There appear to be 10 route variants from a visual check, but the May half term is over 30 days away so my analysis may be excluding some of the school holiday variations if they aren't also used during term time.
Checking further, the TXC data has three routes starting at 150020315 as opposed to two in the GTFS data and GTFS has three starting at 1500IM1420 as opposed to two in the TXC data. Between them this makes nine of the ten route variants, but the TXC variants often include bus stops that the route variant passes which weren't included in the PDF timetable or GTFS data, such as 150033039001, The Albert, in the three route variants starting at 150020315.
Checking the TXC route starting at 150020315 not in GTFS, this is closest to the Apprentice Drive to Railway Station Layby route variant on the PDF timetable but has more stops after that:
- 150020315/platform_entry_only; Colchester Apprentice Drive
- 150020317/platform; Colchester Turbine Road
- 150020318/platform; Colchester Axial Drive
- 150020313/platform; Colchester Aerofoil Grove
- 150033096004/platform; Colchester Tufnell Way
- 150033096002/platform; Colchester Edith Way
- 1500IM1359/platform; Colchester Railway Station Layby
- 150033039001/platform; Colchester The Albert
- 1500AA7/platform; Colchester Causton Road
- 150033039006/platform; Colchester Middleborough
- 150033039004/platform; Colchester Middleborough
- 150033041002/platform_exit_only Colchester Crouch Street
Checking the TXC data, I delete route variants that I calculate have no route journeys in the next 30 days and in the case of the AHRE 77 this excluded journey patterns R5 and R6 which are both services which are school holidays only, as I thought may be the case in earlier notes (above). I am not deleting anything from the GTFS data, but there is a chance something is being filtered out by views based on the calendar or calendar_dates table. Ah! My GTFS views are filtering out route variants which don't intersect the Tendring district, rather than including all route variants where at least one intersects Tendring. Time to change my views, but this will make them slower...