GEOG 6150 Geospatial Big Data
Utah Mountain Biking GIS Database

Background & Introduction

Maps and spatial data serve as essential tools for both facilitating access and supporting conservation efforts in outdoor recreation areas. The increasing adoption of geospatial technologies has transformed traditional cartography into interactive digital experiences, with platforms like Trailforks and MTB Project utilizing geolocation features to extend trail maps' functionality. These applications provide access to over 100,000 miles of trails and enable real-time location services via GNSS.

Figure 1: Mountain biking trails in Utah

Utah is widely recognized for its mountain biking opportunities (Figure 1), with areas like Moab attracting visitors from around the world. The state's diverse terrain and extensive trail networks make it an ideal location for a comprehensive geospatial database focused on mountain biking infrastructure. Land management agencies, including the United States Forest Service (USFS) and Bureau of Land Management (BLM), maintain extensive recreation databases and conduct regular visitor forecasting. In Utah, these agencies have a particularly strong management presence due to high visitation rates.

While existing applications excel at trail discovery and real-time location services, there remains significant potential for enhanced functionality, particularly in comprehensive trip planning. Critical information such as bike service facilities, campground locations, shower facilities, and emergency medical services are often absent or difficult to integrate. The Utah Mountain Biking GIS Database project addresses this gap by creating a comprehensive spatial database that integrates multiple data sources to support advanced trip planning and resource management for mountain biking in Utah.

Methodology

The database design process began with the development of an Entity-Relationship (ER) diagram to establish the foundational relationships between key spatial and attribute data (Figure 2). This conceptual model guided the identification and acquisition of relevant geospatial datasets.

Figure 2: Entity-Relationship diagram for the Utah Mountain Biking GIS Database

Primary geospatial datasets were acquired from the Utah Geospatial Resource Center (UGRC), including shapefiles for trails, ski areas, trailheads, emergency medical services, landmarks, and features. Administrative boundary data (state, county, and city) was sourced from the USDA:NRCS Geospatial Data Gateway. Supplementary information for bike shops and shower facilities was compiled using Google Maps and stored in CSV format with latitude and longitude coordinates.

Data processing and integration were performed using QGIS and PostgreSQL/PostGIS. A connection was established between QGIS and the PostgreSQL database via pgAdmin (Figure 3). All spatial data was transformed to a consistent coordinate reference system (NAD83 UTM 12N) and indexed for optimized query performance. The following processing steps were performed:

Imported shapefiles to PostgreSQL/PostGIS schema
Converted and imported CSV data with point geometries
Created spatial indexes for all tables
Generated derived tables through spatial queries
Created filtered tables for bike-accessible trails
Identified trailheads within 1.5 km of trails
Calculated proximity to emergency medical services
Exported processed tables back to QGIS for verification

Figure 3: Software interface screenshots showing database implementation

A key aspect of the database implementation involved SQL development for spatial analysis and data integration. The following sample SQL code demonstrates the creation of a table containing trailheads within 1.5 km of bike-accessible trails, with the addition of information about the nearest emergency medical service:

-- Create table of trailheads that are within 1500m of a trail
CREATE TABLE utah_mtb.accessible_trailheads AS
SELECT DISTINCT ON(th.id) th.*
FROM utah_mtb.trailheads th
JOIN utah_mtb.bike_trails_allowed bt
ON ST_DWithin(th.geom, bt.geom, 1500);

-- Add closest EMS facility to trailhead
ALTER TABLE utah_mtb.accessible_trailheads
ADD COLUMN closest_ems VARCHAR(100);

UPDATE utah_mtb.accessible_trailheads th
SET closest_ems = (
    SELECT ems.name
    FROM utah_mtb.emergency_medical_services ems
    ORDER BY th.geom <-> ems.geom
    LIMIT 1
);

The database was queried to identify critical information for mountain bike trip planning, such as counties with the highest density of bike trails and trailheads with the greatest distance to emergency services.

Results

The integration of multiple geospatial datasets resulted in a comprehensive database that supports advanced spatial queries relevant to mountain bike trip planning and resource management. Analysis of trail distribution revealed the counties with the highest density of mountain biking trails, providing valuable information for both visitors and land managers.

From a safety perspective, the database identified trailheads that were furthest from emergency medical service providers, with Western Rim, East Forks Black Fork, and Piute Pass trailheads being the most remote in terms of emergency service access (Figure 4). This information is particularly valuable for trip safety planning and could inform future emergency service planning.

Figure 4: The three trailheads farthest from a Utah EMS provider

The integration of additional facilities data (bike shops, shower facilities, campgrounds) with trail and trailhead information creates a unified resource for comprehensive trip planning. The relational database structure supports complex queries that can answer questions such as:

Which counties contain the most bike-only trails?
What trailheads are accessible and within 5 km of shower facilities?
Where are campgrounds located within a reasonable distance of multiple trail systems?
What is the closest emergency medical service to a specific trailhead?

Sample SQL query results demonstrate the analytical capabilities of the database:

-- Counties with most bike trails
SELECT c.countyname, SUM(ST_Length(b.geom))/1000 AS total_trail_km
FROM utah_mtb.counties c
JOIN utah_mtb.bike_trails_allowed b ON ST_Intersects(c.geom, b.geom)
GROUP BY c.countyname
ORDER BY total_trail_km DESC
LIMIT 3;

-- Counties with most bike-only trails
SELECT c.countyname, SUM(ST_Length(b.geom))/1000 AS total_trail_km
FROM utah_mtb.counties c
JOIN utah_mtb.bike_only_trails b ON ST_Intersects(c.geom, b.geom)
GROUP BY c.countyname
ORDER BY total_trail_km DESC
LIMIT 3;

-- Trailheads furthest from EMS facilities
SELECT t.primarynam, MAX(ST_Distance(e.geom, t.geom))/1000 AS max_distance
FROM utah_mtb.accessible_trailheads t
JOIN utah_mtb.emergency_medical_services e ON t.closest_ems = e.name
GROUP BY t.primarynam
ORDER BY max_distance DESC
LIMIT 3;

Discussion & Conclusion

The Utah Mountain Biking GIS Database demonstrates the power of integrating multiple geospatial datasets into a comprehensive resource for outdoor recreation planning. By combining trail data with critical infrastructure information such as emergency services, bike shops, and facilities, the database provides value for multiple stakeholder groups: recreationists planning trips, land managers optimizing resource allocation, and emergency service providers assessing coverage areas.

The combined use of QGIS and PostgreSQL/PostGIS proved to be an effective approach, leveraging the visualization capabilities of QGIS with the robust spatial analysis and database management functions of PostGIS. This integration allowed for efficient data processing and analysis while maintaining visualization capabilities.

Future development of this system could include data scraping and API integration to gather additional information efficiently. The framework established in this project could be expanded to cover a wider geographic area, including the Four Corners states. Additionally, implementing an interactive web mapping interface would significantly enhance accessibility and usability for end users in the mountain biking community.

From a land management perspective, this database could support data-driven decisions about trail development, emergency service placement, and resource allocation. For the mountain biking community, it provides a foundation for comprehensive trip planning that considers both recreational enjoyment and safety considerations.

Skills Used