Tour de France – Analysis using ADF DVT Graphs

In two previous articles, I have explained how it is possible to get hold of the results of the Tour de France 2011 (and previous years) using simple Java programming and how – using JPA – it is straightforward to load these results into a local database for stand alone analysis.

This article demonstrates such analysis using the results of the 2011 edition of the Tour de France through applying the Oracle ADF DVT components for data visualization. Given the many options these components present, I find it hard to choose. So this article and its companion sequel pieces demonstrate a fairly random collection of visualizations of Tour de France results.

The first visualization I will dive into is an overview for the top 5 of the final ranking and their results for each of the 21 stages. How well did they do throughout the tour?

The final picture looks like this:

It indicates that the top 5 of the final ranking hardly ever do any worse than top 50, even in stages where it does not really matter. Yesterday’s Paris arrivee was the worst performance for Cadel Evans. However, in most stages he was at the very front of the group.

After gathering the data into the database – as explained in the two articles listed in the resources section below, using JPA to create the tables and upload the data – it is easiest to base the ADF graphs on ADF BC Read Only View Objects. One of the keys to a successful data visualization turns out to be an appropriate SQL query to retrieve the relevant data in a structure that can easily be visualized in the DVT components.

The query we need for this ‘stage by stage analysis for the top 5 of the 2011 tour’ needs to query the ranking in each stage for each of the top 5 riders. The graph will have the ranking for its Y1 axis, the stage for its X axis and the riders as the serie.

The query I have used for for this chart is listed here:

with final_standing as
( select r.name
  ,      r.team
  ,      r.ridernumber
  ,      r.id rider_id
  ,      s.rank as ranking
  ,      s.gap
  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
  ,      t5.ranking final_ranking
  ,      s.rank as ranking
  ,      s.gap
  ,      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 = 'ITE'
)
select stagesequence
,      ranking
,      ridernumber
,      name
,      final_ranking
from   top5stageresults
order
by   final_ranking, stagesequence

Based on this query is simple read only ViewObject that has been added to the Application Module’s Data Model.

With the ViewObject in place, I create a new JSF page. The ViewObject is available as a collection in the Data Control exposed for the Application Module. The data bound Graph is created by dragging this collection to the page, and dropping it as Graph:

The Graph is configured as a simple Line Chart in the next step:

Then the graph is configured. The stagesequence attribute is dragged to the X-axis field. The Ranking (y-axis) and Name (for the serie) are dragged to the Lines field – in this specific order.

Click OK to complete the configuration of the Line Graph.

The editor displays the following:

After some minor manipulations, the page can be run and the chart appears:

When the mouse hovers over a specific marker, the line for the associated rider is highlighted and a tool tip appears to indicate the specific values:

I have been trying to find an easy way to display the results such that the number one rank shows up on top of the chart and the number 180 position is the bottom of the chart. That would take further manipulating the query – for example taking the inverse of the ranking (and asking the reader to ignore the minus sign). I am not sure if the Axis orientation can be changed (low to high from top to bottom rather than the more common high to low).

Resources

Tour de France 2011 – the official website – http://www.letour.fr/us/index.html

Building Java Object Graph with Tour de France results – using screen scraping, java.util.Parser and assorted facilities – https://technology.amis.nl/blog/12786/building-java-object-graph-with-tour-de-france-results-using-screen-scraping-java-util-parser-and-assorted-facilities

Using JPA to persist the Tour de France Java Object Graph to relational database tables https://technology.amis.nl/blog/12796/using-jpa-to-persist-the-tour-de-france-java-object-graph-to-relational-database-tables

Download JDeveloper 11.1.2 Application: TourDeFranceRankingPerStageTop5Graph.