Last active
August 29, 2015 14:07
-
-
Save TomCamin/89a9dea9e065f4d677dc to your computer and use it in GitHub Desktop.
Import OpenData LaCub - Bus / Tram
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
CREATE DATABASE opendata_bus; | |
USE opendata_bus; | |
DROP TABLE IF EXISTS agency; | |
CREATE TABLE `agency` ( | |
id INT(12) NOT NULL PRIMARY KEY AUTO_INCREMENT, | |
agency_name VARCHAR(255) NOT NULL, | |
agency_id VARCHAR(100), | |
agency_url VARCHAR(255) NOT NULL, | |
agency_phone VARCHAR(100), | |
agency_timezone VARCHAR(100) NOT NULL, | |
agency_lang VARCHAR(100) | |
); | |
DROP TABLE IF EXISTS calendar_dates; | |
CREATE TABLE `calendar_dates` ( | |
id INT(12) NOT NULL PRIMARY KEY AUTO_INCREMENT, | |
service_id VARCHAR(255) NOT NULL, | |
`date` VARCHAR(8) NOT NULL, | |
exception_type TINYINT(2) NOT NULL, | |
KEY `service_id` (service_id), | |
KEY `exception_type` (exception_type) | |
); | |
DROP TABLE IF EXISTS calendar; | |
CREATE TABLE `calendar` ( | |
id INT(12) NOT NULL PRIMARY KEY AUTO_INCREMENT, | |
service_id VARCHAR(255) NOT NULL, | |
monday TINYINT(1) NOT NULL, | |
tuesday TINYINT(1) NOT NULL, | |
wednesday TINYINT(1) NOT NULL, | |
thursday TINYINT(1) NOT NULL, | |
friday TINYINT(1) NOT NULL, | |
saturday TINYINT(1) NOT NULL, | |
sunday TINYINT(1) NOT NULL, | |
start_date VARCHAR(8) NOT NULL, | |
end_date VARCHAR(8) NOT NULL, | |
KEY `service_id` (service_id) | |
); | |
DROP TABLE IF EXISTS routes; | |
CREATE TABLE `routes` ( | |
id INT(12) NOT NULL PRIMARY KEY AUTO_INCREMENT, | |
route_id VARCHAR(100), | |
agency_id VARCHAR(50), | |
route_short_name VARCHAR(50) NOT NULL, | |
route_long_name VARCHAR(255) NOT NULL, | |
route_type VARCHAR(2) NOT NULL, | |
route_text_color VARCHAR(255), | |
route_color VARCHAR(255), | |
route_url VARCHAR(255), | |
route_desc VARCHAR(255), | |
KEY `agency_id` (agency_id), | |
KEY `route_type` (route_type) | |
); | |
DROP TABLE IF EXISTS stop_times; | |
CREATE TABLE `stop_times` ( | |
id INT(12) NOT NULL PRIMARY KEY AUTO_INCREMENT, | |
trip_id VARCHAR(100) NOT NULL, | |
arrival_time VARCHAR(8) NOT NULL, | |
departure_time VARCHAR(8) NOT NULL, | |
stop_id VARCHAR(100) NOT NULL, | |
stop_sequence VARCHAR(100) NOT NULL, | |
stop_headsign VARCHAR(50), | |
pickup_type VARCHAR(2), | |
drop_off_type VARCHAR(2), | |
KEY `trip_id` (trip_id), | |
KEY `arrival_time` (arrival_time), | |
KEY `departure_time` (departure_time), | |
KEY `stop_id` (stop_id), | |
KEY `stop_sequence` (stop_sequence), | |
KEY `pickup_type` (pickup_type), | |
KEY `drop_off_type` (drop_off_type) | |
); | |
DROP TABLE IF EXISTS stops; | |
CREATE TABLE `stops` ( | |
id INT(12) NOT NULL PRIMARY KEY AUTO_INCREMENT, | |
stop_id VARCHAR(255), | |
stop_code VARCHAR(50), | |
stop_name VARCHAR(255) NOT NULL, | |
stop_desc VARCHAR(255), | |
stop_lat DECIMAL(10,6) NOT NULL, | |
stop_lon DECIMAL(10,6) NOT NULL, | |
zone_id VARCHAR(255), | |
stop_url VARCHAR(255), | |
location_type VARCHAR(2), | |
parent_station VARCHAR(100), | |
stop_timezone VARCHAR(50), | |
wheelchair_boarding TINYINT(1), | |
KEY `zone_id` (zone_id), | |
KEY `stop_lat` (stop_lat), | |
KEY `stop_lon` (stop_lon), | |
KEY `location_type` (location_type), | |
KEY `parent_station` (parent_station) | |
); | |
DROP TABLE IF EXISTS trips; | |
CREATE TABLE `trips` ( | |
id INT(12) NOT NULL PRIMARY KEY AUTO_INCREMENT, | |
route_id VARCHAR(100) NOT NULL, | |
service_id VARCHAR(100) NOT NULL, | |
trip_id VARCHAR(255), | |
trip_headsign VARCHAR(255), | |
block_id VARCHAR(11), | |
direction_id TINYINT(1), #0 for one direction, 1 for another. | |
KEY `route_id` (route_id), | |
KEY `service_id` (service_id), | |
KEY `direction_id` (direction_id), | |
KEY `block_id` (block_id) | |
); | |
LOAD DATA INFILE '/tmp/bus/agency.txt' INTO TABLE agency | |
FIELDS TERMINATED BY ',' | |
LINES TERMINATED BY '\n' | |
IGNORE 1 LINES | |
(agency_name,agency_id,agency_url,agency_phone,agency_timezone,agency_lang); | |
LOAD DATA INFILE '/tmp/bus/calendar_dates.txt' INTO TABLE calendar_dates | |
FIELDS TERMINATED BY ',' | |
LINES TERMINATED BY '\n' | |
IGNORE 1 LINES | |
(service_id,date,exception_type); | |
LOAD DATA INFILE '/tmp/bus/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/bus/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,agency_id,route_desc,route_url,route_color,route_text_color); | |
LOAD DATA INFILE '/tmp/bus/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,pickup_type,drop_off_type); | |
LOAD DATA INFILE '/tmp/bus/stops.txt' INTO TABLE stops | |
FIELDS TERMINATED BY ',' | |
LINES TERMINATED BY '\n' | |
IGNORE 1 LINES | |
(stop_id,stop_name,stop_lon,stop_lat,stop_desc,zone_id,stop_url,stop_code,location_type,parent_station); | |
LOAD DATA INFILE '/tmp/bus/trips.txt' INTO TABLE trips | |
FIELDS TERMINATED BY ',' | |
LINES TERMINATED BY '\n' | |
IGNORE 1 LINES | |
(route_id,service_id,trip_id,trip_headsign,block_id,direction_id); |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment