Quite a bit has been written about putting GTFS in a database. That is great, but the power of the Bash shell and structure of GTFS enables faster queries if you know what to do.
For example, you can find the stops on a specific trip using a trip_id. With BART’s GTFS, loaded, let’s give it a try:
Requires: bash shell, GTFS files unzipped to a directory
First, let’s take a look at the top of the trips.txt file.
$ head trips.txt
route_id,service_id,trip_id,trip_headsign,direction_id,block_id,shape_id
01,WKDY,01SFO10,San Francisco Int'l Airport,0,,
01,SAT,01SFO10SAT,Millbrae,0,,
01,SUN,01SFO10SUN,Millbrae,0,,
01,WKDY,02SFO10,San Francisco Int'l Airport,0,,
01,SAT,02SFO10SAT,Millbrae,0,,
01,SUN,02SFO10SUN,Millbrae,0,,
01,WKDY,03SFO10,San Francisco Int'l Airport,0,,
01,SAT,03SFO10SAT,Millbrae,0,,
01,SUN,03SFO10SUN,Millbrae,0,,
The column we’re looking for is the third column (trip_id). “Grep” is the universal text search tool, and “|” tells the Bash shell to pass the output from one command to another, forming a chain. As GTFS is comma-separated, we’re able to use the “cut” tool on a file to get just what we need. The “xargs” tool then can pass that on to another command. Taking a trip ID, you can see the particular stops that are on a trip by looking at a combination of stop_times.txt and stops.txt.
grep 183OAK11 stop_times.txt | cut -d "," -f 4 | xargs -I {} grep {} stops.txt | cut -d "," -f 2
Coliseum/Oakland Airport
Oakland Int'l Airport
You can even go crazy with this. The following beast of a command will give *all* of the stops that are made on the Pittsburg line on any trip during the schedule.
$ grep -E "Pittsburg/Bay Point - SFIA/Millbrae" routes.txt | cut -d "," -f 1| xargs -I {} grep -e ^{} trips.txt | cut -d "," -f 3| xargs -I {} grep {} stop_times.txt | cut -d "," -f 4 | sort -u | xargs -I {} grep {} stops.txt
12TH,12th St. Oakland City Center,,37.803664,-122.271604,12TH,http://www.bart.gov/stations/12TH/
16TH,16th St. Mission,,37.765062,-122.419694,16TH,http://www.bart.gov/stations/16TH/
19TH,19th St. Oakland,,37.80787,-122.269029,19TH,http://www.bart.gov/stations/19TH/
19TH_N,19th St. Oakland,,37.80787,-122.269029,19TH,http://www.bart.gov/stations/19TH/
19TH_N,19th St. Oakland,,37.80787,-122.269029,19TH,http://www.bart.gov/stations/19TH/
24TH,24th St. Mission,,37.752254,-122.418466,24TH,http://www.bart.gov/stations/24TH/
BALB,Balboa Park,,37.72198087,-122.4474142,BALB,http://www.bart.gov/stations/BALB/
CIVC,Civic Center/UN Plaza,,37.779528,-122.413756,CIVC,http://www.bart.gov/stations/CIVC/
COLM,Colma,,37.684638,-122.466233,COLM,http://www.bart.gov/stations/COLM/
CONC,Concord,,37.973737,-122.029095,CONC,http://www.bart.gov/stations/CONC/
DALY,Daly City,,37.70612055,-122.4690807,DALY,http://www.bart.gov/stations/DALY/
EMBR,Embarcadero,,37.792976,-122.396742,EMBR,http://www.bart.gov/stations/EMBR/
GLEN,Glen Park,,37.732921,-122.434092,GLEN,http://www.bart.gov/stations/GLEN/
LAFY,Lafayette,,37.893394,-122.123801,LAFY,http://www.bart.gov/stations/LAFY/
MCAR,MacArthur,,37.828415,-122.267227,MCAR,http://www.bart.gov/stations/MCAR/
MCAR_S,MacArthur,,37.828415,-122.267227,MCAR,http://www.bart.gov/stations/MCAR/
MCAR_S,MacArthur,,37.828415,-122.267227,MCAR,http://www.bart.gov/stations/MCAR/
MLBR,Millbrae,,37.599787,-122.38666,MLBR,http://www.bart.gov/stations/MLBR/
MONT,Montgomery St.,,37.789256,-122.401407,MONT,http://www.bart.gov/stations/MONT/
NCON,North Concord/Martinez,,38.003275,-122.024597,NCON,http://www.bart.gov/stations/NCON/
ORIN,Orinda,,37.87836087,-122.1837911,ORIN,http://www.bart.gov/stations/ORIN/
PHIL,Pleasant Hill/Contra Costa Centre,,37.928403,-122.056013,PHIL,http://www.bart.gov/stations/PHIL/
PITT,Pittsburg/Bay Point,,38.018914,-121.945154,PITT,http://www.bart.gov/stations/PITT/
POWL,Powell St.,,37.784991,-122.406857,POWL,http://www.bart.gov/stations/POWL/
ROCK,Rockridge,,37.844601,-122.251793,ROCK,http://www.bart.gov/stations/ROCK/
SBRN,San Bruno,,37.637753,-122.416038,SBRN,http://www.bart.gov/stations/SBRN/
SFIA,San Francisco Int'l Airport,,37.616035,-122.392612,SFIA,http://www.bart.gov/stations/SFIA/
SSAN,South San Francisco,,37.664174,-122.444116,SSAN,http://www.bart.gov/stations/SSAN/
WCRK,Walnut Creek,,37.905628,-122.067423,WCRK,http://www.bart.gov/stations/WCRK/
WOAK,West Oakland,,37.80467476,-122.2945822,WOAK,http://www.bart.gov/stations/WOAK/