[hozn]

I’ve added weather data and published my changes (which were mostly backend changes). The NCDC data turned out to be pretty poor, so I tucked my tail & went with wunderground. I was wrong about the lack of historical hourly observations, so that was a great discovery. In general wunderground API is a real pleasure to work with compared to NCDC.

I think this data is primarily going to be useful for PeteD since he wanted to do some weather-related data mining the the database.

I did add a miles-below-freezing leaderboard, which is a little misleading; it’s basically the total distance ridden where the average temperature for (each) ride was below 32F. I don’t have the data granularity to know exactly how many miles were ridden below 32F, so this doesn’t favor those who rode for 6 hours, 3 of which were below freezing but then it warmed up such that the average temp was > 32F over the duration of the ride. (I’m also only using hour-level granularity for the temperatures.)

I also added sunrise/sunset times, which makes possible leaderboards like hours ridden “before sunrise” and “after sunset” (which I’ve added).

Currently new leaderboards are only for individuals (not teams): http://freezing.hozn.net/leaderboard/individual_various

I should also caveat that this data excludes those that have no geo data associated with the ride. That includes some (but not all) manually entered rides (I’m not exactly sure what the rules are there, but it would appear that some of the rides did have a start lat/lon even though they were apparently entered manually). I also discarded data from manual rides where there was no time entered, since that shows up as midnight and (may have) skewed the sunrise/sunset results.

I’d be happy to go on & on about the data assumptions and the potential for error, but I suspect I’d be talking to myself. (Of course the source is available for whoever wants to take a look.)

[hozn]

@vvill 44845 wrote:

Looking forward to seeing weather data!

Just one small thing I noticed – the mouseover titles for the individual leaderboard graph bars are mislabeled as “Elevation”.

(Thanks for catching that! I fixed the mouse-over label earlier last week. I added a new bubble chart on the “Explore” tab too.)

I have been working on weather which has proven more troublesome than I expected. I’m trying to use the NCDC APIs because they’re free & not rate-limited, but they’re not as straightforward as wunderground. (I may use wunderground to supplement where NCDC doesn’t turn up any data [for the dataset I’m using].) I let it churn on data last night for awhile on my local copy; I haven’t evaluated the results, but I should not have:
– *daily* min & max temperatures for every ride with a start lat/lon
– *daily* precipitation measures (and snow) for every ride with a start lat/lon

It looks like there are a couple/few hundred rides in the system (out of a few thousand) that do not have any geographic data (I assume manually entered?). I will look more deeply at these later, but I may just default those to a “DC area weather station” (e.g. National Airport).

It also looks like there are a few ride locations where NCDC doesn’t have any ground-station data within 25km. I’ll probably broaden the radius, with the risk that weather gets less accurate. (This is where I may supplement with wunderground too.)

Once I complete this initial exercise I plan to add:
– sunrise/sunset for each ride (yes, not all the rides are local, so we kinda need to calculate it per ride to be correct). I have a library to calculate that, but it also requires that I determine the time zone for each ride. Luckly I also found a library to take lat/lon and find the timezone that contains those coordinates.
– For more useful temperatures, I will look up the previous and next days’ max/min temperatures and then use a diurnal temperature model to estimate the temperature at the start of the ride.

So, in the end we should be able to talk about rides that start/end before sunrise/sunset and talk about the temperatures at start/end of rides. I am not pulling down the entire ride gps data currently, so there’s no way to say exactly how long someone was riding in the dark or in temps below freezing, etc. (Although we might be able to do some basic math to guess at this.) One thing that definitely throws stuff off is that some people keep their computer running between their morning & evening commutes — or other multiple trips.

Anyway, just wanted to provide an update; I know a couple people are wanting to run weather queries. I’m finding myself a little limited by the availability of good data, but plan to have something out there this week so people can start querying it.

[hozn]

BTW, I’ve applied some more changes to the site to make the various team leaderboards match up with individual. I added Hains Point laps for both team & individual (though I understand the prize is just for individual).

I’m going to consult the google docs file to see if there are some other easy ones I can add this evening. Otherwise I’m gonna start working on pulling down weather data.

[hozn]

@elcee 44699 wrote:

[ATTACH=CONFIG]2458[/ATTACH]

Building a bike up for my daughter. KHS frameset (Reynolds 853!!) from eBay; H+Son TB14/Shimano 105 wheels; Tiagra components.

Nice looking bike! – and sounds like it’ll be a great build.

[hozn]

@vvill 44611 wrote:

I think these are the main two. First is the full loop but may miss those who start a loop from the north corner. The second should cover most if not all iterations.
http://app.strava.com/segments/614873
http://app.strava.com/segments/1081507

Thanks, vvill.

So, does this top-X look about right (I just grabbed the top few)?

