Skip to content

Instantly share code, notes, and snippets.

@mheadd
Created October 7, 2010 17:13
Show Gist options
  • Save mheadd/615470 to your computer and use it in GitHub Desktop.
Save mheadd/615470 to your computer and use it in GitHub Desktop.
A SQL script for importing GFTS data from the State of Delaware into a MySQL database.
-- -------------------------------------------------------------------------------------------------------
-- 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);
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment