0

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.

Community
  • 1
  • 1
artembus
  • 427
  • 1
  • 6
  • 13
  • If you're doing a lot of GIS-type work, [PostGIS](http://postgis.net/) for Postgres is far more capable than equivalent solutions in MySQL. Worth checking out if you're just starting. – tadman Dec 16 '14 at 19:08
  • Schema for GTFS database please. – david strachan Dec 16 '14 at 20:21
  • If you meant the content of the GTFS database then its the standard tables and the relevant are: routes-(route_id,agency_id,route_short_name,route_long_name,route_type) shapes-(shape_id,shape_pt_lat,shape_pt_lon,shape_pt_sequence)stop_times-(trip_id,arrival_time,departure_time,stop_id,stop_sequence)stops-(stop_id,stop_code,stop_name,stop_desc,stop_lon,stop_lat) trips-(route_id,service_id,trip_id,direction_id,shape_id) Another detail is that I want to get the shapes which means all the shapes of the routes stopping at a stop within a"radius" distance from the spot – artembus Dec 16 '14 at 20:42
  • I also recommend PostGIS. You can find some code to import into PostGIS [in this repo](https://github.com/harrisony/gtfs_SQL_importer). You can then [read the docs](http://postgis.net/docs/manual-1.3/ch03.html#id434832) for select :) – Tony Laidig Dec 16 '14 at 22:05

1 Answers1

0

(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;
Community
  • 1
  • 1
abeboparebop
  • 7,396
  • 6
  • 37
  • 46
  • Thank you for the great answer.I did add the function and added the table as you specified but again I stumbled across 2 more questions/issues: 1- in your last example I need to insert "stop_lat, stop_lon" to the function, how can I insert them in? (I need to take stops.stop_lan and stops.stop_lon for every row I check and insert them in the function, how its done?). second question-I actually need to select the shapes like so "SELECT `shape_id`, `shape_pt_lat`, `shape_pt_lon` FROM `shapes` WHERE `shape_id`=?" this shape_id need to be equal to the shape_id of the routes selected in your – artembus Dec 18 '14 at 17:20
  • third example.(shapes.shape_id == routes.shape_id of the selected routes.route_id) I guess I know the logical flow I need but I don't have the language knowledge to execute it and I hope you can help me. thanks again for your help – artembus Dec 18 '14 at 17:23