CM-100-V4-EN
Introduction
The Dataset Excel is a format created and is under full control from Optibus.
It can contain data from both the schedule and the timeplan (if it contains stop times) or just the raw Scheduling dataset.
It consists of a single Excel file with multiple sheets, some of which are required and some are optional. In this article we will go over this format in detail.
Basic Data
The data should be provided in the format of an Excel file. You can find the template attached at the end of the article. The minimal data format includes 3 mandatory sheets: Trips, Places and VehicleTypes. All the sheets must be part of the same Excel document with the according sheet names.
Structure
Trips sheet
This sheet represents each trip (on a row) that should be operated with all the necessary information attached to it such as Route Id, Direction, Departure/ Arrival stop and times.
Column index | Field name | Mandatory | Format | Explanation |
0 | "Trip Id" | Yes | Unique Text | The identifier of the reference trip |
1 | "Region" | No | Text | Leave empty |
2 | "Catalog Number" | No | Number | Unique Id for the route of the trip. This will map to Route Code in the timeplan. (Can be identical to the sign) |
3 | "Sign” | Yes | Number | The route number of which the trip belongs to. |
4 | "Direction" | Yes | ‘0’, ‘1’ or '2' | The direction of the trip (0: circular, 1: outbound, 2: inbound) |
5 | "Alternative" | No | Text | The same route can have different patterns. Define Id of the pattern |
6 | "Origin Stop Id" | Yes | Number | Departure Stop ID Value must be present in Stops or Places sheet |
7 | "Destination Stop Id" | Yes | Number | Arrival Stop ID Value must be present in Stops or Places sheet |
8 | "Day Offset" | Yes | '1' or '0' | ‘1' if the trip start time is after 23:59, else '0’ If this column exists, all remaining column indexes need to be offset by 1. |
9 | "Departure" | Yes | Time HH:MM | Trip start time |
10 | "Arrival" | Yes | Time HH:MM | Trip end time |
11 | "Vehicle Type Ids" | Yes | Text separated by '|'. | The legal vehicle types to perform this trip. Values must exist in the vehicle types sheet. (e.g. “Single Decker | Double Decker”) |
12 | "Distance" | Yes | Decimal (KM\Mi) | Trip Distance |
13 | "Existing" | No | N\A | Leave empty |
14 | "Custom" | No | N\A | Leave empty |
15 | “Days” | Yes | Text, chars between 1 and 7 | Days in which the event applies for. 1 for Sunday, 7 for Saturday, 23456 for Monday - Friday |
16 | “Boarding Time” | No | N\A | Leave empty |
17 | “Offboarding Time” | No | N\A | Leave empty |
18 | "Sub Trip Index” | No | Number | In case the trip has a mid-route relief, the trip must be entered in segments. Start of the trip until the mid-route relief and mid-route-relief until the end. Each segment of a trip must have the same Trip ID. Sub trip Index represents the index of a sub trip ('1', ‘2', '3’, etc). If a trip has more than 1 mid-route-relief, then it is possible to have more than 2 sub trips.There is a more recommended way using the “reliefpoints” tab (see below) |
19 | “Route Id” | No | Text | Unique route identifier. A route is identified as a combination of sign, direction, distance and sequence of stops along the route.This must be unique per sign, direction and pattern id. |
20 | “Vehicle Id” | No | Text/Numbers | The vehicle Id performing the trip. |
21 | “Service IDs” | Yes | Text | The Service ID that the trip belongs to. |
22 | “Service Name” | No | Text |
|
Places sheet
This sheet contains all the stops that are the origin or destination of trips. Each stop will occur only once.
Column Index | Field name | Mandatory | Format | Explanation |
0 | "Stop Id" | Yes | Unique text | Index of the stop |
1 | "Stop Name" | Yes | Text | Long name of the stop |
2 | "Address" | No | Text | Address name |
3 | "Latitude" | Yes | Decimal | Float (e.g. 17.628030) |
4 | “Longitude“ | Yes | Decimal | Float (e.g. 58.628030) |
5 | “Depot” | No | 1 or blank value | Stations considered as depot by Optibus must have a 1 in this column otherwise the cell should be blank |
VehicleTypes sheet
This sheet contains all the types of vehicle that are allowed to perform the trips.
Column Index | Field name | Requires Value | Format | Explanation |
0 | "Vehicle Type Id" | Yes | Unique text | Index of vehicle type (e.g. DD) |
1 | "Vehicle Type Name” | No | Text | Long name of the vehicle type (e.g. Double Decker) |
Mandatory sheets for timeplan import
StopTimes sheet
(mandatory for timeplan import , not mandatory for schedule import).
This sheet represents the entire sequence of stops (with stop ID, bus stop time and distance from previous stop) for all routes and alternatives. This information is used to reconstruct the route travel distances and travel times.
Column index | Field name | Required | Format | Explanation |
0 | “Trip” | Yes | Unique text | Trip Id |
1 | “Time“ | Yes | Time HH:MM | Time at the stop |
2 | “Point Id“ | Yes | Unique text | Name of stops |
3 | “Distance” | Yes | Decimal (KM or Miles) | Distance from previous stop |
4 | “TimePoint” | Yes | 1 or blank value | If this stop is a timepoint |
5 | “Sequence” | Yes | Number | Number for the sequence |
Optional sheets
ReliefPoints sheet
This sheet represents the relief points (stop IDs) where drivers can be relieved during the trips (i.e. mid-route). This information will be used in the scheduling phase (with defined preferences) to allow a relief strategy for drivers.
The ReliefPoints sheet replaces the need to fill in subtrip index in the Trips sheet although both can be specified.The ReliefPoints sheet relies directly on the StopTimes sheet.
Column Index | Field name | Requires Value | Format | Explanation |
0 | "Sign" | Yes | Text | Sign of the route |
1 | "Stop id" | Yes | Text | Stop that we want to define as relief point |
Stops sheet
Column Index | Field name | Requires Value | Format | Explanation |
0 | "Stop Id" | Yes | Unique text | Index of the stop |
1 | "Stop Name" | No | Text | Long name of the stop |
2 | "Place" | No | Text | If this stop points to a different stop as it’s main\parent stop |
3 | "Address" | No | Text | Address name |
4 | "Latitude" | Yes | Decimal | Float (e.g. 17.628030) |
5 | “Longitude“ | Yes | Decimal | Float (e.g. 58.628030) |
6 | “Depot” | Yes | 1 or blank value | Stations considered depots by Optibus must have a 1 in this column otherwise the cell should be blank |
Requirements
When importing a Dataset file into Optibus, it must adhere to the following conditions:
Be an excel (.xlsx or .xls) file
Contain the sheets Trips, Places, and VehicleTypes