lirr.sql

DROP TABLE IF EXISTS agency;
DROP TABLE IF EXISTS calendar_dates;
DROP TABLE IF EXISTS routes;
DROP TABLE IF EXISTS shapes;
DROP TABLE IF EXISTS stops;
DROP TABLE IF EXISTS stop_times;
DROP TABLE IF EXISTS trips;

CREATE TABLE agency
(
    agency_id CHAR(2),
    agency_name TEXT,
    agency_url TEXT,
    agency_timezone TEXT,
    agency_lang TEXT,
    agency_phone TEXT
);

CREATE TABLE calendar_dates
(
    service_id TEXT,
    date NUMERIC,
    exception_type NUMERIC
);

CREATE TABLE routes(
    route_id TEXT,
    route_short_name TEXT,
    route_long_name TEXT,
    route_type NUMERIC,
    route_color TEXT,
    route_text_color TEXT
);

CREATE TABLE shapes
(
    shape_id TEXT,
    shape_pt_lat REAL,
    shape_pt_lon REAL,
    shape_pt_sequence NUMERIC
);

CREATE TABLE stops
(
    stop_id INT,
    stop_code CHAR(3),
    stop_name TEXT,
    stop_desc TEXT,
    stop_lat REAL,
    stop_lon REAL,
    stop_url TEXT,
    wheelchair_boarding INTEGER
);

CREATE TABLE stop_times
(
    trip_id TEXT,
    arrival_time TEXT,
    departure_time TEXT,
    stop_id TEXT,
    stop_sequence NUMERIC
);

CREATE TABLE trips
(
    route_id INTEGER,
    service_id TEXT,
    trip_id TEXT,
    trip_headsign TEXT,
    trip_short_name INTEGER,
    direction_id INTEGER,
    shape_id TEXT
);

.separator ','
.mode csv
.import --skip 1 /usr/local/share/lirr_gtfs/agency.txt agency
.import --skip 1 /usr/local/share/lirr_gtfs/calendar_dates.txt calendar_dates
.import --skip 1 /usr/local/share/lirr_gtfs/routes.txt routes
.import --skip 1 /usr/local/share/lirr_gtfs/shapes.txt shapes
.import --skip 1 /usr/local/share/lirr_gtfs/stops.txt stops
.import --skip 1 /usr/local/share/lirr_gtfs/stop_times.txt stop_times
.import --skip 1 /usr/local/share/lirr_gtfs/trips.txt trips

CREATE VIEW IF NOT EXISTS today_service_ids AS SELECT service_id FROM calendar_dates WHERE date == strftime('%Y%m%d', 'now', 'localtime');

nexttrain.sql

SELECT departure_time FROM stop_times
WHERE trip_id in (select trip_id from trips where trips.route_id == 2 and trips.direction_id == 0 and trips.service_id IN today_service_ids)
AND (stop_id == 237 and stop_sequence == 1) 
AND stop_times.departure_time > strftime('%H:%M:%S', 'now', '-05:00')
AND stop_times.departure_time < strftime('%H:%M:%S', 'now', '-02:00');