GTFS: Select stops with associated routes (works on a database build from gtfs files, one table per file) https://developers.google.com/transit/gtfs/reference)

create_stop_route_link_table.sql

CREATE TABLE stop_route_link (stop_id,route_id);

INSERT INTO stop_route_link (stop_id, route_id)
	SELECT DISTINCT stops.stop_id, trips.route_id from stops
	INNER join stop_times on stop_times.stop_id=stops.stop_id
	INNER join trips on trips.trip_id=stop_times.trip_id;

select_with_stop_route_link.sql

select stops.stop_name, routes.route_long_name
from stops
join stop_route_link on stop_route_link.stop_id=stops.stop_id
join routes on routes.route_id=stop_route_link.route_id;

stop_with_route.sql

SELECT DISTINCT stops.stop_name, routes.route_long_name from stops
	INNER join stop_times on stop_times.stop_id=stops.stop_id
	INNER join trips on trips.trip_id=stop_times.trip_id
	INNER join routes on routes.route_id=trips.route_id