SQL script for importing GTFS into postGIS, adding geometry fields where possible

import_TTC_GTFS.sql

DROP TABLE IF EXISTS gtfs_ttc_agency;
CREATE TABLE gtfs_ttc_agency(
	agency_id int,
	agency_name text,
	agency_url text,
	agency_timezone text,
	agency_lang text,
	agency_phone text,
	agency_fare_url text
);
COPY gtfs_ttc_agency FROM 
'/home/nate/gtfs/agency.txt' CSV HEADER;


DROP TABLE IF EXISTS gtfs_ttc_calendar;
CREATE TABLE gtfs_ttc_calendar(
	service_id smallint,
	monday boolean,
	tuesday boolean,
	wednesday boolean,
	thursday boolean,
	friday boolean,
	saturday boolean,
	sunday boolean,
	start_date text, -- todo
	end_date text -- todo
);
COPY gtfs_ttc_calendar FROM 
'/home/nate/gtfs/calendar.txt' CSV HEADER;


DROP TABLE IF EXISTS gtfs_ttc_calendar_dates;
CREATE TABLE gtfs_ttc_calendar_dates(
	service_id smallint,
	"date" text,
	exception_type smallint
);
COPY gtfs_ttc_calendar_dates FROM 
'/home/nate/gtfs/calendar_dates.txt' CSV HEADER;


DROP TABLE IF EXISTS gtfs_ttc_routes;
CREATE TABLE gtfs_ttc_routes (
	route_id int,
	agency_id int,
	route_short_name text,
	route_long_name text,
	route_desc text,
	route_type smallint,
	route_url text,
	route_color text,
	route_text_color text
);
COPY gtfs_ttc_routes FROM 
'/home/nate/gtfs/routes.txt' CSV HEADER;


DROP TABLE IF EXISTS gtfs_ttc_shapes;
CREATE TABLE gtfs_ttc_shapes (
	shape_id int,
	shape_pt_lat numeric,
	shape_pt_lon numeric,
	shape_pt_sequence smallint,
	shape_dist_traveled real
);
COPY gtfs_ttc_shapes FROM 
'/home/nate/gtfs/shapes.txt' CSV HEADER;
-- add geometry field
ALTER TABLE gtfs_ttc_shapes ADD COLUMN geom geography(POINT,4326);
UPDATE gtfs_ttc_shapes SET geom = ST_MakePoint(shape_pt_lon,shape_pt_lat);


DROP TABLE IF EXISTS gtfs_ttc_stops;
CREATE TABLE gtfs_ttc_stops (
	stop_id integer,
	stop_code text,
	stop_name text,
	stop_desc text,
	stop_lat numeric,
	stop_lon numeric,
	zone_id smallint,
	stop_url text,
	location_type smallint,
	parent_station smallint,
	wheelchair_boarding smallint
);
COPY gtfs_ttc_stops FROM 
'/home/nate/gtfs/stops.txt' CSV HEADER;
-- add geometry field
ALTER TABLE gtfs_ttc_stops ADD COLUMN geom geography(POINT,4326);
UPDATE gtfs_ttc_stops SET geom = ST_MakePoint(stop_lon,stop_lat);


DROP TABLE IF EXISTS gtfs_ttc_stop_times;
CREATE TABLE gtfs_ttc_stop_times (
	trip_id int,
	arrival_time interval,
	departure_time interval,
	stop_id int,
	stop_sequence smallint,
	stop_headsign text,
	pickup_type smallint,
	drop_off_type smallint,
	shape_dist_traveled real
);
COPY gtfs_ttc_stop_times FROM 
'/home/nate/gtfs/stop_times.txt' CSV HEADER;


DROP TABLE IF EXISTS gtfs_ttc_trips;
CREATE TABLE gtfs_ttc_trips (
	route_id int,
	service_id smallint,
	trip_id integer,
	trip_headsign text,
	trip_short_name text,
	direction_id text,
	block_id int,
	shape_id int,
	wheelchair_accessible smallint
);
COPY gtfs_ttc_trips FROM 
'/home/nate/gtfs/trips.txt' CSV HEADER;
-- add geometry field
ALTER TABLE gtfs_ttc_trips ADD COLUMN geom geometry(LINESTRING,4326);
UPDATE gtfs_ttc_trips AS trip SET geom = shape
FROM (
	SELECT 
		shape_id,
		ST_MakeLine(geom::geometry ORDER BY shape_pt_sequence ASC) AS shape
	FROM gtfs_ttc_shapes
	GROUP BY shape_id
) AS sub
WHERE sub.shape_id = trip.shape_id;