This article uses the statistics about this year’s Tour de France to visualize the gap between each of the top 5 riders and Cadel Evans – throughout the 21 stages. It produces a line chart that provides easy insight in the history of this year’s event – making it abundantly clear that for example Contador never really stood a chance and only kept losing time. It also shows that Frank Schleck did very well indeed, outpacing is brother for the first 17 stages.
The chart this is created in this article looks like this:
It is fairly basic – created using only simple declarative ADF DVT settings. The greatest hurdle to overcome was the creation of a SQL Query that returns the required data in format that is easily consumed and processed by the graph.
In several earlier articles, I have discussed how to get hold of Tour de France data through screen scraping and how to upload it into the database using JPA. A recent article discusses how to convert a time gap recorded as a string like + 2h 09′ 10” and + 05′ 12” into an INTERVAL DAY TO SECOND value.
With the interval values in place, I have created the following SQL query. It consists of two parts really: the first was used before and only helps to select the top 5 riders from the final overall ranking. The second part – starting with inline view top5stageresults – produces the rankings per stage for each of the top 5 riders including the timegap with the number one in the overall ranking after the stage. Inline view stageintervals uses the timegaps to calculate the gap compared to Cadel Evans, ridernumber 141 which is hard coded in the query. Note the trick with LAG, LEAD, CASE and IGNORE NULLS to find the gap for Cadel Evans that is then combined with the timegap for the current rider itself.
-- gap to CADEL expressed in seconds (negative number means lagging CADEL) with final_standing as ( select r.name , r.team , r.ridernumber , r.id rider_id , s.rank as ranking , s.gap , s.timelag from standing s join rider r on (s.rider_id = r.id) join stage e on (s.stage_id = e.id) join tour t on (e.tour_id = t.id) where e.stagesequence = 21 and t.year = 2011 and s.type_of_standing = 'ITG' order by ranking ) , top5 as ( select * from final_standing where rownum < 6 ) , top5stageresults as ( select t5.name , t5.team , t5.ridernumber , s.rank as ranking , s.gap , s.timelag , e.stagesequence from standing s join top5 t5 on (s.rider_id = t5.rider_id) join stage e on (s.stage_id = e.id) join tour t on (e.tour_id = t.id) where t.year = 2011 and s.type_of_standing = 'ITG' -- overall standing at the end of the stage ) , stageintervals as ( select stagesequence , ridernumber , ranking , -1* (timelag - lag( case ridernumber when 141 then timelag end ) ignore nulls over( partition by stagesequence order by ranking) ) behind_cadel , (lead( case ridernumber when 141 then timelag end) ignore nulls over( partition by stagesequence order by ranking) - timelag ) ahead_of_cadel , name , timelag from top5stageresults ) select stagesequence , ridernumber , ranking , case when behind_cadel is null then 60* extract (minute from ahead_of_cadel) + extract (second from ahead_of_cadel) else 60* extract (minute from behind_cadel) + extract (second from behind_cadel) end gap_cadel , name from stageintervals order by stagesequence, ranking
Using this query, I have created a simple View Object. The VO is added to the application module’s data model. A new JSF page is created. A graph is added for the VO’s collection in the Data Control based on the application module:
Choose the Line Chart:
Press OK and configure the Line Chart:
The stagesequence attribute is associated with the horizontal axis, the GapCadel attribute is tied with the y-axis and the Name (of the rider) is used to determine the Series.
Press OK. Run the page. Here is the chart:
When a data point is selected on one of the lines, a tool tip appears with the precise information for that rider at the selected stage. The line representing that rider is highlighted as well:
Here we see how Frank Schleck was close to or even ahead of Cadel Evans until the 19th stage. Only the contre-le-montre was too much for him. Also note how Thomas Voeckler gained time against Cadel Evans in only a single stage: the infamous 9th stage:
Download JDeveloper 11.1.2 Application: TourDeFrance_OverallGapWithCadelPerStage.