GTFS: MySQL Table DDL and Load Data Infile directives and spatial indices

import_gtfs.sql

# replace <rootpath> with the base path of your gtfs files...

DROP TABLE IF EXISTS agency;
CREATE TABLE agency
(
  agency_id VARCHAR(10) PRIMARY KEY NOT NULL,
  agency_name VARCHAR(255) NOT NULL,
  agency_url VARCHAR(255) NOT NULL,
  agency_timezone VARCHAR(20) NOT NULL,
  agency_lang VARCHAR(10),
  agency_phone VARCHAR(50),
  agency_fare_url VARCHAR(255)
);

CREATE UNIQUE INDEX ix_agency_agency_id ON agency ( agency_id );


DROP TABLE IF EXISTS calendar;
CREATE TABLE calendar
(
  service_id VARCHAR(10) PRIMARY KEY NOT NULL,
  monday TINYINT NOT NULL,
  tuesday TINYINT NOT NULL,
  wednesday TINYINT NOT NULL,
  thursday TINYINT NOT NULL,
  friday TINYINT NOT NULL,
  saturday TINYINT NOT NULL,
  sunday TINYINT NOT NULL,
  start_date DATE NOT NULL,
  end_date DATE NOT NULL
);
CREATE INDEX calendar_ix1 ON calendar ( start_date, end_date );
CREATE INDEX ix_calendar_service_id ON calendar ( service_id );

DROP TABLE IF EXISTS calendar_dates;
CREATE TABLE calendar_dates
(
  service_id VARCHAR(10) NOT NULL,
  date DATE NOT NULL,
  exception_type INT NOT NULL,
  PRIMARY KEY ( service_id, date )
);
CREATE INDEX ix_calendar_dates_date ON calendar_dates ( date );
CREATE INDEX ix_calendar_dates_service_id ON calendar_dates ( service_id );

DROP TABLE IF EXISTS routes;
CREATE TABLE routes
(
  route_id VARCHAR(20) PRIMARY KEY NOT NULL,
  agency_id VARCHAR(10),
  route_short_name VARCHAR(255),
  route_long_name VARCHAR(255),
  route_desc VARCHAR(255),
  route_type INT NOT NULL,
  route_url VARCHAR(255),
  route_color VARCHAR(6),
  route_text_color VARCHAR(6),
  route_sort_order INT
);

CREATE INDEX ix_routes_agency_id ON routes ( agency_id );
CREATE INDEX ix_routes_route_id ON routes ( route_id );
CREATE INDEX ix_routes_route_sort_order ON routes ( route_sort_order );
CREATE INDEX ix_routes_route_type ON routes ( route_type );

DROP TABLE IF EXISTS stop_times;
CREATE TABLE stop_times
(
  trip_id INT NOT NULL,
  arrival_time TIME,
  departure_time TIME,
  stop_id INT NOT NULL,
  stop_sequence INT,
  stop_headsign VARCHAR(255),
  pickup_type INT,
  drop_off_type INT,
  shape_dist_traveled DECIMAL(20,10),
  timepoint TINYINT
);
CREATE INDEX ix_stop_times_departure_time ON stop_times ( departure_time );
CREATE INDEX ix_stop_times_stop_id ON stop_times ( stop_id );
CREATE INDEX ix_stop_times_timepoint ON stop_times ( timepoint );
CREATE INDEX ix_stop_times_trip_id ON stop_times ( trip_id );

DROP TABLE IF EXISTS stops;
CREATE TABLE stops
(
  stop_id INT PRIMARY KEY NOT NULL,
  stop_code VARCHAR(50),
  stop_name VARCHAR(255) NOT NULL,
  stop_desc VARCHAR(255),
  stop_lat DECIMAL(12,9) NOT NULL,
  stop_lon DECIMAL(12,9) NOT NULL,
  zone_id VARCHAR(50),
  stop_url VARCHAR(255),
  location_type INT,
  parent_station VARCHAR(255),
  stop_timezone VARCHAR(20),
  wheelchair_boarding INT,
  platform_code VARCHAR(50),
  direction VARCHAR(50),
  position VARCHAR(50)
) ENGINE=MyISAM; #needed for spatial indices

CREATE INDEX ix_stops_location_type ON stops ( location_type );
CREATE INDEX ix_stops_stop_id ON stops ( stop_id );

DROP TABLE IF EXISTS transfers;
CREATE TABLE transfers
(
  from_stop_id INT,
  to_stop_id INT,
  transfer_type INT,
  min_transfer_time INT
);
CREATE INDEX ix_transfers_transfer_type ON transfers ( transfer_type );

DROP TABLE IF EXISTS trips;
CREATE TABLE trips
(
  route_id VARCHAR(20) NOT NULL,
  service_id VARCHAR(10) NOT NULL,
  trip_id int PRIMARY KEY NOT NULL,
  trip_headsign VARCHAR(255),
  trip_short_name VARCHAR(255),
  direction_id INT,
  block_id VARCHAR(255),
  shape_id VARCHAR(255),
  trip_type VARCHAR(255),
  bikes_allowed INT,
  wheelchair_accessible INT
);
CREATE INDEX ix_trips_route_id ON trips ( route_id );
CREATE INDEX ix_trips_service_id ON trips ( service_id );
CREATE INDEX ix_trips_shape_id ON trips ( shape_id );
CREATE INDEX ix_trips_trip_id ON trips ( trip_id );


LOAD DATA LOCAL INFILE '<rootpath>agency.txt'
INTO TABLE agency
FIELDS TERMINATED BY ',' ENCLOSED BY '"'
IGNORE 1 LINES
;

LOAD DATA LOCAL INFILE '<rootpath>calendar.txt'
INTO TABLE calendar
FIELDS TERMINATED BY ',' ENCLOSED BY '"'
IGNORE 1 LINES
;

LOAD DATA LOCAL INFILE '<rootpath>calendar_dates.txt'
INTO TABLE calendar_dates
FIELDS TERMINATED BY ',' ENCLOSED BY '"'
IGNORE 1 LINES
;

LOAD DATA LOCAL INFILE '<rootpath>routes.txt'
INTO TABLE routes
FIELDS TERMINATED BY ',' ENCLOSED BY '"'
IGNORE 1 LINES
;

LOAD DATA LOCAL INFILE '<rootpath>stop_times.txt'
INTO TABLE stop_times
FIELDS TERMINATED BY ',' ENCLOSED BY '"'
IGNORE 1 LINES
;

LOAD DATA LOCAL INFILE '<rootpath>stops.txt'
INTO TABLE stops
FIELDS TERMINATED BY ',' ENCLOSED BY '"'
IGNORE 1 LINES
;
LOAD DATA LOCAL INFILE '<rootpath>transfers.txt'
INTO TABLE transfers
FIELDS TERMINATED BY ',' ENCLOSED BY '"'
IGNORE 1 LINES
;

LOAD DATA LOCAL INFILE '<rootpath>trips.txt'
INTO TABLE trips
FIELDS TERMINATED BY ',' ENCLOSED BY '"'
IGNORE 1 LINES
;

#transform lat/lng to real geometry
ALTER TABLE stops ADD ll POINT NOT NULL;
UPDATE stops
SET ll = PointFromText(CONCAT('POINT(',stops.stop_lat,' ',stops.stop_lon,')'));
ALTER TABLE stops ADD SPATIAL INDEX(ll);
ALTER TABLE stops DROP stop_lat;
ALTER TABLE stops DROP stop_lon;

#add a nice distance function helps us filtering the nearest stations
DROP FUNCTION IF EXISTS distance;
CREATE FUNCTION `distance`(a POINT, b POINT)
RETURNS double DETERMINISTIC 
BEGIN
  RETURN glength(linestringfromwkb(linestring(a,b)));
END;

SET @pp=POINT(52.49451346852094,13.42128038406372);

#test it:
select stop_name, distance(stops.ll, @pp ) as sdist from stops order by sdist LIMIT 10;