The Census Transportation Planning Products (CTPP) is a special tabulation of the American Community Survey, operated by the US Census. It contains valuable data comprising home and work locations as well as journey to work travel flows, including demographic characteristics, for a variety of state, regional and local transportation policy and planning efforts. CTPP data also supports corridor and project studies, environmental analyses, and emergency operations management. The source data is contained in more than 15,000 separate files and over 300 tables, grouped by state. Once loaded in a database, CTPP represents 2.5 TeraBytes of data.
Beyond 20/20 Inc. was commissioned by AASHTO to build a web-based data access system that would allow users to interact with the data, perform statistical calculations, build custom aggregations, view charts and maps, and download specific data sets originating from multiple files.
• The sheer volume of data and possible combinations presented a significant challenge in data loading and processing efficiency
• Traffic and geographic data is and non-linear, and cannot be presented to users in a neat hierarchy
• Data flow volumes are substantial
• Data flows needed to accommodate the potential for empty cells (queries with no returning data)
• Both real and fabricated data (to protect individual privacy) are present in the input data and are stored separately
Beyond 20/20 Solution:
• Our solution enables statisticians to model commuter activity across the United States with the goal of identifying patterns that can be used when planning highways
• We made expert use of data cubes, SQL Server, and SQL Analysis Services to manage numerous levels and combinations of non-linear data (a total of 600 cubes)
• We integrated DBx GEOMATICS Inc.’s mapping solution, CartoVista into the OLAP engine to provide interactive mapping components
• The solution provides data scientists with easy-to-use features and timely access to data, including thematic analysis on any CTPP variable
The general public and transportation planners alike now enjoy access to all of this data through an easy-to-use interface, which can be found here.