Grand Tour Locations "How-to"

[StarWolve]

I know I posted this on the Milk Barn, but I thought I should bring it over here as well for reference. Here's my post on how to do a "map recon" for the entire US.


This is intended to be a "how-to" on finding Grand Tour locations. These instructions will work for every year that the tour goes off of town names, but the "excel sorting" (aka excel voodoo) will change from year to year depending on the Grand Tour requirements.

Start with the USGS list of populated places:
http://geonames.usgs.gov/domestic/download_data.htm
(2nd paragraph, in the drop down)

Import the file into Excel (File->Open, select all files, select the text file, and set the delimiter to the pipe "|" symbol)

You end up with about 199,403 places. Now sort those from A to Z by the Feature name column, and cut and paste all your Z's into another worksheet. This will give you 362 locations that start with Z.

Getting the "last letter is an A" takes some excel voodoo. You need to create a custom function. I used the instructions here: http://www.ozgrid.com/VBA/ReverseCell.htm

Create a new column after the Feature name, and populate the data using the function you just created. This will reverse the lettering of the town names. Now, sort using this new column and select all the lines that "start with A" using the reverse spelling. (Side note - some of the places have the "(historical)" after them in the name, so I also copied those ones that were historical and ended in A) I put these into a 3rd worksheet, so I can modify it. Roughly 11,886 places here. Delete the "reversed name" column, copy everything, and paste it into the worksheet with your "Z" places.

I also cut and paste the original header back in - this makes the file easier to use in the future.

Now we need to get rid of unnecessary data.

We only need: name, desc, latitude, and longitude in our text file. At this point, if you want to break up the file into states for easier use, I recommend filtering those states out in excel first. If anyone needs a specific state done and needs help, let me know and I'll walk you through it. I might do all 50 states in separate files if I have time, but for now, I'll just do the entire USA.

So remove everything in excel except the 4 columns we need. I leave the state in as the description and use decimals instead of degrees minutes seconds for lat/long. Rename the columns Name, Desc, Latitude, Longitude

Save the worksheet as a CSV (MS-DOS) file.

I use the site GPSvisualizer.com to convert my files into GPS readable files. This page is what you want is: http://www.gpsvisualizer.com/convert_inp...format=gpx

I set my data to be Waypoints, set my delimiter to commas, and output format to GPX (Garmin GPS file format - yours may vary). Choose your CSV file to upload, hit Convert, and save the file. I also converted the file to Google Earth (kmz), just to have another format to plan with.

Ok, if you've followed me this far - congratulations, you're done. Open up your files in whatever mapping program you want, and start planning your rides. Google Earth is free, and gives you a great interface to plan with. You can also use the GPS visualizer site to convert Google Earth files into whatever GPS format your device uses.

Now... I did all this work, so it would be nice if I shared, right? My next post will have a link to the files.

---

Dropbox location for the files:
https://www.dropbox.com/sh/o6l2smjvscsgui8/woD68W3ZGj

I'm relatively new to dropbox, so please let me know if you can't access the above location. The excel and text files are rather large, but the actual GPS Files are small, so I can email those if needed.


2013 GT.xlsm is the Excel "Master" I use. This is the file to use to sort by state, filter results, etc. It contains a Macro to reverse the names.

2013 Grand Tour KMZ.kmz is the Google Earth File.

2013 Grand Tour GPX.gpx is the Garmin file.

CSV File for 2013 GT.csv is the Comma Separated Value file that can be uploaded to GPS Visualizer.

POP_PLACES_20121001.txt is the pipe delimited text file from the NGS, updated October 1, 2012.


I hope this was helpful! I don't claim to be a GPS guru, but after a few years of doing this, I thought it would be good to share my method and files with everyone. I'll be purchasing a new GPS this year for my Buell, as my old GPSMAP 60C is woefully outdated for this type of touring.

[Frump]

(03-19-2013 07:19 AM)StarWolve Wrote:  I know I posted this on the Milk Barn...

Thanks for re-posting this StarWolve. Here is the link to Will England's page again too: http://will.mylanders.com/mc/a-z_grand_tour/

