Using the Trailforks ridelog feature we are able to see what trails and regions are being ridden the most and when. We've queried the database to find the top ridden biking cities for the month of July 2017. You'll notice some big name destinations like Moab are not on the list, probably too hot for riding, less visitors in the middle of summer I guess?
You can make sure your rides are counted by either
connecting your Strava account with Trailforks, or by recording your rides using the new tracking feature in the
Trailforks app! The more people logging their rides to Trailforks the more accurate this data becomes for these fun lists, but more importantly for trail association advocacy use.
If people find these sort of blog posts interesting we can do much more, we can even seperate out locals riding their local network, to better gauge top travel destinations. If you have idea for other ridelog related lists we could do, let us know!
1. Park City, UtahPark City mountain biking trails2. Draper, UtahDraper mountain biking trails3. Bellingham, WashingtonBellingham mountain biking trails4. Issaquah, WashingtonIssaquah mountain biking trails5. Hood River, OregonHood River mountain biking trails6. Lyndonville, VermontLyndonville mountain biking trails7. Phoenix, ArizonaPhoenix mountain biking trails8. Bend, OregonThe place to go for some amazing mountain biking and other outdoor activities. Trails for the whole family, from Downhill at Mt. Bachelor to easy & hard XCountry trails at Phil's.
Bend mountain biking trails9. Cedar Mountain, North CarolinaCedar Mountain biking trails10. Colorado Springs, ColoradoColorado Springs mountain biking trailsThe query for you geeks!
SELECT region.rid, region.title, region.alias, p.title AS prov_title, tr.description, COUNT(*) AS total
FROM wostrailforks.ridelog_trails rt
JOIN wostrailforks.trail t ON rt.trailid=t.trailid
JOIN wostrailforks.ridelog r ON rt.ridelogid=r.id
JOIN wostrailforks.trail_index i ON t.trailid=i.nid
JOIN wostrailforks.region ON i.city=region.rid
JOIN wostrailforks.region p ON i.prov=p.rid
JOIN wostrailforks.region_text tr ON region.rid=tr.rid
WHERE YEAR(FROM_UNIXTIME(r.created)) = '2017' AND MONTH(FROM_UNIXTIME(r.created)) = '7' AND t.difficulty NOT IN (1,7) AND i.country='3001' AND i.city>0
GROUP BY i.city
ORDER BY total DESC
LIMIT 10
Cities with longer trail segments will not 'complete' in this analysis because, although they may be very popular riding destinations, they would not count as many 'ridden trails'. Next time, purely count mileage ridden? Or hours ridden?
Nice analysis nonetheless. I'm a GIS *and* SQL geek, so I love the way this was presented and the fact that you posted the query!!!!
Also, note #8 in the list: Kirkland, Washington. Average trail length is less than a quarter-mile (0.239)!
PB home of the fake NEWS
Corner Canyon is great and a lot, ALOT of time & energy has been put into the trail system but this area is a perfect example of a trail system being "loved to death". It's a multi-use system with bikers, hikers and horsey people as users.
Draper City is going to shit when they see this info, they have been marketing this area as a "MTB destination" for years and have half the money to build a Velodrome on the property.
Cool place to ride but unless you ride early on a weekday, it can be frustrating with the crowds.
b.
I've found sometimes that the analyzing and understanding "all that data" you gather is often far more difficult than any process(es) created to gather the information.
I wrote a system a couple of years ago that creates and manages participation lists for surveys for a 150K kid school system. Maintaining consistency and understanding exactly how the various processes affect that data has been a far greater challenge!!! Especially since the requirement came out of no where and I had a month to the finish line. The follow on being that we couldn't accurately predict how all of the then un-knonwn conditions would affect the system.
well done. for the record, I rarely out stravas anyone.
A lot of mountain bikers live there, and they all ride at night or early morning (I lived there). No one is traveling to Phoenix to ride in the summer though; it is not a summer riding "destination".
Thats all i said.
OK, I am stereotyping here but they are definitely the most pocular spots.
Kingdom Trails - Vermont
Dupont State Forest - NC
www.trailforks.com/region/kingdom-trails-4720
Lotsa people worked their asses off to build a super accessible sweet trail network for all levels of riding.
It's an awesome resource for local riders especially when the higher elevation tails are covered in snow.
It's not alpine singletrack or the Canyons bike park or a destination out-of-towners need to hit, but it's a growing and super accessible network for thousands that want to fit in a quick spin before or after work without having to drive up the canyon or over to park city.
The fact that it's blown out and rough keeps all the joeys off it after they ride it once. It'll be better next year after they touch it up and the soil packs in. It's still good for being a brand new trail.
www.trailforks.com/region/kingdom-trails-4720
Can you please elaborate on what you consider “real trails”?
SELECT distinct top 10 region.rid, region.title, region.alias, p.title AS prov_title, tr.description, COUNT(*) AS total
FROM wostrailforks.ridelog_trails rt
JOIN wostrailforks.trail t ON rt.trailid=t.trailid
JOIN wostrailforks.ridelog r ON rt.ridelogid=r.id
JOIN wostrailforks.trail_index i ON t.trailid=i.nid
JOIN wostrailforks.region ON i.city=region.rid
JOIN wostrailforks.region p ON i.prov=p.rid
JOIN wostrailforks.region_text tr ON region.rid=tr.rid
WHERE YEAR(FROM_UNIXTIME(r.created)) = '2017' AND MONTH(FROM_UNIXTIME(r.created)) = '7' AND t.difficulty NOT IN (1,7) AND i.country='3001' AND i.city>0
GROUP BY i.city
ORDER BY total DESC
--LIMIT 10
Although admittedly the population disparity could be larger or smaller based on nearby cities. I'm too lazy to check though.
Would be nice to have such a page dynamic where the user could specify a date range, but because there is so much data these queries can be slow.
For example this page:
www.trailforks.com/trails/all/top
It shows all the top trails in the world (great), but yet they are still all in USA/Canada when you look at rating/ridden (due to the user base of trailforks). With all the filters on the right hand side, could you please add in a filter for 'country'.
It would be useful if your visiting a country, say I was planning a trip to NZ, I could select NZ and see the most popular trails in that country.
www.trailforks.com/region/new-zealand/trails/?sort=t.global_rank&order=desc
Our trail ranking formula I try and tweak to take out some bias, but it's hard when there is so much more data points for trails in North America and BC in particular... We also have the best trails in the world here in BC...
Edit: just realized that I already responded to canadaka in another post...