Travel Mapping

Web Design Discussion => General Web Design Discussion => Topic started by: Jim on June 19, 2018, 10:13:50 pm

Title: using csv order for routes
Post by: Jim 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.
Title: Re: using csv order for routes
Post by: Jim 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.
Title: Re: using csv order for routes
Post by: mapcat on June 19, 2018, 11:47:29 pm
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/region.php?units=miles&u=mapcat&rg=AR).

http://tmtest.teresco.org/user/system.php?units=miles&u=mapcat&sys=usaif&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 (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?
Title: Re: using csv order for routes
Post by: Jim 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 (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 (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?
Title: Re: using csv order for routes
Post by: Jim 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.
Title: Re: using csv order for routes
Post by: Jim on June 20, 2018, 05:55:11 pm
http://tmtest.teresco.org/user/system.php?units=miles&u=mapcat&sys=usaif&rg=AR (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.
Title: Re: using csv order for routes
Post by: mapcat 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/region.php?units=miles&u=mapcat&rg=AR)


(https://drive.google.com/uc?export=download&id=1JjULATg-ga1D7c--WipDDmju5eG4xfsA)


http://tmtest.teresco.org/user/system.php?units=miles&u=mapcat&sys=usaif&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 (http://tmtest.teresco.org/hb/index.php?units=miles&sys=usaar&u=mapcat)


(https://drive.google.com/uc?export=download&id=1s80IxJUXlZVA4Y7hNu9TaMsD8V0BgREb)


Same order they appear in the first table.
Title: Re: using csv order for routes
Post by: rickmastfan67 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
Title: Re: using csv order for routes
Post by: oscar 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.
Title: Re: using csv order for routes
Post by: Jim 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.
Title: Re: using csv order for routes
Post by: Jim 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?
Title: Re: using csv order for routes
Post by: Jim 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.
Title: Re: using csv order for routes
Post by: Jim 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.
Title: Re: using csv order for routes
Post by: yakra 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 (http://www.catb.org/jargon/html/C/crock.html), but...
Title: Re: using csv order for routes
Post by: Jim 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.
Title: Re: using csv order for routes
Post by: Highway63 on June 28, 2018, 01:50:05 am
Interstates and US routes are appearing in random order in Map View.

My WI USH order: 141, 8, 53, 151, 10, 141N, 14, 18, 45, 2, 51, 63, 12, 41, 61, 2W, then the auxiliary routes in order. That's not by number, total miles, or clinched miles. State highways are all in order.
Title: Re: using csv order for routes
Post by: mapcat on June 28, 2018, 08:54:08 am
Interstates and US routes are appearing in random order in Map View.

My WI USH order: 141, 8, 53, 151, 10, 141N, 14, 18, 45, 2, 51, 63, 12, 41, 61, 2W, then the auxiliary routes in order. That's not by number, total miles, or clinched miles. State highways are all in order.
Confirmed.  And it seems to be limited to US routes and Interstates; other tier 1 and 2 systems (such as usaib and canqca) look fine.
Title: Re: using csv order for routes
Post by: Bickendan on June 29, 2018, 05:04:44 am
Is there a hierarchy for the aux US routes?
Byp 14 Brookings, SD is listed before Trk 14 Pierre. If intentional, great; if not, Trk 14 should be first as it's further west.
Title: Re: using csv order for routes
Post by: Bickendan on June 29, 2018, 05:43:23 am
Split interstates (76, 84 notably) are listed in non-sequential order.
76: CO, OH, NE, PA, NJ
84: OR, PA, ID, NY, UT, CT, MA

86 and 88 avoid this by only having one state in their western portions.
Title: Re: using csv order for routes
Post by: Jim on June 29, 2018, 10:15:11 am
I did some experimenting with more complicated SQL queries and came up with one that seems to order things just as we'd want.  The downside is that it's slow.  There will be a delay of a few seconds on smaller areas, maybe a minute on large (like loading all of the USA data).

Try it by using the tmtest.teresco.org server.  So far, I've only modified mapview but will look into a couple other spots for similar orderings.
Title: Re: using csv order for routes
Post by: Jim on June 29, 2018, 10:26:05 am
region.php on the tmtest server is also now using the more expensive but hopefully preferred ordering.
Title: Re: using csv order for routes
Post by: Jim on July 05, 2018, 09:18:57 am
Unless I hear objections soon, I'll put the new ordering code into place.  It will slow down loading somewhat on larger sets.
Title: Re: using csv order for routes
Post by: Jim on July 06, 2018, 09:59:43 pm
mapview and region.php on the production server are now using the new and improved but somewhat slower implementation that improves order of routes listed by using csv file ordering.