[TABLE=”class: data”]
[TR]
[/TR]
[TR=”class: odd, bgcolor: #E5E5E5″]
[TD=”class: nowrap, align: right”]564600[/TD]
[TD]Kathy Lewis[/TD]
[TD=”class: nowrap, align: right”]46[/TD]
[/TR]
[TR=”class: even, bgcolor: #D5D5D5″]
[TD=”class: nowrap, align: right”]1412719[/TD]
[TD]Brad Hancock[/TD]
[TD=”class: nowrap, align: right”]38[/TD]
[/TR]
[TR=”class: odd, bgcolor: #E5E5E5″]
[TD=”class: nowrap, align: right”]1473093[/TD]
[TD]Justin Antos[/TD]
[TD=”class: nowrap, align: right”]34[/TD]
[/TR]
[TR=”class: even, bgcolor: #D5D5D5″]
[TD=”class: nowrap, align: right”]300685[/TD]
[TD]Chris Randall[/TD]
[TD=”class: nowrap, align: right”]28[/TD]
[/TR]
[TR=”class: odd, bgcolor: #E5E5E5″]
[TD=”class: nowrap, align: right”]313677[/TD]
[TD]Randy Napiza[/TD]
[TD=”class: nowrap, align: right”]27[/TD]
[/TR]
[TR=”class: even, bgcolor: #D5D5D5″]
[TD=”class: nowrap, align: right”]120192[/TD]
[TD]Will Lynn[/TD]
[TD=”class: nowrap, align: right”]25[/TD]
[/TR]
[TR=”class: odd, bgcolor: #E5E5E5″]
[TD=”class: nowrap, align: right”]75834[/TD]
[TD]Pete Beers[/TD]
[TD=”class: nowrap, align: right”]23[/TD]
[/TR]
[TR=”class: even, bgcolor: #D5D5D5″]
[TD=”class: nowrap, align: right”]784659[/TD]
[TD]Christopher N.[/TD]
[TD=”class: nowrap, align: right”]21[/TD]
[/TR]
[TR=”class: odd, bgcolor: #E5E5E5″]
[TD=”class: nowrap, align: right”]1224809[/TD]
[TD]Ken Buja[/TD]
[TD=”class: nowrap, align: right”]12[/TD]
[/TR]
[TR=”class: even, bgcolor: #D5D5D5″]
[TD=”class: nowrap, align: right”]10446[/TD]
[TD]Sean Chisham[/TD]
[TD=”class: nowrap, align: right”]12[/TD]
[/TR]
[TR=”class: odd, bgcolor: #E5E5E5″]
[TD=”class: nowrap, align: right”]1129136[/TD]
[TD]Rod Smith[/TD]
[TD=”class: nowrap, align: right”]11[/TD]
[/TR]
[/TABLE]

I think I did that right …

select A.id, A.name, count(E.id) as hains_loops
from athletes A
join rides R on R.athlete_id = A.id
join ride_efforts E on E.ride_id = R.id
where E.segment_id = 1081507
group by A.id, A.name
order by hains_loops desc;

[hozn]

@GuyContinental 44573 wrote:

Wow Hans, there is some great stuff in there! Something for everyone… I seem to be a very fast sleaze

I suppose that now that you have the pieces in place you could implement for the BA Strava club, no?

Yeah, this can be applied to any Strava club (or set of clubs), really. After the competition is over, I’m going to go back through to clean things up, make the application more generic, and write up more detailed instructions so that it can be used/extended by others around the world that might want to use Strava for similar cycling competitions. (I suppose it would work for other activities too, though there are probably some cycling assumptions here.)

[hozn]

@eminva 44428 wrote:

A category that favors the Custis commuter!

hozn, this is amazing magic that you are performing. Is it possible to datamine (is that a word?) a particular segment? I am awarding most Hains Point loops (team and possibly individual, if a clear winner can be determined with certainty). I imagine the way to do this would be to pick one of the HP segments (e.g., gate to gate) and tally the number of times each participant has ridden it over the time period. Is this possible?

Thanks.

Liz

Sorry that I never responded directly to this; I missed it in my updates.

Yeah, I went ahead and added support this weekend for segment tracking. It took awhile to initially fill that data in, but it’s all there now (but not exposed in the UI). I need to look up the Hains Point segments (and any others that might be of interest), but it should be relatively easy to build a leaderboard for most times around Hains point, etc.

On a less-directly-related note, I began updating the UI to support a more generic way of pulling up different leaderboards. I will update everything to use this new model over the course of the week. Here’s the new basic format with a couple new metrics, such as time in the saddle (perhaps not surprisingly dominated by Rod Smith):

http://freezing.hozn.net/leaderboard/individual_generic

-Hans

[hozn]

@lordofthemark 44550 wrote:

Am I the only one here who also drives? Because on some occasions I see someone cycling from my car and I do admire them – and on other occasions envy them. Though usually not both at the same time.

Fair enough I drive a fair bit (not as much as I bike, but probably close) and I love my car. It’s true I definitely do not like traffic, but before I commuted by bicycle, it never occurred to me to envy cyclists. It’s true that as a cyclist now I would probably have some admiration for someone riding through freezing rain, because having done that I know that it’s not necessarily all fun. But I don’t think that perspective is necessarily shared by those who aren’t cyclists. Maybe I’m wrong and they really are all envious or admiring of us all the time, but I’m just gonna carry on with the assumption that most drivers don’t actually care about anything until it gets in their way — and then they honk, pass too close, throw stuff, and yell at us to get off the road (or worse).

[hozn]

@lordofthemark 44549 wrote:

Urban planners, transportation planners, and folks concerned with sustainability DO consider cycling (and other ways to reduce gasoline consumption) part of strategies to address global warming and other environmental issues. I was a supporter of pro-biking policy before I got back to cycling myself, and I would remain so if I stopped biking. Just as I support hybrid vehicles, though I may never own one. I hope that is not doing a disservice to the cycling community.

I hope that I didn’t imply that supporting cycling is doing a disservice to the cycling community I support cycling too, both in spirit and in more practical ways when I can. And I believe that more people cycling for transportation is a generally good thing. My only point (and perhaps this wasn’t very clearly stated) is that when an attitude of moral superiority is perceived by others (drivers, pedestrians, etc.) this does a disservice to being taken seriously by the driving majority.

[hozn]

@ShawnoftheDread 44521 wrote:

Second broken spoke on new rear wheel. My legs are clearly too powerful for cheap hybrid wheels.

That sucks. I’m not sure from your earlier post, but if you need a freewheel removal tool (is this a single-speed?) I have an extra one that you can have.

After breaking a couple spokes in a short period of time on my cheap factory single-speed wheels a few years back, I decided that I would build all my wheels from then on. At least then I’d only have myself to blame. Well, probably a half dozen wheelsets later (on various bikes), I was feeling pretty smug about this decision and feeling that I had “figured out” this wheel building thing; I hadn’t broken any spokes, my wheels had all remained properly tensioned & true after the building, etc. And then a month ago I discovered that I had a broken spoke on my rear cx-turned-commuter disc wheel. I didn’t know how that had happened, but that sucked. And then a week later I broke a front spoke while braking. This was very humbling. After replacing the spoke, I couldn’t find anything wrong with the wheel tension-wise, etc; it all seemed within parameters. I’ve ridden them pretty hard off-road, but only for a couple thousand miles — and I’ve never had issues with my mountain bike wheelsets that get ridden much harder. So far no more broken spokes — so fingers crossed (if another one breaks I will probably rebuild the wheels). I don’t think this story really has a moral, other than perhaps a note-to-self about appropriate humility and the idea that even handbuilt wheelsets break sometimes — either due to errors in the build (likely, in my case) or freak chance of weakness in the materials, damage from abusive riding, etc.

[hozn]

@ShawnoftheDread 44540 wrote:

I stand by my statement: you’re just doing something you like.

I couldn’t agree more. I love biking and I am happy to go [a little] out of my way to support the environment, but those are two independent sentiments. I don’t ride my bike because I think it’s making the world a better place; I bike because I get exercise I want/need and I just like being outside. Saving money on gas & tolls makes the economics at least break even. I know there are other people that believe strongly in cycling as a way of life. I respect that, but when it turns into an expression of superiority — environmental or otherwise — I think it does disservice to the cycling community.

I definitely echo the sentiment that no one in their heated seats is looking out with envy — or with admiration — at the cyclist riding by in the rain. That sentiment — and I’ve certainly had it cross my mind — is 100% smugness-induced and has no basis in reality. I’d like to believe they’re envious, because being cold & wet sucks. But they’re not.

[hozn]

@Bilsko 44474 wrote:

Wunderground is good, but not always the best for hourly data. I use the Utah Climate Center’s tool for good hrly data ( no API, but data can be exported to Google Docs or similar for integration with Strava data). Dont have the coding skills to be of much help, but I can help you find the right data if it helps…

I will look at alternatives. As it is my wunderground developer key limits me to 500 calls per day, so weather information for the rides (almost 3k, I think?) would need to be spaced out over a week or so. That’s kind clunky. I played with the Utah Climate Center’s tool a bit; it looks interesting, but I really need to be able to use some sort of API (even if screen scraping) so I can feet it lat & lon (or locality name if there’s no geo for the ride) and have it return weather for that area. (Of course *most* of the rides are local here, but not all of them.)

[hozn]

@ShawnoftheDread 44467 wrote:

No, two half-mile rides would be two sleaze rides, as you’d get the minimum daily points.

This was as I understood it, though my query was more like “sleaze days”. I assumed the 10 points were based on total miles (i.e. at least 1.0) for the day. If that is wrong I need to adjust my points algorithm.

[hozn]

@vvill 44445 wrote:

Now we need elevation gain on a single speed.

just kidding! Great work hozn.

If everyone provides the names of their singlespeeds (according to Strava), I can do that

Probably another good leaderboard is total time in the saddle.

[hozn]

@Justin Antos 44439 wrote:

What makes a ride even more sleazy is when the short ride was also the only ride for that day – i.e. you just did it for the 10 point bonus. I can’t tell if that’s what the SQL statement is showing? Is there a way to make the Sleaze Leaderboard the most “days where Points < 12?"

Yes, absolutely. (That query is still similar, since it is looking at total miles for the day.) I will add this to the site and use points instead of mileage.

[Author]

Posts