GTFS playground

fill_database.sh

#!/bin/bash

. mysql.ini
schema_url="https://raw.githubusercontent.com/mauryquijada/gtfs-mysql/master/gtfs-sql.sql"
mysqlexec="mysql -h $host -u $user -p${pass} $name"
datafolder="./data"
provider_url="http://www.data.gouv.fr/en/dataset/offre-transport-de-la-ratp-format-gtfs-ratp"

# List files that are available from the provider
files=`curl -s $provider_url | egrep -o 'data-url="[^"]+"' | awk -F\" '{print $2}'`

# We want the full lines data
$url="`echo '$files' | grep FULL`"
curl $url > $datafolder/full_data.zip
unzip $datafolder/full_data.zip

# Retrieve tables schema
curl -s $schema_url > .mysql_schema

# Create the tables if needed
echo -n "Create tables if they do not exist... "
create=`sed 's/CREATE TABLE /CREATE TABLE IF NOT EXISTS /g' < .mysql_schema`
$mysqlexec -e "$create"
echo "done."

# Update the DB
for file in $datafolder/*.txt; do
	table=`echo $file | tr '.' '/' | awk -F/ '{print $(NF-1)}'`
	cmd="SET autocommit=0;
	SET unique_checks=0;
	SET foreign_key_checks=0;

	TRUNCATE $table;

	LOAD DATA LOCAL INFILE '$file'
	REPLACE INTO TABLE $table
	COLUMNS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '\"'
	LINES TERMINATED BY '\n' IGNORE 1 LINES
	(`head -n 1 $file | tr -d '\n'`);

	COMMIT;
	SET unique_checks=1;
	SET foreign_key_checks=1;"

	echo -n "Inserting data for file: $file... "
	$mysqlexec --local_infile=1 -e "$cmd"
	echo "done."
done

# Cleanup
rm .mysql_schema

result

$ time ./test.sh 
+------+-------------------------------------------------------------------------+
| id   | route_long_name                                                         |
+------+-------------------------------------------------------------------------+
|  290 | (PLACE DE CLICHY <-> CHATILLON METRO) - Aller                           |
|  291 | (PLACE DE CLICHY <-> CHATILLON METRO) - Retour                          |
|  404 | (PORTE D'ORLEANS-METRO <-> ECOLE VETERINAIRE DE MAISON-ALFORT) - Aller  |
|  405 | (PORTE D'ORLEANS-METRO <-> ECOLE VETERINAIRE DE MAISON-ALFORT) - Retour |
|  453 | (PORTE D'ORLEANS-METRO <-> LYCEE POLYVALENT) - Retour                   |
|  457 | (PORTE D'ORLEANS-METRO <-> LYCEE POLYVALENT) - Retour                   |
|  810 | (PLACE DE LA LIBERATION <-> GARE MONTPARNASSE) - Aller                  |
|  989 | (PORTE D'ORLEANS-METRO) - Retour                                        |
| 1034 | (PLACE DE LA LIBERATION <-> HOTEL DE VILLE DE PARIS_4E__AR) - Aller     |
+------+-------------------------------------------------------------------------+

real	0m34.816s
user	0m0.000s
sys	0m0.000s

test.sh

#!/bin/bash

# Load mysql connection parameters
. mysql.ini

# Script parameters
lat=48.824699
lon=2.3243
max_dist=200

# 1. select stops nearby
sql="SELECT stop_id, (6371000*acos(cos(radians($lat))*cos(radians(s.stop_lat))*cos(radians($lon)-radians(s.stop_lon))+sin(radians($lat))*sin(radians(s.stop_lat)))) AS distance
	FROM stops s
	GROUP BY s.stop_id
	HAVING distance < $max_dist
	ORDER BY distance ASC"
stops=`mysql -h $host -N -B -u $user -p${pass} $name -e "$sql" | awk '{print $1}'`

# 2. select stop times later today
stops_list="stop_id IN (`echo "$stops" | tr '\n' '#' | sed -e 's/#$//' -e 's/#/, /g'`)"
departure_minimum="18:30:00" #`date +%T`
sql="SELECT trip_id
	FROM stop_times
	WHERE $stops_list AND departure_time >= '$departure_minimum'
	GROUP BY trip_id"
trip_ids=`mysql -h $host -N -B -u $user -p${pass} $name -e "$sql" | tr '\n' ',' | sed 's/,$//'`

# 3. select routes
sql="SELECT r.id, r.route_long_name
	FROM routes r, trips t
	WHERE t.trip_id IN ($trip_ids) AND r.route_id = t.route_id
	GROUP BY t.route_id"
mysql -h $host -u $user -p${pass} $name -e "$sql"