Querying TM data to get lists of route numbers

[Eiddderf]

Over at the AARoads Wiki we have gone back and forth on how we want to recreate lists of highways like List of highways numbered 1 that are still on Wikipedia.  Do we import like we've done with most other content or do we start from scratch?  Someone, I think Scott5114, asked about using TM to populate the lists.  Now, the lists wouldn't be dynamic or anything, but it would give us really good base to start from.  The trouble is, we don't really know how to get this data.  All we'd really need is the ability to find the country, state/province, and route type (Interstate, U.S. Highway, state highway, county road, etc.) for every route number.  I guess in the parlance of this site, being able to find the usaia.ia.ia136 of route data by searching the usaia.ia.ia136 part.  Any help or direction is appreciated.

[Jim]

I have a few thoughts on this but I'm headed out for the day now.  If I don't post here within a week or so feel free to bump the topic so I can take a look.  Basically there are the .csv files in the TM repository that could be parsed or appropriate queries could be run on the TM database.

[si404]

A scan of the AA roads wiki would be better to have as a base (at least for North America) - there's lots of road lists there that you can just pull an entire entry from. Plus the wiki has future/former/unsigned/unbuilt routes listed, whereas we do not.

However a scan of TM would be a useful further foundation to build the list from.

Wegenwiki.nl is a further possible source for gleaning stuff.

[michih]

I didn't find an appropiate log under https://travelmapping.net/logs/ but https://travelmapping.net/hb/findroute.php might help albeit it is just scanning for substrings, i.e. if you filter for "1" you'd also get "10", "11" etc.

I'm quite sure that there will be a simple way with some coding, e.g. like findroute.php but searching for full string while omitting alphabetic characters. However, only @Jim and @yakra are familar with the code.

[Eiddderf]

Plus the wiki has future/former/unsigned/unbuilt routes listed, whereas we do not.

Yeah we're just looking for a good base.  We know the enwiki lists are incomplete, too, but not to what degree.

[si404]

Plus the wiki has future/former/unsigned/unbuilt routes listed, whereas we do not.

Yeah we're just looking for a good base.  We know the enwiki lists are incomplete, too, but not to what degree.

Well the lists you already have* make up your base, and you can cross-reference with other sources - TM, Wikipedia, Wegenwiki, etc to try and make them as complete as possible...


*I'm not talking the untransferred wikipedia 'List of routes numbered 1' type articles, but the ones found in https://wiki.aaroads.com/wiki/Category:Lists_of_roads_in_the_United_States and similar on the AA roads wiki.

[si404]

I've made a start at gleaning the information by using Notepad, Ctrl-c, Ctrl-v, Ctrl-h with the _con.csvs

I've got the USA and CAN systems in a state where it just needs opening in a spreadsheet program and sorting. I'll get some other macro areas sorted later this afternoon and post the CSV later.

[si404]

I forgot how many routes we have and so that took longer than I thought! Anyway, the spreadsheet is here:

https://docs.google.com/spreadsheets/d/1jO1UYkUDwJ57rzJxOGaZYh09uju0dfxAUJPI9j41HDs/edit?usp=sharing

There needs to be a gleaning of letters (eg 153a -> 153, 0001busfra -> 1) so it sorts properly, and a clean up of where split routes are the same routes with a gap (or are unbannered spurs/loops) though I've purged quite a lot of them. And French D/M roads aren't in as they will take me as long to just to them (large number of systems, more work to do for each one than most others).

Here's a sample - the 1s:

Code: [Select]