Good thing you reposted too, because I had the rules wrong in my brain (too busy thinking about dancing gals and trolling motors probably, will there be video on Kory Rides?). I had been looking forward to visiting all the towns which started with A again for the third year in a row (Alton, Ashton, Applington, etc, hehe).

[MikeH]

Quote:I had been looking forward to visiting all the towns which started with A again for the third year in a row (Alton, Ashton, Applington, etc, hehe).

You still can visit those towns if you like, likely not get much for points though.

[Lapchik]

(03-25-2013 02:37 PM)MikeH Wrote:  

Quote:I had been looking forward to visiting all the towns which started with A again for the third year in a row (Alton, Ashton, Applington, etc, hehe).

You still can visit those towns if you like, likely not get much for points though.

He is too focused on figuring out how a stripper pole fits in a boat to realize he has the A at the wrong end of the town name

[Shooter]

Thanks again Starwolve!!!!

I snagged this gem the first time you posted it. It saved me a ton of time tagging towns. I bet I had over 100 hours tagging towns in S&T last year. I'm not complaining but you have to look at the tags after it imports them. Some are Garmin towns and some are old historical towns that are long gone. Once again a small price to pay to have almost every available point at your finger tips.

It's a good thing that this contest is only US and Canada. If it included Mexico I would be doing a little stint south of the border this year. You know how many Mexican towns end in A?

Looks like most of the clusters are out east again this year right in JohnF's backyard. Maybe I will have to go visit him?

Starwolve, Willie, Dale Ty, and many others out east keep a look out for a green and white Ultra Classic. I might have to make another trip and meet more of you this summer.

Haley is on spring break April 1st and it's my birthday so I wonder where we will be? Maybe head toward Canistota, SD and leave a note on someone's door since they will not be home....hehehe

Hope everyone gets out and has fun chasing GT's this summer!!!!

[StarWolve]

I just finished updating the data files for the Double Down GT - I have GPX and KMZ files ready to go. However, there are a lot more points than in previous years. If you're planning on loading these points into a GPS, I would definitely sort the excel file by state first.

I took a little time to review the points in my area, and I think there's going to be a lot more false positives from these files than in previous years. The USGS updates their files regularly, and it looks like a lot of "communities" (read as: Suburbia Housing Developments with crazy HOA's) are showing up in the list... and most of them like to use a "Nature Name" (Briar CrEEk, Whispering WOOds, Bramble BrOOk, etc.) that has a double letter - usually to impart some natural feel to a completely jam packed development. (Can you tell I dislike housing developments?!) So I would definitely recommend a solid map reconnaissance before riding out for points unknown.

I'll upload the files this weekend - my company blocks dropbox so I can't do it now. If anyone needs help or has any specific requests, let me know and I can help out with creating some customized data sets.

By the way - I could have done something really slick in Visual Basic or a Macro, but the statement I ended up using to determine if a town had double letters was this formula, after converting the names to all uppercase: =IF(COUNT(SEARCH({"AA","BB","CC","DD","EE","FF","GG","HH","II","JJ","KK","MM","NN","OO","PP","QQ","RR","SS","TT","UU","VV","WW","XX","YY","ZZ"},CELL_ADDRESS_HERE)),"TRUE","FALSE")

---

I was able to upload the files from my phone (sad that I have more functionality on my phone than my work laptop) so try this link. Let me know if it doesn't work and I'll fix it tonight.

https://www.dropbox.com/sh/zhptnxmedmeh5k4/QN1WjO6cXt

[Willie]

(03-25-2013 07:08 PM)Shooter Wrote:  Thanks again Starwolve!!!!

Starwolve, Willie, Dale Ty, and many others out east keep a look out for a green and white Ultra Classic. I might have to make another trip and meet more of you this summer.

Haley is on spring break April 1st and it's my birthday so I wonder where we will be? Maybe head toward Canistota, SD and leave a note on someone's door since they will not be home....hehehe

Hope everyone gets out and has fun chasing GT's this summer!!!!

I usually make a couple trips out to western NY and PA, perhaps our paths can cross there. If you are in New England please send me an email and I will buy lunch/dinner for ya. If you are in MA you got a couch to sleep on if you want. It would be great to meet ya.

Willie

[UncleLumpy]

(01-17-2014 11:20 AM)StarWolve Wrote:  I took a little time to review the points in my area, and I think there's going to be a lot more false positives from these files than in previous years. The USGS updates their files regularly, and it looks like a lot of "communities" (read as: Suburbia Housing Developments with crazy HOA's) are showing up in the list... and most of them like to use a "Nature Name" (Briar CrEEk, Whispering WOOds, Bramble BrOOk, etc.) that has a double letter - usually to impart some natural feel to a completely jam packed development. (Can you tell I dislike housing developments?!) So I would definitely recommend a solid map reconnaissance before riding out for points unknown.

I cross the "Feature Name" column against the "Map Name" column and pull the matches out into a file. After doing that, I figure I have the "true locations" and, even if I've lost a few places, I still have more than enough to play with. Seemed to save a lot of time looking for places that really don't exist in terms of locations as we need them.

Lumpy

[LSGiant]

I just have Alex do it

[Lapchik]

Has anyone done this for the Pirate GT yet?

[LSGiant]

Didn't you see the post before yours ?

[Lapchik]

I don't have an Alex to do this for me . . .unless he has a .gpx file all ready to share . . .with everyone . . .maybe

[Shooter]

I have this one "started"... Don't know that I want to share it?!?!?!?!

[xander84]

(02-02-2015 10:53 PM)Lapchik Wrote:  I don't have an Alex to do this for me . . .unless he has a .gpx file all ready to share . . .with everyone . . .maybe

I don't have a gpx file yet; I'm about 3/4 done with my excel spreadsheet.
I lost my master list of towns from last year , I think it was on my computer from my old job.
Just need to take the time to finish it all up, but I get easily distracted when doing non-work activities on the computer.

[Sleddog]

(02-02-2015 11:18 PM)Shooter Wrote:  I have this one "started"... Don't know that I want to share it?!?!?!?!

Too many East of the Mississippi River.....

[Shooter]

(02-03-2015 01:12 PM)Sleddog Wrote:  

(02-02-2015 11:18 PM)Shooter Wrote:  I have this one "started"... Don't know that I want to share it?!?!?!?!

Too many East of the Mississippi River.....

Not enough in the northwest


I mean seriously Alaska only has 2!!! (okay mabye some other little villages tucked away somewhere) But you'd think the Rally Master would have looked at Alaska and said "Anchorage...oh anchor that is piratey" but nope. Besides only a JackASS and fool would try and go to Garner, AK. It's 6 miles north of the entrance to Denali NP!!!!!!!!




Maybe a little "Kansas chicken route" is in order? It's west of the Mississippi...

Anyone?

[StarWolve]

Sorry I haven't been on the forum as often. Just been super busy with work and life. I see the 2015 GT was posted, so I'll work on a GPX file for those who want it.

Quick update on life - still loving NC, added a DRZ400 to the stable and have been learning how much fun single-track off road riding is, and just purchased some land adjoining my property. Going to be building a "bikes and brews" barn on the land - basically a party barn where I can park the finished toys, with a bar in the back where we can shoot pool, enjoy craft beer, and tell lies. Should be a nice place when it's done.

If anyone is thinking about taking a spin through NC as a part of the GT, my house is always open to 2 wheeled travelers. There's enough room in the garage for a couple more bikes, there's cold beer in the fridge, and 2 guest rooms. Or if you're in Charlotte visiting, let me know and we'll go for a ride (there's enough spare bikes in the garage, just pick one!) and pick up a few GT points locally.

I'll try to get back on the board more often - and I'll get a GPX file ready in the next couple of weeks!

[Frump]

(02-02-2015 11:18 PM)Shooter Wrote:  I mean seriously Alaska only has 2!!!

"New Math" Shooter?
Barrow, Alaska
Clark's Point, Alaska
Fairbanks North Star Borough, Alaska
Larsen Bay, Alaska
Marshall, Alaska (16)
Northwest Arctic Borough, Alaska
Old Harbor, Alaska
Seward, Alaska (7)
Shishmaref, Alaska
St. Mary's, Alaska
TOTAL Alaska: 10

Granted some of these are only small villages and boroughs, and they might not have signs - but they are all places in census data.

Using my little program which runs off of census data, I get the following (probably not totally accurate). There are plenty both east and west of the Mississippi:

TOTAL Alabama: 33 (Dupes 17).
TOTAL Alaska: 9 (Dupes 2).
TOTAL Arizona: 12 (Dupes 4).
TOTAL Arkansas: 54 (Dupes 38).
TOTAL California: 56 (Dupes 24).
TOTAL Colorado: 22 (Dupes 12).
TOTAL Connecticut: 12 (Dupes 9).
TOTAL Delaware: 8 (Dupes 5).
TOTAL Florida: 58 (Dupes 17).
TOTAL Georgia: 63 (Dupes 39).
TOTAL Idaho: 19 (Dupes 12).
TOTAL Illinois: 228 (Dupes 120).
TOTAL Indiana: 91 (Dupes 64).
TOTAL Iowa: 110 (Dupes 73).
TOTAL Kansas: 112 (Dupes 78).
TOTAL Kentucky: 48 (Dupes 26).
TOTAL Louisiana: 89 (Dupes 10).
TOTAL Maine: 55 (Dupes 24).
TOTAL Maryland: 27 (Dupes 12).
TOTAL Massachusetts: 30 (Dupes 19).
TOTAL Michigan: 252 (Dupes 88).
TOTAL Minnesota: 213 (Dupes 108).
TOTAL Mississippi: 25 (Dupes 17).
TOTAL Missouri: 133 (Dupes 75).
TOTAL Montana: 7 (Dupes 6).
TOTAL Nebraska: 81 (Dupes 58).
TOTAL Nevada: 2 (Dupes 2).
TOTAL New Hampshire: 24 (Dupes 17).
TOTAL New Jersey: 64 (Dupes 18).
TOTAL New Mexico: 13 (Dupes 8).
TOTAL New York: 145 (Dupes 83).
TOTAL North Carolina: 58 (Dupes 29).
TOTAL North Dakota: 126 (Dupes 75).
TOTAL Ohio: 145 (Dupes 92).
TOTAL Oklahoma: 58 (Dupes 32).
TOTAL Oregon: 12 (Dupes 9).
TOTAL Pennsylvania: 184 (Dupes 61).
TOTAL Rhode Island: 5 (Dupes 4).
TOTAL South Carolina: 26 (Dupes 11).
TOTAL South Dakota: 77 (Dupes 61).
TOTAL Tennessee: 34 (Dupes 27).
TOTAL Texas: 114 (Dupes 53).
TOTAL Utah: 28 (Dupes 16).
TOTAL Vermont: 23 (Dupes 17).
TOTAL Virginia: 22 (Dupes 13).
TOTAL Washington: 25 (Dupes 11).
TOTAL West Virginia: 30 (Dupes 19).
TOTAL Wisconsin: 123 (Dupes 81).
TOTAL Wyoming: 9 (Dupes 3).

TOTAL Census records counted: 74841
TOTAL Census records matched (dupes from same state removed): 3243
TOTAL Duplicate matches: 1282 (in multiple states)

[Shooter]

It was late last night when I looked at Alaska and I do it manually one by one. I just wanted to poke a certain couple to maybe get an Alaskan GT or two when they went.

or 10?

East-West North-South doesn't matter....they will all be fun rides. Can't wait to get out and be a pirate.

Arrrr you ready?

[Babbitt]

I used this populated places data to create a spreadsheet with the following places in USA.

AR 29,883
AYE 230
AHOY 2
AVAST 0

I could probably upload a google sheet with these 30,115 cities if people want. I speak Excel but BaseCamp is beyond me. You are on your own for that.

Let me know.