Attachments_1_through_4.docx

National Park Service Park Facility Management Division/Transportation Branch Traffic Count Database Requirement

Appendix A Minimum Database Requirements The traffic database needs to be able to store traffic data collected from continuous traffic count stations and also short-term (two to fourteen day) coverage counts. Minimum database requirements are as follows:

· Lane and loop assignments in each count station

· Hourly traffic volumes by lane for each count station

· Ability to store data in predefined speed and vehicle length bins in various time increments (15 minute to 60 minute)

· Direction

· Year of count

· Month of count

· Date of month of count

· Day of week of count

· GPS coordinates of traffic count

Appendix B Minimum List of Data Fields For organizational purposes, traffic data is organized into four groups:

· Continuous Count Station Data

· Coverage Count Station Data

· Traffic Summary Reporting

· Traffic Segment Data

Continuous Count Station Data

· Park Name and Park Alpha Code

· Park Number

· NPS Region

· Count Station Name

· Count Station Number

· Lane ID

· Lane Direction of Travel

· Year of the Count

· Month of the Count

· Day of Week of the Count

· Calendar Date of the Count

· Traffic Volume during hour from 12 AM to 1AM

· Traffic Volume during hour from 1AM to 2 AM

· Traffic Volume during hour from 2 AM to 3 AM

· Traffic Volume during hour from 3 AM to 4 AM

· Traffic Volume during hour from 4 AM to 5 AM

· Traffic Volume during hour from 5 AM to 6 AM

· Traffic Volume during hour from 6 AM to 7 AM

· Traffic Volume during hour from 7 AM to 8 AM

· Traffic Volume during hour from 8 AM to 9 AM

· Traffic Volume during hour from 9 AM to 10 AM

· Traffic Volume during hour from 10 AM to 11 AM

· Traffic Volume during hour from 11 AM to 12 PM

· Traffic Volume during hour from 12 PM to 1 PM

· Traffic Volume during hour from 1 PM to 2 PM

· Traffic Volume during hour from 2 PM to 3 PM

· Traffic Volume during hour from 3 PM to 4 PM

· Traffic Volume during hour from 4PM to 5 PM

· Traffic Volume during hour from 5 PM to 6 PM

· Traffic Volume during hour from 6 PM to 7 PM

· Traffic Volume during hour from 7 PM to 8 PM

· Traffic Volume during hour from 8 PM to 9 PM

· Traffic Volume during hour from 9 PM to 10 PM

· Traffic Volume during hour from 10 PM to 11 PM

· Traffic Volume during hour from 11 PM to 12 AM

· Total Daily Traffic

Coverage Count Station Data

· Park Name and Park Alpha Code

· Park Number

· NPS Region

· Count Station Name

· Count Station Number

· Reference Count Station Number

· Lane Direction of Travel

· Start Date of the Count

· Start Time of the Count

· End Date of the Count

· End Time of the Count

· Year of the Count

· Month of the Count

· Day of Week of the Count

· Calendar Date of the Count

· Traffic Volume during hour from 12 AM to 1AM

· Traffic Volume during hour from 1AM to 2 AM

· Traffic Volume during hour from 2 AM to 3 AM

· Traffic Volume during hour from 3 AM to 4 AM

· Traffic Volume during hour from 4 AM to 5 AM

· Traffic Volume during hour from 5 AM to 6 AM

· Traffic Volume during hour from 6 AM to 7 AM

· Traffic Volume during hour from 7 AM to 8 AM

· Traffic Volume during hour from 8 AM to 9 AM

· Traffic Volume during hour from 9 AM to 10 AM

· Traffic Volume during hour from 10 AM to 11 AM

· Traffic Volume during hour from 11 AM to 12 PM

· Traffic Volume during hour from 12 PM to 1 PM

· Traffic Volume during hour from 1 PM to 2 PM

· Traffic Volume during hour from 2 PM to 3 PM

· Traffic Volume during hour from 3 PM to 4 PM

· Traffic Volume during hour from 4PM to 5 PM

· Traffic Volume during hour from 5 PM to 6 PM

· Traffic Volume during hour from 6 PM to 7 PM

· Traffic Volume during hour from 7 PM to 8 PM

· Traffic Volume during hour from 8 PM to 9 PM

· Traffic Volume during hour from 9 PM to 10 PM

· Traffic Volume during hour from 10 PM to 11 PM

· Traffic Volume during hour from 11 PM to 12 AM

· Total Daily Traffic

Traffic Summary Reporting

· See Requirement 2 Under 3.0 TASKS

Traffic Segment Data

· Park Name and Park Alpha Code

· Park Number

· NPS Region

· The number associated with the RIP Cycle Number in which the data was collected

· Count Station Name

· Count Station Number

· Seven RIP Route Number

