General Approach
Why do we use a Google Spreadsheet?
Google Spreadsheets have some basic version control features, allowing to revert changes, if errors were made. Also they allow different editing rights for each table, allowing to let multiple editors just edit “their” data in one sheet without granting full editing rights to the whole spreadsheet. Then, it’s quite close to other spreadsheet tools, so it does not require to learn some markup language and, finally, it’s a free and widely used tool – so there should be many API for diverse use cases of the data be available, making it unnecessary to permanently maintain an individual API.
Why and how do we use the GTFS namespace?
We ackknowledge that NetEX is bound to be the official European standard for traffic data. However, NetEx is very elaborated (aiming to cover all means of transport) there is no well maintained resource introducing in how to use the standard. While GTFS has no official approval in Europe it is a worldwide de-facto standard, so we assume it might be easier to obtain train data as GTFS files rather than the NetEX format. Documentation being a one-pager has the disadvantage of allowing different interpretations but the advantage of quickly implementing the standard.
What did we change?
GTFS was originally developed for a local public transport. This is why its terminology rather fits to buses than nigh trains (e.g. using “stop” for stations or “headsign” for the destination). We nevertheless use this terminology as-is to stay compatible (just in case we get data automatically delivered as GTFS files in the future). For this reason we also needed to add a lot of additional data colums, not foreseen in GTFS – like “having a dining car”, for example, is not a typical quality category of Buses in public transport, so it was not foreseen. We highlighted all data fields beyond the GTFS standard in blue.
GTFS also has problems to accomodate peculiarities of night trains like X- or Y-shaped lines or through coaches. We thus defined that each endpoint to endpoint connection with a couchette or a sleeping car qualifies as a route, each route being typically (but not necessarily) a pair of trips, each direction being one trip. This means a Y-shaped concept are two routes and an X-shaped train run being four routes. Also a through coach would be considered as a new route. This made it tricky as through coaches do not necessarily have a train number, which is however needed as an identifier.
The data you would typically like to export would be trains (trips) e.g. for travel planning or Routes (Lines) e.g. for drawing a map. We try to make sure both views have all the relevant data. However, as you would not like to maintain every information at two places we made sure that all info that typically applies for both directions are maintained in routes and all information that could differ for each direction are maintained in trips. A lot of data is cross-referenced, meaning it automatically appears in the other sheet. These fields have a light grey background. and a dark grey font colour.
Meaning of Colours:
- Black font: Data foreseen in GTFS
- Blue font: Additional ONTD-data, not foreseen in GTFS.
- Grey background: Auto generated data: Copy configuration for these the lines from fields above or below, but don’t enter any data here (unless you need override an auto-generated value)
General Maintenance Rules:
- List should be always be sorted according to their ID. When sorting items, create a new line(s) where the item(s) belong(s), then cut and paste, then delete the empty line(s). Otherwise references inbetween the tables may be broken
agencies (Operators)
Needed for: There should be no need to edit these.
stops (Stations)
Needed for: Adding not yet existing stops (e.g. when adding an itinerary)
We differentiate between Station (stop_name) and the City/Metropolitan area (stop_cityname e.g. for the map) .
Has a city multiple stations there are different forms in Europe how to add their specific name wo the city name. In oder to apply a standard we use dashes (“-“) only, when they are part of the city’s name (e.g. Clermont-Ferrand) but not to link station and city name. So it is “Paris Austerlitz” like “Praha hl.n.”, “Budapest Keleti” and “Bruxelles Midi / Brussel Zuid”.
We need Timezone (stop_timezone), Latitude (stop_lat) and Longitude (stop_lon) Position (stop_lat) as in the GTFS standard these are required. As a reference use maps.google.com, search for “[stop_name] station” and right-click the pin at the station. The numbers are the lat/long values and can be copied into the table (separating the values and removing the comma). Timezones are GMT (IE, PT, UK), EET (BG, EE, FI, GR, LT, LV, RO, all of UA), Europe/Istanbul (TR), the rest is CET.
stop_name_romanized / stop_cityname_romanized:
We write the station names in itineraries as they would appear on the station signs, unless they are written in Greek or Cyrillic, then we add a Version in latin letters using the English transcription, for a reference look for the place name in the English-language Wikipedia.
stop_name_alt / stop_cityname_alt:
This is a bit complicated as this is highly political and there is no common European language policy. So, to avoid inconsistencies, we needed a working policy four ourselves, which is for the moment (feel free to challenge it):
- We support the diversity of languages in Europe, this is why we add the place name in minority languages (e.g. Basque, Welsh, Sámi), at least as long as this language is alive and spoken by a significant number of people in the area (e.g. > 5% of population / > 1000 speakers), regardless if it is official policy to acknowledge these or not.
- We don’t support irredentism, so this is about making minority languages visible, not about making minorities (or majorities) visible, who speak a different language (which is a majority language elsewhere). As an exception to this rule we may acknowledge other ethnies in traditional multi-ethnic areas (e.g. Transsylvania or Brussels) only if it is also official policy. The alternative name is in this case the language which is spoken to a lesser degree (so it is Bruxelles/Brussel and Innichen/San Candido) regardless if this is the official policy (in order to apply a standard).
- We don’t support regionalism (beyond the application of the subsidiarity principle) by giving local dialects (e.g. Skånska, Ligurian) a language status unless this is official policy (e.g. in Luxembourg) and linguistically accepted.
routes (Lines)
Needed for: Adding new lines in the network (e.g. for the night train map), updating line-specific Informations such as distance, service categories picture or operator.
This table contains all the information that is typically attributed to a line, being valid for both directions, and in case multiple trains use the same itinerary, for all trains on this route.
When adding X- or Y-shaped lines to our database each link between endpoints constitutes a line in its own right (in order to make all of these clickable on the map. However on the map, we would use the same colour for all of these, unless they must be booked using different platforms like Berlin = Wien / Budapest). When adding temporary deviations of the route we would add a version for the applicable timeframe (so we can display a different intinerary on the map, when applicable).
Please note: Some train-specific informations from the trips table are also displayed in the routes table as for some applications this makes is easier to import the data.
Operators: Some night trains are run as a cooperation. We may therefore select multiple partners form a list, ranking partners according to their share of the route. To keep things simple, we mention multiple partners only, when they are visible, meaning partners each contribute their branded night train rolling stock. Cooperation partners only hauling, staffing or adding seater cars to the train don’t need to be mentioned.
trips (Trains)
Needed for: Displaying single night trains services (one direction) and for assigning any information which might apply to only one direction.
trip_stop (Itinerary)
Needed for: Updating train-specific Informations such as train number and service days
Train Numbers: Most international trains have multiple numbers, if it has an international EN number we only use this one. Otherwise we use the number displayed at departure. Train numbers must be unique. In some countries (i.e. NO) both directions use the same train number. In this case we add (an abbreviation of) the destination (headsign) in brackets behind the number (separated with a space). In some countries (i.e. UK) trains numbers are not displayed in the national directory. However, they have one.
GTFS requires to identify the direction with „0“ and „1“. We use “0“ for the train with the alphanumerically lower number. In the case of international trains using multiple number systems, prioritising directions differently, we rank according to the number system of the first named operator.
In calendar_id we allocate a schedule from the calendar table. If the needed schedule is missing, please add a new one in calendar.
calendar (Schedule)
Needed for: Displaying when the trains are running. In order to better manage timetable shifts in December we do now assign schemes to a year (beginning with the timetable in December of the previous year).
A schedule is typically a combination of Weekdays. In some countries the trains run every other day, meaning they run on Mon/Wed/Fri/Sun in one wee and on Tue/Wed/Sat in the other week. In this case we put “0.5” in the day, however this must be replaced with a daily schedule this is not GTFS-compliant.
calendar_dates (Exceptions)
Needed for: This table is meant to note exceptions from the calendar schedule. We do not note all the exceptions and thus we do not display them. Important interruptions of service are announced in the column “irregularities” in trips.
translations
Needed for: This table is used to note names of train operators and of major cities in other than the local language (Venice -> Venezia).