Closed Topvennie closed 5 months ago
Are the differences between RL and SL errors? Have you found where the difference happens? (especially for HILOK and VTK), since these have pretty consistent lap times
Are the differences between RL and SL errors? Have you found where the difference happens? (especially for HILOK and VTK), since these have pretty consistent lap times
Lets start with the errors for HILOK (1) and VLK (3). If you look at the query , in no_duplicates I make a new column called first_timestamps. This column is aimed to filter out the first registered lap as SL tended to assign one additional lap to most teams. However, this surplus lap primarily came from teams registering some detections at station 7 at the start of the race. Modifying the query to start lap counting only when teams pass station 2 or 3 for the first time solves the lap problem for HILOK and VLK. However it gave teams 9, 12, 16 and 22 an extra lap. After looking at the lap times I couldn't find the cause of this problem as the lap times appeared consistent with those from EL.
As for VTK (2): They jump twice from station 6 to station 2. RL allows for a leeway of 2 missed stations, while SL permits only one. Increasing the leeway to 2 would resolve it. However it would also inflate the lap count for team 19 by three laps. Ultimately I opted to keep the leeway at 1, as one of the reasons for VTK's jump is because of a switchover which I believe is an isolated instance of misfortune.
I've pushed a new revised query, which results in a slight increase in the total number of wrong laps (from 11 to 12). However, aside from VTK, I couldn't find any significant issues.
Team ID | EL | RL | SL | Diff EL - RL | Diff EL - SL | Diff RL - SL
1 | 873 | 873 | 873 | 0 | 0 | 0 2 | 867 | 867 | 865 | 0 | -2 | -2 3 | 774 | 774 | 774 | 0 | 0 | 0 4 | 738 | 738 | 738 | 0 | 0 | 0 5 | 738 | 737 | 738 | 1 | 0 | 1 8 | 693 | 693 | 693 | 0 | 0 | 0 9 | 672 | 673 | 674 | 1 | 2 | 1 11 | 654 | 656 | 656 | 2 | 2 | 0 12 | 610 | 611 | 611 | 1 | 1 | 0 14 | 586 | 586 | 586 | 0 | 0 | 0 15 | 672 | 672 | 673 | 0 | 1 | 1 16 | 611 | 611 | 610 | 0 | -1 | -1 17 | 587 | 586 | 586 | -1 | -1 | 0 18 | 580 | 580 | 580 | 0 | 0 | 0 19 | 581 | 585 | 582 | 4 | 1 | -3 20 | 577 | 576 | 576 | -1 | -1 | 0 22 | 618 | 618 | 618 | 0 | 0 | 0
I'll add it as an issue ;)
Slapper
Introducing Slapper, the lapper written in SQL!
Logic
Slapper's lap-counting logic is inspired by
Robust Lapper
. Both lappers determine if a new lap is completed by analyzing the difference between station IDs. The main differences lay in the data preprocessing.The objective is to identify a sequence of detections progressing sequentially from station 1 to 7. Slapper achieves this by selecting the most likely location of a runner every second and then filtering out any duplicate entries. For instance, if a runner is detected at station 1 for five consecutive seconds, only the first detection is retained. This process results in a series of cycles spanning from station 1 to 7. The final step involves counting how many times the sequence returns from station 7 to 1. To account for possible station failures baton transitions from station 6 to 1 or 7 to 2 are also accepted.
At last Slapper is sensitive to detections switching between 1 and 7 (whether intentional or accidental). However these rare instances are filtered by the data preprocessing and seeing as it is a race it's unlikely someone will actually try this on purpose.
Results
The following table shows the results of last years 12urenloop using three lapper:
External Lapper
(EL),Robust Lapper
(RL) and Slapper (SL)Team ID | EL | RL | SL | Diff EL - RL | Diff EL - SL | Diff RL - SL
1 | 873 | 873 | 872 | 0 | -1 | -1
2 | 867 | 867 | 865 | 0 | -2 | -2
3 | 774 | 774 | 773 | 0 | -1 | -1
4 | 738 | 738 | 738 | 0 | 0 | 0
5 | 738 | 737 | 738 | 1 | 0 | 0
8 | 693 | 693 | 693 | 0 | 0 | 0
9 | 672 | 673 | 673 | 1 | 1 | 0
11 | 654 | 656 | 656 | 2 | 2 | 0
12 | 610 | 611 | 610 | 1 | 0 | -1
14 | 586 | 586 | 586 | 0 | 0 | 0
15 | 672 | 672 | 673 | 0 | 1 | 1
16 | 611 | 611 | 611 | 0 | 0 | 0
17 | 587 | 586 | 587 | -1 | 0 | 1
18 | 580 | 580 | 580 | 0 | 0 | 0
19 | 581 | 585 | 582 | 4 | 1 | -3
20 | 577 | 576 | 576 | -1 | -1 | 0
22 | 618 | 618 | 619 | 0 | 1 | 1
Slapper sits somewhere between
External lapper
andRobust Lapper
.Speed
Both
Robust Lapper
and Slapper were executed 20 times 10 times with an empty lap table and 10 times with an already filled lap table.Empty lap table
Robust Lapper
Median
: 1 474 msAverage
: 1 433.8 msSlapper
Median
: 638.5 msAverage
: 628.6 msFilled lap table
Robust Lapper
Median
: 1 298 msAverage
: 1 296.3 msSlapper
Median
: 558.5 msAverage
: 554.1 ms