Suboptimal links in user stats

[michihdeu]

http://tm.teresco.org/user/system.php?u=michih&sys=deua

The "Statistics per Route" table links to rte only and shows all routes with the corresponding name of all systems. I think it should link to the route of the selected system only.

http://tm.teresco.org/user/mapview.php?u=michih&rte=A1
-->
http://tm.teresco.org/user/mapview.php?u=michih&rte=A1&sys=deua

That means, both German A1 sections would be shown on a map!

[michihdeu]

It still does NOT work.

[yakra]

http://tm.teresco.org/user/mapview.php?u=michih&rte=A1&sys=deua appears to work now. Good to close this?

[michihdeu]

It always worked.

The problem is that the table does not link to it but to
http://travelmapping.net/user/mapview.php?u=michih&rte=A1 (w/o &sys=deua)

[yakra]

Aah, gotcha. Should be a simple enough fix.

[yakra]

A fix is live on lab2. Give it a try.

[michihdeu]

Thanks 👍 http://205.209.84.174/user/system.php?u=michih&sys=deua
Click on the first A1 route. Then click on the second A1 route. What would you expect?

It's better than before but is it really what you would expect?
Note: The table shows the connected routes.

[michihdeu]

Maybe it's clearer when you compare clicking on A5 and the tiny A5AST route.

[yakra]

It was what I expected based upon the link you posted above, but I see what you mean with the A5 example.
What's shown on the map includes connected routes other than the one that was clicked in the table.

What we need is a way for mapview to display a discrete connected route, i.e., all the chopped routes therein.
Easier said that done; the DB is currently set up so that each connectedRoutes entry only contains a reference to the first chopped route (root) therein:

mysql> select * from connectedRoutes where route = 'A1' and systemName = 'deua';
+------------+-------+--------+-----------+------------+---------+----------+
| systemName | route | banner | groupName | firstRoot  | mileage | csvOrder |
+------------+-------+--------+-----------+------------+---------+----------+
| deua       | A1    |        | Trier     | deurp.a001 | 85.7375 |     2366 |
| deua       | A1    |        | Bremen    | deush.a001 | 380.748 |     2365 |
+------------+-------+--------+-----------+------------+---------+----------+
2 rows in set (0.05 sec)

Given a specific connected route, we don't know what all its chopped routes are.
Seems we need a way to identify, for each Route, what Connected Route it's a member of. We already have a unique identifier for each Connected Route, in the csvOrder column.
We can compute that earlier in the site update process, store the value in each chopped Route object, and add it as an additional column to the Routes table when writing the SQL file.
Then, it's a matter of new SQL queries, new PHP code, and... :)
I'm still pretty new to mapview.php, but I can probably figure it out...

So yeah, #352 is not the ultimate fix we're looking for, but IMO it's still an improvement that we can merge in until getting a better fix off the ground. Thoughts?

[yakra]

It's better than before but

Oh, I can read! Shall we merge it in until working out a better solution?

[michihdeu]

yes, merge it but it's not the final solution for the issue.

[yakra]

I overlooked the connectedRouteRoots table. This may be possible with the data we have in the DB...

[yakra]

Thanks 👍 http://205.209.84.174/user/system.php?u=michih&sys=deua
Click on the first A1 route. Then click on the second A1 route. What would you expect?

First:

mysql> SELECT r.region, r.root, conr.firstRoot, r.route, r.systemName, r.banner, r.city, sys.tier, 
    ->   round(r.mileage, 2) AS total, 
    ->   round(COALESCE(cr.mileage, 0), 2) as clinched 
    -> FROM routes AS r 
    ->   LEFT JOIN clinchedRoutes AS cr ON r.root = cr.route AND traveler = 'michih' 
    ->   LEFT JOIN systems as sys on r.systemName = sys.systemName
    ->   LEFT JOIN connectedRouteRoots AS crr ON r.root = crr.root
    ->   LEFT JOIN connectedRoutes as conr on crr.firstRoot = conr.firstRoot OR conr.firstRoot = r.root
    -> WHERE  
    -> 
    -> (conr.firstRoot = 'deush.a001') AND 
    -> (r.systemName = 'deua')
    -> ORDER BY sys.tier, conr.csvOrder, r.rootOrder;
+--------+---------------+------------+-------+------------+--------+-------------+------+--------+----------+
| region | root          | firstRoot  | route | systemName | banner | city        | tier | total  | clinched |
+--------+---------------+------------+-------+------------+--------+-------------+------+--------+----------+
| DEU-SH | deush.a001    | deush.a001 | A1    | deua       |        |             |    1 |  73.63 |    73.63 |
| DEU-HH | deuhh.a001    | deush.a001 | A1    | deua       |        |             |    1 |  11.74 |    11.74 |
| DEU-NI | deuni.a001buc | deush.a001 | A1    | deua       |        | Buchholz    |    1 |  59.10 |    59.10 |
| DEU-HB | deuhb.a001    | deush.a001 | A1    | deua       |        | Hemelingen  |    1 |   3.52 |     3.52 |
| DEU-NI | deuni.a001dre | deush.a001 | A1    | deua       |        | Dreye       |    1 |   0.14 |     0.14 |
| DEU-HB | deuhb.a001ars | deush.a001 | A1    | deua       |        | Arsten      |    1 |   2.09 |     2.09 |
| DEU-NI | deuni.a001    | deush.a001 | A1    | deua       |        | Cloppenburg |    1 |  69.15 |    69.15 |
| DEU-NW | deunw.a001wer | deush.a001 | A1    | deua       |        | Wersen      |    1 |   1.63 |     1.63 |
| DEU-NI | deuni.a001osn | deush.a001 | A1    | deua       |        | Osnabrück   |    1 |   1.44 |     1.44 |
| DEU-NW | deunw.a001    | deush.a001 | A1    | deua       |        |             |    1 | 158.32 |   158.32 |
+--------+---------------+------------+-------+------------+--------+-------------+------+--------+----------+
10 rows in set (0.37 sec)

