Strava Hunting

[rando]

Thanks for doing all of this bam.  You are almost entirely the only the reason anyone is giving this thread any attention.   

[Seth]

Thanks for doing all of this bam.  You are almost entirely the only the reason anyone is giving this thread any attention.   

Agreed. Once we all get done geeking out on these tools we may actually use them to do what you created the thread for in the first place. Give it time.

[bam]

geek out:

http://modbog.no-ip.org/strava/user_kom.php?athlete_id=6393
http://modbog.no-ip.org/strava/user_kom.php?athlete_id=14962

[JoelS]

Thanks bam!

[rando]

I suppose I better get in line next.  120318. 


Joel, you really weren't kidding about not being close on any.  You better get on that directly when the ultimate climbing  machine gets built.

[bam]

poacher payback:

http://app.strava.com/segment_efforts/101735118
http://app.strava.com/segment_efforts/101735125

[Vicelord]

122614

so this tells you what place you are in on every segment you've passed over?

[Tri Gator]

I got a "QOM" for our big bridge today 

[bam]

122614

so this tells you what place you are in on every segment you've passed over?

it looks at every segment you've ridden but initially will only displays ones where you have a time in the top 50 efforts.

http://modbog.no-ip.org/strava/user_kom.php?athlete_id=120318
http://modbog.no-ip.org/strava/user_kom.php?athlete_id=122614

[rando]

Thanks bam, I'll put it to good use in the coming months.

[JoelS]

Yesterday I tied for 7th overall on the Empire Ranch TT.  Never thought I'd see that!


Hope this link is the right one: http://app.strava.com/rides/5396102#102826757

[ygduf]

Bogdan,


How about parsing all KoMs for times where the delta between 1 and 2 is like 30%.


Or can you pull segment data to see where KoM has speed of like, 35+ mph on a >0 average grade?


What I'm saying is, I want to be KoFlagging

[rando]

Well this is called Strava hunting.  And the request lines are open.  With the die in play we now get to take bets on what useful feature will disappear in place of a couple that aren't being used just quite yet.


 


I laud any further refinement so dont give my smart comments any merit.  I'm just of a mind that they should have calved off a separate section of the site to explore growth in the market.  Leaving the initial core for the most part intact with a crossover in relevant data like segments. 


Let them hand out patches like it's seasonal candy, to the fred crowd, and on the other side make worthwhile refinements like you are doing for the more serious users.  And thanks again.

[bam]

Bogdan,


How about parsing all KoMs for times where the delta between 1 and 2 is like 30%.


Or can you pull segment data to see where KoM has speed of like, 35+ mph on a >0 average grade?


What I'm saying is, I want to be KoFlagging

you can flag an effort from your kom page. i have a couple stored procedures to identify and flag suspect efforts but it's problematic in automate. you can probably throw out the really far off ones, but not always. if you have some slow guys and 1 fast guy run the same segment it goes out the window.  i have run this and it matches much of the user submitted flags, and even caught some of the not-yet-flagged efforts. if i get a chance i'll see about putting a front end on the listing

Code: [Select]

bogdan@sloth:~/strava/scripts$ more ../stored_procedures/suspect_efforts.stored.sql
DELIMITER |

DROP PROCEDURE IF EXISTS suspect_efforts;

CREATE PROCEDURE suspect_efforts ( a1_in double(10,2), a2_in double(10,2) )
LANGUAGE SQL
READS SQL DATA
COMMENT 'query suspect efforts'
BEGIN
START TRANSACTION;
SELECT *,
    mae1/((mae1+mae2+mae3)/3) a1,
    mae2/((mae1+mae2+mae3)/3) a2
