Development of Analysis Methods Using Recent Data (2012)

Below is the uncorrected machine-read text of this chapter, intended to provide our own search engines and external engines with highly rich, chapter-representative searchable text of each book. Because it is UNCORRECTED material, please consider the following text as a useful but insufficient proxy for the authoritative book pages.

71 A p p e n d i x B database Architecture This section gives a detailed description of the database schema and metadata used for each field site. The metadata are described first, followed by the associated schema that enables extraction of target tracking and raw sensor point tracking data. Metadata The metadata are contained within several XML text files, which describe the range sensor locations, the zone-to-region mapping, the intersection center, and the maneuver topol- ogy. Of key importance for the report herein are the defini- tions of various maneuver events, which consist of tracked targets passing through a defined combination of three zones (Figure B.1). The project team is interested in the side-road vehicle maneuver events that correspond with the crash event occurring at the site: the side-road vehicle attempts to cross the median zone to the other side, requiring the driver of that vehicle to judge and accept gaps from each mainline direc- tion during the crossing maneuver. This maneuver is defined when the vehicle enters and exits from zones 2 and 1, respec- tively, and vice versa, entering and exiting zones 1 and 2; i.e., the maneuver attribute within the vehicle_acpt_lag relational table (Table B.1). For a given maneuver, other zones define stop events (stop bar), pullout after the stop (accepted lag), and progression after pullout (transition) events. A maneu- ver type is defined only if a vehicle (e.g., a tracked target) has been determined to pass through all zones constituting the maneuver. Thus, continuing with the example, and referring to Figure B.1, the straight-through zone1-zone2 maneuver passes through zones 9, 10, 11, 7, 15, 16, and 17. Note that the recorded crash for this site did not have the straight-straight maneuver assigned because the maneuver was never com- pleted. A piece of the metadata file defining maneuver types, maneuver_config_<database name>.xml, follows, with the zones referenced in Figure B.1. <?xml version= “1.0” encoding= “UTF-8”?> <!-- follow xml rules --> <!-- this is a comment --> <!-- comments can span more than one line --> <maneuver_config> <config> <num_maneuver ctype=“int”>14</num_maneuver> </config> <maneuver1> <!-- maneuverID --> <maneuverID ctype=“int”>1</maneuverID> <!-- enter_zone --> <enter_zone ctype= “int”>1</enter_zone> <!-- exit_zone --> <exit_zone ctype=“int”>2</exit_zone> <!-- description of the maneuver --> <description ctype=“char”>Strawberry Blvd N to Strawberry Blvd N</description> <!-- type of maneuver, straight,right,left --> <type ctype=“char”>straight,straight</type> <roadType ctype=“char”>minor</roadType> </maneuver1> <maneuver2> <!-- maneuverID --> <maneuverID ctype=“int”>2</maneuverID> <!-- enter_zone --> <enter_zone ctype=“int”>2</enter_zone> <!-- exit_zone --> <exit_zone ctype=“int”>1</exit_zone> <!-- description of the maneuver --> <description ctype=“char”>Strawberry Blvd S to Strawberry Blvd S</description> <!-- type of maneuver, straight,right,left --> <type ctype=“char”>straight,straight</type> <roadType ctype=“char”>minor</roadType> </maneuver2> The CICAS Site-Based System

72 Schema Presently, nine tables make up the database schema for each site: (1) gap_data, (2) sensor_collector, (3) sensor_status, (4) tracked_targets, (5) vehicle_acpt-lag, (6) vehicle_avail_gap, (7) vehicle_region, (8) vehicle_time, (9) vehicle_zone. Of most importance to the extraction of near-crash information are the relational tables: vehicle_acpt_lag, tracked_targets, sensor_ collector, and vehicle_zone (Tables B.1–B.4). Other tables— vehicle_time and vehicle_region—can be used to “follow” a known tracked target and qualify time ranges of interest and so forth. The simplest way to mine interesting traffic events is to query the dates and times of specific events within the vehicle_acpt_lag table. The returned date-time stamps can then be used to develop secondary queries to extract actual tracked-target trajectories within the tracked_ targets table. Regarding the vehicle classification attribute veh_class: Although nine classification categories are detected, they are grouped into four categories that can be reliably discriminated (within 5% accuracy). They are as follows: 1. Motorcycles, sedans, and small SUVs (Classes 1 and 2); 2. Large SUVs and pickup trucks (Class 3); 3. Single-unit trucks (Class 4–Class 6); and 4. Semitrucks and other large vehicles (Class 7 and higher). The optimal decision boundaries of the class-discriminate function are based on using vehicle height and length. For most of the intersection decision support (IDS) sites to date, there are 14 maneuver types; most contain two sub- maneuver actions. A close cousin to the vehicle_zone table is the vehicle_ region table. The attributes are similar except “region” is sub- stituted for “zone.” The primary keys are therefore targetid and region. There is also the zone attribute, which can be used for relational outer-joins with vehicle_zone to obtain all region-based events of the tracked target, as follows: SELECT zone, region, enter_region_date, enter_region_time, exit_region_date, exit_region_time, enter_region_speed, exit_ region_speed FROM vehicle_region AS vr OUTER JOIN vehicle_zone AS vz ON (vz.targetid = vr.targetid, vz.zone = vr.zone); The vehicle_time table contains relationships defining when a tracked target was first and last observed, as well as other information about the tracked target. Table B.5 lists the attributes. Figure B.1. Zone convention for the North Carolina site.

