Building Timing Software for Athletic Races

A while back I got the opportunity to work with a friend who times athletic races. After a bit of questioning he showed me some of his equipment and how it worked. I realised I could probably put together some home made timing software and so I set about doing it. The following is an overview of the stages that I went through to create the software.

Firstly its important to understand how the hardware works. To my mind this is very much a pattern and irrespective of the software you are using it is going to have to perform the same tasks in the same order.

Pre race test and hook up

Each competitor is given an RFID chip that has a unique number registered to it. (Numbers can be changed but requires specialist equipment)
A timing box is used to both power the matts (aka antennae) and act as a clock. When someone goes over the matt the chip emits the number and sends to the box. The box then places a time stamp on the chip and places in a file. A file or stream of all these strings is then pushed out of the timing box to awaiting computer.
Computer is listening and takes this information and software does the rest

In terms of the information that is produced by the box this is remakably simple – a raw hex string for each read.

My solution has three main tables
T1 – Raw times
T2 – Competitors
T3 – Chip Tag numbers.

The hard coded chip numbers are hex and each competitor is allocated a chip – they are actually being allocated this hex key but the number equates so on the chip itself will be a readable number eg 14001, 14002 the actual raw code from the box is often something like 45ab32c

What the software then does.

Step 1
Ensure exact duplicates reads are not entered generally because of multiple imports of same file.This is done by setting the full hex key as a primary key disallowing the same value to go into the Raw Times table multiple times.

Step 2
Hex to Decimal translation

Step 3
Drop times before race start time.
Simple select query.

Step 4
Match Reading to competitors – A simple join after hex to decimal translation of information from 3

Step 5
Sort times within competitor in ascending order
Data sort on dual columns

Step 6
Gate times – Competitors get multiple reads over the matt. I have set up something called a gate ( no idea whether this is a standard pattern or not but its what I call it ) Then a gate period is subjectively decided, lets say 30 seconds, (this can be altered for lapped races where you know a racer can’t complete a time in a certain time). A Query is then set up to look to the first time of a competitor and the gate period is added. The software continues looking down the times and deletes any times greater than the first gate time but less than the first gate time plus the gate period.

Step 7
Count timing points
In some races there are laps some are point to point – a lapped race can be thought of as recursive non lap race in that it is a series of non lap races where the end of one non lap race is the start of another. All times for each competitor are sorted in increasing order and an additional field is added with incremented numbers 1,2,3,4 etc. Typically there will be 2 a start and a finish. There can be one where a gun has gone off and competitors are not running across a start matt. In that instance everyone is considered as having the same start time the time of the Starting pistol and start one is given the 2 point and 1 is assumed to be a pre-set time.

Step 8
Pivot the times
Place point number as column header – name of competitor as row heading and time value as the value of the pivot.

Step 9
Calculate the lap times
Simple n+1 time minus n time

Step 10
Add up individual times to get total race time sort by any additional category eg gender age etc… sort time order and allocate prizes appropriately.

Step 11
Pick up whatever pretty report writer you can get your hands on – excel will do at a push and print out and hand to race organisers.

Step 12
Make pretty forms so you can easily change competitors add in extra times for missed times allow for edge cases like DNFs DQs penalties etc.

Sunday 11th of May I had the opportunity to test the software out by acting as lead timer on the Castle of Mey 10k the most northerly 10k in the country. Glad to say worked perfectly. Video below I was in the rented CONDOR van.

Nice when a plan comes together.

About Mark

Mark Brooks a forty something individual working and living in and around Edinburgh
This entry was posted in All, Database Design, Problem Solving. Bookmark the permalink.