Border Data Warehouse 3.0

This project upgraded the existing border wait time data warehouse located at www.borderdata.org. The database stores passenger and commercial vehicle wait times, volume, and other data relating to vehicle traffic at the four Cascade Gateway land ports-of-entry. This project built on investments made in 2016 to integrate U.S. Customs & Border Protection’s (CBP) booth status data system into southbound wait time calculations for border travelers.

Location

The project is software development and online, but includes data from the four primary land ports-of-entry between the Lower Mainland of British Columbia and Whatcom County, Washington (Peace Arch/Douglas, Pacific Highway, Lynden/Aldergrove, and Sumas/Abbotsford-Huntingdon ports).

Why This Project Was Needed

Since the Cascade Gateway Border Data Warehouse’s inception in 2007 several challenges have developed that need to be addressed:

• Data accuracy: because the current version doesn’t use the newly available booth status data from CBP, it is erroneously archiving wait time for NEXUS and non-NEXUS passenger cars.
• Increasing hosting expense: because the booth status data is archived in raw format (a data field for every car) and not binned, it is growing much faster than before and costing more to host.
• Loss of U.S. Bureau of Transportation Statistics (BTS) compatibility: since the BTS converted its transborder freight data query tool to Tableau it is no longer possible for the warehouse to “screen scrape” relevant regional data for local queries.
• End of maintenance funding: this project is the only ongoing archive of five-minute increment border wait times going back to 2007. Keeping this resource maintained and hosted is very important to regional stakeholders.
• No way to access to new datasets: the booth status data and archived commercial wait times are not currently accessible on the existing website, even though the data are being saved.

Results

The project resulted in an upgraded database with improved efficiencies, access to new datasets, improved data visualizations and reporting functionality, and a more affordable maintenance structure.

Cost

$200,000 (USD)

Funding & Partnerships

• U.S. Federal Highway Administration: $100,000
• B.C. Ministry of Transportation & Infrastructure: $50,000
• Whatcom Council of Governments: $38,000
• Transport Canada: $12,000