I know I’ve given an overview of what I did to create the timing system but here is the kernel explanation of the algebra modeled in SQL that does the real heavy lifting in my system and probably most other timing systems as well.
A review of the problem firstly;
The data works on the principle of a chip passing a matt. As the chip passes the matt times are recorded in a timing box which will then be passed through to a database.
Problem is that the timing matt will give multiple readings every time a runner runs across the matt and he or she may be running across multiple times the results only need the first of those passes and not any of the other reads as they are duplicates of a single pass.
If we take a simple example.
Imagine a race with two runners Runner 1 and Runner 2.
Runner 1 and Runner 2 start at the same time 01:00:00 by both passing a matt
Runner 1 finishes across the matt first at 01:30:00
Runner 2 finishes second at 01:35:00
The timing box picks up all following times
ID RFIDtag Time
1 Runner1 01:00:00
2 Runner1 01:00:07
10 Runner2 01:00:00
11 Runner2 01:00:05
15 Runner1 01:30:00
16 Runner1 01:30:10
17 Runner2 01:35:00
18 Runner2 01:35:01
19 Runner2 01:35:02
The first step would be to sort these by the Runner
So that we get
MyTable (sorted first by RFIDtag then by time.
RFIDtag Time
Runner1 01:00:00
Runner1 01:00:07
Runner1 01:30:00
Runner1 01:30:10
Runner2 01:00:00
Runner2 01:00:05
Runner2 01:35:00
Runner2 01:35:01
Runner2 01:35:0
We then need to somehow programmatically or via a query identify times that are relevant against times that are not relevant. So how is this done? Well the trick here is to algebraically model this in SQL using alias tables. You compare the table above against itself and only select for those times where the runner is the same. Here we use a 20 second window or gate and ID field is added back into MyTable (ID field MUST be automatically incremented and unique ie a primary key)
SELECT T1.*
FROM MyTable T1
WHERE EXISTS
(SELECT T2.*
FROM MyTable T2
WHERE T2.RFIDtag = T1.RFIDtag
AND T2.ID < T1.ID
AND T2.Time <= T1.Time
AND T2.Time >= T1.Time - TimeSerial(0, 0, 20));
In my database application I create a variable for the 20 number which in this example equates to 20 seconds and I have made it easy to alter this period through a simple user interface. I call this period the GATE and it means that for a given race an appropriate gate can be set. In this way the application easily copes with lapped races.
A really beautiful and simple pattern which must be used very widely.
