Quick & easy import of GTFS data into a SQL database.
Released under the MIT (X11) license. See LICENSE in this directory.
This is how to import GTFS data into SQL: ( see below for SQLite)
cat gtfs_tables.sql \
<(python import_gtfs_to_sql.py path/to/gtfs/data/directory) \
gtfs_tables_makeindexes.sql \
vacuumer.sql \
| psql mydbname
cat gtfs_tables.sql \
<(python import_gtfs_to_sql.py path/to/gtfs/data/directory) \
gtfs_tables_makespatial.sql \
gtfs_tables_makeindexes.sql \
vacuumer.sql \
| psql mydbname
This will use "INSERT" statements instead of "COPY" statements. Also, I believe the vacuumer.sql file is also postgres specific, so omit it if it gives errors.
cat gtfs_tables.sql \
<(python import_gtfs_to_sql.py path/to/gtfs/data/directory nocopy) \
gtfs_tables_makeindexes.sql \
vacuumer.sql \
| psql mydbname
Most GTFS data has errors in it, so you will likely encounter an error when running the step above. After fixing the error by manually correcting the GTFS files, you can simply repeat the command (which will likely break again, and so on).
If you are editing data within the SQL database, it is usually much faster to drop all the indexes first and then reapply them afterwards:
psql -f gtfs_tables_dropindexes.sql
# do your stuff
psql -f gtfs_tables_makeindexes.sql
The corrected (even google's example data has errors) demo feed from the GTFS website is included in this distribution. You should play around with that first to get everything to work and to see how the data gets put into tables.
From this directory (assuming postgres):
createdb testgtfs
cat gtfs_tables.sql \
<(python import_gtfs_to_sql.py sample_feed) \
gtfs_tables_makeindexes.sql \
vacuumer.sql \
| psql testgtfs
psql testgtfs -c "\dt"
Contributed by Justin Jones, Feb 07, 2011 bjustjones@netscape.net
Note: From http://www.sqlite.org/omitted.html as of Feb, 2011: "Only the RENAME TABLE and ADD COLUMN variants of the ALTER TABLE command are supported. Other kinds of ALTER TABLE operations such as DROP COLUMN, ALTER COLUMN, ADD CONSTRAINT, and so forth are omitted."
gtfs_tables.sqlite includes the constraints on creation.
cat gtfs_tables.sqlite \
<(python import_gtfs_to_sql.py sample_feed nocopy) \
| sqlite3 ANewDatabase.db
SQLite doesn't enforce constraints by default. See first section of gtfs_tables.sqlite for line to change. Move it to the end? If you need to makeindices or dropindices as above you'll have to experiment with doing it yourself.
sqlite3 -init gtfs_tables_dropindexes.sqlite myDatabase.db
# do your stuff
sqlite3 -init gtfs_tables_makeindexes.sqlite myDatabase.db