How to find routes near a point

Question

I have a lat and lon coordinates of a spot and the radius in which I want to search for a stop, I then execute a function from google-maps to query my GTFS database with those variables but I don't know how the query should look. Can I select the wanted routes using only sql query ? If so, how can I do that? If it can't be done using only sql what are my options?

*sorry for the broad question and no code samples but I'm new to this and need some basic concept guidance sometimes.

anyway thanks for the help.

Answer 1

(Caveat: I'm not that familiar with MySQL and these queries are untested.)

First define a function in MySQL to calculate the distance between pairs of lat-long points. See e.g. this answer. Then, to select stops near a given point:

SELECT stop_id
FROM stops
WHERE getDistanceBetweenPointsNew(stop_lat, stop_lon, my_lat, my_lon) < my_dist;

There is no extremely natural way to find routes associated with stops in the GTFS spec. To do so, you'll need to join trips against stop_times, which will be slow if your stop_times table is large and/or unindexed. I suggest pre-calculating a table associating stops and routes:

CREATE TABLE route_stop AS
SELECT DISTINCT route_id, stop_id
FROM trips
JOIN stop_times
ON trips.trip_id = stop_times.trip_id;

Assuming this table has been created, you can find the list of routes that stop near a given point like so:

SELECT route_id
FROM stops
JOIN route_stop
ON stops.stop_id = route_stop.stop_id
WHERE getDistanceBetweenPointsNew(stop_lat, stop_lon, my_lat, my_lon) < my_dist;