BSHT Data - dssg/cta-sim GitHub Wiki
Dates in mm-dd-yyyy hh24:mi:ss format
| Variable | Type | Description |
|---|---|---|
| BUS_STATE_ID | NUMBER | Database-assigned unique id for each time point actual headway record |
| EVENT_TIME | DATE | Wainwright thinks this is the time the bus entered the time-point bubble. |
| BUS_ID | NUMBER(5) | Unique id of bus at time point. |
| BUSTOOLS_VER_ID | NUMBER(4) | |
| ROUTE_ID | VARCHAR2(5) | Unique id for each route. Information about each route is in planning_pwr.lu_routes. |
| PATTERN | VARCHAR2(2) | 2 character pattern codes. A route-direction can have several patterns - segments of the route that bus operators drive during a run - so there can be a higher frequency of service on buss parts of the route. The lookup table is planning_pwr.bt_pattern, join on the PATTERN field. |
| STOP_SEQUENCE | NUMBER(3) | |
| OPERATOR_ID | NUMBER(10) | Driver's badge number. |
| RUN_ID | VARCHAR2(5) | Unique id of driver run this trip belongs to. |
| ODOMETER_DISTANCE | NUMBER | |
| ROUTE_STATUS | NUMBER(2) | Some kind of metadata about the route. According to PLANNING_PWR.lu_routestatus: 0 = Off Route, 1 = On Route GPS, 2 = On Route LPS, 3 = End of Route, 4 = New Route |
| DWELL_TIME | NUMBER(16) | |
| DELTA_TIME | NUMBER(16) | ?? Difference between time_scheduled and time_actual_depart. In seconds. |
| LATITUDE | FLOAT | |
| LONGITUDE | FLOAT | |
| HEADING | FLOAT | |
| NAV_STATE | NUMBER(1) | Unclear. |
| BLOCK_ID | NUMBER(8) | id given to the bus while out of the garage. Like a run, but for buses - buses may be out on the road for days and driven by several operators. |
| WORK_VERSION | VARCHAR2(8) | Unclear. |
| WORK_STATUS | VARCHAR2(2) | |
| TRIP_ID | NUMBER(8) | Unique id of the scheduled route-diction-pattern trip this timepoint belongs to. Same is TRIP_ID in schd_bus_timept_times. Look up table seems to be PLANNING_PWR.schd_bus_trips, including the patterns and route the trip belong to, the trip's start/end time points, trip's scheduled state/end time, whether its a revenue trip, the CTA day type, garage, and schedule version. |
| TRIP_TYPE | NUMBER(1) | |
| DEPARTURE_TIME | DATE | |
| TRIP_START_TIME | DATE | |
| ???????????? | VARCHAR? | DD-MMM-YY |
| TIMEPOINT_ID | VARCHAR2(8) | Lookup table with full time point names seems to be PLANNING_PWR.schd_bus_timepoint. Called PLACEID in some tables? |
| TIMEPOINT_TYPE | NUMBER(2) | |
| TIMEPOINT_DWELL | NUMBER(16) | Amount of time bus spent in timepoint bubble, in seconds. |
| TIMEPOINT_STATUS | NUMBER(2) | Unclear. |
| SCHEDULE_MODE | NUMBER(1) |
SQL query?
Data from January 2006 - April 2013
One file that we received, BSHT2012FEB.txt, contains non-ASCII characters in 6 lines. This was fixed with the following command:
$ iconv -f ISO88591 -t ASCII//TRANSLIT < BSHT2012FEB.txt > BSHT2012FEB.txt.fix