Database tables schema

data_sample	represents arrival, departure at a station, time as part of a trip
id	automatic ID by DB
index	the index of the stop in the trip [1]
gtfs_stop_id	the station GTFS id
stop_id	the station id, a foreign key to the data_stops table
valid	data sanity check
invalid_reason	description of invalid reason, if invalid
is_source	whether it the first passengers stop (there may be non-passenger stops before)
is_dest	whether it the last passengers stop
actual_arrival	time of arrival
actual_arrival_fixed	is field actual_arrival missing, if so - used exp_arrival
exp_arrival	the planned time
delay_arrival	the delta of actual_arrival – exp_arrival
actual_departure	time of departure
actual_departure_fixed	is field actual_departure missing, if so - used exp_departure
exp_departure	planned departure
delay_departure	the delta of actual_departure – exp_departure
filename	source of data (for debugging purposes)
line_number	the line in that file (for debugging purposes)
sheet_idx	the sheet in that file (for debugging purposes)
trip_id	the id of the trip <train, date> (train = route id)

[1]	Note that there are gaps in the indexes since the original indexing includes operational stops.

data_trip	collection of samples representing a unique trip from source to destination
id	the trip id, a non generated primary key (timestamp & train nr)
train_num	train num as given by the train
date	date of trip
valid	data sanity check
invalid_reason	description of invalid reason, if invalid
x_week_day_local	day of week (0 to 6) (first sample in the trip)
x_hour_local	expected hour of departure (first sample in the trip)
route_id	foreign key to the route table
x_avg_delay_arrival	average delay over all samples in the trip
x_cache_version	cache version being used for this table, used for table updates
x_max2_delay_arrival	second largest delay among the route’s samples
x_max_delay_arrival	largest delay among the route’s samples
x_before_last_delay_arrival	delay at route’s second to last sample
x_last_delay_arrival	delay at route’s last sample

[2]	Service is a collection of trips of the same route and same hours. E.g. all trains from Beer Sheva to Nahariya at 8 am.

data_route	list of stops for a repeating route
id	automatic ID by DB
stop_ids	json list of stop ids

data_stop	static info about stops in the rail network
id	automatic ID by DB
gtfs_stop_id	gtfs (General Transit Feed Specification) station id
english	english name
hebrews	hebrew names (json list)
lat	latitude
lon	longitude