gtfs-queries.sql

-- add indexes to the columns
CREATE INDEX stop_lon_idx ON stops(stop_lon)
CREATE INDEX stop_lat_idx ON stops(stop_lat)

-- find closest stops near me
SELECT 
stop_index, stop_name
FROM stops 
WHERE
stop_lon > (-90.085316 - .005) AND stop_lon < (-90.085316 + .005)
AND
stop_lat < (29.926353 + .005)  AND stop_lat > (29.926353 - .005) 


-- find closest stops and upcoming times for each trip in both directions
SELECT
  DISTINCT(s.stop_index), s.stop_name,
  r.route_short_name, r.route_long_name, r.route_text_color, r.route_type,
  t.direction_id, t.trip_headsign,
  strftime('%s','now') - strftime('%s','now', 'start of day') AS seconds_since_today_started
FROM stops s
JOIN stop_times st ON s.stop_index = st.stop_index
JOIN trips t ON st.trip_index = t.trip_index
JOIN routes r ON t.route_index = r.route_index
WHERE
  -- stops near me
  s.stop_lon > (${lon} - .005) AND s.stop_lon < (${lon} + .005)
  --s.stop_lon >  (-90.0804212 - .005) AND s.stop_lon < (-90.0804212 + .005)
  AND
  s.stop_lat < (${lat} + .005)  AND s.stop_lat > (${lat} - .005)
  --s.stop_lat < (29.9301714 + .005)  AND s.stop_lat > (29.9301714 - .005)
  AND
  -- arrivals & departures 1 hour (3600 seconds) into the future
  st.arrival_time_secs  > seconds_since_today_started  AND st.departure_time_secs > seconds_since_today_started
  AND
  st.arrival_time_secs < (st. arrival_time_secs + 3600) AND st.departure_time_secs < (st. departure_time_secs + 3600)