Long term stop visits historization

Ara stores in BigQuery long_term_stop_visits table “terminated” Stop Visits:

when departure status is departed
when arrival and departure statuses are cancelled

Table schema

The following schema is used in the long_term_stop_visits

Field name	BigQuery Type	Value	Example(s)

Field name	BigQuery Type	Value	Example(s)
uuid	STRING	Universal Unique Identifier of the record	`f7bd34b3-4693-4ca4-8b25-408046d1b9da`
timestamp	TIMESTAMP	Timestamp of the record	`2023-07-18 09:41:06.966681 UTC`
stop_visit_uuid	STRING	Universal Unique Identifier of the Stop Visit	`1e9515e9-27f8-4d58-af13-26c794aaf368`
aimed_departure_time	TIMESTAMP	Theoretical departure time	`2023-07-18 09:39:19 UTC`
aimed_arrival_time	TIMESTAMP	Theoretical arrival time	`2023-07-18 09:39:19 UTC`
expected_departure_time	TIMESTAMP	Expected departure time	`2023-07-18 09:40:42 UTC`
expected_arrival_time	TIMESTAMP	Expected arrival time	`2023-07-18 09:40:42 UTC`
actual_departure_time	TIMESTAMP	Real arrival time	`2023-07-18 09:40:44 UTC`
actual_arrival_time	TIMESTAMP	Real departure time	`2023-07-18 09:40:50 UTC`
departure_status	TIMESTAMP	Departure status	`onTime`, `Delayed`, `Cancelled`, ...
arrival_status	STRING	Arrival Status	`onTime`, `Delayed`, `Cancelled`, ...
stop_area_name	STRING	Name of the stop area	`Hautes Plaines`
stop_area_codes	RECORD	List of identifiers of a stop area. Each identifier is composed of a kind ( defined for an Ara partner by the `remote_objectid_kind`) and a Value	`[{"kind":"internal","value": "50076644"}, ...]`
stop_area_coordinates	GEOGRAPHY	Geographical coordinates of the stop area	`POINT(22, 34)`
line_name	STRING	Name of the line	`Line T10`
line_number	STRING	Number of the line	`T10`
transport_mode	STRING	Type of transport mode	`bus`, ...
line_codes	RECORD	List of identifiers of a line. Each identifier is composed of a kind ( defined for an Ara partner by the `remote_objectid_kind`) and a Value	`[{"kind": "external","value": "Boston:Line::C00495:"}, ...]`
vehicle_journey_direction_type	STRING	Direction of the vehicle journey	`Aller`, `Retour`
vehicle_journey_origin_name	STRING	stop area origin name of the vehicle journey	`Gare de Gif-sur-Yvette`
vehicle_journey_destination_name	STRING	stop area destination name of the vehicle journey	`Parc Orsay université`
vehicle_journey_codes	RECORD	List of identifiers of a vehicle journey Each identifier is composed of a kind ( defined for an Ara partner by the `remote_objectid_kind`) and a Value	`[{"kind":"internal","value":"MTA:VehicleJourney::9544592:LOC"}, ...]`
vehicle_driver_ref	STRING	Driver reference of the vehicle	`53434`
vehicle_occupancy	STRING	Occupancy of the vehicle	`fewSeatsAvailable`

Accessing the data

Prerequisite: ask to the enRoute support team a JSON credential file.

For a Ara referential named my_referential, the BigQuery table is named enroute-production.ara_my_referential.long_term_stop_visits

The given key file can be used to request our long term stop visits historization table by using the BigQuery client: Load and query data with the bq tool | BigQuery | Google Cloud

For example, in a docker environment:

docker run -it -v $PWD:/data google/cloud-sdk:latest
$ gcloud auth activate-service-account --key-file /data/key.json
$ gcloud config set project enroute-production
$ bq query --use_legacy_sql=false --format json 'select * from enroute-production.ara_my_referential.long_term_stop_visits order by timestamp desc limit 1;'