Script for importing Maryland MTA data, based on mheadd's git://gist.github.com/615470.git

import.sql

-- -------------------------------------------------------------------------------------------------------
--  A SQL script for importing GFTS data from the State of Maryland into a MySQL database.
--
--  Copyright 2010 Mark J. Headd
--  http://www.voiceingov.org
--  Tweaked for Maryland data by Ray Wenderlich
--  http://www.raywenderlich.com
--  
--  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/mta
--	cd /tmp/mta
--	wget http://mta.maryland.gov/_googletransit/latest/google_transit.zip
-- 	unzip google_transit.zip
--
--  You must create a database for this and specify it before using this script
--
--  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
DROP TABLE IF EXISTS agency;
CREATE TABLE agency (
    agency_id INT,
	agency_name VARCHAR(150),
	agency_url VARCHAR(150),
	agency_timezone VARCHAR(75),
    agency_lang VARCHAR(75),
    agency_phone 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),
    agency_id INT(3),
	route_short_name VARCHAR(25),
	route_long_name VARCHAR(150),
    route_desc VARCHAR(150),
	route_type INT(2),
    route_url VARCHAR(150),
    route_color VARCHAR(150),
    route_text_color VARCHAR(150),
	PRIMARY KEY(route_id),
	INDEX(route_long_name)
);

DROP TABLE IF EXISTS shapes;
CREATE TABLE shapes(
	shape_id INT(5),
	shape_pt_lat DOUBLE,
	shape_pt_lon DOUBLE,
	shape_pt_sequence INT(4),
    shape_dist_traveled 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),
    stop_headsign VARCHAR(150),
    pickup_type INT,
    drop_off_type INT,
    shape_dist_traveled DOUBLE,
	INDEX(trip_id),
	INDEX(stop_id)
);

DROP TABLE IF EXISTS stops;
CREATE TABLE stops(
	stop_id INT(5),
	stop_name VARCHAR(200),
    stop_desc VARCHAR(200),
	stop_lat DOUBLE,
	stop_lon DOUBLE,
    zone_id INT,
    stop_url VARCHAR(200),
	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),
	route_id INT(3),
	service_id INT(1),
    trip_headsign INT(3),
    direction_id INT(5),
	block_id INT(6),
	shape_id INT(5),
	INDEX(route_id),
	INDEX(trip_id),
	INDEX(service_id)
);

LOAD DATA INFILE '/home/rwenderlich/transit/agency.txt' INTO TABLE agency
	FIELDS TERMINATED BY ','
	LINES TERMINATED BY '\n'
	IGNORE 1 LINES
	(agency_id,agency_name,agency_url,agency_timezone,agency_lang,agency_phone);

LOAD DATA INFILE '/home/rwenderlich/transit/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 '/home/rwenderlich/transit/routes.txt' INTO TABLE routes
	FIELDS TERMINATED BY ','
	LINES TERMINATED BY '\n'
	IGNORE 1 LINES
	(route_id,agency_id,route_short_name,route_long_name,route_desc,route_type,route_url,route_color,route_text_color);

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

LOAD DATA INFILE '/home/rwenderlich/transit/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,stop_headsign,pickup_type,drop_off_type,shape_dist_traveled);

LOAD DATA INFILE '/home/rwenderlich/transit/stops.txt' INTO TABLE stops
	FIELDS TERMINATED BY ','
	LINES TERMINATED BY '\n'
	IGNORE 1 LINES
	(stop_id,stop_name,stop_desc,stop_lat,stop_lon,zone_id,stop_url);

LOAD DATA INFILE '/home/rwenderlich/transit/trips.txt' INTO TABLE trips
	FIELDS TERMINATED BY ','
	LINES TERMINATED BY '\n'
	IGNORE 1 LINES
	(trip_id,route_id,service_id,trip_headsign,direction_id,block_id,shape_id);