gtfs-rt

// SPDX-FileCopyrightText: 2023 Friedrich-Alexander-Universitat Erlangen-Nurnberg
//
// SPDX-License-Identifier: AGPL-3.0-only

// Example 3: GTFS Realtime Data
// Learning goals:
// - Understand the construction of a csv file with multiple tables
// - Understand how to work with live data

// 1. This Jayvee model describes a pipeline 
// from a GTFS RT data source in the web 
// to a SQLite file with multiple tables.
pipeline GtfsRTSimplePipeline {

  // 2. As you can see here, we have three independent
  // sequences of pipes in this pipeline.
  GTFSRTTripUpdateFeedExtractor
    ->GtfsRTTripUpdateInterpreter
    ->TripUpdateTableInterpreter
    ->TripUpdateLoader;

  GTFSRTVehiclePositionFeedExtractor
    ->GtfsRTVehiclePositionInterpreter
    ->VehiclePositionTableInterpreter
    ->VehicleLoader;

  GTFSRTAlertFeedExtractor
    ->GtfsRTAlertInterpreter
    ->AlertTableInterpreter
    ->AlertLoader;

  // 3. We define a series of HttpExtractors that each pull data
  // from an HTTP endpoint
  block GTFSRTTripUpdateFeedExtractor oftype HttpExtractor {
    url: "https://proxy.transport.data.gouv.fr/resource/bibus-brest-gtfs-rt-trip-update";
  }

  block GTFSRTVehiclePositionFeedExtractor oftype HttpExtractor {
    url: "https://proxy.transport.data.gouv.fr/resource/bibus-brest-gtfs-rt-vehicle-position";
  }

  block GTFSRTAlertFeedExtractor oftype HttpExtractor {
    url: "https://proxy.transport.data.gouv.fr/resource/bibus-brest-gtfs-rt-alerts";
  }

  // 4. In the next step, we use the domain-specific GtfsRTInterpreter
  // to interpret the fetched files as sheets
  block GtfsRTTripUpdateInterpreter oftype GtfsRTInterpreter {
    entity: "trip_update";
  }

  block GtfsRTAlertInterpreter oftype GtfsRTInterpreter {
    entity: "alert";
  }

  block GtfsRTVehiclePositionInterpreter oftype GtfsRTInterpreter {
    entity: "vehicle";
  }

  // 5. Next, we interpret the sheets as tables
  valuetype TripUpdate {
    property headerGtfsRealtimeVersion oftype text;
    property headerTimestamp oftype text;
    property headerIncrementality oftype text;
    property entityId oftype text;
    property entityTripUpdateTripTripId oftype text;
    property entityTripUpdateTripRouteId oftype text;
    property entityTripUpdateStopTimeUpdateStopSequence oftype text;
    property entityTripUpdateStopTimeUpdateStopId oftype text;
    property entityTripUpdateStopTimeUpdateArrivalTime oftype text;
    property entityTripUpdateStopTimeUpdateDepartureTime oftype text;
  }
  transform TripUpdateParser {
    from r oftype Collection<text>;
    to tripUpdate oftype TripUpdate;

    tripUpdate: {
      headerGtfsRealtimeVersion: r cellInColumn "header.gtfs_realtime_version",
      headerTimestamp: r cellInColumn "header.timestamp",
      headerIncrementality: r cellInColumn "header.incrementality",
      entityId: r cellInColumn "entity.id",
      entityTripUpdateTripTripId: r cellInColumn "entity.trip_update.trip.trip_id",
      entityTripUpdateTripRouteId: r cellInColumn "entity.trip_update.trip.route_id",
      entityTripUpdateStopTimeUpdateStopSequence: r cellInColumn "entity.trip_update.stop_time_update.stop_sequence",
      entityTripUpdateStopTimeUpdateStopId: r cellInColumn "entity.trip_update.stop_time_update.stop_id",
      entityTripUpdateStopTimeUpdateArrivalTime: r cellInColumn "entity.trip_update.stop_time_update.arrival.time",
      entityTripUpdateStopTimeUpdateDepartureTime: r cellInColumn "entity.trip_update.stop_time_update.departure.time",
    };
  }
  block TripUpdateTableInterpreter oftype TableInterpreter {
    header: true;
    columns: TripUpdate;
    parseWith: TripUpdateParser;
  }


  valuetype VehiclePosition {
    property headerGtfsRealtimeVersion oftype text;
    property headerTimestamp oftype text;
    property headerIncrementality oftype text;
    property entityId oftype text;
    property entityVehiclePositionVehicleDescriptorId oftype text;
    property entityVehiclePositionTripTripId oftype text;
    property entityVehiclePositionTripRouteId oftype text;
    property entityVehiclePositionPositionLatitude oftype text;
    property entityVehiclePositionPositionLongitude oftype text;
    property entityVehiclePositionTimestamp oftype text;
  }
  transform VehiclePositionParser {
    from r oftype Collection<text>;
    to vehiclePosition oftype VehiclePosition;

    vehiclePosition: {
      headerGtfsRealtimeVersion: r cellInColumn "header.gtfs_realtime_version",
      headerTimestamp: r cellInColumn "header.timestamp",
      headerIncrementality: r cellInColumn "header.incrementality",
      entityId: r cellInColumn "entity.id",
      entityVehiclePositionVehicleDescriptorId: r cellInColumn "entity.vehicle_position.vehicle_descriptor.id",
      entityVehiclePositionTripTripId: r cellInColumn "entity.vehicle_position.trip.trip_id",
      entityVehiclePositionTripRouteId: r cellInColumn "entity.vehicle_position.trip.route_id",
      entityVehiclePositionPositionLatitude: r cellInColumn "entity.vehicle_position.position.latitude",
      entityVehiclePositionPositionLongitude: r cellInColumn "entity.vehicle_position.position.longitude",
      entityVehiclePositionTimestamp: r cellInColumn "entity.vehicle_position.timestamp",
    };
  }
  block VehiclePositionTableInterpreter oftype TableInterpreter {
    header: true;
    columns: VehiclePosition;
    parseWith: VehiclePositionParser;
  }


  valuetype Alert {
    property headerGtfsRealtimeVersion oftype text;
    property headerTimestamp oftype text;
    property headerIncrementality oftype text;
    property entityId oftype text;
    property entityAlertInformedEntityRouteId oftype text;
    property entityAlertHeaderText oftype text;
    property entityAlertDescriptionText oftype text;
  }
  transform AlertParser {
    from r oftype Collection<text>;
    to alert oftype Alert;

    alert: {
        headerGtfsRealtimeVersion: r cellInColumn "header.gtfs_realtime_version",
        headerTimestamp: r cellInColumn "header.timestamp",
        headerIncrementality: r cellInColumn "header.incrementality",
        entityId: r cellInColumn "entity.id",
        entityAlertInformedEntityRouteId: r cellInColumn "entity.alert.informed_entity.route_id",
        entityAlertHeaderText: r cellInColumn "entity.alert.header_text",
        entityAlertDescriptionText: r cellInColumn "entity.alert.description_text",
      };
  }
  block AlertTableInterpreter oftype TableInterpreter {
    header: true;
    columns: Alert;
    parseWith: AlertParser;
  }

  // 6. Last, we load the tables into the same SQLite file.
  // Each loader has to define a different table name.
  // For working with live data, we use the property "dropTable: false"
  // to append data instead of deleting the previous data.
  block TripUpdateLoader oftype SQLiteLoader {
    table: "gtfs-rt-trip_update";
    file: "./gtfs.sqlite";
    dropTable: false;
  }

  block VehicleLoader oftype SQLiteLoader {
    table: "gtfs-rt-vehicle_position";
    file: "./gtfs.sqlite";
    dropTable: false;
  }

  block AlertLoader oftype SQLiteLoader {
    table: "gtfs-rt-alert";
    file: "./gtfs.sqlite";
    dropTable: false;
  }
}