73 Table B.1. vehicle_acpt_lag Attribute Type Description/Comments targetid Double Primary key, target vehicle ID of tracked maneuver. maneuver Integer Primary key, maneuver type, if not defined, equals a blank space. veh_class Integer Vehicle type (1–9 categories) by experimentally determined length/max-height ranges. sub1_acpt_lag_zone Integer Submaneuver accepted lag zone number. sub1_acpt_lag_zone_exit_date Date Date front of vehicle crossed over the lag zone. sub1_acpt_lag_zone_exit_time Time without time zone Time front of vehicle crossed over the lag zone. sub1_stopbar_zone Integer Submaneuver. sub1_stopbar_zone_exit_date Double var length array Date front of vehicle crossed over the stop-bar zone. sub1_stopbar_zone_exit_time Double var length array Time front of vehicle crossed over the stop-bar zone. sub1_acpt_lags Double var length array Accepted lag times in seconds of mainline vehicles for each conflicting lane (front of side vehicle has crossed over lag zone). Typically, length = 2 for two lanes w.r.t. intersection center reference. sub1_acpt_gaps Double var length array Accepted gap times of mainline vehicles in seconds for each conflicting lane (front of vehicle has crossed over stop-bar zone [not pulled onto mainline]) w.r.t. intersection center reference. sub1_rej_gaps Double var length array Same convention as accepted gaps but rejected gaps (vehicle did not proceed with a maneuver). sub1_stopbar_acpt_lags Double var length array Accepted lag times in seconds of mainline vehicles for each conflicting lane (front of side vehicle has crossed over stop-bar zone). Typically, length = 2 for two lanes w.r.t. intersection center reference. sub1_stopbar_rej_lags Integer var length array Accepted lag times in seconds of mainline vehicles for each conflicting lane (front of side vehicle has crossed over stop-bar zone). Typically, length = 2 for two lanes w.r.t. intersection center reference. sub2_acpt_lag_zone Integer Second submaneuver portion of maneuver = n. sub2_acpt_lag_zone_exit_date Date Date front of vehicle crossed over the second lag zone. sub2_stopbar_zone Integer Same convention as sub1 attributes but for second submaneuver. sub2_acpt_lag_zone_exit_time Time without time zone Same convention as sub1 attributes but for second submaneuver. sub2_stopbar_zone Integer Same convention as sub1 attributes but for second submaneuver. sub2_stopbar_zone_exit_date Double var length array Same convention as sub1 attributes but for second submaneuver. sub2_stopbar_zone_exit_time Double var length array Same convention as sub1 attributes but for second submaneuver. sub2_acpt_lags Double var length array Same convention as sub1 attributes but for second submaneuver. sub2_acpt_gaps Double var length array Same convention as sub1 attributes but for second submaneuver. sub2_rej_gaps Double var length array Same convention as sub1 attributes but for second submaneuver. sub2_stopbar_acpt_lags Double var length array Same convention as sub1 attributes but for second submaneuver. sub2_stopbar_rej_lags Integer var length array Same convention as sub1 attributes but for second submaneuver.