Second:

mysql> SELECT r.region, r.root, conr.firstRoot, r.route, r.systemName, r.banner, r.city, sys.tier, 
    ->   round(r.mileage, 2) AS total, 
    ->   round(COALESCE(cr.mileage, 0), 2) as clinched 
    -> FROM routes AS r 
    ->   LEFT JOIN clinchedRoutes AS cr ON r.root = cr.route AND traveler = 'michih' 
    ->   LEFT JOIN systems as sys on r.systemName = sys.systemName
    ->   LEFT JOIN connectedRouteRoots AS crr ON r.root = crr.root
    ->   LEFT JOIN connectedRoutes as conr on crr.firstRoot = conr.firstRoot OR conr.firstRoot = r.root
    -> WHERE  
    -> 
    -> (conr.firstRoot = 'deurp.a001') AND 
    -> (r.systemName = 'deua')
    -> ORDER BY sys.tier, conr.csvOrder, r.rootOrder;
+--------+------------+------------+-------+------------+--------+------+------+-------+----------+
| region | root       | firstRoot  | route | systemName | banner | city | tier | total | clinched |
+--------+------------+------------+-------+------------+--------+------+------+-------+----------+
| DEU-RP | deurp.a001 | deurp.a001 | A1    | deua       |        |      |    1 | 55.36 |    55.36 |
| DEU-SL | deusl.a001 | deurp.a001 | A1    | deua       |        |      |    1 | 30.38 |    30.38 |
+--------+------------+------------+-------+------------+--------+------+------+-------+----------+
2 rows in set (0.35 sec)

So, those are the routes that go in the table, at least.
That's for system.php; the firstRoot value would gets passed to mapview.php as a QS parameter.

How to gather the needed waypoints and segments, and get everything to play nice together in PHP & JS, is still a work in progress. There are a lot of moving parts to learn about.

[yakra]

A good test case for development; few waypoints & few segments:

mysql> SELECT r.region, r.root, conr.firstRoot, r.route, r.systemName, r.banner, r.city, sys.tier, 
    ->   round(r.mileage, 2) AS total, 
    ->   round(COALESCE(cr.mileage, 0), 2) as clinched 
    -> FROM routes AS r 
    ->   LEFT JOIN clinchedRoutes AS cr ON r.root = cr.route AND traveler = 'michih' 
    ->   LEFT JOIN systems as sys on r.systemName = sys.systemName
    ->   LEFT JOIN connectedRouteRoots AS crr ON r.root = crr.root
    ->   LEFT JOIN connectedRoutes as conr on crr.firstRoot = conr.firstRoot OR conr.firstRoot = r.root
    -> WHERE  
    -> 
    -> (conr.firstRoot = 'me.nh113b') AND 
    -> (r.systemName = 'usanh')
    -> ORDER BY sys.tier, conr.csvOrder, r.rootOrder;
+--------+-----------+-----------+--------+------------+--------+------+------+-------+----------+
| region | root      | firstRoot | route  | systemName | banner | city | tier | total | clinched |
+--------+-----------+-----------+--------+------------+--------+------+------+-------+----------+
| ME     | me.nh113b | me.nh113b | NH113B | usanh      |        |      |    4 |  1.02 |     0.00 |
| NH     | nh.nh113b | me.nh113b | NH113B | usanh      |        |      |    4 |  2.94 |     0.00 |
+--------+-----------+-----------+--------+------------+--------+------+------+-------+----------+
2 rows in set (0.16 sec)

[jteresco]

I've obviously been deep in SQL, PHP, and JS for the last couple of weeks. So now might be a good time for me to look at this.

Is the work mentioned above that was being tested on lab2 in the master repository? Can you (meaning either @yakra or @michihdeu) give me the quick summary of what doesn't work as expected given the work done in late 2019?

[michihdeu]

http://travelmapping.net/user/system.php?u=michih&sys=deua
Click on the first A1 entry. It is one out of two connected (!) routes but both are loaded.
Click on the 2nd A1 entry, and the very same link is opened.

It's more obvious when you click the 276mi A5 route and the 0.29mi A5AST route.

The problem is that ALL connected routes of A1 (or A5) are loaded, not just the selected one.

[jteresco]

I see - it's an issue I've been aware of from the start, in the context of the duplicate numbers on the US Interstates, which all show up in this view. I think it's a case of those links trying to use Mapview for a purpose it wasn't necessarily intended (the combination of rte and sys QS parameters).

I'd like to delay this until I have a chance to revamp the rest of the Mapview functionality to use the new back end SQL code, at which time I can add a way to load only routes within a given connected route.

[yakra]

None of my work after #352 is in the master repo.
I have a local git stash on BiggaTomato that I didn't get very far with; I'm not worried about it becoming irrelevant due to newer code.

[jteresco]

The new cr= functionality is now being used in this spot on tmtest. I think it's what we want. Looking forward to closing an almost 4-year-old issue once people have had a chance to try it out in various situations.

[michihdeu]

I think it works 😄 when _con.csv is correct. Already found incorrect _con.csv entries in my regions 😞

TravelMapping/HighwayData#3956