Regional Statistics - Discrepancy on Total Clinchable Mileage

[Jim]

The fix is live on the main site now.  Please report here or in GitHub if it looks like this is still problematic.

[bejacob]

I have a possible fix for region.php on the tmtest server now.  It no longer will list devel systems, but I never thought they made sense on the stat pages anyway since we can't clinch them.  Please let me know how it looks.

I just checked my ABW routes, which was how I identified the issue in the first place, and the discrepancy is gone. This appears to have fixed the problem. Thank you.

[new_friends_gr]

The problem I had is fixed now too. I even looked at one of my other states for which I have Interstate highway mileage where there's concurrencies in that state, and the separate pages match now in terms of Interstate mileage.

Thanks, Jim!

[Markkos1992]

I was looking for the thread that the oscar .01 mileage difference issue was mentioned in, but I could not find it.

I bring it up because my log file is currently showing myself having 11290.95 miles in PA while my stats page shows myself having 11290.90 miles.

The overall mileage of 42044.44 miles in active status and 42059.50 miles in active and preview is the same on both pages.  I added preview mileage on my last trip because of the Korean War Veterans Pkwy being in the maybe activated soon usanyp instead of usasf.

I did not see any discrepancies in my other regions.

EDIT:  There is also a difference between the total mileage on my stats (17653.20 miles) and my log file (17653.22 miles).

[yakra]

Running a site update on lab2 to see if the C++ siteupdate program produces different results.
Edit 2: Same results.

Edit 1: Rounding errors & point-to-point great circle distance: the curious case of Oscar's mileage on ME103
That was the smallest of freak occurrences, and produced the only diff in anyone's stats sitewide at the time.
Whatever's going on here has got to be something different. Multiple errors that far down in the sig figs won't add up to a discrepancy this big.

[yakra]

The 11290.9 figure is ostensibly stored directly in the DB (the tm_convert_distance function then adds back one more sig fig):

Code: [Select]

mysql> describe clinchedOverallMileageByRegion;
+----------------------+-------------+------+-----+---------+-------+
| Field                | Type        | Null | Key | Default | Extra |
+----------------------+-------------+------+-----+---------+-------+
| region               | varchar(8)  | YES  |     | NULL    |       |
| traveler             | varchar(48) | YES  |     | NULL    |       |
| activeMileage        | float       | YES  |     | NULL    |       |
| activePreviewMileage | float       | YES  |     | NULL    |       |
+----------------------+-------------+------+-----+---------+-------+
4 rows in set (0.00 sec)

mysql> select * from clinchedOverallMileageByRegion where region = 'PA' and traveler = 'markkos1992';
+--------+-------------+---------------+----------------------+
| region | traveler    | activeMileage | activePreviewMileage |
+--------+-------------+---------------+----------------------+
| PA     | markkos1992 |       11290.9 |              11290.9 |
+--------+-------------+---------------+----------------------+
1 row in set (0.02 sec)

But the line from the .sql file itself is:
,('PA','markkos1992','11290.9455438252','11290.9455438252')

Is this a MySQL bug? Why is this getting rounded so far down?
Other regions are rounded to different precisions that make little to no sense:

,('VA','markkos1992','7032.97920062242','7032.97920062242')

Code: [Select]

mysql> select * from clinchedOverallMileageByRegion where region = 'VA' and traveler = 'markkos1992';
+--------+-------------+---------------+----------------------+
| region | traveler    | activeMileage | activePreviewMileage |
+--------+-------------+---------------+----------------------+
| VA     | markkos1992 |       7032.98 |              7032.98 |
+--------+-------------+---------------+----------------------+
1 row in set (0.01 sec)

,('NH','markkos1992','2.86282452101549','2.86282452101549')

Code: [Select]

mysql> select * from clinchedOverallMileageByRegion where region = 'NH' and traveler = 'markkos1992';
+--------+-------------+---------------+----------------------+
| region | traveler    | activeMileage | activePreviewMileage |
+--------+-------------+---------------+----------------------+
| NH     | markkos1992 |       2.86282 |              2.86282 |
+--------+-------------+---------------+----------------------+
1 row in set (0.01 sec)

Jim, what results do you get for the same SQL queries on noreaster?

