I want to RIDE...and work with DATA!
Michael Whaley
Data Analyst | Transforming Data into Insights with SQL, Tableau, and Visualization Expertise.
I love mountain biking! Trails aren't always rideable and the crowd sourced model of updating conditions isn't always current, accurate, or in some cases even used! I wondered if there was soil moisture data available and if so would it help predict trail conditions. I created UseDryTrails to capture useful weather data including soil moisture to help determine if a trail is rideable or not. After myself and others using the app for over a year, I discovered it was lacking context.
The main negative feedback I have received from users is that they don't understand what the soil moisture number means. 30% soil moisture at Alum Creek near Columbus, OH is unrideable while this number is normal and rideable at Tiger Mountain near Seattle, WA for example. Giving the user context with data is a problem definitely worth solving!
Basically, I want to provide the average soil moisture for that date and compare it to the current soil moisture. If a user ran the application on May 4, 2025 they would get the current soil moisture at the specified location and an average soil moisture for May 4 at that location and some sort of comparison between the current and average.
Key Takeaways
The Business Problem
The three problems I intend to solve in this article are:
Data Layer -> Weather Data API
OpenMeteo is the only weather API I have found that has soil moisture and a free tier which is why I used their forecast API in the original UseDryTrails app. There is a Historical Weather API which is also available but upon further inspection, only hourly soil moisture data at a depth of 0-7 cm is available and in the current application 0-1 cm is used. There is a Historical Forecast API which is based on the forecast API currently used in the application and starts in 2022.
Fetches Historical Weather Data from Open Meteo
On my first API call on the historical forecast data I will use the data from January 1, 2022 to December 31, 2024. I will use the GPS coordinates of my local trail Chestnut Ridge in Carroll, Ohio for my examples.
The API response indicates the equivalent to 5.4 calls! More importantly, there is no data previous to November 14, 2022. The history will start on January 1, 2023.
Weather Data API Summary
Business Logic -> Weather Comparison Module -> Compares current weather vs Historical Averages
Historical averages will need to be calculated. One approach would be to calculate every hour's average. This approach seems excessive and would result in over 8,000 averages per location. Another approach would be to calculate the daily average which would provide 366 averages per location. Daily average is the approach I will take.
Interestingly, Open-Meteo provides Python Code for the API call.
Running the script in Deep Note I noticed an issue. I was retrieving all the hourly soil moisture data from 2023 and 2024 and ended up missing the first five hours of 2023 and getting the first five hours of 2025.
The timezone needs to be adjusted. I used my time zone but to get the data to cover just the two years I adjusted the timezone to "GMT+0" resulting in the correctly aligned data.
I exported a .csv of the 17,544 rows and loaded in Excel to manually check the averages. I did a few days to get a result that looks like this:
For daily averages all 24 hours in a day will be used to reduce the 17,544 rows into 366 rows of data. Fortunately I have a leap year in this historical data. Otherwise the first time February 29 was encountered by the app it is possible it would throw an error.
The averages look good so it is time to pull today's average out of the list in percentage format.
The current soil moisture will need to be retrieved and compared to the daily average. Instead of just pulling out the soil moisture for one day in the API call I wanted to use the same call the current application uses. To verify, I opened the function in XCode.
Currently, the application gets the past three days and forecasts for three days with several parameters. I will duplicate this call with the gps coordinates for Chestnut Ridge.
This call returns several data fields but for this all I want is current soil moisture or one that matches the current hour.
I ran the actual application that is installed on my phone to check if the soil moisture numbers match, they do!
Next, compare the current and today's average.
Data Processing:?Parses and formats soil moisture data for display
For more reference a line graph would help show soil moisture comparisons. I imported matplotlib and numpy to create a line graph for the averages every day of the year and marked today with current soil moisture.
This gives a good reference on the time of year and how current compares to average but what about today? Currently, Chestnut Ridge is in the freeze/thaw cycle and while that soil moisture looks good currently it may not be later today.
As the temperature rises today the snow will melt and the ground will thaw making for a very sloppy ride after 4pm today!
Conclusion & Recommendations
This analysis shows that providing users with context on soil moisture would greatly improve their intelligence on trail conditions.
To Summarize the steps taken:
The next step will be to implement these changes in Swift and explore graphing options for my iOS application.
Let’s Connect!
If you have insights or are interested in data-driven problem-solving or iOS application development, let’s connect! Whether you have feedback on this analysis or are looking for a data analyst or an iOS developer, I’d love to chat.
Data Analyst | Sales Operations | Excel | Tableau | SQL | Power BI | R | Python
6 天前This is a really cool project, Michael! You are very skilled!
Epic Healthy Planet Certified | Data analysis in Healthcare | Excel & Tableau Enthusiast
1 周Hi Michael, this is very interesting topic and you did a great work on your project. I learned a lot about this topic from this article! Thank you for sharing.
Data Analyst | Music Education
1 周This is such a cool and impressive project. You really showcase a depth of understanding. Documenting your process when you noticed a gap in the data I think might have been the most significant to me. I definitely learned a lot through your work. This is fantastic!
That's awesome! Great job on your capstone project!
Data Analyst??|| SQL | Tableau | Excel | Data Visualization | Data Storytelling | C++ | Python
1 周Michael Whaley Your project was thorough and precise. This project clearly depicts your software developing capabilities. Good luck with the app that you created.