74 Currently Available Crash data The crashes involve two events from the Minnesota Highway 52 fixed site and one from the North Carolina site. The proj- ect team obtained four videos for the three crashes that were date-time stamped (one of the crashes had video from two camera perspectives). The status of these crash data sets is summarized in Table B.8. The project team harvested target trajectories of the main- road and side-road vehicles for one of the crashes (date stamped: 4-06-2007, which occurred at the Highway 52 site). In this case, the side-road vehicle did not contain a valid tracked-target trajectory. Rather, the team filtered the raw sensor collector data, which contained the front-vehicle tar- gets of the side-road vehicle. Targets through the center zones of the intersections are missing. In terms of near crashes, the general strategy to harvest data has been to search for occurrences of side-road vehicles accepting short lags for certain maneuver types and then to use MATLAB to visually infer potential “near crashes” for Other Relational Tables There are two other tables within each site database that have not been considered for extraction of near-misses or crash data. Essentially, they can be used to derive traffic character- istics (related to lag times) and sensor operation over desired time periods. The gap_data table (Table B.6) contains more global information about primary, secondary, and tertiary lags for every time stamp during field operation. The size of the variable length array is based on the number of lanes used for each lag attribute (table name does not reflect that it rep- resents mainline lag times). If there is no measurable lag, the lag times are set to 1,000 (sec). Lastly, the sensor_status table, summarized in Table B.7, contains the status of all sensors at the site for each time stamp recorded during operation. It can be used to ensure that all the roadside data collection sensors were operational over the time period of the near-miss event candidates under study. This is achieved by querying for any values equal to zero in the sensor_status attribute. Table B.2. tracked_targets Attribute Type Description/Comments date Date Primary key. time Time without time zone Primary key. num_targets Integer Defines number of elements in remaining table attributes. targetid Double var length array Unique target ID, based on time-date encoded number in the mantissa and a unique identifier in the abscissa. x_state Double var length array NAD83 state plane coordinate, meters. x_state Double var length array NAD83 state plane coordinate, meters. x_dot_state Double var length array Estimate of velocity, w.r.t. NAD83 state plane coordinate system. y_dot_state Double var length array Estimate of velocity, w.r.t. NAD83 state plane coordinate system. speed Double var length array Speed, m/s. heading Double var length array Vehicle heading, radians. acceleration Double var length array m/s2 distance_to_intersection Double var length array w.r.t. intersection center. time_to_intersection Double var length array In seconds, if not calculated, equals 1,000. cur_lane Integer var length array Mainline lanes; lane numbers defined in map file for each site. region Integer var length array Refer to appropriate region map and datafile. sensor_type Integer var length array Refer to metadata description for sensor type, equals –1 for undefined. sensor_index Integer var length array Refer to metadata description for sensor number and associated state plane coordinate. Location, equals –1 for undefined. length Double var length array Estimate of vehicle length, if not defined equals 0. max_height Double var length array Estimate of vehicle height, if not defined equals 0.

75 Table B.3. sensor_collector Attribute Type Description/Comments date Date Primary key, date stamp. time Time without time zone Primary key, time stamp. num_targets Integer Number of raw sensor target points. sensortimestamp Double var length array Internal sensor time stamp. x_local Double var length array Sensor target point w.r.t. sensor coordinate system. y_local Double var length array x_dot_local Double var length array Sensor estimate of velocity. y_dot_local Double var length array Sensor estimate of velocity. x_state Double var length array Sensor target point w.r.t. NAD83 state plane system. y_state Double var length array Sensor target point w.r.t. NAD83 state plane system. x_dot_state Double var length array Sensor target point w.r.t. NAD83 state plane system. y_dot_state Double var length array Sensor target point w.r.t. NAD83 state plane system. height Double var length array Vehicle max. height. length Double var length array Vehicle length. num_sensors Integer Number of entries for the following three attributes: sensor_index, sensor_type, and sensor_status. sensor_index Integer var length array The sensor number as defined in the XML metadata file. sensor_type Integer var length array The sensor type as defined in the XML metadata file. sensor_status Integer var length array Sensor status. Table B.4. vehicle_zone Attribute Type Description/ Comments targetid Date Primary key, date stamp. zone Integer Primary key. region_first_seen Integer enter_zone_date Date enter_zone_time Time without time zone region_last_seen Integer exit_zone_date Date exit_zone_time Time without time zone enter_zone_speed Double enter_zone_inter_dist Double veh_time_in_zone Double min_speed_zone Double max_speed_zone Double ave_speed_zone Double Table B.5. vehicle_time Attribute Type Description/ Comments targetid Double Primary key, tracked target. date_first_seen Date time_first_seen Time without time zone region_first_seen Integer date_last_time Date time_last_seen Integer region_last_seen Integer zone_first_seen Integer zone_last_seen Integer maneuver Double veh_time_tracked Double veh_length Double veh_height Double veh_min_speed Double veh_max_speed Double veh_avg_speed Double

