Updating the Recreational Trails Program database using AI

“Flexion was a great partner to work with—strong technical capability, but always humble in their approach. They just got things done quickly. The update that Flexion delivered brought measurable value to the trails community and sets us up well for what’s ahead.”

— Mike Passo, Executive Director, American Trails

Challenge

The Recreational Trails Program (RTP) provides federal funding to states for trail construction, restoration, and maintenance projects across the United States. Data about these projects is used by planners, policymakers, trail organizations, and citizens to understand how federal investments are supporting outdoor recreation infrastructure.

However, the Recreational Trails database had not been updated since 2021 due to the absence of an active contract supporting ongoing updates. As a result, the public-facing database no longer reflected recent trail funding activity.

This created several challenges:

• Outdated information on federally funded trail projects caused limited visibility into recent trail investments across states
• Manual and time-consuming processes were required to update project data
• Trail organizations and other stakeholders had difficulty generating reports

American Trails and the broader trails community rely on this data to support planning, transparency, and communication about the impact of federal trail funding. Without updated information, it became increasingly difficult to tell the full story of how RTP investments were supporting outdoor recreation nationwide.

Approach

Flexion worked with American Trails to perform a tactical refresh of the Recreational Trails Database by leveraging alternative federal transportation datasets and developing automated data processing and reporting workflows.

Identifying alternative data sources

Because updated RTP data was unavailable, Flexion worked with American Trails to identify Transportation Alternatives (TA) program datasets from the Federal Highway Administration as a viable proxy to approximate recent trail-related funding activity.

These datasets contain detailed information about transportation and trail-related investments across the United States, but are delivered in large, complex spreadsheets that require transformation before they can be used within the RTP database.

Data processing and normalization

Flexion developed Python-based automation scripts to extract and transform relevant project data from the 2022 and 2023 TA datasets.

The scripts were used to:

• Extract trail-related project records by filtering Transportation Alternatives datasets, isolating trail-related projects from a broader set of transportation investments using defined filtering criteria and business rules
• Normalize fields such as funding amounts, project descriptions, and locations, including resolving inconsistencies in geolocation data (e.g., varying formats and incomplete records) by applying business rules to standardize formats across datasets
• Align data fields with the existing Recreational Trails database schema
• Prepare records for integration into the national trails database

AI-assisted development tools, including ChatGPT and ClaudeCode, were used to accelerate the creation and refinement of the automation routines. Throughout this process, Flexion and American Trails applied domain-informed business rules, validation logic, and human inspection to ensure data accuracy, while enabling built-in data quality checks and the ability to quickly re-run processing as new Transportation Alternatives data becomes available.

Database update and reporting automation

Once the database was updated with the 2022 and 2023 project data, Flexion developed additional scripts to automatically generate state-level reports based on the refreshed dataset.

These reports allow American Trails to distribute updated trail investment information to stakeholders, including state agencies, trail organizations, and community partners. The refreshed database is available at: https://recreationaltrailsinfo.org/

Outcomes

The project successfully refreshed the Recreational Trails Database with the most recent available project data, improving access to national trail investment information.

Key outcomes included:

• Updated database records using 2022–2023 federal transportation data
• Development of reusable, automated Python scripts for processing and integrating large transportation datasets
• Creation of automated state-level reports that American Trails can distribute to stakeholders
• Reduction of manual effort, with the automated approach eliminating an estimated two weeks of manual data processing based on prior manual update efforts
• Improved transparency into how federal trail funding is supporting projects nationwide

The reusable automation framework extends beyond this initial tactical refresh, enabling American Trails to efficiently process and integrate future Transportation Alternatives datasets as they become available.

The updated dataset provides planners, trail organizations, and policymakers with a more accurate and current view of trail infrastructure investments across the United States.

Why this matters

The Recreational Trails Program is one of the primary federal funding sources supporting trail infrastructure across the country. RTP investments help build and maintain thousands of miles of trails used for hiking, biking, snowmobiling, horseback riding, and other recreational activities.

Accurate and current data helps stakeholders:

• Understand how federal funds are being distributed across states
• Identify opportunities for future trail development and investment
• Communicate the economic and recreational impact of trail systems
• Support planning efforts that improve public access to outdoor recreation

Trail systems also play an important role in local and regional economies, driving tourism, supporting outdoor recreation businesses, and improving the quality of life for communities.

By updating the database and creating automated reporting tools, this effort restored visibility into recent trail investments and provided a stronger foundation for data-driven planning, public transparency, and advocacy for outdoor recreation infrastructure.