CockroachDB 19.2 IMPORT INTO with GTFS data

gtfs.sql

-- IMPORT INTO for GTFS data (e.g. http://transitfeeds.com/p/mta/87)
create table agencies (
	agency_id int primary key,
	agency_name string,
	agency_url string,
	agency_timezone string,
	agency_phone string,
	agency_lang string
);

create table routes (
	route_id int primary key,
	agency_id int references agencies (agency_id),
	route_short_name string,
	route_long_name string,
	route_desc string,
	route_type int,
	route_url string,
	route_color string,
	route_text_color string
);

create table stops (
	stop_id int primary key,
	stop_code string,
	stop_name string,
	stop_desc string,
	stop_lat float,
	stop_lon float,
	zone_id string,
	stop_url string,
	location_type int,
	parent_station int,
	wheelchair_boarding boolean
);

create table notes (
	note_id string primary key,
	note_mark string,
	note_title string,
	note_description string
);

create table shapes (
	shape_id int,
	shape_pt_lat float,
	shape_pt_lon float,
	shape_pt_sequence int,
	shape_dist_traveled float,
	primary key (shape_id, shape_pt_sequence)
);

create table trips (
	route_id int references routes (route_id),
	service_id int,
	trip_id int primary key,
	trip_headsign string,
	trip_short_name string,
	direction_id int,
	block_id string,
	shape_id int,
	wheelchair_accessible boolean,
	peak_offpeak int
);

create table stop_times (
	trip_id int references trips (trip_id),
	arrival_time interval,
	departure_time interval,
	stop_id int,
	stop_sequence int,
	pickup_type int,
	drop_off_type int,
	track string,
	note_id string references notes (note_id),
	primary key (trip_id, stop_sequence)
);



import into agencies (
	agency_id,
	agency_name,
	agency_url,
	agency_timezone,
	agency_phone,
	agency_lang
) csv data (
	'nodelocal:///agency.txt'
) with skip='1', nullif='';

import into routes (
	route_id,
	agency_id,
	route_short_name,
	route_long_name,
	route_desc,
	route_type,
	route_url,
	route_color,
	route_text_color
) csv data (
	'nodelocal:///routes.txt'
) with skip='1', nullif='';

import into stops (
	stop_id,
	stop_code,
	stop_name,
	stop_desc,
	stop_lat,
	stop_lon,
	zone_id,
	stop_url,
	location_type,
	parent_station,
	wheelchair_boarding
) csv data (
	'nodelocal:///stops.txt'
) with skip='1', nullif='';

import into notes (
	note_id,
	note_mark,
	note_title,
	note_description
) csv data (
	'nodelocal:///notes.txt'
) with skip='1', nullif='';

import into shapes (
	shape_id,
	shape_pt_lat,
	shape_pt_lon,
	shape_pt_sequence,
	shape_dist_traveled
) csv data (
	'nodelocal:///shapes.txt'
) with skip='1', nullif=''; 

import into trips (
	route_id,
	service_id,
	trip_id,
	trip_headsign,
	trip_short_name,
	direction_id,
	block_id,
	shape_id,
	wheelchair_accessible,
	peak_offpeak
) csv data (
	'nodelocal:///trips.txt'
) with skip='1', nullif=''; 

import into stop_times (
	trip_id,
	arrival_time,
	departure_time,
	stop_id,
	stop_sequence,
	pickup_type,
	drop_off_type,
	track,
	note_id
) csv data (
	'nodelocal:///stop_times.txt'
) with skip='1', nullif=''; 

http_imports.sql

-- All except stop_times to which access is denied

import into agencies (
	agency_id,
	agency_name,
	agency_url,
	agency_timezone,
	agency_phone,
	agency_lang
) csv data (
	'https://openmobilitydata-data.s3-us-west-1.amazonaws.com/public/feeds/mta/87/20190818/original/agency.txt'
) with skip='1', nullif='';

import into routes (
	route_id,
	agency_id,
	route_short_name,
	route_long_name,
	route_desc,
	route_type,
	route_url,
	route_color,
	route_text_color
) csv data (
	'https://openmobilitydata-data.s3-us-west-1.amazonaws.com/public/feeds/mta/87/20190818/original/routes.txt'
) with skip='1', nullif='';

import into stops (
	stop_id,
	stop_code,
	stop_name,
	stop_desc,
	stop_lat,
	stop_lon,
	zone_id,
	stop_url,
	location_type,
	parent_station,
	wheelchair_boarding
) csv data (
	'https://openmobilitydata-data.s3-us-west-1.amazonaws.com/public/feeds/mta/87/20190818/original/stops.txt'
) with skip='1', nullif='';

import into notes (
	note_id,
	note_mark,
	note_title,
	note_description
) csv data (
	'https://openmobilitydata-data.s3-us-west-1.amazonaws.com/public/feeds/mta/87/20190818/original/notes.txt'
) with skip='1', nullif='';

import into shapes (
	shape_id,
	shape_pt_lat,
	shape_pt_lon,
	shape_pt_sequence,
	shape_dist_traveled
) csv data (
	'https://openmobilitydata-data.s3-us-west-1.amazonaws.com/public/feeds/mta/87/20190818/original/shapes.txt'
) with skip='1', nullif=''; 

import into trips (
	route_id,
	service_id,
	trip_id,
	trip_headsign,
	trip_short_name,
	direction_id,
	block_id,
	shape_id,
	wheelchair_accessible,
	peak_offpeak
) csv data (
	'https://openmobilitydata-data.s3-us-west-1.amazonaws.com/public/feeds/mta/87/20190818/original/trips.txt'
) with skip='1', nullif=''; 

time_from_gct.csv

time_from_gct.sql

SELECT
	rank() OVER (ORDER BY avg), *
FROM
	(
		SELECT
			stats.stop_id AS stop_id,
			stop_name,
			stop_lat,
			stop_lon,
			count(trip_id) AS trips,
			min(time_from_gct::INT8)::INTERVAL,
			round(avg(time_from_gct::INT8))::INTERVAL
				AS avg,
			max(time_from_gct::INT8)::INTERVAL
		FROM
			(
				SELECT
					trip_id,
					stop_id,
					arrival_time,
					arrival_time
					- min(departure_time) OVER (
							PARTITION BY trip_id
						)
						AS time_from_gct
				FROM
					stop_times
				WHERE
					trip_id
					IN (
							SELECT
								DISTINCT trip_id
							FROM
								stop_times
							WHERE
								stop_id = 1
								AND stop_sequence = 1
						)
			)
				AS stats
			JOIN stops ON stats.stop_id = stops.stop_id
		GROUP BY
			stats.stop_id, stop_name, stop_lat, stop_lon
	)
ORDER BY
	rank ASC