Generate Stop Arrival Times

[radumas]

PostgreSQL function that gets called every scraper run.
Following the gtfs spec

 {trip_id, arrival_time, departure_time, stop_id, stop_sequence}

Note: The times would actually be timestamps.

Which brings up:

• check the current TTC gtfs schema for trip_ids, stop_ids

[radumas]

Or we could use the form developed by my friend of use date + interval for the timestamps.

[radumas]

First draft

SELECT DISTINCT ON (pollid, lineid, trainid, traindirection, stationid) lineid, create_date, traindirection, trainid, stationid, timint, train_message
FROM ntas_data 
INNER JOIN requests USING (requestid)
INNER JOIN polls USING (pollid)
WHERE train_message = 'AtStation' OR timint < 1
ORDER BY pollid, lineid, trainid, traindirection, stationid, create_date

Which leads to some problems with delayed trains

'lineid'	'create_date'	'traindirection'	'trainid'	'stationid'	'timint'	'train_message'
1	'2017-03-07 20:27:13'	'North'	102	4	0.0	'AtStation'
1	'2017-03-07 20:27:13'	'North'	102	13	0.0	'Delayed'
1	'2017-03-07 20:27:13'	'South'	102	11	0.0	'AtStation'
1	'2017-03-07 20:27:13'	'South'	102	18	0.21618222222222222	'Arriving'

[radumas]

The arrival time inference algorithm I built in SQL has some issues. The below graph shows the difference in the number of trips having different lengths (number of stops) for inferred vs scheduled (gtfs).

Digging into where these problems might Interesting finding of where my SQL query creates a lot of short (2,3 station) trips

[radumas]

@moh-osman is also working on this for headway analysis https://github.com/moh-osman/ttc_subway_times/blob/master/doc/ttc_headway_analysis.ipynb

[radumas]

Turns out those station_char for the "3 stop trips" are actually for Line 2, not Line 1... so there may be an issue of cross-pollination in the API...

[radumas]

I managed to take a solid chunk out of the really short trips, but have introduced.... trips that are somehow longer than scheduled.

[radumas]

If grouping by station (getting rid of the platform number at the end), that helps.