76 Table B.6. gap_data Attribute Type Description/Comments date Date Primary key, date stamp. time Time without time zone Primary key, every time step during field operation. num_lanes Integer primary_gap Double var array First vehicle lag times in each lane; defaults to 1,000 if there is no measurable lag. secondary_gap Double var array Second vehicle lag times in each lane; defaults to 1,000 if there is no measurable lag. tertiary_gap Integer Third vehicle lag times in each lane; defaults to 1,000 if there is no measurable lag. Table B.7. sensor_status Attribute Type Description/Comments date Date Primary key, date stamp. time Time without time zone Primary key, time stamp. num_sensors Integer Total number of sensors used at the site. sensor_status Integer var array Stores “1” for good, “0” for faulty/not operational. sensor_index Integer var array Sensor index ID numbers. sensor_type Integer var array List of the type of sensor (LIDAR, RADAR, and so on) corresponding to each sensor index. Table B.8. Current Crash Data Sets Site, Date of Crash Camera Data Database Trajectories Accident Reports Comments HW52/CSAH9, 4/6/2007 1 camera Y Y Side vehicle trajectory available as raw sensor data from camera tracking system. HW52/CSAH9, 09/21/2007 2 cameras N Y Side vehicle trajectory available as raw sensor collector (SC) data from camera tracking system. North Carolina 1 camera Y N Side vehicle tracked-targets file is not correct; advice is to go back to raw, sensor collector data for visual observation of potential “matches” of moving side vehicle target. further verification and analysis. For example, there may be points in the mainline trajectories where the tracked target changed lanes well enough ahead of the side-road vehicle to “easily” avoid a collision. Or perhaps the mainline vehicle was not in a conflicting lane at all. This is not possible to detect based on a query alone. Initially the attempt at drilling down to find this data was not successful. This was primarily because of a “bug” in the accepted lag/gap calculations stored within the site databases. However, this bug has now been resolved and the database of particular interest (North Carolina) has been rebuilt and reinstalled. The database will essentially be queried for straight-line cross-intersection maneuvers that are similar to the attempted maneuver by the side vehicle involved in the crash at the North Carolina site. In essence, the side-road vehicle must accept two lags, one from each mainline direction, during its mainline crossing maneuver. The criteria of the accepted lag calculation is arbitrary, of course, but times of less than 3 s have been chosen; that is, as the vehicle crosses the road, the first submaneuver of the vehicle crossing into the median or the second, last submaneuver of the vehicle crossing over the remainder of the mainline road accepted lags of 3 s.

77 Note that zones 9 and 17 represent the stop-bar and transi- tion zone, respectively, for the given maneuver type. For the side-road maneuver crossing the other direction, maneuver=2, the stop-bar and exiting transition zones are 12 and 20, respec- tively. Yet another method to obtain a more “broad” envelope is to query the vehicle_time relational table as follows: SELECT zone_first_seen, date_first_seen, time_first_seen, zone_last_seen, date_last_seen, time_last_seen FROM vehicle_ time WHERE (targetid == < returned targetid from vehicle_ acpt_lag >); In either case, the time ranges are then used to harvest the tracked_targets relational table within the desired envelope. Note that possible mainline lane changing maneuvers by the potentially conflicting vehicle are not precisely characterized within the tracked-targets data. That is, after the lateral movement of the vehicle exceeds an experimentally determined threshold, the tracked-target attribute lane will be updated accordingly. Thus, in addition to the tracked-targets data, the raw sensor collector data will be harvested over the same period for later analysis. In order to use the raw sensor collector data, however, a completely new predictive filter will need to be developed to extract the desired vehicles to “track,” in addition to creating optimal lateral paths for the vehicle. Note that the lateral error of the radar sensors used to obtain the mainline vehicle trajectories incur large lateral errors because of surface ambiguity of the vehicle’s front or rear bumper and surfaces. SELECT targetid, maneuver, veh_class, sub1_acpt_lag_zone_ exit_date, sub1_acpt_lag_zone_exit_time FROM vehicle_ acpt_lag WHERE (maneuver = 1) AND ((sub1_acpt_lags < 3.0) OR (sub2_acpt_lags < 3.0)); The returned date-time stamps and tracked-target ID of the vehicles that completed the maneuvers will be used as part of the criteria to harvest tracked-target and raw sensor collector data. The other part of the search criteria, particularly the amount of time before the accepted lag event times occurred, needs to be considered. It is desired to observe the side vehicle trajectory starting immediately before the vehicle entered the stop-bar region of the side road. The vehicle_zone relational table will be used to obtain the date-time when the particular target ID entered the first stop-bar zone. In this case, the desired zone number is specified in the following query: SELECT enter_zone_date, enter_zone_time FROM vehicle_ zone WHERE (zone == 9) AND (targetid == < returned targetid from vehicle_acpt_lag >); Optionally, the completion time for the near-miss candi- date to be observed could be defined using the same vehicle_ zone table as follows: SELECT exit_zone_date, exit_zone_time FROM vehicle_zone WHERE (zone == 17) AND (targetid == < returned targetid from vehicle_acpt_lag >);