tnc trip table structure

tnc trip table structure#

The TNC_Trip table stores all trips made by shared mobility vehicles - referred to here as TNC vehicles. Each record in the table includes a leg of travel - either pickup, dropoff, charging, or repositioning. Several records together for a specific vehicle may form a tour, meaning the vehicle was continuously in operation from one trip to the next, without a break.

Table Structure#
Field	Type	NULL allowed	Default Value	Foreign key	Description
TNC_trip_id_int*	INTEGER	NO			Integerized trip ID for each TNC_Trip record
TNC_trip_id	INTEGER	NO			Not used
path	INTEGER	NO	-1		Path ID is not -1 if there exists Path-related information in the Path table (in H5) referenced by this value (foreign key to the Path table)
path_multimodal	INTEGER	YES			All TNC trips are on auto network, so no multimodal travel information is logged Always NULL
tour	INTEGER	NO	0		Counter for tours that are formed from a series of continuous trips undertaken by the TNC vehicle
start	REAL	YES	0		Time at which the TNC vehicle started its trip (units: seconds)
end	REAL	YES	0		Time at which the TNC vehicle ended its trip (units: seconds)
duration	REAL	YES	0		Not used
origin	INTEGER	NO	0		Origin location of the trip (foreign key to the Location table)
destination	INTEGER	NO	0		Destination location of the trip (foreign key to the Location table)
purpose	INTEGER	NO	0		Not used
mode	INTEGER	NO	0		The mode of the trip should always be 9: “TAXI/TNC”Values at mode.
type	INTEGER	NO	0		Trip type as defined in , should always be ABMValues at type.
vehicle	INTEGER	YES		Vehicle(vehicle)	TNC vehicle ID associated with the trip (foreign key to the Vehicle table)
passengers	INTEGER	NO	0		Number of passengers on board the TNC vehicle during the execution of the trip recorded
travel_distance	REAL	YES	0		Distance traveled during the trip (units: meters)
skim_travel_time	REAL	YES	0		Travel time from the skim table for comparison with execution (units: seconds)
routed_travel_time	REAL	YES	0		Travel time from the router for comparison with execution (units: seconds)
request_time	REAL	YES	0		Simulation time when the request being served actually requested that trip (units: seconds)
init_status	INTEGER	NO	0		TNC status denoting what operation was being done when trip started. Allowable values are: Pickup (-1), Dropoff (-2), (-3), Repositioning (-3), and Charging (-4)
final_status	INTEGER	NO	0		TNC status denoting what operation was actually done when trip ended because of mid-trip detours. Allowable values are: Pickup (-1), Dropoff (-2), Repositioning (-3), and Charging (-4)
init_battery	REAL	YES	0		If electric vehicle, battery state of charge at the beginning of the trip (units: %)
final_battery	REAL	YES	0		If electric vehicle, battery state of charge at the end of the trip (units: %)
fare	REAL	YES	0		Fare collected from executing this leg of the trip - Not to be used - Use fare from TNC_Request instead.
person	INTEGER	YES		Person(person)	If request is related to a person, then the person ID is logged (foreign key to the Person table)
request	INTEGER	NO	0		Request object which tracks additional details of the request that generated this trip (foreign key to the TNC_Request table)
toll	REAL	NO	0.0		Toll paid in executing the trip (units: $USD)
has_artificial_trip	INTEGER	NO	0		Denotes the type of artificial trip and can include values in Values at has_artificial_trip.

(* - Primary key)

The SQL statement for table and index creation is below.

CREATE TABLE "TNC_Trip" (
  "TNC_trip_id_int" INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
  "TNC_trip_id" INTEGER NOT NULL,
  "path" INTEGER NOT NULL DEFAULT -1,
  "path_multimodal" INTEGER NULL,
  "tour" INTEGER NOT NULL DEFAULT 0,
  "start" REAL NULL DEFAULT 0,
  "end" REAL NULL DEFAULT 0,
  "duration" REAL NULL DEFAULT 0,
  "origin" INTEGER NOT NULL DEFAULT 0,
  "destination" INTEGER NOT NULL DEFAULT 0,
  "purpose" INTEGER NOT NULL DEFAULT 0,
  "mode" INTEGER NOT NULL DEFAULT 0,
  "type" INTEGER NOT NULL DEFAULT 0,
  "vehicle" INTEGER NULL,
  "passengers" INTEGER NOT NULL DEFAULT 0,
  "travel_distance" REAL NULL DEFAULT 0,
  "skim_travel_time" REAL NULL DEFAULT 0,
  "routed_travel_time" REAL NULL DEFAULT 0,
  "request_time" REAL NULL DEFAULT 0,
  "init_status" INTEGER NOT NULL DEFAULT 0,
  "final_status" INTEGER NOT NULL DEFAULT 0,
  "init_battery" REAL NULL DEFAULT 0,
  "final_battery" REAL NULL DEFAULT 0,
  "fare" REAL NULL DEFAULT 0,
  "person" INTEGER NULL,
  "request" INTEGER NOT NULL DEFAULT 0,
  "toll" REAL NOT NULL DEFAULT 0.0,
  "has_artificial_trip" INTEGER NOT NULL DEFAULT 0,
  CONSTRAINT "vehicle_fk"
    FOREIGN KEY ("vehicle")
    REFERENCES "Vehicle" ("vehicle_id")
    DEFERRABLE INITIALLY DEFERRED,
  CONSTRAINT "person_fk"
    FOREIGN KEY ("person")
    REFERENCES "Person" ("person")
    DEFERRABLE INITIALLY DEFERRED)

