REAXblog

Recently I’ve been preparing data to get a small local transit agency on Google Transit, and I’ve been able to do so rather rapidly. Perhaps one of the most intensive portions of this process - especially for a small agency with limited resources - is generating the shapes.txt without the aid of existing GIS data for the routes.

First, the specifics - this method requires no knowledge of GIS, no prior knowledge of shape data, and does not require any (downloaded) software. It’s as simple and painless as could possible be.

The agency I was working for had a small system of just five routes, although they ran in a few configurations, so all told, I had to create about 14 different shapes (each route has at least two shapes, the route there and the route back; plus a few extras. One route was a steady loop with required only one shape).

Step one involved stepping over to Google Maps and generating driving directions for the route. This was simple enough. Our routes sometimes had turnarounds or stops that weren’t on the street network, and it was impossible to get the route as it was actually driven; but that was OK - get as close as possible before proceeding.

Once your driving directions are complete, hit the Link button in the upper righthand corner, and copy that link to your clipboard.

In step 2, we’ll take that KML file over to Google MyMaps. Google MyMaps is a handy utility which you can use to draw your own lines and placemarks on a map. Go ahead and hit ‘Create New Map’ and name it whatever you like. You should see a link to “Import” data. Hit that. In the popup window, you’ll see a text field that says ‘enter the url of map data on the web’. Go ahead and paste your link. But before you hit ‘Upload from URL’, you want to add the following text to the end of the URL you just pasted: &output=kml. Don’t worry about why, just do it.Clea’

Your map should be right there in my maps. Go ahead and fix up your route if the driving directions weren’t exactly what you needed. Our system visited alot of senior homes and utilized turnarounds in their parking lot. Needless to say, Google driving directions didn’t consider such a thing and often had some extremely complicated turnaround procedures, so I had to do alot of optimizing of the routes.

Once you are done, go ahead and grab the link for your map (again, the upper-right hand corner of your map) and copy that. But this time, open up this site to convert your map to a shapes.txt file. You should have a dead simple form here. Paste your link in, hit go, and it should prompt you to download another file, called shapes.txt. Rename it whatever you named it before (except with a .csv extension) and save it to your hard drive.

You can go ahead and open up the .CSV in excel now. It is more or less ready to copy and paste right over into your shapes.txt file (note: I usually keep all the files in a single excel workbook for safekeeping, exporting CSV’s as I update the data). Before you do so, you should make sure to change the shape_id to something sensible. You will also notice that shape_dist_traveled is not calculated. We can use a simple excel formula to calculate the distance from point to point and generate these values rapidly. The formula I used is =IF($D8=0,0,ACOS(SIN($B7)*SIN($B8)+COS($B7)*COS($B8)*COS($C8-$C7))*6371+$E7). You can paste that right into column E for each row of  data you have.

This formula will actually error from time to time, giving the value “#ERROR” occassionally. You will notice this occurring when there is an identical sequence of latitude/longitude pairs (and thus no distance between them). If you see this, delete the firt row giving the error. You may now get a “#REF” error code. Go ahead and copy the formula from another cell and repaste - this will fix the links. You may need to do this several times.

I find it helpful to then copy that entire column and the re-paste it in place, taking care to ‘paste special’ and paste as values. This makes it possible to reorder your results (if you need to for whatever reason) and not change your values.