Author Topic: Regional Statistics - Discrepancy on Total Clinchable Mileage  (Read 6097 times)

0 Members and 1 Guest are viewing this topic.

Offline Jim

  • TM Collaborator
  • Hero Member
  • *****
  • Posts: 2451
  • Last Login:Today at 04:59:17 pm
Re: Regional Statistics - Discrepancy on Total Clinchable Mileage
« Reply #15 on: July 24, 2017, 02:54:57 pm »
The fix is live on the main site now.  Please report here or in GitHub if it looks like this is still problematic.

Offline bejacob

  • Full Member
  • ***
  • Posts: 208
  • Last Login:Today at 06:37:18 am
Re: Regional Statistics - Discrepancy on Total Clinchable Mileage
« Reply #16 on: July 25, 2017, 09:53:52 am »
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.

Offline new_friends_gr

  • Newbie
  • *
  • Posts: 7
  • Gender: Male
  • Last Login:July 29, 2017, 02:35:01 pm
  • A.J. Bertin
Re: Regional Statistics - Discrepancy on Total Clinchable Mileage
« Reply #17 on: July 29, 2017, 02:35:01 pm »
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!

Offline Markkos1992

  • TM Collaborator
  • Hero Member
  • *****
  • Posts: 2646
  • Last Login:Today at 09:22:03 pm
Re: Regional Statistics - Discrepancy on Total Clinchable Mileage
« Reply #18 on: February 15, 2022, 08:58:03 am »
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).
« Last Edit: February 15, 2022, 11:56:19 am by Markkos1992 »

Offline yakra

  • TM Collaborator
  • Hero Member
  • *****
  • Posts: 3936
  • Last Login:Today at 04:17:36 pm
Re: Regional Statistics - Discrepancy on Total Clinchable Mileage
« Reply #19 on: February 15, 2022, 10:32:44 am »
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.
« Last Edit: February 15, 2022, 10:57:08 am by yakra »
Sri Syadasti Syadavaktavya Syadasti Syannasti Syadasti Cavaktavyasca Syadasti Syannasti Syadavatavyasca Syadasti Syannasti Syadavaktavyasca

Offline yakra

  • TM Collaborator
  • Hero Member
  • *****
  • Posts: 3936
  • Last Login:Today at 04:17:36 pm
Re: Regional Statistics - Discrepancy on Total Clinchable Mileage
« Reply #20 on: February 15, 2022, 12:10:20 pm »
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"
« Last Edit: February 15, 2022, 12:54:56 pm by yakra »
Sri Syadasti Syadavaktavya Syadasti Syannasti Syadasti Cavaktavyasca Syadasti Syannasti Syadavatavyasca Syadasti Syannasti Syadavaktavyasca

Offline michih

  • TM Collaborator
  • Hero Member
  • *****
  • Posts: 3882
  • Last Login:Today at 02:04:23 pm
Re: Regional Statistics - Discrepancy on Total Clinchable Mileage
« Reply #21 on: February 15, 2022, 12:55:20 pm »
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....

Offline yakra

  • TM Collaborator
  • Hero Member
  • *****
  • Posts: 3936
  • Last Login:Today at 04:17:36 pm
Re: Regional Statistics - Discrepancy on Total Clinchable Mileage
« Reply #22 on: February 15, 2022, 01:06:09 pm »
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?
Sri Syadasti Syadavaktavya Syadasti Syannasti Syadasti Cavaktavyasca Syadasti Syannasti Syadavatavyasca Syadasti Syannasti Syadavaktavyasca

Offline Jim

  • TM Collaborator
  • Hero Member
  • *****
  • Posts: 2451
  • Last Login:Today at 04:59:17 pm
Re: Regional Statistics - Discrepancy on Total Clinchable Mileage
« Reply #23 on: February 15, 2022, 01:15:33 pm »
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!).

Offline michih

  • TM Collaborator
  • Hero Member
  • *****
  • Posts: 3882
  • Last Login:Today at 02:04:23 pm
Re: Regional Statistics - Discrepancy on Total Clinchable Mileage
« Reply #24 on: February 15, 2022, 01:19:03 pm »
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?

Offline yakra

  • TM Collaborator
  • Hero Member
  • *****
  • Posts: 3936
  • Last Login:Today at 04:17:36 pm
Re: Regional Statistics - Discrepancy on Total Clinchable Mileage
« Reply #25 on: February 15, 2022, 01:20:20 pm »
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.
Sri Syadasti Syadavaktavya Syadasti Syannasti Syadasti Cavaktavyasca Syadasti Syannasti Syadavatavyasca Syadasti Syannasti Syadavaktavyasca

Offline michih

  • TM Collaborator
  • Hero Member
  • *****
  • Posts: 3882
  • Last Login:Today at 02:04:23 pm
Re: Regional Statistics - Discrepancy on Total Clinchable Mileage
« Reply #26 on: February 15, 2022, 01:29:29 pm »
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 :D

Offline yakra

  • TM Collaborator
  • Hero Member
  • *****
  • Posts: 3936
  • Last Login:Today at 04:17:36 pm
Re: Regional Statistics - Discrepancy on Total Clinchable Mileage
« Reply #27 on: February 15, 2022, 01:31:45 pm »
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?
« Last Edit: February 15, 2022, 01:41:56 pm by yakra »
Sri Syadasti Syadavaktavya Syadasti Syannasti Syadasti Cavaktavyasca Syadasti Syannasti Syadavatavyasca Syadasti Syannasti Syadavaktavyasca

Offline Jim

  • TM Collaborator
  • Hero Member
  • *****
  • Posts: 2451
  • Last Login:Today at 04:59:17 pm
Re: Regional Statistics - Discrepancy on Total Clinchable Mileage
« Reply #28 on: February 15, 2022, 01:45:27 pm »
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.

Offline yakra

  • TM Collaborator
  • Hero Member
  • *****
  • Posts: 3936
  • Last Login:Today at 04:17:36 pm
Re: Regional Statistics - Discrepancy on Total Clinchable Mileage
« Reply #29 on: February 15, 2022, 02:08:46 pm »
Locally converted: overallMileageByRegion & clinchedOverallMileageByRegion

Less sure about systemMileageByRegion & clinchedSystemMileageByRegion.
Shell script, mumble mumble, curl... see if differences...
« Last Edit: February 15, 2022, 03:05:10 pm by yakra »
Sri Syadasti Syadavaktavya Syadasti Syannasti Syadasti Cavaktavyasca Syadasti Syannasti Syadavatavyasca Syadasti Syannasti Syadavaktavyasca