Skip to content

Database Tables

Jump to bottom Edit New page
sheldonabrown edited this page Jul 29, 2013 · 41 revisions

Overview

This page describes “app” and “reporting” database tables. It also lists all the columns and explains the data in each table and column.

Timezones

The app database dates are stored with respect to the local timezone. All reporting database dates are stored in the UTC timezone.

App Database Tables

This database consists of tables that store user information, API keys and service alerts. All the tables in this database are described below.

oba_users

This table stores users of the system. Users can either be admin users or API keys required for web service calls. The MySQL DDL is as shown below



  
CREATE TABLE `oba_users` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `creationTime` datetime DEFAULT NULL,
  `lastAccessTime` datetime DEFAULT NULL,
  `properties` longblob,
  `temporary` bit(1) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=375 DEFAULT CHARSET=latin1

A brief description of the columns for oba_users is as given below.

Column Description
id Unique row id
creationTime Creation timestamp of the user record
lastAccessTime Last access time of this record
properties Reference to user properties
temporary Flag to indicate that user record is temporary

oba_user_indices

This table stores user credentials and is linked to the oba_users table. The MySQL DDL is as shown below

  
CREATE TABLE `oba_user_indices` (
  `type` varchar(50) NOT NULL,
  `value` varchar(200) NOT NULL,
  `credentials` varchar(255) DEFAULT NULL,
  `user_id` int(11) DEFAULT NULL,
  PRIMARY KEY (`type`,`value`),
  KEY `FK1AAC12E4159A0621` (`user_id`),
  CONSTRAINT `FK1AAC12E4159A0621` FOREIGN KEY (`user_id`) REFERENCES `oba_users` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1

A brief description of the columns for oba_user_indices is as given below.

Column Description
type Type of user record. Can be a user name or API key
value The actual user name or API key value.
credentials Hash of password used to create a user
user_id User record’s unique id. Foreign key to oba_users id column

oba_user_roles

This table stores roles of system users. These roles are used to grant the required privileges to the users. The MySQL DDL is as shown below

  
CREATE TABLE `oba_user_roles` (
  `name` varchar(255) NOT NULL,
  PRIMARY KEY (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1

The column ‘name’ is used to store name of the user role such as ‘administrator’, ‘operator’ or ‘anonymous’.

oba_user_roles_mapping

This table associates users to their roles in the system. It is a link between oba_users and oba_user_roles table. The MySQL DDL is as shown below

  
CREATE TABLE `oba_user_roles_mapping` (
  `user_id` int(11) NOT NULL,
  `roles_name` varchar(255) NOT NULL,
  PRIMARY KEY (`user_id`,`roles_name`),
  KEY `FK3AAF66C9159A0621` (`user_id`),
  KEY `FK3AAF66C9B8180675` (`roles_name`),
  CONSTRAINT `FK3AAF66C9159A0621` FOREIGN KEY (`user_id`) REFERENCES `oba_users` (`id`),
  CONSTRAINT `FK3AAF66C9B8180675` FOREIGN KEY (`roles_name`) REFERENCES `oba_user_roles` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1

A brief description of the columns for oba_user_roles_mapping is as given below.

Column Description
user_id User record’s unique id. Foreign key to oba_users id column
roles_name Role assigned to the given user. Foreign key to oba_roles name column

obanyc_servicealerts

This table stores service alerts sent to the system by Transit Agency. These service alerts can either be route specific or global. The public facing application picks up service alerts from this table and renders them on the user interfaces. The MySQL DDL is as shown below

  
CREATE TABLE `obanyc_servicealerts` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `active_windows_json` longtext,
  `all_affects_json` longtext,
  `consequences_json` longtext,
  `created_at` datetime DEFAULT NULL,
  `creation_time` bigint(20) NOT NULL,
  `deleted` bit(1) DEFAULT NULL,
  `descriptions_json` longtext,
  `publication_windows_json` longtext,
  `reason` varchar(64) DEFAULT NULL,
  `service_alert_id` varchar(64) NOT NULL,
  `severity` varchar(64) DEFAULT NULL,
  `summaries_json` longtext,
  `updated_at` datetime DEFAULT NULL,
  `urls_json` longtext,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=7563 DEFAULT CHARSET=latin1

A brief description of the columns for obanyc_servicealerts is as given below.

Column Description
id Unique row id
active_windows_json
all_affects_json JSON of all the routes that are affected by this service alert
consequences_json JSON indicating consequences due to this service alert such as Delayed service
created_at db record creation date
creation_time Creation time of this service alert
deleted soft delete flag
descriptions_json JSON with service alert description
publication_windows_json JSON with time window in which this service alert should be effective
reason (not used)
service_alert_id Unique service alert id
severity Severity of the service alert
summaries_json JSON with service alert summary
updated_at db record update date
urls_json (not used) link to further information (_json implies multi-language support)

obanyc_servicealert_subscription

This table stores service alert subscriptions. The MySQL DDL is as shown below

  
CREATE TABLE `obanyc_servicealert_subscription` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `address` longtext,
  `consecutive_failures` int(11) DEFAULT NULL,
  `created_at` datetime DEFAULT NULL,
  `identifier` varchar(64) DEFAULT NULL,
  `ref` varchar(64) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=730 DEFAULT CHARSET=latin1

A brief description of the columns for obanyc_servicealert_subscription is as given below.

Column Description
id Unique row id
address subscribed host
consecutive_failures count of consecutive failure attempts to send updates to subscriber
created_at record creation date
identifier SIRI subscription disambiguator
ref SIRI subscription disambiguator

Real-time Database

This database consists of tables that store vehicle real time and inference data. It also persists UTS and SPEAR data uploaded by Transit Agency daily.

obanyc_cclocationreport

This table stores the real time vehicle data received from the queue. The MySQL DDL is as shown below



 
  CREATE TABLE `obanyc_cclocationreport` (
  `id` bigint(20) NOT NULL DEFAULT '0',
  `archive_time_received` datetime NOT NULL,
  `data_quality_qualitative_indicator` tinyint(4) DEFAULT NULL,
  `dest_sign_code` int(11) NOT NULL,
  `direction_deg` decimal(5,2) NOT NULL,
  `emergency_code` varchar(1) DEFAULT NULL,
  `latitude` decimal(9,6) NOT NULL,
  `longitude` decimal(9,6) NOT NULL,
  `manufacturer_data` varchar(64) NOT NULL,
  `nmea_sentence_gpgga` varchar(160) DEFAULT NULL,
  `nmea_sentence_gprmc` varchar(160) DEFAULT NULL,
  `operator_id_designator` varchar(16) NOT NULL,
  `raw_message` longtext NOT NULL,
  `request_id` int(11) NOT NULL,
  `route_id_designator` varchar(16) NOT NULL,
  `run_id_designator` varchar(32) NOT NULL,
  `speed` decimal(4,1) NOT NULL,
  `time_received` datetime NOT NULL,
  `time_reported` datetime NOT NULL,
  `UUID` varchar(36) NOT NULL,
  `vehicle_agency_designator` varchar(64) NOT NULL,
  `vehicle_agency_id` int(11) NOT NULL,
  `vehicle_id` int(11) NOT NULL,
  `time_received_index` datetime DEFAULT NULL,
  PRIMARY KEY (`time_received`,`vehicle_id`,`id`),
  KEY `id` (`id`),
  KEY `time_received_index` (`time_received_index`,`dest_sign_code`),
  KEY `vehicle_id` (`vehicle_id`,`time_reported`),
  KEY `vehicle_id_2` (`vehicle_id`,`time_received`),
  KEY `manufacturer_data` (`manufacturer_data`),
  KEY `UUID` (`UUID`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
/*!50100 PARTITION BY RANGE (
        TO_DAYS(time_received))
(PARTITION P20130313 VALUES LESS THAN (735306) ENGINE = InnoDB,
...
 PARTITION PMAX VALUES LESS THAN MAXVALUE ENGINE = InnoDB) */ |

A brief description of the columns for obanyc_cclocationreport is as given below.

Column Description
id Unique row id
archive_time_received date and time when message was received by archive process
data_quality_qualitative_indicator An optional indicator for the “quality” of the location/speed data. 4 signifies data not available
dest_sign_code DSC entered on bus
direction_deg Direction, in compass degrees (0 is north)
emergency_code System has detected presence of emergency message from ODK on bus. Only present when “1” (activated).
latitude Latitude in decimal degrees
longitude Longitude in decimal degrees
manufacturer_data The “Terminal ID” with and assigned prefix of the on-bus computer sending the data
nmea_sentence_gpgga NMEA GGA sentence
nmea_sentence_gprmc NMEA RMC sentence
operator_id_designator Pass number (operator ID) entered on bus.
raw_message JSON of message as it is picked up from the real time queue
request_id an incremental counter starting at 1 when the on-bus computer powers up
route_id_designator “Run Route” entered on bus. Numeric due to constraints imposed by farebox.
run_id_designator Run number entered on bus. Numeric due to constraints imposed by farebox.
speed Speed in MPH. Received instantaneously from GPS unit.
time_received Timestamp when packet received by server
time_reported Timestamp from report packet, generated on the bus
UUID Generated hash to make this record unique and to join against inferred data
vehicle_agency_designator Vehicle’s agency id
vehicle_agency_id Agency ID per APTA TCIP
vehicle_id Unique id assigned to the vehicle
time_received_index index allowing efficient querying by time

obanyc_inferredlocation

This table stores the data processed by inference engine. Inference engine puts its results on inference queue, and they are persisted by archiver web application from this queue. The MySQL DDL is as shown below



  
CREATE TABLE `obanyc_inferredlocation` (
  `id` bigint(20) NOT NULL DEFAULT '0',
  `agency_id` varchar(64) NOT NULL,
  `archive_time_received` datetime NOT NULL,
  `assigned_run_number` varchar(8) DEFAULT NULL,
  `assigned_run_route` varchar(8) DEFAULT NULL,
  `depot_id` varchar(16) DEFAULT NULL,
  `distance_along_block` double DEFAULT NULL,
  `distance_along_trip` double DEFAULT NULL,
  `emergency_flag` bit(1) NOT NULL,
  `inference_is_formal` bit(1) NOT NULL,
  `inferred_block_id` varchar(64) DEFAULT NULL,
  `inferred_dest_sign_code` int(11) DEFAULT NULL,
  `inferred_direction_id` varchar(1) DEFAULT NULL,
  `inferred_latitude` decimal(9,6) DEFAULT NULL,
  `inferred_longitude` decimal(9,6) DEFAULT NULL,
  `inferred_operator_id` varchar(16) DEFAULT NULL,
  `inferred_phase` varchar(32) NOT NULL,
  `inferred_route_id` varchar(32) DEFAULT NULL,
  `inferred_run_id` varchar(16) DEFAULT NULL,
  `inferred_status` varchar(32) NOT NULL,
  `inferred_trip_id` varchar(64) DEFAULT NULL,
  `last_location_update_time` bigint(20) NOT NULL,
  `last_update_time` bigint(20) NOT NULL,
  `next_scheduled_stop_distance` double DEFAULT NULL,
  `next_scheduled_stop_id` varchar(32) DEFAULT NULL,
  `schedule_deviation` int(11) DEFAULT NULL,
  `service_date` datetime NOT NULL,
  `time_reported` datetime NOT NULL,
  `UUID` varchar(36) NOT NULL,
  `vehicle_id` int(11) NOT NULL,
  `time_reported_index` datetime DEFAULT NULL,
  `assigned_run_id` varchar(16) DEFAULT NULL,
  PRIMARY KEY (`time_reported`,`vehicle_id`,`id`),
  KEY `id` (`id`),
  KEY `time_reported_index` (`time_reported_index`,`inferred_route_id`),
  KEY `vehicle_id` (`vehicle_id`,`time_reported`),
  KEY `UUID` (`UUID`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
/*!50100 PARTITION BY RANGE (
        TO_DAYS(time_reported))
(PARTITION P20130313 VALUES LESS THAN (735306) ENGINE = InnoDB,
...
 PARTITION PMAX VALUES LESS THAN MAXVALUE ENGINE = InnoDB) */ |

A brief description of the columns for obanyc_inferredlocation is as given below.

Column Description
id Unique row id
agency_id Vehicle’s agency id
archive_time_received date and time message received by archive process
assigned_run_number (deprecated) run number provided by the operator assignment service (first part of run ID)
assigned_run_route (deprecated) run route provided by the operator assignment service (last part of run ID)
depot_id Depot to which this vehicle is assigned at the time of inference
distance_along_block distance along block in meters
distance_along_trip distance along trip in meters
emergency_flag System has detected presence of emergency message from ODK on bus. Only present when “1” (activated).
inference_is_formal Boolean indicating confidence with vehicle inference (see formal vs informal)
inferred_block_id Block ID (agency_id_)
inferred_dest_sign_code DSC associated with the inferred_trip_id
inferred_direction_id Direction associated with the inferred_trip_id, matches to GTFS. Boolean 0/1 representing two directions of route.
inferred_latitude Latitude in decimal degrees for location of bus snapped to its current trip.
inferred_longitude Inferred longitude in decimal degrees for location of bus snapped to its current trip.
inferred_operator_id (deprecated) Operator Id inferred by CIS (agency_id_)
inferred_phase Phase inferred by inference engine such as IN_PROGRESS, DEADHEAD etc
inferred_route_id Route ID (agency_id_) associated with the inferred_trip_id.
inferred_run_id Run ID (Run Route + Run Number) associated with the inferred_trip_id.
inferred_status Operational Statuses
inferred_trip_id Trip ID, as presented in GTFS. For non-revenue trips or layovers, this may be the revenue following trip. (agency_id_)
last_location_update_time last time valid co-ordinates were provided
last_update_time last update
next_scheduled_stop_distance Distance to the next scheduled stop in meters
next_scheduled_stop_id Stop ID (DOT provided Box ID) of next stop according to GTFS. (agency_id_)
schedule_deviation Deviation from schedule, in seconds. Only present if inference is “formal.” Negative values indicate early, while positive values indicate delays. When Phase indicates a non-revenue move (e.g. “LAYOVER” or “DEADHEAD”) schedule deviation is the time until scheduled departure of the next revenue trip.
service_date Date which the bus’ inferred block is assigned to. This field is formatted as a date and does not permit NULL or N/A values—-1/1/1970 should be interpreted as NULL.
time_reported Timestamp from the original real-time packet
UUID Generated hash to make this record unique and indexable against realtime records
vehicle_id Unique id assigned to the vehicle
time_reported_index for effecient querying
assigned_run_id the run route + run number assigned to the logged in bus operator at the time of inference

obanyc_last_known_vehicle

This table stores vehicle’s last known location supporting the operation API. Data persisted is a combination of real time and inference data to indicate vehicle’s latest location. The MySQL DDL is as shown below



  
CREATE TABLE `obanyc_last_known_vehicle` (
  `vehicle_id` int(11) NOT NULL,
  `agency_id` varchar(64) NOT NULL,
  `archive_time_received` varchar(255) NOT NULL,
  `depot_id` varchar(16) DEFAULT NULL,
  `dest_sign_code` int(11) NOT NULL,
  `direction_deg` decimal(5,2) NOT NULL,
  `distance_along_block` double DEFAULT NULL,
  `distance_along_trip` double DEFAULT NULL,
  `emergency_code` varchar(1) DEFAULT NULL,
  `inference_is_formal` bit(1) NOT NULL,
  `inferred_block_id` varchar(64) DEFAULT NULL,
  `inferred_dest_sign_code` int(11) DEFAULT NULL,
  `inferred_direction_id` varchar(1) DEFAULT NULL,
  `inferred_latitude` decimal(9,6) DEFAULT NULL,
  `inferred_longitude` decimal(9,6) DEFAULT NULL,
  `inferred_operator_id` varchar(16) DEFAULT NULL,
  `inferred_phase` varchar(32) NOT NULL,
  `inferred_route_id` varchar(32) DEFAULT NULL,
  `inferred_run_id` varchar(16) DEFAULT NULL,
  `inferred_status` varchar(32) NOT NULL,
  `inferred_trip_id` varchar(64) DEFAULT NULL,
  `latitude` decimal(9,6) NOT NULL,
  `longitude` decimal(9,6) NOT NULL,
  `next_scheduled_stop_distance` double DEFAULT NULL,
  `next_scheduled_stop_id` varchar(32) DEFAULT NULL,
  `operator_id_designator` varchar(16) NOT NULL,
  `route_id_designator` varchar(16) NOT NULL,
  `run_id_designator` varchar(32) NOT NULL,
  `schedule_deviation` int(11) DEFAULT NULL,
  `service_date` varchar(255) NOT NULL,
  `speed` decimal(4,1) NOT NULL,
  `time_received` varchar(255) NOT NULL,
  `time_reported` varchar(255) NOT NULL,
  `UUID` varchar(36) NOT NULL,
  `vehicle_agency_id` int(11) NOT NULL,
  `assigned_run_id` varchar(16) DEFAULT NULL,
  PRIMARY KEY (`vehicle_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1

The columns are combination of real time and inferred table columns with their data and purpose the same as respective columns in source tables. The column descriptions are intentionally omitted here.

obanyc_invalidlocationreport

This table stores error messages that occur when persisting real time or inference records. The MySQL DDL is as shown below



  
CREATE TABLE `obanyc_invalidlocationreport` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `archive_time_received` datetime NOT NULL,
  `exception_message` longtext NOT NULL,
  `raw_message` longtext NOT NULL,
  `time_received` datetime DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=7240 DEFAULT CHARSET=latin1

A brief description of the columns for obanyc_invalidlocationreport is as given below.

Column Description
id Unique row id
archive_time_received Time at which error message is logged
exception_message The error message logged explaining why exception occurred
raw_message JSON containing real time/inference data that failed to persist
time_received Time at which error message is received at queue proxy

obanyc_crewassignment

This table stores crew assignment data that is uploaded by Transit Agency everyday. The MySQL DDL is as shown below



  
CREATE TABLE `obanyc_crewassignment` (
  `id` int(11) NOT NULL,
  `agency_id` varchar(64) DEFAULT NULL,
  `depot_id` varchar(16) DEFAULT NULL,
  `operator_id` varchar(16) DEFAULT NULL,
  `run` varchar(255) DEFAULT NULL,
  `service_date` date NOT NULL,
  `updated` datetime DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1

A brief description of the columns for obanyc_crewassignment is as given below.

Column Description
id Unique row id
agency_id Vehicle’s agency id
depot_id Depot to which this vehicle is assigned
operator_id Pass number of operator.
run Bus operator’s assigned run.
service_date Date on which the assignment applies.
updated Update timestamp of this record

obanyc_vehiclepullout

This table stores vehicle pullout data that is uploaded by Transit Agency everyday. The MySQL DDL is as shown below



  
CREATE TABLE `obanyc_vehiclepullout` (
  `id` int(11) NOT NULL,
  `agency_id` varchar(64) DEFAULT NULL,
  `agency_id_tcip` int(11) DEFAULT NULL,
  `depot_id` varchar(16) DEFAULT NULL,
  `operator_id` varchar(16) DEFAULT NULL,
  `pullin_time` datetime DEFAULT NULL,
  `pullout_time` datetime DEFAULT NULL,
  `run` varchar(255) DEFAULT NULL,
  `service_date` date NOT NULL,
  `vehicle_id` int(11) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1

A brief description of the columns for obanyc_vehiclepullout is as given below.

Column Description
id Unique row id
agency_id Vehicle’s agency id
agency_id_tcip Vehicle’s agency id obtained from TCIP translation
depot_id Depot from which the pullout occurs.
operator_id Pass number of operator who pulled the vehicle out.
pullin_time Time at which vehicle is scheduled to pull into the depot
pullout_time Time at which vehicle is scheduled to pull out of depot
run Run that is scheduled to pull the vehicle out.
service_date Date on which vehicle is in service
updated Update timestamp of this record

obanyc_depot

This table stores the depot assignment data that is uploaded by Transit Agency everyday. The MySQL DDL is as shown below



  
CREATE TABLE `obanyc_depot` (
  `id` int(11) NOT NULL,
  `agency_id` varchar(64) DEFAULT NULL,
  `date` date NOT NULL,
  `depot_id` varchar(16) DEFAULT NULL,
  `vehicle_id` int(11) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1

A brief description of the columns for obanyc_depot is as given below.

Column Description
id Unique row id
agency_id Vehicle’s agency id
date Date for which the assignment applies.
depot_id Depot to which this vehicle is assigned
vehicle_id Unique id assigned to the vehicle

obanyc_systemlog

This table acts as a system log and stores messages logged by all the components of the system such as TDM, Admin server, Archiver etc. The MySQL DDL is as shown below



  
CREATE TABLE `obanyc_systemlog` (
  `id` bigint(20) NOT NULL,
  `component` varchar(32) DEFAULT NULL,
  `message` varchar(255) NOT NULL,
  `message_date` datetime NOT NULL,
  `priority` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1

A brief description of the columns for obanyc_systemlog is as given below.

Column Description
id Unique row id
component Component such as TDM, Admin etc that is logging the message
message The message that needs to be logged
message_date Date at which the message is logged
priority Message priority such as ROUTINE, SEVERE etc

Reporting Database

The reporting database goes contains data from yesterday through two years back. It partitions data daily based on the time_received column. For this reason, queries against the reporting database should include time_received in the where clause if at all possible.

Reorting Schema

Create Table: CREATE TABLE `obanyc_reporting` (
`data_quality_qualitative_indicator` tinyint(4) DEFAULT NULL,
`dest_sign_code` int(11) NOT NULL,
`direction_deg` decimal(5,2) NOT NULL,
`emergency_code` varchar(1) DEFAULT NULL,
`latitude` decimal(9,6) NOT NULL,
`longitude` decimal(9,6) NOT NULL,
`manufacturer_data` varchar(64) NOT NULL,
`operator_id_designator` varchar(16) NOT NULL,
`request_id` int(11) NOT NULL,
`route_id_designator` varchar(16) NOT NULL,
`run_id_designator` varchar(32) NOT NULL,
`speed` decimal(4,1) NOT NULL,
`time_received` datetime NOT NULL,
`time_reported` datetime NOT NULL,
`vehicle_agency_designator` varchar(64) NOT NULL,
`vehicle_agency_id` int(11) NOT NULL,
`vehicle_id` int(11) NOT NULL,
`time_received_index` datetime DEFAULT NULL,
`has_inference` tinyint(1) NOT NULL,
`agency_id` varchar(64) DEFAULT NULL,
`archive_time_received` datetime DEFAULT NULL,
`depot_id` varchar(16) DEFAULT NULL,
`distance_along_block` double DEFAULT NULL,
`distance_along_trip` double DEFAULT NULL,
`inference_is_formal` tinyint(1) DEFAULT NULL,
`inferred_block_id` varchar(64) DEFAULT NULL,
`inferred_dest_sign_code` int(11) DEFAULT NULL,
`inferred_direction_id` varchar(1) DEFAULT NULL,
`inferred_latitude` decimal(9,6) DEFAULT NULL,
`inferred_longitude` decimal(9,6) DEFAULT NULL,
`inferred_operator_id` varchar(16) DEFAULT NULL,
`inferred_phase` varchar(32) DEFAULT NULL,
`inferred_route_id` varchar(32) DEFAULT NULL,
`inferred_run_id` varchar(16) DEFAULT NULL,
`inferred_status` varchar(32) DEFAULT NULL,
`inferred_trip_id` varchar(64) DEFAULT NULL,
`next_scheduled_stop_distance` double DEFAULT NULL,
`next_scheduled_stop_id` varchar(32) DEFAULT NULL,
`schedule_deviation` int(11) DEFAULT NULL,
`service_date` datetime DEFAULT NULL,
`assigned_run_id` varchar(16) DEFAULT NULL,
PRIMARY KEY (`time_received`,`vehicle_id`),
KEY `inferred_route_id` (`inferred_route_id`,`time_reported`),
KEY `vehicle_id` (`vehicle_id`,`time_reported`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 ROW_FORMAT=COMPRESSED
/*!50100 PARTITION BY RANGE (
TO_DAYS(time_received))
(PARTITION P201201 VALUES LESS THAN (734899) ENGINE = InnoDB,
…
PARTITION P201307 VALUES LESS THAN (735446) ENGINE = InnoDB,
PARTITION PMAX VALUES LESS THAN MAXVALUE ENGINE = InnoDB) */

A brief description of the columns for obanyc_reporting is as given below.

Column Description
data_quality_qualitative_indicator See obanyc_cclocationreport
dest_sign_code See obanyc_cclocationreport
direction_deg See obanyc_cclocationreport
emergency_code See obanyc_cclocationreport
latitude See obanyc_cclocationreport
longitude See obanyc_cclocationreport
manufacturer_data See obanyc_cclocationreport
operator_id_designator See obanyc_cclocationreport
request_id See obanyc_cclocationreport
route_id_designator See obanyc_cclocationreport
run_id_designator See obanyc_cclocationreport
speed See obanyc_cclocationreport
time_received See obanyc_cclocationreport
time_reported See obanyc_cclocationreport
vehicle_agency_designator See obanyc_cclocationreport
vehicle_agency_id See obanyc_cclocationreport
vehicle_id See obanyc_cclocationreport
time_received_index See obanyc_cclocationreport
has_inference true if an inference record was matched to the realtime record
agency_id obanyc_inferredlocation
archive_time_received obanyc_inferredlocation
depot_id obanyc_inferredlocation
distance_along_block obanyc_inferredlocation
distance_along_trip obanyc_inferredlocation
inference_is_formal obanyc_inferredlocation
inferred_block_id obanyc_inferredlocation
inferred_dest_sign_code obanyc_inferredlocation
inferred_direction_id obanyc_inferredlocation
inferred_latitude obanyc_inferredlocation
inferred_longitude obanyc_inferredlocation
inferred_operator_id obanyc_inferredlocation
inferred_phase obanyc_inferredlocation
inferred_route_id obanyc_inferredlocation
inferred_run_id obanyc_inferredlocation
inferred_status obanyc_inferredlocation
inferred_trip_id obanyc_inferredlocation
next_scheduled_stop_distance obanyc_inferredlocation
next_scheduled_stop_id obanyc_inferredlocation
schedule_deviation obanyc_inferredlocation
service_date obanyc_inferredlocation
assigned_run_id obanyc_inferredlocation

Colums truncated from source table table during the copy:

Excluded Column Description
nmea_sentence_gpgga NMEA GGA sentence
nmea_sentence_gprmc NMEA RMC sentence
raw_message JSON of message as it is picked up from the real time queue
UUID Generated hash to make this record unique and to join against inferred data
last_location_update_time last time valid co-ordinates were provided
last_update_time last update
Add a custom footer
Add a custom sidebar
Clone this wiki locally