Skip to content

Instantly share code, notes, and snippets.

@nckltcha

nckltcha/gist:f8fb4402ce5b27eda044

Created July 22, 2014 21:15
Show Gist options

  • Select an option

    Learn more about clone URLs
  • Save nckltcha/f8fb4402ce5b27eda044 to your computer and use it in GitHub Desktop.

Select an option

Learn more about clone URLs
Save nckltcha/f8fb4402ce5b27eda044 to your computer and use it in GitHub Desktop.
Download ZIP
gmdata closest stops
Raw
gistfile1.sql
SELECT distinct stops.stop_id, stops.stop_name, route_long_name, arrival_time, ( 3959 * ACOS( COS( RADIANS( 53.48 ) ) * COS( RADIANS(stop_lat ) ) * COS( RADIANS( stop_lon ) - RADIANS( - 2.25 ) ) + SIN( RADIANS( 53.48 ) ) * SIN( RADIANS( stop_lat ) ) ) ) AS distance
FROM stops
LEFT JOIN stop_times ON stops.stop_id = stop_times.stop_id
LEFT JOIN trips ON stop_times.trip_id = trips.trip_id
LEFT JOIN routes ON routes.route_id = trips.route_id
WHERE routes.route_type =0
AND arrival_time
BETWEEN CURRENT_TIME( )
AND ADDTIME( CURRENT_TIME( ) , '00:10:00.0' )
having distance <1
ORDER BY arrival_time
LIMIT 10
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment