A SQL script for importing GFTS data from the State of Delaware into a MySQL database.

dartfirststate_de_us.sql

-- -------------------------------------------------------------------------------------------------------
--  A SQL script for importing GFTS data from the State of Delaware into a MySQL database.
--
--  Copyright 2010 Mark J. Headd
--  http://www.voiceingov.org
--  
--  This file is free software; you can redistribute it and/or modify it under the terms of the 
--  GNU Library General Public License as published by the Free Software Foundation; either version 2 of the 
--  License, or (at your option) any later version.
--  This file is distributed in the hope that it will be useful, but WITHOUT ANY WARRANTY; 
--  without even the implied warranty of MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.
--  See the GNU Library General Public License for more details.
--  If you modify and/or redistrubute this script, you must give attribution to the author.
--
--  Before running this script, do
--	mkdir /tmp/dartfirst_de_us
--	cd /tmp/dartfirst_de_us
--	wget http://www.dartfirststate.com/information/routes/gtfs_data/dartfirststate_de_us.zip
-- 	unzip dartfirststate_de_us.zip
--
--  This will ensure that the files used by LOAD DATA INFILE are present.
--  Tested with MySQL 5.x
-- 
-- -------------------------------------------------------------------------------------------------------

-- Create a new database for transit data
CREATE DATABASE transitdata;

USE transitdata;

DROP TABLE IF EXISTS agency;
CREATE TABLE agency (
	agency_name VARCHAR(150),
	agency_url VARCHAR(150),
	agency_timezone VARCHAR(75)
);

DROP TABLE IF EXISTS calendar;
CREATE TABLE calendar (
	service_id INT(2),
	monday TINYINT(1),
	tuesday TINYINT(1),
	wednesday TINYINT(1),
	thursday TINYINT(1),
	friday TINYINT(1),
	saturday TINYINT(1),
	sunday TINYINT(1),
	start_date DATE,
	end_date DATE,
	PRIMARY KEY(service_id)
);

DROP TABLE IF EXISTS routes;
CREATE TABLE routes(
	route_id INT(3),
	route_short_name VARCHAR(25),
	route_long_name VARCHAR(150),
	route_type INT(2),
	PRIMARY KEY(route_id),
	INDEX(route_long_name)
);

DROP TABLE IF EXISTS shapes;
CREATE TABLE shapes(
	shape_id INT(5),
	shape_pt_sequence INT(4),
	shape_pt_lat DOUBLE,
	shape_pt_lon DOUBLE
);

DROP TABLE IF EXISTS stop_times;
CREATE TABLE stop_times(
	trip_id INT(6),
	arrival_time TIME,
	departure_time TIME,
	stop_id INT(5),
	stop_sequence INT(3),
	INDEX(trip_id),
	INDEX(stop_id)
);

DROP TABLE IF EXISTS stops;
CREATE TABLE stops(
	stop_id INT(5),
	stop_code CHAR(4),
	stop_name VARCHAR(200),
	stop_lon DOUBLE,
	stop_lat DOUBLE,
	PRIMARY KEY(stop_id),
	INDEX(stop_name),
	INDEX(stop_lon),
	INDEX(stop_lat)
);

DROP TABLE IF EXISTS trips;
CREATE TABLE trips(
	trip_id INT(6),
	service_id INT(1),
	route_id INT(3),
	shape_id INT(5),
	block_id INT(6),
	INDEX(route_id),
	INDEX(trip_id),
	INDEX(service_id)
);

LOAD DATA INFILE '/tmp/dartfirst_de_us/agency.txt' INTO TABLE agency
	FIELDS TERMINATED BY ','
	LINES TERMINATED BY '\n'
	IGNORE 1 LINES
	(agency_name,agency_url,agency_timezone);

LOAD DATA INFILE '/tmp/dartfirst_de_us/calendar.txt' INTO TABLE calendar
	FIELDS TERMINATED BY ','
	LINES TERMINATED BY '\n'
	IGNORE 1 LINES
	(service_id,monday,tuesday,wednesday,thursday,friday,saturday,sunday,start_date,end_date);

LOAD DATA INFILE '/tmp/dartfirst_de_us/routes.txt' INTO TABLE routes
	FIELDS TERMINATED BY ','
	LINES TERMINATED BY '\n'
	IGNORE 1 LINES
	(route_id,route_short_name,route_long_name,route_type);

LOAD DATA INFILE '/tmp/dartfirst_de_us/shapes.txt' INTO TABLE shapes
	FIELDS TERMINATED BY ','
	LINES TERMINATED BY '\n'
	IGNORE 1 LINES
	(shape_id,shape_pt_sequence,shape_pt_lat,shape_pt_lon);

LOAD DATA INFILE '/tmp/dartfirst_de_us/stop_times.txt' INTO TABLE stop_times
	FIELDS TERMINATED BY ','
	LINES TERMINATED BY '\n'
	IGNORE 1 LINES
	(trip_id,arrival_time,departure_time,stop_id,stop_sequence);

LOAD DATA INFILE '/tmp/dartfirst_de_us/stops.txt' INTO TABLE stops
	FIELDS TERMINATED BY ','
	LINES TERMINATED BY '\n'
	IGNORE 1 LINES
	(stop_id,stop_code,stop_name,stop_lon,stop_lat);

LOAD DATA INFILE '/tmp/dartfirst_de_us/trips.txt' INTO TABLE trips
	FIELDS TERMINATED BY ','
	LINES TERMINATED BY '\n'
	IGNORE 1 LINES
	(trip_id,service_id,route_id,shape_id,block_id);