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.