I get comparable results with grep "'$rg','markkos1992'" "home/tmp/tm/TravelMapping-2022-02-14@22:27:17.sql"

[michih]

I was curious. 2 out of the my first 10 regions were different, so I checked all 86 regions. 172 values. But didn't find more.

First line log, second line web (in log format so that the diffs can be seen):

Code: [Select]

DEU-BY: 11219.47 of 13849.34 mi (81.01%), 11265.43 of 13930.47 mi (80.87%)
DEU-BY: 11219.50 of 13849.30 mi (81.01%), 11265.40 of 13930.50 mi (80.87%)
Active traveled is off by 0.03mi.
Active total is off by 0.04mi.
Preview traveled is off by 0.03mi.
Preview total is off by 0.03mi.

Code: [Select]

DEU-HE: 2563.86 of 6598.61 mi (38.85%), 2563.86 of 6598.61 mi (38.85%)
DEU-HE: 2563.85 of 6598.61 mi (38.85%), 2563.85 of 6598.61 mi (38.85%)
Both travel values are off by 0.01mi

The only conspicuity is that BY and HE are neighboring regions....

[yakra]

grep 'DEU-BY','michih' /home/tmp/tm/TravelMapping-2022-02-14@22:27:17.sql
NEer: ,('DEU-BY','michih','11219.469861011894','11265.430332183509')
lab2: ,('DEU-BY','michih','11219.4698610119','11265.4303321835')

Code: [Select]

mysql> select * from clinchedOverallMileageByRegion where region = 'DEU-BY' and traveler = 'michih';
+--------+----------+---------------+----------------------+
| region | traveler | activeMileage | activePreviewMileage |
+--------+----------+---------------+----------------------+
| DEU-BY | michih   |       11219.5 |              11265.4 |
+--------+----------+---------------+----------------------+
1 row in set (0.02 sec)

grep 'DEU-BY','michih' /home/tmp/tm/TravelMapping-2022-02-14@22:27:17.sql
NEer: ,('DEU-HE','michih','2563.855005560839','2563.855005560839')
lab2: ,('DEU-HE','michih','2563.85500556084','2563.85500556084')

Code: [Select]

mysql> select * from clinchedOverallMileageByRegion where region = 'DEU-HE' and traveler = 'michih';
+--------+----------+---------------+----------------------+
| region | traveler | activeMileage | activePreviewMileage |
+--------+----------+---------------+----------------------+
| DEU-HE | michih   |       2563.85 |              2563.85 |
+--------+----------+---------------+----------------------+
1 row in set (0.02 sec)

What's with DEU-BY getting rounded up while DEU-HE gets truncated?

[Jim]

Maybe we need to try making it a DOUBLE instead of FLOAT for those entries, as they seem to be limited to 6 digits of precision whether before or after the decimal point (though a float should do much better than that!).

[michih]

Maybe we need to try making it a DOUBLE instead of FLOAT for those entries

I also thought about a data type issue.... but why does it only affect so less values?

And why do we mostly face diffs only for traveled mileage - only BY totals are different so far?

[yakra]

I wonder if under the hood, that's fixed, or text, or something.
In .dbf files, numeric fields are actually just stored as text. The more you know...

I can give DOUBLEs a try on lab2 and see what I come up with.

[michih]

And why do we mostly face diffs only for traveled mileage - only BY totals are different so far?

nope. CZE has also diffs for active and active+preview totals (12875.58 vs 12875.60), Finland for active (16873.6 vs. 16873.62),.... I just didn't compare them with the first run

[yakra]

Crossing my fingers & trying out a
sed 's~FLOAT~DOUBLE~g' 'TravelMapping-2022-02-15@10:32:20.sql' | mysql --defaults-group-suffix=travmapadmin -u travmapadmin TravelMapping

Edit: This appears to fix it. Shall I go ahead & make the changes to siteupdate?

[Jim]

Sure, let's change that where needed.  More memory/disk but we have plenty of that.  I don't think we want a blanket FLOAT->DOUBLE, just placed where numbers are likely to get larger.

[yakra]

Locally converted: overallMileageByRegion & clinchedOverallMileageByRegion

Less sure about systemMileageByRegion & clinchedSystemMileageByRegion.
Shell script, mumble mumble, curl... see if differences...