Race distances

Question

Hi

Why does this:

select count(*) from historic_races_beta hra
inner join daily_races_beta dra on dra.race_id = hra.race_id
where dra.distance_yards <> hra.distance_yards

Give me 77k rows?  Surely the race distances should be consistent?  This really throws out my model.

Thanks

Rob

Answer 1

Hi - likely you are encountering the fact that the daily racecard feed includes the exact distance yards, subject to rail movements etc, and historic data often includes the rounded distance in yards or distance in yards corresponding to furlongs.  They are usually small variations but will not always be an exact match.

If you want to join between both tables, a distance_furlongs variable is useful, as supplied in the *insights tables - see the schemas at:

https://www.betwise.co.uk/smartform/historic_runners_insights

This will make joining on distances between the tables easier.

Comments

Hi

I don't see the insights tables, I only have races and runners tables, daily/historic and beta.  I've just installed the latest updater but the scripts don't pull down any extra tables.

Figured this out now...

Thanks
Rob

---

As per the initial install for the main database, before the updater can automatically update the insights tables (for both daily and historic runners), you must first download, unzip and install the base tables.  

If you log in to the site as a member, using the links provided, namely
https://www.betwise.co.uk/smartform/historic_runners_insights

.. scrolling down to the end of the page (last section) is a "Downloading the data" section with a link to the data and the curl command that you can use to fetch it.

Similarly you can do the same to install the daily_runners insights table:
https://www.betwise.co.uk/smartform/daily_runners_insights

---

Sorry Colin, I've installed the insights table and I'm not sure how this will help me.  When I'm predicting today's results, I have to use daily_races_beta.distance_yards and I can't really automatically match that to distances in historical races as they are rounded to furlongs.  

Is there no historic data that exactly matches the daily distances?  I find this a bit surprising given it's one of the key metrics.

---

You can of course use distance yards in daily races, but without understanding the model that you are using (and based solely on the requirement to match exactly the same distance measurement in daily and historic races), then using distance_furlongs is the exact match to use to compare distances.  Eg. 1544 yards reported as the distance for an upcoming race due to some intended rail movement that is subsequently reported in the results as 1540 yards in historic races are both 7 furlongs.  Or if you prefer to keep as yards, then they are both 1540 yards.  

Thus you can use the insights tables or a function or SQL expression as described below to create a distance_furlongs variable in both historic and daily data that can then be matched.

So how you choose to do this (if required) is up to you - you can create a view which incorporates distance_furlongs from daily and historic insights and creates a new field in a view table, or you can incorporate this as a direct join which joins distance_furlongs in insights to the other tables for daily and historic.  

Alternatively you could also  create a distance yards variable easily (in either your SQL query or a programming language) by an expression/ function equivalent to round(distance_yards/220).

---

Thanks Colin.  What I tend to do is bring race distances into a fairly smallish subset - I seem get the best profit results this way.  E.g. for race id 1260609, daily_races_beta has distance_yards of 2419, but the historic_races_beta has this at 2420.  My code essentially checks yardages, in this example, like this:

        ...
        } else if (yards < 2420) {
            race.setRaceDistance(2200L);
        } else if (yards < 3080) {
            race.setRaceDistance(2640L);
        }
        ...

so the problem here for race id 1260609 is that 2419 (on daily_races_beta) will bring it down to 2200 yards, but historic_races_beta will take it up to 2640 (where there is slightly more race history).

I can just use the nearest furlong as you suggest, which gives 2420 yards, but I actually end up with less choice of winners this way which results in less profit overall.