from (  select a.segment_id,  a.athlete_id, a.segment_name, 
        (select b.id from athlete_efforts b left join flagged_efforts f on b.id = f.id where f.id is NULL and b.segment_id = a.segment_id order by elapsed_time asc limit 1) id1, 
        (select b.id from athlete_efforts b left join flagged_efforts f on b.id = f.id where f.id is NULL and b.segment_id = a.segment_id order by elapsed_time asc limit 1,1) id2,
        (select b.id from athlete_efforts b left join flagged_efforts f on b.id = f.id where f.id is NULL and b.segment_id = a.segment_id order by elapsed_time asc limit 2,1) id3, 
        a.id id4,
        (select elapsed_time from athlete_efforts b left join flagged_efforts f on b.id = f.id where f.id is NULL and b.segment_id = a.segment_id  order by elapsed_time asc limit 1) mae1, 
        (select elapsed_time from athlete_efforts b left join flagged_efforts f on b.id = f.id where f.id is NULL and b.segment_id = a.segment_id  order by elapsed_time asc limit 1,1) mae2,
        (select elapsed_time from athlete_efforts b left join flagged_efforts f on b.id = f.id where f.id is NULL and b.segment_id = a.segment_id  order by elapsed_time asc limit 2,1) mae3, 
        (select elapsed_time from athlete_efforts b left join flagged_efforts f on b.id = f.id where f.id is NULL and b.segment_id = a.segment_id  order by elapsed_time asc limit 3,1) mae4 
        from athlete_efforts a
        group by segment_id ) x   
where mae1 is not null and mae2 is not null and mae3 is not null
having a1 < a1_in and a2 < a2_in
order by a1,a2 desc;

COMMIT;
END ; |

DELIMITER ;

CALL suspect_efforts(0.7,1.1);
bogdan@sloth:~/strava/scripts$ more ../stored_procedures/flag_suspect_efforts.stored.sql
DELIMITER |

DROP PROCEDURE IF EXISTS flag_suspect_efforts;

CREATE PROCEDURE flag_suspect_efforts ( a1_in double(10,2), a2_in double(10,2) )
LANGUAGE SQL
READS SQL DATA
COMMENT 'flag suspect segments'
BEGIN
START TRANSACTION;
INSERT INTO flagged_efforts (id,reason) SELECT id1 id, CONCAT('[a1: ',a1_in,', a2: ',a2_in,']') FROM (
        SELECT *,
        mae1/((mae1+mae2+mae3)/3) a1,
        mae2/((mae1+mae2+mae3)/3) a2
        from (
            select a.segment_id,  a.athlete_id, a.segment_name, 
                (select b.id from athlete_efforts b left join flagged_efforts f on b.id = f.id where f.id is NULL and b.segment_id = a.segment_id order by elapsed_time asc limit 1) id1, 
                (select b.id from athlete_efforts b left join flagged_efforts f on b.id = f.id where f.id is NULL and b.segment_id = a.segment_id order by elapsed_time asc limit 1,1) id2,
                (select b.id from athlete_efforts b left join flagged_efforts f on b.id = f.id where f.id is NULL and b.segment_id = a.segment_id order by elapsed_time asc limit 2,1) id3, 
                a.id id4,
                (select elapsed_time from athlete_efforts b left join flagged_efforts f on b.id = f.id where f.id is NULL and b.segment_id = a.segment_id  order by elapsed_time asc limit 1) mae1, 
                (select elapsed_time from athlete_efforts b left join flagged_efforts f on b.id = f.id where f.id is NULL and b.segment_id = a.segment_id  order by elapsed_time asc limit 1,1) mae2,
                (select elapsed_time from athlete_efforts b left join flagged_efforts f on b.id = f.id where f.id is NULL and b.segment_id = a.segment_id  order by elapsed_time asc limit 2,1) mae3, 
                (select elapsed_time from athlete_efforts b left join flagged_efforts f on b.id = f.id where f.id is NULL and b.segment_id = a.segment_id  order by elapsed_time asc limit 3,1) mae4 
                from athlete_efforts a
                group by segment_id
            ) x   
        where mae1 is not null and mae2 is not null and mae3 is not null
        having a1 < a1_in and a2 < a2_in
        order by a1,a2 desc
) flag;
COMMIT;
END ; |

DELIMITER ;

/* CALL flag_suspect_efforts(0.7,1.1); */


and if Ben Jacques-Maynes starts uploading his rides again this whole KOM business is moot.

[Vicelord]

it looks at every segment you've ridden but initially will only displays ones where you have a time in the top 50 efforts.

http://modbog.no-ip.org/strava/user_kom.php?athlete_id=120318
http://modbog.no-ip.org/strava/user_kom.php?athlete_id=122614

Interesting, thanks. Some of them shows me #2 or some other place with a different person as KOM, though when I go to that segment, I am KOM and that person's name isn't on the leaderboard...?