Transcript Border Data Warehouse - Hugh Conroy (WHATCOM)

Border Data
Warehouse
The
Cascade Gateway
Variable Message Signs
Southbound
Northbound
CascadeGatewayData.com
Volume
Departure rate
Delay
Queue length (meters)
Vehicles in queue
Project product
BWT border wide interface
• Nationally summarized wait time data
• Portal to regional archives
Cascade Gateway
data warehouse
• Standard format wait time
data
• Other, regionally available
border data
Existing
Niagara region
data warehouse
Border region data
warehouse 3
Border region data
warehouse 4, etc.
• Standard format wait time
data
• Other, regionally available
border data
• Standard format wait time
data
• Other, regionally available
border data
• Standard format wait time
data
• Other, regionally available
border data
Project product
Future
Future
Simplified comparison of Cascade Gateway ATIS and Niagara BWT schema
Cascade Gateway
•
•
•
•
•
•
•
•
•
•
•
•
•
•
•
•
•
•
•
•
Organization (WSDOT or BC MoT)
Data collection period
Organizational identifier
Detector report
Crossing (crossing & traffic type)
• Crossing ID
• Status (open, closed)
• Lanes (fixed number of lanes)
• Link direction
• Delay
• Open lanes
• Queue length
• Vehicles in queue
• Service rate (vehicles per hour)
Date
Lane data (actually loop data?)
Detector lane number
Detector ID
Detector vehicle speed
Detector vehicle count
Detector occupancy
Detector average-vehicle length
Detector start time
Detector end-time
Link measurement duration
VDS identifier (actually loop IDs)
VDS name (loop names)
Link direction
ATC error code
Niagara
• Network status
• Lane (Traveler, NEXUS, Commercial, FAST)
• Port of Entry code
• System status (operational, down)
• Time/date of last update
• Delay
• Wait time event
• System readiness
Border-wide BWT system data elements for highlevel schema
• Location (port name)
• Direction (to U.S., to Canada)
• Traffic type (Passenger, NEXUS, Commercial,
FAST)
• Time unit associated with records
• System status (operational, down)
• CURRENT delay of cars now arriving at port
• ACTUAL delay of cars now departing port
Task category
1. Establish project advisory
committee
2. Identify system component
owners
3. Document BWT systems
data
4. User needs survey, & BIFA
integration
5. Site visit, systems
requirements, agreements
6. Scope for developer
7. Consultant engagement and
project management
8. System maintenance
strategy and plan
9. Project report & developer
documetation
2012
2013
Sep Oct Nov Dec Jan Feb Mar Apr May Jun
Jul Aug Sep
Sample of System Requirements
• The custom query shall result in multiple output types
including data table, chart, and downloadable file.
• The custom query tool shall allow users to select date
ranges and days of the week.
• The custom query tool shall allow users to group by
month, day of week, day, am/pm, hourly, or by five minute
increment.
• The custom query tool shall allow users to query multiple
detectors or crossings at a time.
Over 100 requirements were identified for Cascade
Gateway Border Data Warehouse