System Route Banner Name Number
uryrn RN1 1
colrn RN01 1
chlrn R1 Iquique 1
mexf MEX1 1
mexemex EMEX1 1
mexedn SINAE1 1
mexed SINAE1 1
naca CA1 1
crirp RP1 1
mexsf YUC01 YUC 01 1
mexnlm M1 1
gufn N1 1
gufd D1 1
glpn N1 1
glpd D1 1
brbh H1 1
blzar AR1 George Price Highway 1
spmn N1 1
mtqn N1 1
mtqd D1 1
mtqa A1 1
jamt T1 1
jama A1 1
afrtah TAH1 Trans-Magrebian Highway 1
tuna A1 1
senn N1 1
sena A1 1
ngae E1 1
marn N1 1
mara A1 1
gnbn N1 1
tunrn RN1 1
afrea EA1 Northern Corridor 1
stpen EN1 1
tzat T1 1
reun N1 1
reud D1 1
mytn N1 1
mytd D1 1
kenb B1 1
kena A1 1
gabn N1 1
gaba A1 1
comrn RN1 1
cogn N1 1
codn N1 1
bdirn RN1 1
rwanr NR1 1
musm M1 1
musb B1 1
musa A1 1
lsoa A1 1
bwaa A1 1
mwim M1 1
zwer R1 1
zwep P1 1
zmbt T1 1
zmbm M1 1
zafn N1 1
swzmr MR1 1
namb B1 1
nama A1 1
mwim M1 1
jpntk SEC1 (Shuto Expressway) Inner Circular Route 1
jpntk SE1 (Shuto Expressway) Haneda Route 1
jpntk SEK1 (Shuto Expressway) Yokohane Route 1
jpntk SES1 (Shuto Expressway) Kawaguchi Route 1
jpntk HE1 (Hanshin Expressway) Loop Route 1
jpntk NEC1 (Nagoya Expressway) Central Loop Line 1
jpntk NE1 (Nagoya Expressway) Kusunoki Line 1
jpntk FE1 (Fukuoka Expressway) Kashii Line 1
jpntk HiE1 (Hiroshima Expressway) Aki Fuchu Route 1
jpntk KE1 (Kitakyushu Expressway) Route 1 1
jpnh N1 1
jpne E1 1
chng G1 1
hkgrt RT1 1
korex Ex1 1
myse E1 1
phle E1 1
twnf F1 1
vnmct CT01 1
idnnun N1 1
idnt T1 Java 1
nplh H01 1
lkae E01 1
lkaa A1 1
indwb WB1 1
indne NE1 Ahmedabad-Vadodara Expressway 1
pakm M1 1
geos S1 1
armm M1 1
azem M1 1
kwtr R1 1
isrh H1 1
isrf F1 1
irnf F1 1
kgzem EM01 1
kaza A1 1
tjkrb RB01 1
asiah AH1 1
deua A1 1
deub B1 Hameln 1
deub B1 Magdeburg 1
deub B1 Berlin 1
deustl L1 1
deusns S1 1
deushl L1 1
deurpl L1 1
deunwl L1 1
deunil L1 1
deumvl L01 1
deuhhr Ring1 1
chea A1 1
cheh H1 1
fraa A1 1
fran N1 Amiens 1
itaa A1 Autostrada del Sole 1
itaa RA1 Tangenziale di Bologna 1
itaa T1 Traforo del Monte Bianco 1
itass SS1 Grosseto 1
nlda A1 1
mltt T1 1
luxn N1 1
luxa A1 Tréierer Autobunn 1
belr R1 1
beln N1 Brasschaat 1
bela A1 1
mkda A1 1
hrvd D1 1
hrva A1 1
biha A1 1
svna A1 1
svng G1 1
rksrr RT1 1
mner R1 1
mnem M1 1
mnea A1 1
roua A1 1
mdar R1 1
mdam M1 1
grceo EO1 Larissa 1
grca A1 1
bgri I1 1
bgra A1 Trakia' 1
albsh SH1 1
alba A1 1
roudn DN1 1
prtn N1 1
prtic IC1 1
prta A1 1
andcg CG1 1
espcn FV1 1
espcn GC1 1
espcn GM1 1
espcn HI1 1
espcn LP1 1
espcn LZ1 1
espcn TF1 1
espa A1 1
espa AP1 1
espa ARAA1 1
espa ASI 1
espa EXA1 1
espa EXC1 1
espn NI Venturada 1
espn NI Briviesca 1
espn NI La Puebla de Arganzón 1
espn NI Beasain 1
espib Ma1 1
prtve VE1 Porto da Cruz 1
espmc RM1 1
islth TH1 1
alavt Vt1 1
finvt Vt1 1
lvap P1 1
estp T1 1
ltua A1 1
lvaa A1 1
gbna A1 1
gbnmkgr H1 Ridgeway 1
gbnmkgr V1 Shelshall Street 1
gbnm M1 1
nirm M1 1
nira A1 1
nirb B1 1
jeya A1 1
irln N1 1
irlm M1 1
imna A1 1
imnb B1 1
auts S1 Wiener Außenring Schnellstraße 1
autb B1 Wiener Straße 1
auta A1 West Autobahn 1
czed D1 1
svkd D1 Bratislava 1
pols S1 1
poldk DK1 1
pola A1 1
hunm M1 1
hunf F1 1
svkr R1 1
eure E1 1
eure E001 1
rusm M1 Belarus' 1
eursf VR1 Via Rápida 1, Madeira 1
cypb B1 1
cypa A1 1
blrm M01 1
ukrm M01 1
usaus US1 1
usatxr RM1 1
usagu GU1 1
usaga GA1 1
usade DE1 1
usaco CO1 1
usaca CA1 1
usaas AS001 1
usaar AR1 Jonesboro 1
usaak AK1 1
usala LA1 1
usaky KY1 1
usaks KS1 1
usain IN1 1
usail IL1 1
usaid ID1 1
usaia IA1 1
usai I-A1 1
usai I-H1 1
usai I-PR1 1
usand ND1 1
usamt MT1 1
usams MS1 1
usamo MO1 1
usamn MN1 1
usami MI1 1
usanm NM1 1
usane NE1 1
usatxf FM1 1
usatx NASA1 NASA Road 1 1
usatn TN1 Memphis 1
usapr PR1 1
usaok OK1 1
canbc BC1 1
canab AB1 1
canmb MB1 1
cannt NT1 1
cannst NS1 1
cannl NL1 1
cannba NB1 1
canyt YT1 1
cansk SK1 1
canpe PE1 1
ausqldtd TD1 Settlers Route 1
ausntb B1 1
ausnswtd TD1 Alpine Way 1
ausn N1 Perth, WA 1
ausn N1 Cairns, QLD 1
ausn N1 Hobart, TAS 1
ausm M1 Brisbane, QLD 1
ausm M1 Melbourne, VIC 1
ausm M1 Adelaide, SA 1
ausa A1 Darwin, NT 1
ausa A1 Townsville, QLD 1
ausa A1 Port Augusta, SA 1
ausr R1 Adelaide Ring Road 1
pyft T1 Tahiti 1
nzlsh SH1 1
wlft T1 Wallis 1
nclt T1 1
ncle E1 1
aussab B1 1

[michih]

^^ Great