· Route Identified (Combination of Park Alpha Code + "-" + RIP Route Number

· RIP Route Name

· Segment Name

· Segment’s beginning mile point

· Segment’s ending mile point

· Segment’s beginning latitude

· Segment’s beginning longitude

· Segment’s ending latitude

· Segment’s ending longitude

· Segment’s beginning description

· Segment’s ending description

· General comments

Appendix C

NPS QA/QC

Table 1. Traffic Count Validation Rules for NPS FOTSC Data, Years 2008 through 2013

Rule #

Rule Name & Description Phase 1 Thresholds Flagged as Invalid IF:

Final Recommendations Flagged as Invalid IF:

Not a validation rule, but a reported statistic.

For non-recreational locations: more than 8 consecutive zero values For recreational locations: more than 20 consecutive zero values For non-recreational locations: more than 8 consecutive zero values

For non-recreational locations: more than 2 zero values between 7 am and 7 pm This rule is not applied if daily volume is less than 200 vehicles.

For recreational locations: more than 4 zero values between 7 am and 7 pm For non-recreational locations: more than 2 zero values between 7 am and 7 pm This rule is not applied if daily volume is less than 200 vehicles.

This rule is not applied if daily volume is less than 200 vehicles.

4 or more consecutive identical non-zero count values (hourly volume values).

This rule is not applied if hourly volumes are equal to or lower than 15.

For non-recreational locations: greater than 2,400 vehicles per hour per lane For recreational locations: greater than 600 vehicles per hour per lane with these exceptions: 800 at N0404N, N0404W (lane 2 only), N2701W, N4701S (Lane 2 only), N4803E; 1,200 at N0401N; 1,400 at N4202N and N4804W (Lane 1 only) For non-recreational locations: greater than 2,400 vehicles per hour per lane

For non-recreational locations: ratio of passing lane to right-most lane more than 150% For recreational locations: not recommended at any current location For the following non-recreational locations:

3804: 200% for NB, 150% for SB 5301: 150% for both directions 6001: 210% for EB, 440% for WB 6002: none for NB, 150% for SB 6009: 220% for NB, 600% for SB

For non-recreational locations: ratio of noon to midnight count less than 1 OR greater than 75 This rule is not applied if daily volume is less than 200 vehicles.

For recreational locations: ratio of noon to midnight count less than 1 For non-recreational locations: ratio of noon to midnight count less than 1 OR greater than 120.

This rule is not applied if daily volume is less than 200 vehicles.

For non-recreational locations: ratio of peak hour to daily count greater than 25%

This rule is not applied if daily volume is less than 200 vehicles.

For recreational locations: ratio of peak hour to daily count greater than 35% with these exceptions: 40% at N3602N; 50% at N0801E, N2701S, N2701W, N3301N, N5801E For non-recreational locations: ratio of peak hour to daily count greater than 25% with these exceptions: 30% at N5501S, N6003N This rule is not applied if daily volume is less than 200 vehicles.

Appendix D Example of Traffic Count Database View to integrate with other Management Systems – (double click on image to see enlargement)

Legend 0 Incomplete 1 Complete -1 N/A

NPS_TRAFFIC

Columns Data Type Primary Key Foreign Keys DB Comments Excel Schema

Main Tables

PARK_TOTALS 1 1 1 1 1 1

ROUTE_ID 1 1 1 1 1 1

COUNT_STATIONS 1 1 1 1 1 1

COUNT_STATION_MAINT 1 1 1 1 1 1

COUNT_STATION_EXT_FACTORS 1 1 1 1 1 1

TRAFFIC_SEGMENTS 1 1 1 1 1 1

TRAFFIC_DATA_DAILY 1 1 1 1 1 1

TRAFFIC_DATA_MONTHLY 1 1 1 1 1 1

TRAFFIC_DATA_ANNUAL 1 1 1 1 1 1

SCR_TABLE_LIST 1 1 1 1 1 1

LOOKUP TABLES

US_STATES 1 1 1 -1 1 1

NPS REGIONS 1 1 1 -1 1 1

COUNT_STATION_TYPES 1 1 1 -1 1 1

NPS_PARKS (eventually part of NPS Data)

Database

NPS PARKS

Table of all NPS Parks, with roll up summary of traffic data.

COLUMN_NAME DATA_TYPE NULLABLE DATA_

DDEPENDENCY DB_COMMENTS REMARKS

OBJECTID INT No Internal sequence number required by ESRI Geodatabase.

PARK_ALPHA NVARCHAR(4) No Unique four letter code assigned to NPS unit.

PARK_NAME NVARCHAR(150) No Full name of park Will come from NPS_DATA as VIEW PARK_NO NVARCHAR(15) No Park Number Will come from NPS_DATA as VIEW GROUP_ALPHA NVARCHAR(4) Four letter code assigned to NPS units under the same management.

PARK_ALPHA equals GROUP_ALPHA when a park is managed by itself.

Will come from NPS_DATA as VIEW

NPS_REGION

NVARCHAR(25) NPS_REGIONS

The Region in which the Park resides. Please see the NPS_REGIONS table for a listing of the possible NPS_REGION values. Will come from NPS_DATA as VIEW

STATE1 NVARCHAR(2) No US_STATES State (abbreviation) in which the park resides Will come from NPS_DATA as VIEW STATE2 NVARCHAR(2) Yes US_STATES Additional state (abbreviation) in which park resides Will come from NPS_DATA as VIEW STATE3 NVARCHAR(2) Yes US_STATES Additional state (abbreviation) in which park resides TOT_COUNT_STNS SMALLINT No 0 Total number of traffic count stations in park FIRST_DATE_COLL DATETIME2(7) First date for which traffic data is available

LAST_DATE_COLL DATETIME2(7)

Last data for which traffic data is available. This should be updated when data is collected for the park

TRAFFIC_ROLLUP Vehicle Miles Traveled REMARKS NVARCHAR(MAX) Yes General comments

Return to Index

ROUTE_ID

Table of all routes in the NPS parks, with roll up summary for each route.

COLUMN_NAME DATA_TYPE NULLABDATA_

DEFAULT DEPENDEN DB_COMMENTS REMARKS

OBJECTID INT No Internal sequence number required by ESRI Geodatabase.

ROUTE_ID NVARCHAR(15) No ROUTE_ID Unique record identifier: PARK_ALPHA – RTE_NO To be provided by RIP RIP_CYCLE BIT No RIP cycle for which data is being used. This required for picking up right RIP routes when used in onjuction with this data as MP can vary. To be provided by RIP PARK_ALPHA NVARCHAR(4) No PARK_TOTALS Unique four letter code assigned to NPS unit. To be provided by RIP RTE_NO NVARCHAR(7) No FHWA-RIP Route Number To be provided by RIP RTE_NAME NVARCHAR(100) Yes NPS Route Name To be provided by RIP FROM_DESC NVARCHAR(255) Yes Description of beginning point of route. To be provided by RIP TO_DESC NVARCHAR(255) Yes Description of end point of route. To be provided by RIP

INSP_DATE DATE Yes Data Collection Date: Final collection date for DCV routes. Field condition assessment date for all manually rated routes. To be provided by RIP

FUNCT_CLASS SMALLINT Yes Functional Classification (1-8) To be provided by RIP USER_ACCESS NVARCHAR(10) Yes Access Designation for ALL ROUTES (Public or Nonpublic). To be provided by RIP STATE1 NVARCHAR2(2) No US_STATES State abbreviation in which route resides To be provided by RIP STATE2 NVARCHAR(2) Yes US_STATES Additional state in which route resides To be provided by RIP STATE3 NVARCHAR2(2) Yes US_STATES Additional state in which route resides To be provided by RIP

FMSS_NO NVARCHAR2(15) Yes NPS Facility Management Software System (FMSS) Asset number associated with Route. To be provided by RIP

M_DISTRICT NVARCHAR2(50) Yes Park Maintenance District in which Route resides. To be provided by RIP

CONCESSION BIT No 0 Concession Flag: Value of 1 indicates that NPS Route is maintained by Concessionaire. Default: 0. Flag: 1 (Concession route). To be provided by RIP

TOT_COUNT_STNS SMALLINT No 0 Total number of traffic count stations on a route.

COUNT_STATIONS

Table for Automatic Traffic Recorder (ATR) continuous traffic count stations (109 count)

COLUMN_NAME DATA_TYPE NULLAB DATA_

DEFAU DEPENDENCY DB_COMMENTS

OBJECTID INT No Internal sequence number required by ESRI Geodatabase.

COUNT_STATION_ID NVARCHAR(6) No Count Station ID according to FHWA TMAS DB COUNT_STATION_NAME NVARCHAR(75) No Count Station Name PARK_ALPHA NVARCHAR(4) No PARK_TOTALS Unique four letter code assigned to NPS unit.

COUNT_STATION_TYPE NVARCHAR(10) No COUNT_STATION_TYPES Type of Count Station COUNT_TYPE NVARCHAR(10) No The Type of hardware used ( Loop, Tube, etc.)

SSTATE NVARCHAR2(2) No US_STATES State abbreviation in which Station resides ROUTE_IDENT1 NVARCHAR(15) No ROUTE_ID Primary NPS route associated with count station ROUTE_IDENT2 NVARCHAR(15) Yes ROUTE_ID Secondary NPS route associated with count station MP1 NUMERIC(7,3) No Milepost for primary NPS route MP2 NUMERIC(7,3) Yes Milepost for secondary NPS route STATION_LAT NUMERIC(9,6) Yes GPS Latitude Coordinate of location of count station (decimal degrees) STATION_LONG NUMERIC(9,6) Yes GPS Longitude Coordinate of location of count station (decimal degrees) DATE_EST DATETIME2(7) No Date on which traffic counter was established DATE_CLOSED DATETIME2(7) Yes Date which traffic counter was permanently closed TRAFFIC_COUNTER_MAKE NVARCHAR(50) No Traffic counter make TRAFFIC_COUNTER_MODEL NVARCHAR(50) No Traffic counter model VEH_CLASS_COLL BIT Does this site collects vehicle classification. Possible values are 1 (True) and 0 (False) VEH_LENGTH_COLL BIT Does this site collects vehicle length. Possible values are 1 (True) and 0 (False) VEH_SPEED_COLL BIT Does this site collects vehicle speed. Possible values are 1 (True) and 0 (False) COUNTER_ACTIVE BIT Is traffic counter currently in active state. Possible values are 1 (True) and 0 (False)

COUNT_STATION_MAINT

Table for record keeping of count station maintenance

DEFADEPENDENCY DB_COMMENTS REMARKS

OBJECTID INT No Internal sequence number required by ESRI Geodatabase.

COUNT_STATION_ID NVARCHAR(6) No COUNT_STATIONS Count Station ID MAINT_STARTED_DATE DATETIME2(7) No Maintenance start date MAINT_COMPLETE_DATE DATETIME2(7) Yes Maintenance completion date PARK_CONTACT USER_TABLE Park Contact Person MAINT_ISSUE NVARCHAR(MAX) No Maintenance Issue REMEDY NVARCHAR(MAX) Yes Remedy

COUNT_STATION_EXT_FACTORS

Table for record keeping of external factors and information affecting count stations

COLUMN_NAME DATA_TYPE NULL DATA_

DEFAULDEPENDENCY DB_COMMENTS REMARKS

OBJECTID INT No Internal sequence number required by ESRI Geodatabase.

COUNT_STATION_ID NVARCHAR(6) No COUNT_STATIONS Count Station ID ISSUE_DATE DATETIME2(7) No Count Station Issue date EXT_FACTOR_TYPE DATETIME2(7) Yes EXT_FACTOR_TYPES External factors types for ex. Weather, Lane Closure PARK_CONTACT USER_TABLE Park Contact Person EXT_ISSUE_DETAILS NVARCHAR(MAX) Details of external issue

TRAFFIC_SEGMENTS

Table for Automatic Traffic Recorder (ATR) continuous traffic count stations (109 count), with roll up summary for segment data.

COLUMN_NAME DATA_TYPE NULLABL DATA_

DEFAULT DEPENDENCY DB_COMMENTS

OBJECTID INT No Internal sequence number required by ESRI Geodatabase.

SEGMENT_ID NVARCHAR(25) No Segment ID SEGMENT_NAME NVARCHAR(100) No Segment Name COUNT_STATION_ID NVARCHAR(6) No COUNT_STATIONS Count Station ID ROUTE_IDENT NVARCHAR(15) No ROUTE_ID Primary NPS route associated with count station BEG_MP NUMERIC(7,3) No Segment beginning milepost. This should match with MP of count station END_MP NUMERIC(7,3) No Segment ending milepost SEGMENT_LENGTH NUMERIC(7,3) No 0 Difference of END_MP and BEG_MP BEG_LAT NUMERIC(9,6) No GPS Latitude Coordinate at BEG_MP 0.000 (decimal degrees) BEG_LONG NUMERIC(9,6) No GPS Longitude Coordinate at BEG_MP 0.000 (decimal degrees). This should match with coordinates of END_LAT NUMERIC(9,6) No GPS Latitude Coordinate at End of route milepost (decimal degrees). This should match with coordinates END_LONG NUMERIC(9,6) No GPS Longitude Coordinate at End of route milepost (decimal degrees) FROM_DESC NVARCHAR(255) No Description of beginning point of segment.

TO_DESC NVARCHAR(255) No Description of end point of segment.

TRAFFIC_TOT_V_CLASS_1 INT NO -1 Total ADT in Vehicle Class 1 (Motorcycles) TRAFFIC_PCT_V_CLASS_1 NUMERIC(5,2) NO -1 Percentage of ADT in Vehicle Class 1 (Motorcycles) TRAFFIC_TOT_V_CLASS_2 INT NO -1 Total ADT in Vehicle Class 2 (Passenger Cars) TRAFFIC_PCT_V_CLASS_2 NUMERIC(5,2) NO -1 Percentage of ADT in Vehicle Class 2 (Passenger Cars) TRAFFIC_TOT_V_CLASS_3 INT NO -1 Total ADT in Vehicle Class 3 (Recreational Vehicles / RV) TRAFFIC_PCT_V_CLASS_3 NUMERIC(5,2) NO -1 Percentage of ADT in Vehicle Class 3 (Recreational Vehicles / RV) TRAFFIC_TOT_V_CLASS_4 INT NO -1 Total ADT in Vehicle Class 4 (Vehicles Pulling Trailers, RV) TRAFFIC_PCT_V_CLASS_4 NUMERIC(5,2) NO -1 Percentage of ADT in Vehicle Class 4 (Vehicles Pulling Trailers, RV) TRAFFIC_TOT_V_CLASS_5 INT NO -1 Total ADT in Vehicle Class 5 (Transit Buses) TRAFFIC_PCT_V_CLASS_5 NUMERIC(5,2) NO -1 Percentage of ADT in Vehicle Class 5 (Transit Buses) TRAFFIC_TOT_V_CLASS_6 INT NO -1 Total ADT in Vehicle Class 6 (Tour Buses) TRAFFIC_PCT_V_CLASS_6 NUMERIC(5,2) NO -1 Percentage of ADT in Vehicle Class 6 (Tour Buses) TRAFFIC_TOT_V_CLASS_7 INT NO -1 Total ADT in Vehicle Class 7 (Light-Duty Trucks) TRAFFIC_PCT_V_CLASS_7 NUMERIC(5,2) NO -1 Percentage of ADT in Vehicle Class 7 (Light-Duty Trucks) TRAFFIC_TOT_V_CLASS_8 INT NO -1 Total ADT in Vehicle Class 8 (Heavy-Duty Trucks) TRAFFIC_PCT_V_CLASS_8 NUMERIC(5,2) NO -1 Percentage of ADT in Vehicle Class 8 (Heavy-Duty Trucks) TRAFFIC_TOT_O_CLASS_1 INT NO -1 Total Vehicles in Vehicle Occupancy Class 1 (1 Single Occupant) TRAFFIC_PCT_O_CLASS_1 NUMERIC(5,2) NO -1 Percentage of Vehicles in Vehicle Occupancy Class 1 (1 Single Occupant) TRAFFIC_TOT_O_CLASS_2 INT NO -1 Total Vehicles in Vehicle Occupancy Class 2 (2 Occupants) TRAFFIC_PCT_O_CLASS_2 NUMERIC(5,2) NO -1 Percentage of Vehicles in Vehicle Occupancy Class 2 (2 Occupants) TRAFFIC_TOT_O_CLASS_3 INT NO -1 Total Vehicles in Vehicle Occupancy Class 3 (3-6 Car Load) TRAFFIC_PCT_O_CLASS_3 NUMERIC(5,2) NO -1 Percentage of Vehicles in Vehicle Occupancy Class 3 (3-6 Car Load) TRAFFIC_TOT_O_CLASS_4 INT NO -1 Total Vehicles in Vehicle Occupancy Class 4 (More than six(non-Bus vehicle load)) TRAFFIC_PCT_O_CLASS_4 NUMERIC(5,2) NO -1 Percentage of Vehicles in Vehicle Occupancy Class 4 (More than six(non-Bus vehicle load)) TRAFFIC_TOT_O_CLASS_5 INT NO -1 Total Vehicles in Vehicle Occupancy Class 5 (No Passenger Bus) TRAFFIC_PCT_O_CLASS_5 NUMERIC(5,2) NO -1 Percentage of Vehicles in Vehicle Occupancy Class 5 (No Passenger Bus) TRAFFIC_TOT_O_CLASS_6 INT NO -1 Total Vehicles in Vehicle Occupancy Class 6 (Few Passenger Bus) TRAFFIC_PCT_O_CLASS_6 NUMERIC(5,2) NO -1 Percentage of Vehicles in Vehicle Occupancy Class 6 (Few Passenger Bus) TRAFFIC_TOT_O_CLASS_7 INT NO -1 Total Vehicles in Vehicle Occupancy Class 7 (Semi-Loaded Passenger Bus) TRAFFIC_PCT_O_CLASS_7 NUMERIC(5,2) NO -1 Percentage of Vehicles in Vehicle Occupancy Class 7 (Semi-Loaded Passenger Bus) TRAFFIC_TOT_O_CLASS_8 INT NO -1 Total Vehicles in Vehicle Occupancy Class 8 (Fully Loaded Passenger Bus)

TRAFFIC_PCT_O_CLASS_8 NUMERIC(5,2) NO -1 Percentage of Vehicles in Vehicle Occupancy Class 8 (Fully Loaded Passenger Bus)

TRAFFIC_DATA_DAILY

Table for daily traffic data

COLUMN_NAME DATA_TYPE NULLA

BLE

DATA

DEFA

ULT

DEPENDENCY DB_COMMENTS REMARKS

OBJECTID INT No Internal sequence number required by ESRI Geodatabase.

COUNT_STATION_ID NVARCHAR(6) No COUNT_STATIONS Count Station ID

COLL_DATE DATETIME2(7) No Start Date and time of collection COLL_DAY_OF_WEEK NVARCHAR(9) No Day of the week LANE_NO SMALLINT No Lane number each lane is assigned a number

LANE_DIR_TRAVEL NVARCHAR(3) No Lane direction of travel N, S, E, W TRAFFIC_VOL_TOT INT No -1 Daily total traffic volume ADT INT No -1 Average Daily Traffic TARFFIC_HOUR_0_1 INT No -1 Daily traffic volume during hour from 12 AM to 1AM TRAFFIC_HOUR_1_2 INT No -1 Daily traffic volume during hour from 1AM to 2 AM TRAFFIC_HOUR_2_3 INT No -1 Daily traffic volume during hour from 2 AM to 3 AM TRAFFIC_HOUR_3_4 INT No -1 Daily traffic volume during hour from 3 AM to 4 AM TRAFFIC_HOUR_4_5 INT No -1 Daily traffic volume during hour from 4 AM to 5 AM TRAFFIC_HOUR_5_6 INT No -1 Daily traffic volume during hour from 5 AM to 6 AM TRAFFIC_HOUR_6_7 INT No -1 Daily traffic volume during hour from 6 AM to 7 AM TRAFFIC_HOUR_7_8 INT No -1 Daily traffic volume during hour from 7 AM to 8 AM TRAFFIC_HOUR_8_9 INT No -1 Daily traffic volume during hour from 8 AM to 9 AM TRAFFIC_HOUR_9_10 INT No -1 Daily traffic volume during hour from 9 AM to 10 AM TRAFFIC_HOUR_10_11 INT No -1 Daily traffic volume during hour from 10 AM to 11 AM TRAFFIC_HOUR_11_12 INT No -1 Daily traffic volume during hour from 11 AM to 12 PM TRAFFIC_HOUR_12_13 INT No -1 Daily traffic volume during hour from 12 PM to 1 PM TRAFFIC_HOUR_13_14 INT No -1 Daily traffic volume during hour from 1 PM to 2 PM TRAFFIC_HOUR_14_15 INT No -1 Daily traffic volume during hour from 2 PM to 3 PM TRAFFIC_HOUR_15_16 INT No -1 Daily traffic volume during hour from 3 PM to 4 PM TRAFFIC_HOUR_16_17 INT No -1 Daily traffic volume during hour from 4PM to 5 PM TRAFFIC_HOUR_17_18 INT No -1 Daily traffic volume during hour from 5 PM to 6 PM TRAFFIC_HOUR_18_19 INT No -1 Daily traffic volume during hour from 6 PM to 7 PM TRAFFIC_HOUR_19_20 INT No -1 Daily traffic volume during hour from 7 PM to 8 PM TRAFFIC_HOUR_20_21 INT No -1 Daily traffic volume during hour from 8 PM to 9 PM TRAFFIC_HOUR_21_22 INT No -1 Daily traffic volume during hour from 9 PM to 10 PM TRAFFIC_HOUR_22_23 INT No -1 Daily traffic volume during hour from 10 PM to 11 PM TRAFFIC_HOUR_23_24 INT No -1 Daily traffic volume during hour from 11 PM to 12 AM TRAFFIC_V_CLASS_1 NUMERIC(5,2) NO -1 Daily traffic volume of Vehicle Class 1 (Motorcycles) TRAFFIC_V_CLASS_2 NUMERIC(5,2) NO -1 Daily traffic volume of Vehicle Class 2 (Passenger Cars) TRAFFIC_V_CLASS_3 NUMERIC(5,2) NO -1 Daily traffic volume of Vehicle Class 3 (Recreational Vehicles / RV) TRAFFIC_V_CLASS_4 NUMERIC(5,2) NO -1 Daily traffic volume of Vehicle Class 4 (Vehicles Pulling Trailers, RV) TRAFFIC_V_CLASS_5 NUMERIC(5,2) NO -1 Daily traffic volume of Vehicle Class 5 (Transit Buses) TRAFFIC_V_CLASS_6 NUMERIC(5,2) NO -1 Daily traffic volume of Vehicle Class 6 (Tour Buses)

TRAFFIC_V_CLASS_7 NUMERIC(5,2) NO -1 Daily traffic volume of Vehicle Class 7 (Light-Duty Trucks) TRAFFIC_V_CLASS_8 NUMERIC(5,2) NO -1 Daily traffic volume of Vehicle Class 8 (Heavy-Duty Trucks) TRAFFIC_O_CLASS_1 NUMERIC(5,2) NO -1 Daily traffic volume of Vehicle Occupancy Class 1 (1 Single Occupant) TRAFFIC_O_CLASS_2 NUMERIC(5,2) NO -1 Daily traffic volume of Vehicle Occupancy Class 2 (2 Occupants) TRAFFIC_O_CLASS_3 NUMERIC(5,2) NO -1 Daily traffic volume of Vehicle Occupancy Class 3 (3-6 Car Load) TRAFFIC_O_CLASS_4 NUMERIC(5,2) NO -1 Daily traffic volume of vehicles in Vehicle Occupancy Class 4 (More than six(non-Bus vehicle load)) TRAFFIC_O_CLASS_5 NUMERIC(5,2) NO -1 Daily traffic volume of vehicles in Vehicle Occupancy Class 5 (No TRAFFIC_O_CLASS_6 NUMERIC(5,2) NO -1 Daily traffic volume of vehicle Occupancy Class 6 (Few Passenger Bus) TRAFFIC_O_CLASS_7 NUMERIC(5,2) NO -1 Daily traffic volume of vehicle Occupancy Class 7 (Semi-Loaded Passenger TRAFFIC_O_CLASS_8 NUMERIC(5,2) NO -1 Daily traffic volume of vehicles in Vehicle Occupancy Class 8 (Fully Loaded

Passenger Bus)

REMARKS NVARCHAR(MA

X) Yes General comments

TRAFFIC_DATA_MONTHLY

Table for monthly traffic data rolled up daily traffic data.

COLUMN_NAME DATA_TYPE NULLABLEDATA_

DDEPENDENDB_COMMENTS

OBJECTID INT No Internal sequence number required by ESRI Geodatabase.

COUNT_STATION_ID NVARCHAR(6) No COUNT_STCount Station ID YEAR_COLL INT No Year of collection MONTH_COLL INT No Month of collection TOT_COLL_DAYS INT No Number of days traffic was collected TRAFFIC_VOL_TOT INT No -1 Monthly total traffic volume MADT INT No -1 Monthly Average Daily Traffic MTWDT INT No -1 Monthly Total Weekday Traffic MAWDT INT No -1 Monthly Average Weekday Traffic MTWET INT No -1 Monthly Total Weekend Traffic MAWET INT No -1 Monthly Average Weekend Traffic ADTWDF INT No -1 Average Daily Traffic Weekday Factor ADTWEF INT No -1 Average Daily Traffic Weekend Factor ADTF INT No -1 Average Daily Traffic Week Factor STWDF INT No -1 Seasonal Traffic Weekday Factor STWEF INT No -1 Seasonal Traffic Weekend Factor STWF INT No -1 Seasonal Traffic Week Factor TRAFFIC_TOT_HOUR_0_1 INT No -1 Total traffic volume during hour from 12 AM to 1 AM TRAFFIC_PCT_HOUR_0_1 NUMERIC(5,2) No -1 Percentage of traffic during hour from 12 AM to 1 AM TRAFFIC_TOT_HOUR_1_2 INT No -1 Total traffic volume during hour from 1AM to 2 AM TRAFFIC_PCT_HOUR_1_2 NUMERIC(5,2) No -1 Percentage of traffic during hour from 1 AM to 2 AM TRAFFIC_TOT_HOUR_2_3 INT No -1 Total traffic volume during hour from 2 AM to 3 AM TRAFFIC_PCT_HOUR_2_3 NUMERIC(5,2) No -1 Percentage of traffic during hour from 2 AM to 3 AM TRAFFIC_TOT_HOUR_3_4 INT No -1 Total traffic volume during hour from 3 AM to 4 AM TRAFFIC_PCT_HOUR_3_4 NUMERIC(5,2) No -1 Percentage of traffic during hour from 3 AM to 4 AM TRAFFIC_TOT_HOUR_4_5 INT No -1 Total traffic volume during hour from 4 AM to 5 AM TRAFFIC_PCT_HOUR_4_5 NUMERIC(5,2) No -1 Percentage of traffic during hour from 4 AM to 5 AM TRAFFIC_TOT_HOUR_5_6 INT No -1 Total traffic volume during hour from 5 AM to 6 AM TRAFFIC_PCT_HOUR_5_6 NUMERIC(5,2) No -1 Percentage of traffic during hour from 5 AM to 6 AM TRAFFIC_TOT_HOUR_6_7 INT No -1 Total traffic volume during hour from 6 AM to 7 AM TRAFFIC_PCT_HOUR_6_7 NUMERIC(5,2) No -1 Percentage of traffic during hour from 6 AM to 7 AM TRAFFIC_TOT_HOUR_7_8 INT No -1 Total traffic volume during hour from 7 AM to 8 AM TRAFFIC_PCT_HOUR_7_8 NUMERIC(5,2) No -1 Percentage of traffic during hour from 7 AM to 8 AM TRAFFIC_TOT_HOUR_8_9 INT No -1 Total traffic volume during hour from 8 AM to 9 AM TRAFFIC_PCT_HOUR_8_9 NUMERIC(5,2) No -1 Percentage of traffic during hour from 8 AM to 9 AM TRAFFIC_TOT_HOUR_9_10 INT No -1 Total traffic volume during hour from 9 AM to 10 AM TRAFFIC_PCT_HOUR_9_10 NUMERIC(5,2) No -1 Percentage of traffic during hour from 9 AM to 10 AM TRAFFIC_TOT_HOUR_10_11 INT No -1 Total traffic volume during hour from 10 AM to 11 AM TRAFFIC_PCT_HOUR_10_11 NUMERIC(5,2) No -1 Percentage of traffic during hour from 10 AM to 11 AM TRAFFIC_TOT_HOUR_11_12 INT No -1 Total traffic volume during hour from 11 AM to 12 PM TRAFFIC_PCT_HOUR_11_12 NUMERIC(5,2) No -1 Percentage of traffic during hour from 11 AM to 12 PM TRAFFIC_TOT_HOUR_12_13 INT No -1 Total traffic volume during hour from 12 PM to 1 PM TRAFFIC_PCT_HOUR_12_13 NUMERIC(5,2) No -1 Percentage of traffic during hour from 12 PM to 1 PM TRAFFIC_TOT_HOUR_13_14 INT No -1 Total traffic volume during hour from 1 PM to 2 PM

TRAFFIC_PCT_HOUR_13_14 NUMERIC(5,2) No -1 Percentage of traffic during hour from 1 PM to 2 PM TRAFFIC_TOT_HOUR_14_15 INT No -1 Total traffic volume during hour from 2 PM to 3 PM TRAFFIC_PCT_HOUR_14_15 NUMERIC(5,2) No -1 Percentage of traffic during hour from 2 PM to 3 PM TRAFFIC_TOT_HOUR_15_16 INT No -1 Total traffic volume during hour from 3 PM to 4 PM TRAFFIC_PCT_HOUR_15_16 NUMERIC(5,2) No -1 Percentage of traffic during hour from 3 PM to 4 PM TRAFFIC_TOT_HOUR_16_17 INT No -1 Total traffic volume during hour from 4 PM to 5 PM TRAFFIC_PCT_HOUR_16_17 NUMERIC(5,2) No -1 Percentage of traffic during hour from 4 PM to 5 PM TRAFFIC_TOT_HOUR_17_18 INT No -1 Total traffic volume during hour from 5 PM to 6 PM TRAFFIC_PCT_HOUR_17_18 NUMERIC(5,2) No -1 Percentage of traffic during hour from 5 PM to 6 PM TRAFFIC_TOT_HOUR_18_19 INT No -1 Total traffic volume during hour from 6 PM to 7 PM TRAFFIC_PCT_HOUR_18_19 NUMERIC(5,2) No -1 Percentage of traffic during hour from 6 PM to 7 PM TRAFFIC_TOT_HOUR_19_20 INT No -1 Total traffic volume during hour from 7 PM to 8 PM TRAFFIC_PCT_HOUR_19_20 NUMERIC(5,2) No -1 Percentage of traffic during hour from 7 PM to 8 PM TRAFFIC_TOT_HOUR_20_21 INT No -1 Total traffic volume during hour from 8 PM to 9 PM TRAFFIC_PCT_HOUR_20_21 NUMERIC(5,2) No -1 Percentage of traffic during hour from 8 PM to 9 PM TRAFFIC_TOT_HOUR_21_22 INT No -1 Total traffic volume during hour from 9 PM to 10 PM TRAFFIC_PCT_HOUR_21_22 NUMERIC(5,2) No -1 Percentage of traffic during hour from 9 PM to 10 PM TRAFFIC_TOT_HOUR_22_23 INT No -1 Total traffic volume during hour from 10 PM to 11 PM TRAFFIC_PCT_HOUR_22_23 NUMERIC(5,2) No -1 Percentage of traffic during hour from 10 PM to 11 PM TRAFFIC_TOT_HOUR_23_24 INT No -1 Total traffic volume during hour from 11 PM to 12 AM TRAFFIC_PCT_HOUR_23_24 NUMERIC(5,2) No -1 Percentage of traffic during hour from 11 PM to 12 AM TRAFFIC_TOT_HOUR_SUN INT No -1 Monthly total traffic volume on Sunday TRAFFIC_TOT_DAY_MON INT No -1 Monthly total traffic volume on Monday TRAFFIC_TOT_DAY_TUE INT No -1 Monthly total traffic volume on Tuesday TRAFFIC_TOT_DAY_WED INT No -1 Monthly total traffic volume on Wednesday TRAFFIC_TOT_DAY_THU INT No -1 Monthly total traffic volume on Thursday TRAFFIC_TOT_DAY_FRI INT No -1 Monthly total traffic volume on Friday TRAFFIC_TOT_DAY_SAT INT No -1 Monthly total traffic volume on Saturday TRAFFIC_TOT_V_CLASS_1 INT NO -1 Total ADT in Vehicle Class 1 (Motorcycles) TRAFFIC_PCT_V_CLASS_1 NUMERIC(5,2) NO -1 Percentage of ADT in Vehicle Class 1 (Motorcycles) TRAFFIC_TOT_V_CLASS_2 INT NO -1 Total ADT in Vehicle Class 2 (Passenger Cars) TRAFFIC_PCT_V_CLASS_2 NUMERIC(5,2) NO -1 Percentage of ADT in Vehicle Class 2 (Passenger Cars) TRAFFIC_TOT_V_CLASS_3 INT NO -1 Total ADT in Vehicle Class 3 (Recreational Vehicles / RV) TRAFFIC_PCT_V_CLASS_3 NUMERIC(5,2) NO -1 Percentage of ADT in Vehicle Class 3 (Recreational Vehicles / RV) TRAFFIC_TOT_V_CLASS_4 INT NO -1 Total ADT in Vehicle Class 4 (Vehicles Pulling Trailers, RV) TRAFFIC_PCT_V_CLASS_4 NUMERIC(5,2) NO -1 Percentage of ADT in Vehicle Class 4 (Vehicles Pulling Trailers, RV) TRAFFIC_TOT_V_CLASS_5 INT NO -1 Total ADT in Vehicle Class 5 (Transit Buses) TRAFFIC_PCT_V_CLASS_5 NUMERIC(5,2) NO -1 Percentage of ADT in Vehicle Class 5 (Transit Buses) TRAFFIC_TOT_V_CLASS_6 INT NO -1 Total ADT in Vehicle Class 6 (Tour Buses) TRAFFIC_PCT_V_CLASS_6 NUMERIC(5,2) NO -1 Percentage of ADT in Vehicle Class 6 (Tour Buses) TRAFFIC_TOT_V_CLASS_7 INT NO -1 Total ADT in Vehicle Class 7 (Light-Duty Trucks) TRAFFIC_PCT_V_CLASS_7 NUMERIC(5,2) NO -1 Percentage of ADT in Vehicle Class 7 (Light-Duty Trucks) TRAFFIC_TOT_V_CLASS_8 INT NO -1 Total ADT in Vehicle Class 8 (Heavy-Duty Trucks) TRAFFIC_PCT_V_CLASS_8 NUMERIC(5,2) NO -1 Percentage of ADT in Vehicle Class 8 (Heavy-Duty Trucks) TRAFFIC_TOT_O_CLASS_1 INT NO -1 Total Vehicles in Vehicle Occupancy Class 1 (1 Single Occupant) TRAFFIC_PCT_O_CLASS_1 NUMERIC(5,2) NO -1 Percentage of Vehicles in Vehicle Occupancy Class 1 (1 Single Occupant) TRAFFIC_TOT_O_CLASS_2 INT NO -1 Total Vehicles in Vehicle Occupancy Class 2 (2 Occupants) TRAFFIC_PCT_O_CLASS_2 NUMERIC(5,2) NO -1 Percentage of Vehicles in Vehicle Occupancy Class 2 (2 Occupants) TRAFFIC_TOT_O_CLASS_3 INT NO -1 Total Vehicles in Vehicle Occupancy Class 3 (3-6 Car Load)

TRAFFIC_PCT_O_CLASS_3 NUMERIC(5,2) NO -1 Percentage of Vehicles in Vehicle Occupancy Class 3 (3-6 Car Load) TRAFFIC_TOT_O_CLASS_4 INT NO -1 Total Vehicles in Vehicle Occupancy Class 4 (More than six(non-Bus vehicle load)) TRAFFIC_PCT_O_CLASS_4 NUMERIC(5,2) NO -1 Percentage of Vehicles in Vehicle Occupancy Class 4 (More than six(non-Bus vehicle load)) TRAFFIC_TOT_O_CLASS_5 INT NO -1 Total Vehicles in Vehicle Occupancy Class 5 (No Passenger Bus) TRAFFIC_PCT_O_CLASS_5 NUMERIC(5,2) NO -1 Percentage of Vehicles in Vehicle Occupancy Class 5 (No Passenger Bus) TRAFFIC_TOT_O_CLASS_6 INT NO -1 Total Vehicles in Vehicle Occupancy Class 6 (Few Passenger Bus) TRAFFIC_PCT_O_CLASS_6 NUMERIC(5,2) NO -1 Percentage of Vehicles in Vehicle Occupancy Class 6 (Few Passenger Bus) TRAFFIC_TOT_O_CLASS_7 INT NO -1 Total Vehicles in Vehicle Occupancy Class 7 (Semi-Loaded Passenger Bus) TRAFFIC_PCT_O_CLASS_7 NUMERIC(5,2) NO -1 Percentage of Vehicles in Vehicle Occupancy Class 7 (Semi-Loaded Passenger Bus) TRAFFIC_TOT_O_CLASS_8 INT NO -1 Total Vehicles in Vehicle Occupancy Class 8 (Fully Loaded Passenger Bus) TRAFFIC_PCT_O_CLASS_8 NUMERIC(5,2) NO -1 Percentage of Vehicles in Vehicle Occupancy Class 8 (Fully Loaded Passenger Bus)

TRAFFIC_DATA_ANNUAL

Table for annual traffic data rolled up daily traffic data.

COLUMN_NAME DATA_TYPE NULLABLDATA_

DEFAULDEPENDENCY DB_COMMENTS

OBJECTID INT No Internal sequence number required by ESRI Geodatabase.

COUNT_STATION_ID NVARCHAR(6) No COUNT_STATIOCount Station ID YEAR_COLL INT No Year of collection TRAFFIC_VOL_TOT INT No -1 Annual total traffic volume SEASONAL_FACTOR NUMERIC(7,4) Yes Seasonal Factor ANNUAL_FACTOR NUMERIC(7,4) Yes Annual Factor AADT INT No -1 Annual Average Daily Traffic SADT INT No -1 Seasonal Average Daily Traffic AAWDT INT No -1 Annual Average Weekday Traffic ATWET INT No -1 Annual Total Weekend Traffic AAWET INT No -1 Annual Average Weekend Traffic

K_FACTOR_AM INT No -1 The percentage of the AADT in both directions during the peak hour (AM). For road with an NPS Functional Classification of VII (Urban Parkway).

K_FACTOR_PM INT No -1 The percentage of the AADT in both directions during the peak hour (PM). For road with an NPS

D_FACTOR_AM INT No -1 The percentage of traffic in the peak direction during the peak hour (AM). For road with an NPS

D_FACTOR_PM INT No -1 The percentage of traffic in the peak direction during the peak hour (PM). For road with an NPS

TRAFFIC_TOT_HOUR_0_1 INT No -1 Total traffic volume during hour from 12 AM to 1 AM TRAFFIC_PCT_HOUR_0_1 NUMERIC(5,2) No -1 Percentage of traffic during hour from 12 AM to 1 AM TRAFFIC_TOT_HOUR_1_2 INT No -1 Total traffic volume during hour from 1AM to 2 AM TRAFFIC_PCT_HOUR_1_2 NUMERIC(5,2) No -1 Percentage of traffic during hour from 1 AM to 2 AM TRAFFIC_TOT_HOUR_2_3 INT No -1 Total traffic volume during hour from 2 AM to 3 AM TRAFFIC_PCT_HOUR_2_3 NUMERIC(5,2) No -1 Percentage of traffic during hour from 2 AM to 3 AM TRAFFIC_TOT_HOUR_3_4 INT No -1 Total traffic volume during hour from 3 AM to 4 AM TRAFFIC_PCT_HOUR_3_4 NUMERIC(5,2) No -1 Percentage of traffic during hour from 3 AM to 4 AM TRAFFIC_TOT_HOUR_4_5 INT No -1 Total traffic volume during hour from 4 AM to 5 AM TRAFFIC_PCT_HOUR_4_5 NUMERIC(5,2) No -1 Percentage of traffic during hour from 4 AM to 5 AM TRAFFIC_TOT_HOUR_5_6 INT No -1 Total traffic volume during hour from 5 AM to 6 AM TRAFFIC_PCT_HOUR_5_6 NUMERIC(5,2) No -1 Percentage of traffic during hour from 5 AM to 6 AM TRAFFIC_TOT_HOUR_6_7 INT No -1 Total traffic volume during hour from 6 AM to 7 AM TRAFFIC_PCT_HOUR_6_7 NUMERIC(5,2) No -1 Percentage of traffic during hour from 6 AM to 7 AM TRAFFIC_TOT_HOUR_7_8 INT No -1 Total traffic volume during hour from 7 AM to 8 AM TRAFFIC_PCT_HOUR_7_8 NUMERIC(5,2) No -1 Percentage of traffic during hour from 7 AM to 8 AM TRAFFIC_TOT_HOUR_8_9 INT No -1 Total traffic volume during hour from 8 AM to 9 AM TRAFFIC_PCT_HOUR_8_9 NUMERIC(5,2) No -1 Percentage of traffic during hour from 8 AM to 9 AM TRAFFIC_TOT_HOUR_9_10 INT No -1 Total traffic volume during hour from 9 AM to 10 AM TRAFFIC_PCT_HOUR_9_10 NUMERIC(5,2) No -1 Percentage of traffic during hour from 9 AM to 10 AM TRAFFIC_TOT_HOUR_10_11 INT No -1 Total traffic volume during hour from 10 AM to 11 AM TRAFFIC_PCT_HOUR_10_11 NUMERIC(5,2) No -1 Percentage of traffic during hour from 10 AM to 11 AM TRAFFIC_TOT_HOUR_11_12 INT No -1 Total traffic volume during hour from 11 AM to 12 PM TRAFFIC_PCT_HOUR_11_12 NUMERIC(5,2) No -1 Percentage of traffic during hour from 11 AM to 12 PM TRAFFIC_TOT_HOUR_12_13 INT No -1 Total traffic volume during hour from 12 PM to 1 PM

TRAFFIC_PCT_HOUR_13_14 NUMERIC(5,2) No -1 Percentage of traffic during hour from 1 PM to 2 PM TRAFFIC_TOT_HOUR_14_15 INT No -1 Total traffic volume during hour from 2 PM to 3 PM TRAFFIC_PCT_HOUR_14_15 NUMERIC(5,2) No -1 Percentage of traffic during hour from 2 PM to 3 PM TRAFFIC_TOT_HOUR_15_16 INT No -1 Total traffic volume during hour from 3 PM to 4 PM TRAFFIC_PCT_HOUR_15_16 NUMERIC(5,2) No -1 Percentage of traffic during hour from 3 PM to 4 PM TRAFFIC_TOT_HOUR_16_17 INT No -1 Total traffic volume during hour from 4 PM to 5 PM TRAFFIC_PCT_HOUR_16_17 NUMERIC(5,2) No -1 Percentage of traffic during hour from 4 PM to 5 PM TRAFFIC_TOT_HOUR_17_18 INT No -1 Total traffic volume during hour from 5 PM to 6 PM TRAFFIC_PCT_HOUR_17_18 NUMERIC(5,2) No -1 Percentage of traffic during hour from 5 PM to 6 PM TRAFFIC_TOT_HOUR_18_19 INT No -1 Total traffic volume during hour from 6 PM to 7 PM TRAFFIC_PCT_HOUR_18_19 NUMERIC(5,2) No -1 Percentage of traffic during hour from 6 PM to 7 PM TRAFFIC_TOT_HOUR_19_20 INT No -1 Total traffic volume during hour from 7 PM to 8 PM TRAFFIC_PCT_HOUR_19_20 NUMERIC(5,2) No -1 Percentage of traffic during hour from 7 PM to 8 PM TRAFFIC_TOT_HOUR_20_21 INT No -1 Total traffic volume during hour from 8 PM to 9 PM TRAFFIC_PCT_HOUR_20_21 NUMERIC(5,2) No -1 Percentage of traffic during hour from 8 PM to 9 PM TRAFFIC_TOT_HOUR_21_22 INT No -1 Total traffic volume during hour from 9 PM to 10 PM TRAFFIC_PCT_HOUR_21_22 NUMERIC(5,2) No -1 Percentage of traffic during hour from 9 PM to 10 PM TRAFFIC_TOT_HOUR_22_23 INT No -1 Total traffic volume during hour from 10 PM to 11 PM TRAFFIC_PCT_HOUR_22_23 NUMERIC(5,2) No -1 Percentage of traffic during hour from 10 PM to 11 PM TRAFFIC_TOT_HOUR_23_24 INT No -1 Total traffic volume during hour from 11 PM to 12 AM TRAFFIC_PCT_HOUR_23_24 NUMERIC(5,2) No -1 Percentage of traffic during hour from 11 PM to 12 AM TRAFFIC_TOT_V_CLASS_1 INT NO -1 Total ADT in Vehicle Class 1 (Motorcycles) TRAFFIC_PCT_V_CLASS_1 NUMERIC(5,2) NO -1 Percentage of ADT in Vehicle Class 1 (Motorcycles) TRAFFIC_TOT_V_CLASS_2 INT NO -1 Total ADT in Vehicle Class 2 (Passenger Cars) TRAFFIC_PCT_V_CLASS_2 NUMERIC(5,2) NO -1 Percentage of ADT in Vehicle Class 2 (Passenger Cars) TRAFFIC_TOT_V_CLASS_3 INT NO -1 Total ADT in Vehicle Class 3 (Recreational Vehicles / RV) TRAFFIC_PCT_V_CLASS_3 NUMERIC(5,2) NO -1 Percentage of ADT in Vehicle Class 3 (Recreational Vehicles / RV) TRAFFIC_TOT_V_CLASS_4 INT NO -1 Total ADT in Vehicle Class 4 (Vehicles Pulling Trailers, RV) TRAFFIC_PCT_V_CLASS_4 NUMERIC(5,2) NO -1 Percentage of ADT in Vehicle Class 4 (Vehicles Pulling Trailers, RV) TRAFFIC_TOT_V_CLASS_5 INT NO -1 Total ADT in Vehicle Class 5 (Transit Buses) TRAFFIC_PCT_V_CLASS_5 NUMERIC(5,2) NO -1 Percentage of ADT in Vehicle Class 5 (Transit Buses) TRAFFIC_TOT_V_CLASS_6 INT NO -1 Total ADT in Vehicle Class 6 (Tour Buses) TRAFFIC_PCT_V_CLASS_6 NUMERIC(5,2) NO -1 Percentage of ADT in Vehicle Class 6 (Tour Buses) TRAFFIC_TOT_V_CLASS_7 INT NO -1 Total ADT in Vehicle Class 7 (Light-Duty Trucks) TRAFFIC_PCT_V_CLASS_7 NUMERIC(5,2) NO -1 Percentage of ADT in Vehicle Class 7 (Light-Duty Trucks) TRAFFIC_TOT_V_CLASS_8 INT NO -1 Total ADT in Vehicle Class 8 (Heavy-Duty Trucks) TRAFFIC_PCT_V_CLASS_8 NUMERIC(5,2) NO -1 Percentage of ADT in Vehicle Class 8 (Heavy-Duty Trucks) TRAFFIC_TOT_O_CLASS_1 INT NO -1 Total Vehicles in Vehicle Occupancy Class 1 (1 Single Occupant) TRAFFIC_PCT_O_CLASS_1 NUMERIC(5,2) NO -1 Percentage of Vehicles in Vehicle Occupancy Class 1 (1 Single Occupant) TRAFFIC_TOT_O_CLASS_2 INT NO -1 Total Vehicles in Vehicle Occupancy Class 2 (2 Occupants) TRAFFIC_PCT_O_CLASS_2 NUMERIC(5,2) NO -1 Percentage of Vehicles in Vehicle Occupancy Class 2 (2 Occupants) TRAFFIC_TOT_O_CLASS_3 INT NO -1 Total Vehicles in Vehicle Occupancy Class 3 (3-6 Car Load) TRAFFIC_PCT_O_CLASS_3 NUMERIC(5,2) NO -1 Percentage of Vehicles in Vehicle Occupancy Class 3 (3-6 Car Load) TRAFFIC_TOT_O_CLASS_4 INT NO -1 Total Vehicles in Vehicle Occupancy Class 4 (More than six(non-Bus vehicle load)) TRAFFIC_PCT_O_CLASS_4 NUMERIC(5,2) NO -1 Percentage of Vehicles in Vehicle Occupancy Class 4 (More than six(non-Bus vehicle load)) TRAFFIC_TOT_O_CLASS_5 INT NO -1 Total Vehicles in Vehicle Occupancy Class 5 (No Passenger Bus) TRAFFIC_PCT_O_CLASS_5 NUMERIC(5,2) NO -1 Percentage of Vehicles in Vehicle Occupancy Class 5 (No Passenger Bus)

TRAFFIC_TOT_O_CLASS_6 INT NO -1 Total Vehicles in Vehicle Occupancy Class 6 (Few Passenger Bus) TRAFFIC_PCT_O_CLASS_6 NUMERIC(5,2) NO -1 Percentage of Vehicles in Vehicle Occupancy Class 6 (Few Passenger Bus) TRAFFIC_TOT_O_CLASS_7 INT NO -1 Total Vehicles in Vehicle Occupancy Class 7 (Semi-Loaded Passenger Bus) TRAFFIC_PCT_O_CLASS_7 NUMERIC(5,2) NO -1 Percentage of Vehicles in Vehicle Occupancy Class 7 (Semi-Loaded Passenger Bus) TRAFFIC_TOT_O_CLASS_8 INT NO -1 Total Vehicles in Vehicle Occupancy Class 8 (Fully Loaded Passenger Bus) TRAFFIC_PCT_O_CLASS_8 NUMERIC(5,2) NO -1 Percentage of Vehicles in Vehicle Occupancy Class 8 (Fully Loaded Passenger Bus) REMARKS NVARCHAR(MAX Yes General comments

SCR_TABLE_LIST

List and type of the tables in the database

DEFAULT DEPENDENCY COMMENTS REMARKS

OBJECTID INT No Internal sequential number required by ESRI Geodatabase.

TABLE_NAME NVARCHAR(35) No Name of the table MAIN_TABLE NUMERIC(1,0) No 0 Is it a main table REF_TABLE NUMERIC(1,0) No 0 Is it a reference or lookup table

US_STATES

Reference table for all US States. Traffic data will be available only for 21 states

DEFAULT DEPENDENCY DB_COMMENTS

OBJECTID INT No Internal sequence number required by ESRI Geodatabase.

STATE NVARCHAR(2) No State 2 letter abbreviation STATE_FIPS NVARCHAR2(2) No State Federal Information Processing Series (FIPS) code STATE_ANSI NVARCHAR(8) Yes State ANSI code STATE_NAME NVARCHAR(60) No State name

NPS_REGIONS

Table of all NPS Regions

NPS_REGION NVARCHAR(25) No The NPS Region in which the Park resides.

COUNT_STATION_TYPES

Types of count station

OBJECTID INT No Internal sequence number required by ESRI Geodatabase.

COUNT_STATION_TYPE NVARCHAR(10) No Type of Count Station DESCRIPTION NVARCHAR(255) No Description of Count Station

OBJECTID COUNT_STN_TYPE DESCRIPTION

1 COVERAGE

2 CONTINUOUS

ALL_TABLES

PARK_TOTALS

ROUTE_ID

COUNT_STATIONS

COUNT_STATION_MAINT

COUNT_STATION_EXT_FACTORS

TRAFFIC_SEGMENTS

TRAFFIC_DATA_DAILY

TRAFFIC_DATA_MONTHLY

TRAFFIC_DATA_ANNUAL

SCR_TABLE_LIST

US_STATES

NPS_REGIONS

COUNT_STATION_TYPES

image2.emf

NPS PARKS

Table of all NPS Parks, with roll up summary of traffic data.

DDEPENDENCY DB_COMMENTS REMARKS

OBJECTID INT No Internal sequence number required by ESRI Geodatabase.

PARK_ALPHA NVARCHAR(4) No Unique four letter code assigned to NPS unit.

PARK_NAME NVARCHAR(150) No Full name of park Will come from NPS_DATA as VIEW PARK_NO NVARCHAR(15) No Park Number Will come from NPS_DATA as VIEW GROUP_ALPHA NVARCHAR(4) Four letter code assigned to NPS units under the same management.

PARK_ALPHA equals GROUP_ALPHA when a park is managed by itself.

Will come from NPS_DATA as VIEW

NPS_REGION

NVARCHAR(25) NPS_REGIONS

The Region in which the Park resides. Please see the NPS_REGIONS table for a listing of the possible NPS_REGION values. Will come from NPS_DATA as VIEW

STATE1 NVARCHAR(2) No US_STATES State (abbreviation) in which the park resides Will come from NPS_DATA as VIEW STATE2 NVARCHAR(2) Yes US_STATES Additional state (abbreviation) in which park resides Will come from NPS_DATA as VIEW STATE3 NVARCHAR(2) Yes US_STATES Additional state (abbreviation) in which park resides TOT_COUNT_STNS SMALLINT No 0 Total number of traffic count stations in park FIRST_DATE_COLL DATETIME2(7) First date for which traffic data is available

LAST_DATE_COLL DATETIME2(7)

Last data for which traffic data is available. This should be updated when data is collected for the park

PARK_TOTALS

image3.emf

ROUTE_ID

Table of all routes in the NPS parks, with roll up summary for each route.

DEFAULT DEPENDEN DB_COMMENTS REMARKS

OBJECTID INT No Internal sequence number required by ESRI Geodatabase.

ROUTE_ID NVARCHAR(15) No ROUTE_ID Unique record identifier: PARK_ALPHA – RTE_NO To be provided by RIP RIP_CYCLE BIT No RIP cycle for which data is being used. This required for picking up right RIP routes when used in onjuction with this data as MP can vary. To be provided by RIP PARK_ALPHA NVARCHAR(4) No PARK_TOTALS Unique four letter code assigned to NPS unit. To be provided by RIP RTE_NO NVARCHAR(7) No FHWA-RIP Route Number To be provided by RIP RTE_NAME NVARCHAR(100) Yes NPS Route Name To be provided by RIP FROM_DESC NVARCHAR(255) Yes Description of beginning point of route. To be provided by RIP TO_DESC NVARCHAR(255) Yes Description of end point of route. To be provided by RIP

INSP_DATE DATE Yes Data Collection Date: Final collection date for DCV routes. Field condition assessment date for all manually rated routes. To be provided by RIP

FUNCT_CLASS SMALLINT Yes Functional Classification (1-8) To be provided by RIP USER_ACCESS NVARCHAR(10) Yes Access Designation for ALL ROUTES (Public or Nonpublic). To be provided by RIP STATE1 NVARCHAR2(2) No US_STATES State abbreviation in which route resides To be provided by RIP STATE2 NVARCHAR(2) Yes US_STATES Additional state in which route resides To be provided by RIP STATE3 NVARCHAR2(2) Yes US_STATES Additional state in which route resides To be provided by RIP

FMSS_NO NVARCHAR2(15) Yes NPS Facility Management Software System (FMSS) Asset number associated with Route. To be provided by RIP

M_DISTRICT NVARCHAR2(50) Yes Park Maintenance District in which Route resides. To be provided by RIP

CONCESSION BIT No 0 Concession Flag: Value of 1 indicates that NPS Route is maintained by Concessionaire. Default: 0. Flag: 1 (Concession route). To be provided by RIP