Enums#

The following enums are used in this table.

mode#

Enum Values Description#
Value	Name	Description
0	SOV	Car driver
1	AUTO_NEST	Car modes aggregate nest alternative
2	HOV	Car passenger
3	TRUCK
4	BUS	Mode identifier associated with an articulated 60 ft bus vehicle, if the
5	RAIL	Public transit with walk access and egress to any combination of rail sub-modes (subway, light
6	NONMOTORIZED_NEST
7	BICYCLE	Privately owned bicycle
8	WALK	Walking
9	TAXI	Taxi and TNC ride hailing for passengers
10	SCHOOLBUS	School bus
11	PARK_AND_RIDE	Public transit, accessed by driving and parking the car. The egress leg is walk. Can
12	KISS_AND_RIDE	Public transit, accessed by car with someone dropping off. The egress leg is walk.
13	PARK_AND_RAIL	Public transit, accessed by driving and parking the car. The egress leg is walk. Can
14	KISS_AND_RAIL	Public transit, accessed by car with someone dropping off. The egress leg is walk.
15	TNC_AND_RIDE	Public transit, with access and/or egress by taxi or rideshare.
17	MD_TRUCK	Medium Duty Trucks, Classes 3-6 (10,001-26,000 lbs) according to FHWA classification
18	HD_TRUCK	Heavy Duty Trucks, Class 7-8 (> 26,001 lbs) according to FHWA classification
19	BPLATE	Pickup trucks and vans (Chicago model only), Class 2 (< 10,000 lbs) according to FHWA
20	LD_TRUCK	Light Duty Trucks, Class 2 (< 10,000 lbs) according to FHWA classification
21	RAIL_NEST	Rail modes aggregate nest alternative
22	BUS40
23	BUS60
24	PNR_BIKE_NEST	Park and ride, and bike modes aggregate nest alternative
25	RIDE_AND_UNPARK	Public transit, access is walk and egress is by parked car. Can use any any
26	RIDE_AND_REKISS	Public transit, access is walk and egress is by being picked up by a car. Can use
27	RAIL_AND_UNPARK	Public transit, access is walk and egress is by parked car. Can only use rail modes
28	RAIL_AND_REKISS	Public transit, access is walk and egress is by being picked up by a car. Can only
29	MICROM	Docked micromobility mode (if micromobility and transit has bike time but no transit time)
30	MICROM_NODOCK	Dockless micromobility modes (if dockless micromobility and transit has bike time but
31	MICROM_AND_TRANSIT	Docked micromobility modes for access/egress to transit
32	MICROM_NODOCK_AND_TRANSIT	Dockless micromobility and transit modes combination
33	ODDELIVERY	Groceries and meals delivery
999	FAIL_MODE	If a traveler left home using drive-to-transit but returning home via transit and
1000	FAIL_ROUTE	Not used
1001	FAIL_REROUTE
1002	FAIL_UNPARK	If the traveler arrives to their parked vehicle on a return (transit-and-drive) trip
1003	FAIL_UNPARK2
1004	FAIL_MODE1
1005	FAIL_MODE2
1006	FAIL_MODE3
1007	FAIL_ROUTE_ACTIVE	If the multimodal routing fails for a walking or biking trip the mode becomes
1008	FAIL_ROUTE_WALK_AND_TRANSIT
1009	FAIL_ROUTE_DRIVE_TO_TRANSIT
1010	FAIL_ROUTE_DRIVE_FROM_TRANSIT	If the multimodal routing fails for a drive-from-transit trip;
1011	FAIL_ROUTE_TNC_AND_TRANSIT
1012	FAIL_ROUTE_TNC	If the multimodal routing fails for a tnc trip; replan mode is triggered
1013	FAIL_ROUTE_SOV	Not used
1014	FAIL_ROUTE_MICROMOBILITY
1015	NO_MOVE	If the origin and destination locations are the same, no movement needs to happen
9999	UNSIMULATED	If both the origin and destination locations fall into the no-simulation portion of

type#

Enum Values Description#
Value	Name	Description
-1	NULLTRIP	Invalid trip types
11	ABM	Trips which are synthesized by travelers during the simulation
22	EXTERNAL	Fixed demand read in from DB (background traffic, freight, out-of-model SOV, etc.)
32	TNC_VEHICLE	Movements undertaken by TNC vehicles, written to the TNC_Trip table not Trip
33	TNC_REQUEST	Fixed TNC Demand which is read in from the Trips table of database
34	FREIGHT	Trips synthetically generated by CRISTAL Freight model
44	FIXED	Not used
55	TRANSIT	Movements undertaken by transit vehicles (operated by transit agencies)
99	UNSIMULATED	If both the origin and destination locations fall into the no-simulation portion of

has_artificial_trip#

Enum Values Description#
Value	Name	Description
0	ALL_GOOD
1	NOT_ROUTED
2	CONGESTION_REMOVAL
3	SIMULATION_ENDED
4	STUCK_IN_ENTRY_QUEUE