Store _con.csv order in the DB

[jteresco]

To support things like TravelMapping/Web#40 better we could add a field to each route with its position in the corresponding connected route. This wouldn't directly address "chopped up" routes like I-69, I-73, etc, as discussed in #55, and it would be nice to include a solution that would allow us to order those properly as well.

[jteresco]

Looking at https://github.com/TravelMapping/HighwayData/blob/2edcc244bf6173635f9f7f064692d496aab758cc/hwy_data/_systems/usai_con.csv#L72 for example, we might be able to get the chopped routes handled correctly by not resetting the position counter unless the "route" field changes.

[michihdeu]

Not sure that I got you right...

_con.csv:

usai;I-74;;Iowa - Ohio;ia.i074,il.i074,in.i074,oh.i074
usai;I-74;;Mount Airy, NC;nc.i074
usai;I-74;;High Point, NC;nc.i074hig
usai;I-74;;Pembroke, NC;nc.i074pem

--> add new "order" column to csv:

usai;IA;I-74;;;;ia.i074;;1
usai;IL;I-74;;;;il.i074;;2
usai;IN;I-74;;;;in.i074;;3
usai;OH;I-74;;;;oh.i074;;4
usai;NC;I-74;;;Mount Airy, NC;nc.i074;;1
usai;NC;I-74;;Hig;High Point, NC;nc.i074hig;I-74Sea,I-74_E,I-74FutEll,I-74FutHig;1
usai;NC;I-74;;Pem;Pembroke, NC;nc.i074pem;;1

I think it would work but what's about compatibility?

I think we could automatically convert all csv files (if there's no objection by anyone don't wanna has his files touched at all):
Read _con.csv, take root column position and put this value to the new order column in the corresponding .csv file.

I'd love to see #55 implemented

[jteresco]

No, no changes would be needed to existing CSV files. The extra information can be gathered from existing files.

[jteresco]

Of course this would assume that each _con.csv file has its routes in the proper order, but I expect most if not all already do.

[michihdeu]

Ok, got it!

[jteresco]

The systems, routes, and connectedRoutes tables now have the csvOrder column available. I'll see if I can sprinkle "order by" qualifiers on some SQL queries to improve ordering on the web front end. Any requests?

[ssoworld]

Consider leading zeros in the map view (1,10,2,3,300,4 should be 1,2,3,4,10,300) From: Jim Teresco <notifications@github.com> Sent: Monday, June 18, 2018 3:26 PM To: TravelMapping/DataProcessing <DataProcessing@noreply.github.com> Cc: Subscribed <subscribed@noreply.github.com> Subject: Re: [TravelMapping/DataProcessing] Store _con.csv order in the DB (#87) The systems, routes, and connectedRoutes tables now have the csvOrder column available. I'll see if I can sprinkle "order by" qualifiers on some SQL queries to improve ordering on the web front end. Any requests? — You are receiving this because you are subscribed to this thread. Reply to this email directly, view it on GitHub<#87 (comment)>, or mute the thread<https://github.com/notifications/unsubscribe-auth/AGO3UsDk04XV6Y_ehFC4eml62yPIN-bKks5t-Ay9gaJpZM4UQPc8>.

[jteresco]

The systems, routes, and connectedRoutes tables now have the csvOrder column available. I'll see if I can sprinkle "order by" qualifiers on some SQL queries to improve ordering on the web front end. Any requests?