Author Topic: using csv order for routes  (Read 546 times)

0 Members and 1 Guest are viewing this topic.

Offline Jim

  • TM Collaborator
  • Hero Member
  • *****
  • Posts: 1279
  • Last Login:Today at 06:33:35 pm
using csv order for routes
« on: June 19, 2018, 10:13:50 pm »
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.

Offline Jim

  • TM Collaborator
  • Hero Member
  • *****
  • Posts: 1279
  • Last Login:Today at 06:33:35 pm
Re: using csv order for routes
« Reply #1 on: June 19, 2018, 10:33:48 pm »
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.

Offline mapcat

  • TM Collaborator
  • Hero Member
  • *****
  • Posts: 684
  • Last Login:Today at 03:27:02 pm
Re: using csv order for routes
« Reply #2 on: June 19, 2018, 11:47:29 pm »
Clinched:

Offline Jim

  • TM Collaborator
  • Hero Member
  • *****
  • Posts: 1279
  • Last Login:Today at 06:33:35 pm
Re: using csv order for routes
« Reply #3 on: June 20, 2018, 05:42:47 pm »
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?

Quote
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..

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

Are routes there not already in csv order?

Offline Jim

  • TM Collaborator
  • Hero Member
  • *****
  • Posts: 1279
  • Last Login:Today at 06:33:35 pm
Re: using csv order for routes
« Reply #4 on: June 20, 2018, 05:44:01 pm »
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.

Offline Jim

  • TM Collaborator
  • Hero Member
  • *****
  • Posts: 1279
  • Last Login:Today at 06:33:35 pm
Re: using csv order for routes
« Reply #5 on: June 20, 2018, 05:55:11 pm »
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..

Quote

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

Offline mapcat

  • TM Collaborator
  • Hero Member
  • *****
  • Posts: 684
  • Last Login:Today at 03:27:02 pm
Re: using csv order for routes
« Reply #6 on: June 20, 2018, 06:02:19 pm »
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.

Quote
Quote
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.
Clinched:

Offline rickmastfan67

  • TM Collaborator (A)
  • Hero Member
  • *****
  • Posts: 575
  • Gender: Male
  • Last Login:Today at 01:33:36 pm
Re: using csv order for routes
« Reply #7 on: June 20, 2018, 06:16:57 pm »
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

Offline oscar

  • TM Collaborator
  • TM Collaborator
  • Hero Member
  • *****
  • Posts: 612
  • Last Login:Today at 06:00:10 pm
    • Hot Springs and Highways pages
Re: using csv order for routes
« Reply #8 on: June 20, 2018, 06:50:47 pm »
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.

Offline Jim

  • TM Collaborator
  • Hero Member
  • *****
  • Posts: 1279
  • Last Login:Today at 06:33:35 pm
Re: using csv order for routes
« Reply #9 on: June 20, 2018, 08:14:39 pm »
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.

Offline Jim

  • TM Collaborator
  • Hero Member
  • *****
  • Posts: 1279
  • Last Login:Today at 06:33:35 pm
Re: using csv order for routes
« Reply #10 on: June 20, 2018, 09:02:14 pm »
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?

Offline Jim

  • TM Collaborator
  • Hero Member
  • *****
  • Posts: 1279
  • Last Login:Today at 06:33:35 pm
Re: using csv order for routes
« Reply #11 on: June 20, 2018, 09:04:20 pm »
^^ 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.

Offline Jim

  • TM Collaborator
  • Hero Member
  • *****
  • Posts: 1279
  • Last Login:Today at 06:33:35 pm
Re: using csv order for routes
« Reply #12 on: June 20, 2018, 09:22:57 pm »
^^ 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.

Offline yakra

  • TM Collaborator
  • Hero Member
  • *****
  • Posts: 1575
  • Last Login:Today at 06:21:47 pm
Re: using csv order for routes
« Reply #13 on: June 20, 2018, 10:40:37 pm »
Quote
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...

Offline Jim

  • TM Collaborator
  • Hero Member
  • *****
  • Posts: 1279
  • Last Login:Today at 06:33:35 pm
Re: using csv order for routes
« Reply #14 on: June 26, 2018, 06:43:11 pm »
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.