Case Study
Sophisticated & Efficient Mapping
Functionality for 2.5Tb of Complex OLAP Data
Census Transportation Planning Products (CTPP) is a special tabulation of the American Community Survey operated by the United States Census. CTPP data comprises commuter data, such as home and work locations, journey-to-work travel flows, demographic characteristics and employment data. These data are analyzed for a variety of state, regional, and local transportation policy and planning efforts. It also supports corridor and project studies, envi-ronmental analyses, and emergency operations management. AASHTO, the American Association of State Highway and Transportation Officials, was instrumental in having the special tabulation created. “All states contribute to it. What this data set has that you can’t get in the standard tabulation is commuter flow information, both related to commuter residence and workplace,” explains Penelope Weinberger, Census Transportation Planning Products Program Manager, at AASHTO.
Beyond 20/20 was commissioned by AASHTO to build a
web-based data access system that would allow transporta-tion planners to interact with CTPP data, perform statistical calculations, build custom aggregations, view charts and maps, and more. For the mapping functions, Beyond 20/20 teamed with DBx GEOMATICS to provide a sophisticated yet efficient and exceptionally intuitive tool that is helping traffic planners to better visualize and communicate the messages contained inside very complex data.
Capturing America’s
Commuting Experience
CTPP data is used by state Departments of Transportation and Metropolitan Planning Organizations, as well as students, universities, and consultants. The general public also has access to the data and to the mapping (Figure 1), although, as Weinberger points out, it’s not designed for play: “You do have to have a plan for how you are going to use the tool and what you want to analyze.”
Figure 1: One of the many commuter-flow tables of data accessible via the CTPP user/public interface, driven by Beyond 20/20 Perspective software.
Beyond 20/20 and DBx GEOMATICS worked together to provide America’s transportation planners with a sophisticated mapping experience for users who analyze the complex data of Census Transportation Planning Products.
Sophisticated & Efficient Mapping Functionality
for 2.5Tb of Complex OLAP Data
While large transportation agencies have their own mapping software and download CTPP data into their own systems, small agencies like smaller and medium sized Metropolitan Planning Organizations don’t have the same resources. “This is a real benefit for them to be able to have access to CTPP data through Perspective and to be able to visually map their analyses in the same tool,” Weinberger explains. “They can produce the same kind of reports and presentations that big agencies do.”
A common use for the maps is when transport planners give presentations at public meetings or to executive directors – audiences who are not necessarily versed in data or analysis, or who may be more readily moved to action by compelling images. A few examples of when the data may be presented to the public are:
To demonstrate traffic congestion problems and
graphi-cally illustrate how many people are coming from and going to a certain place at a certain time of day.
To show the demography of an area, such as the density
of people living in poverty which may give clues about public transportation planning.
In an air quality analysis, to show what industries, such
as factories, forestry, heavy industry are in a particular area and how that relates to commuter traffic.
The Multi-Dimensional Mapping Challenge
The data set that underlies these types of analyses and visualizations is necessarily vast and complex, providing commuter flow information for the entire United States from state-level down to quite specific geographic regions.
Beyond 20/20 enlisted the web-based mapping experts at DBx GEOMATICS for an efficient solution that would deliver attractive visual results quickly to users. By bringing together the skills of the two companies, the team was able to overcome three major challenges: the complexity of the geography dimensions, working with flow data, and the multi-dimensional nature of the data.
Beyond 20/20 provided expertise with complex relationships in the multi-dimensional data set and DBx GEOMATICS provided expertise in the mapping and the user experience.
“The CTPP source data is contained in more
than 15,000 separate files and over 300 tables
grouped by state,” explains Chris Bonyun,
CEO at Beyond 20/20. “CTPP represents 2.5
TeraBytes of data. This is exactly the kind of
Solution Overview
The new mapping solution integrates the Beyond 20/20 Perspective product, DBx GEOMATICS’ CartoVista web mapping software, and Microsoft SQL Server 2012 Analysis Services in a Microsoft .NET envi-ronment (Figure 3).
“This was one of the most complex data mapping solutions that we have imple-mented,” confirms Dany Bouchard, President at DBx GEOMATICS. “Not just because of the volume of the data, but because of the number of options a user has for how to ana-lyze it. Perspective provides a vast array of options to view the data through calculations, aggregations, and by integrating data from multiple dimensions. In addition to that, our CartoVista product provides a high level of flexibility for the end user. So, empowering the user to navigate efficiently anywhere in the U.S. while letting them fully customize the views and analysis was our biggest chal-lenge for this project.”
Figure 3: The solution architecture
Figure 2: An example traffic-flow analysis rendered in the new mapping tool.
Web Server
(Microsoft .NET)
Web BROWSER
Microsoft
Analysis Services
CTPP DataCubes (Vector Map Features)Geographic Data Javascript Map Tiles (Base Map)
Microsoft
SQL Server 2012
Map
Cache
CartoVista 4
Perspective 6
Mapping Flow Data
Traffic flow data, which is a critical element of the CTPP data set, presented another challenge. To create the flow data, two geography dimensions are included in the tables: Residence and Workplace. Data can then be shown for com-muters who live in one neighborhood and work in another, such as means of transportation and commuting time. The flows are further complicated because some are asymme-trical in nature. An example of this is a table in which the residences are aggregated to the “County” level and the workplace to the “Place” level.
DBx extended CartoVista functionality to provide multiple ways to visualize flow data. These include the ability to create thematic maps of residences that all flow into a single workplace, and thematic maps for workplaces that flow from a single residence. Flows can also be displayed on the map by animated “flow arrows” that show values from a target residence or flow values to a target workplace (Figure 2). The size and color of these arrows are adjusted based on the data being mapped.
Complex Geographic Data
In addition to the sheer volume of commuter data to be mapped, the geographic hierarchies are non-linear. Many levels of geography in the CTPP have orthogonal rela-tionships and cannot be organized in neat hierarchies. A few examples include:
A State is divided into multiple “Public Use MicroData
Areas” (PUMAs), Counties, and Places, but these three levels have no direct relationships to each other. A County can span multiple PUMAs; and Places can reside within a single County or may span several.
Counties and PUMAs are contiguous (every location
within a State is within a single County and within a single PUMA), but Places are discrete and do not necessarily aggregate to cover the entire State.
Counties, in turn, are subdivided into Minor Civil
Divisions (MCD) (in 12 states), and TAZ, which are also independent of each other (Figure 4).
Figure 4: Showing MCDs on the United States map, just one of many levels of CTPP data that Perspective makes accessible to users, and CartoVista can render visually.
Mapping Multidimensional Data
Most of the CTPP tables contain cross-tabulations of mul-tiple variables – such as the time commuters leave home and their mode of transportation – which needed to be represented on a single map. To address this, the CartoVista software was customized with a new capability: the ability to define indicators based on selections on multiple dimensions. All dimensions from the table being mapped are presented to the user, and these can be used to generate thematic maps and/or pie charts for each of the selected geographies. In this way the multi-dimensional nature of the data can be exposed on the map without undue complexity (Figure 5).
Map Rendering of Complex Data
To ensure the mapping functionality would communicate efficiently with the data and cubes despite these com-plexities, DBx GEOMATICS relied on CartoVista’s native architecture.
“Our solution is structured in such a way that we are able to focus on the business data – in this case, the CTPP geo-graphies of interest – and not have the server tied up with rendering the base map. The server power is reserved for traffic analysis,” Bouchard explains.
The base map – which includes geographic details down to the street level – uses a tile cache approach, taking that strain off of the server. The application base map is carto-graphically designed to provide maximum readability of thematic and base map information together.
Users can now use quite advanced tools to control and cus-tomize the data displayed in their analyses. This includes the ability to render multiple dimensions on a single map and to use colour palates, flow arrows and other symbols, and charts layered on top of maps (Figure 5).
Sophisticated & Efficient Mapping Functionality
for 2.5Tb of Complex OLAP Data
Figure 5: An example of advanced thematic analysis of two simultaneous dimensions, using colour, symbols, and charts.
Fast & Friendly Advanced Mapping
Functions
The CartoVista mapping functionality integrates seamlessly with the Perspective software. “It’s easy for users to go back and forth between data selection, table views, and maps,” Bouchard says.
“No matter how complex the data is, or how much analysis the user performs,” Bonyun confirms, “you get very fast visual results in the form of maps that can be customized with color coding, symbols, and charts. It has been very easy to work with DBx and we look forward to our next project together.”
Weinberger also notes that “The mapping solution is consis-tent with similar types of GIS products, and that’s important – it’s recognizable. It’s good, solid mapping software that works and functions like similar products out there.”
Beyond 20/20 Inc.
265 Carling Ave., Suite 500
Ottawa, Ontario, Canada K1S 2E1
Toll free in North America: 1-888-TRY-2020 phone: 613-563-3993
fax: 613-563-7233
email: [email protected]
www.beyond2020.com
DBx GEOMATICS Inc.
37 St-Joseph Boulevard, Second Floor Gatineau, Quebec, Canada J8Y 3V8 Toll free in North America: 1 866 772 2660 phone: 819 772 2000
fax: 819 772 2001
email: [email protected]
www.dbxgeomatics.com
“Everyone was using our data without the maps, but now that
they have the maps, they love them.”
Penelope Weinberger
Census Transportation Planning Products Program Manager, at AASHTO.
