Tour de France 2011 – Analysis using ADF DVT Graphs – Part 2 – Gap with Cadel for Top 5 throughout the stages

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:

Resources

Download JDeveloper 11.1.2 Application: TourDeFrance_OverallGapWithCadelPerStage.