using csv order for routes

[Jim]

On the tmtest server, I've sprinkled in some "ORDER BY" clauses to order routes displayed by various pages to match the order they appear in the CSV files.  So far, I've hit mapview.php, region.php, and system.php.  Please have a look at these and let me know where else might benefit from this kind of ordering.

[Jim]

Note that the recent DB enhancement does not include the order of routes within a connected route, only the order of routes and connected routes within their csv for their systems, so that kind of ordering has not improved.

[mapcat]

I'm not seeing the new behavior in http://tmtest.teresco.org/user/region.php?units=miles&u=mapcat&rg=AR.

http://tmtest.teresco.org/user/system.php?units=miles&u=mapcat&sys=usaif&rg=AR returns an error.

http://tmtest.teresco.org/user/mapview.php?units=miles&u=mapcat&rg=AR seems ok.

Would you consider using it for the tables in the HB?

[Jim]

I'm not seeing the new behavior in http://tmtest.teresco.org/user/region.php?units=miles&u=mapcat&rg=AR.

What's not ordered as expected?

http://tmtest.teresco.org/user/system.php?units=miles&u=mapcat&sys=usaif&rg=AR returns an error.

Looks like I didn't consider that someone might combine a system and a region..

Would you consider using it for the tables in the HB?

Are routes there not already in csv order?

[Jim]

Note that the recent DB enhancement does not include the order of routes within a connected route, only the order of routes and connected routes within their csv for their systems, so that kind of ordering has not improved.

This information is now also in the test DB (and will be in the main DB after tonight's site update) so modifications to the web front end that use it can start to be tested soon.

[Jim]

http://tmtest.teresco.org/user/system.php?units=miles&u=mapcat&sys=usaif&rg=AR returns an error.

Looks like I didn't consider that someone might combine a system and a region..

No, wait, I just realized that URL was to system.php, not mapview.php.  Fixed the bad query, I believe.

[mapcat]

What's not ordered as expected?

Here's usaar.csv's first 10 lines:

systemName;region;route;banner;abbrev;city;root;altRouteNames
usaar;AR;AR1;;McG;McGehee;ar.ar001mcg;
usaar;AR;AR1;;;Jonesboro;ar.ar001;
usaar;AR;AR1B;;DeW;DeWitt;ar.ar001bdew;
usaar;AR;AR1B;;Jon;Jonesboro;ar.ar001bjon;
usaar;AR;AR1B;;Che;Cherry Valley;ar.ar001bche;
usaar;AR;AR1B;;Mar;Marianna;ar.ar001bmar;
usaar;AR;AR1B;;For;Forrest City;ar.ar001bfor;
usaar;AR;AR1B;;Van;Vanndale;ar.ar001bvan;
usaar;AR;AR1S;;;;ar.ar001s;
usaar;AR;AR1Y;;;;ar.ar001y;

The first 10 entries from usaar on http://tmtest.teresco.org/user/region.php?units=miles&u=mapcat&rg=AR





http://tmtest.teresco.org/user/system.php?units=miles&u=mapcat&sys=usaif&rg=AR is working now.

Would you consider using it for the tables in the HB?

Are routes there not already in csv order?

Not here: http://tmtest.teresco.org/hb/index.php?units=miles&sys=usaar&u=mapcat





Same order they appear in the first table.

[rickmastfan67]

Seems to be working fine for FL, as it's put the A1A routes in the correct place, as well as FL-9336.
http://tmtest.teresco.org/user/region.php?units=miles&u=rickmastfan67&rg=FL

[oscar]

Seems to be working fine for FL, as it's put the A1A routes in the correct place, as well as FL-9336.
http://tmtest.teresco.org/user/region.php?units=miles&u=rickmastfan67&rg=FL

But not for Hawaii. For example, the .csv files have HI 3000 right after HI 2000 and right before HI 3400. That's not what either the test version or the regular version show.

Both versions are ordered by the first two digits of the route number. That matches the csvs for two-digit routes (for which the first digit is 0), but things get goofy for the three- and four-digit routes which don't have leading zeroes.

[Jim]

Seems to be working fine for FL, as it's put the A1A routes in the correct place, as well as FL-9336.
http://tmtest.teresco.org/user/region.php?units=miles&u=rickmastfan67&rg=FL

But not for Hawaii. For example, the .csv files have HI 3000 right after HI 2000 and right before HI 3400. That's not what either the test version or the regular version show.

Both versions are ordered by the first two digits of the route number. That matches the csvs for two-digit routes (for which the first digit is 0), but things get goofy for the three- and four-digit routes which don't have leading zeroes.

It was coming out of the DB in the intended order, but the sorting of the HTML table was redoing it strictly alphabetically.  For the moment, I've removed the table sorting from that one and we get CSV order.  I don't know how to get the table to sort by CSV order without adding a column to the table which would show an otherwise meaningless number.  If we have to have one or the other, would we want routes shown in CSV order or the ability to sort by columns?  Better yet, anyone looking to learn how to configure this tablesorter code better or find something to replace it?  I'm not sure when it got added, but it wasn't by me.  It's been a nice feature, but I don't know it well enough to make it work properly everywhere.

[Jim]

This looks like what I'd want for ordering for mapview with rte= parameters:

http://tmtest.teresco.org/user/mapview.php?units=miles&u=terescoj&rte=I-90

This is the first place using the rootOrder column to honor order of the segments of connected routes.  Where else should this be applied?

[Jim]

^^ the place where my current SQL will fail is for routes within a tier where there's a connected route of at least two parts.  It will sort first by tier, then connected csv order, then it's system's csv order.

[Jim]

^^ example:

http://tmtest.teresco.org/user/mapview.php?units=miles&u=terescoj&rte=I-80

Notice that the last two business routes are the second halves of a route that crosses a state line.

[yakra]

adding a column to the table which would show an otherwise meaningless number

How about a column of (near?) zero width, zero-point font, text color = background color?
It's a crock, but...

[Jim]

What was only on tmtest is now on the main travelmapping.net server.  Hopefully this gives more sane route orderings at least in most cases.