How can I import GTFS files into MySQL?

Question

GTFS is a common format for public transportation schedules.

The General Transit Feed Specification (GTFS) defines a common format for public transportation schedules and associated geographic information. GTFS "feeds" allow public transit agencies to publish their transit data and developers to write applications that consume that data in an interoperable way.

Now, I have a trace in GTFS format, including several .txt files (tables). How can I import it into MySQL?

afaik data from GTFS emits several csv filess , which can be further imported into mysql using LOAD DATA INFILE FIELDS SEPARATED BY ',' SKIP FIRST 1 ROW — Satya, Jun 16 '15 at 13:07
@Satya How to assign a specific database? (I created several databases in my machine) — SparkAndShine, Jun 16 '15 at 13:28
Use `LOAD DATA INFILE INTO TABLE FIELDS TERMINATED BY ',' IGNORE 1 LINES`, or something like that. https://dev.mysql.com/doc/refman/5.1/en/load-data.html — Shotgun Ninja, Jun 16 '15 at 14:10
Also, I think you're confusing "database" and "table"; technically, there are databases, which contain schemas, which contain tables made up of columns and other organization entities, which contain rows of data. — Shotgun Ninja, Jun 16 '15 at 14:12
@ShotgunNinja store the trace in a database (say `gtfs`). `gtfs` contains several tables (each table corresponds to a text file). — SparkAndShine, Jun 16 '15 at 14:46
Okay, so for each file, `LOAD DATA INFILE INTO TABLE \`gtfs\`. FIELDS TERMINATED BY ',' IGNORE 1 LINES`, replacing , , and appropriately. Either that or call `use \`gtfs\`;` beforehand, and ignore the `\`gtfs\`.` part. — Shotgun Ninja, Jun 16 '15 at 14:50
Oh, right, this is MySQL. "Schemas" (in other SQL dialects) are called "Databases" in MySQL... roughly. There's still a slight conceptual difference, in that databases contain schemas, but in MySQL you effectively operate on the Database level, whereas you operate on the Schema level in many other RDBMSes. — Shotgun Ninja, Jun 16 '15 at 14:55

score 2 · Accepted Answer · answered Jun 16 '15 at 20:05

With the help of @Shotgun Ninja, @Satya, I make it. Here is what I do.

cat load.sql | mysql -p -u root

load.sql is based on github/sbma44/py-gtfs-mysql, here. The main code is as follows:

CREATE DATABASE IF NOT EXISTS gtfs;
USE gtfs

DROP TABLE IF EXISTS agency;
-- agency_id,agency_name,agency_url,agency_timezone,agency_phone,agency_lang
CREATE TABLE `agency` (
    agency_id INT(20) PRIMARY KEY,
    agency_name VARCHAR(255),
    agency_url VARCHAR(255),
    agency_timezone VARCHAR(50),
    agency_phone VARCHAR(255),
    agency_lang VARCHAR(50)
);

LOAD DATA LOCAL INFILE 'agency.txt' INTO TABLE agency FIELDS TERMINATED BY ',' IGNORE 1 LINES;

I share it on GitHub: tisseo_toulouse_gtfs.

How can I import GTFS files into MySQL?

1 Answers1