Skip to content

Instantly share code, notes, and snippets.

@mheadd
Created October 18, 2010 14:35
Show Gist options
  • Save mheadd/632306 to your computer and use it in GitHub Desktop.
Save mheadd/632306 to your computer and use it in GitHub Desktop.
Stored Procedures for querying GFTS data from the State of Delaware in a MySQL database.
-- ----------------------------------------------------------------------------------------------------
-- Stored Procedures for querying GFTS data from the State of Delaware in a MySQL database.
--
-- Copyright 2010 Mark J. Headd
-- http://www.voiceingov.org
--
-- This file is free software; you can redistribute it and/or modify it under the terms of the
-- GNU Library General Public License as published by the Free Software Foundation; either version 2 of the
-- License, or (at your option) any later version.
-- This file is distributed in the hope that it will be useful, but WITHOUT ANY WARRANTY;
-- without even the implied warranty of MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.
-- See the GNU Library General Public License for more details.
-- If you modify and/or redistribute this script, you must give attribution to the author.
--
-- ----------------------------------------------------------------------------------------------------
DELIMITER $$
-- Get the next departure times and the routes served from a stop using stop_id.
DROP PROCEDURE IF EXISTS `transitdata`.`GetDepartureTimesAndRoutesByStopID`$$
CREATE DEFINER=`root`@`localhost` PROCEDURE `GetDepartureTimesAndRoutesByStopID`(
IN StopID INT,
IN NumDepartures INT,
IN DayOfTheWeek CHAR
)
BEGIN
SET @StopID = StopID;
SET @NumDepartures = NumDepartures;
SET @DayOfTheWeek = DayOfTheWeek;
PREPARE STMT FROM
'SELECT a.trip_id, SUBTIME(a.departure_time, CURTIME()) AS min_leaving, b.route_id, c.route_long_name
FROM transitdata.stop_times a, transitdata.trips b, transitdata.routes c
WHERE a.stop_id = ?
AND a.departure_time > CURTIME()
AND a.trip_id = b.trip_id
AND b.route_id = c.route_id
AND b.service_id IN (SELECT service_id FROM calendar WHERE ?)
ORDER BY min_leaving
LIMIT ?';
EXECUTE STMT USING @StopID, @DayOfTheWeek, @NumDepartures;
END$$
-- Get the stop_id and stop_name of routes based on distance.
DROP PROCEDURE IF EXISTS `transitdata`.`GetClosestStopsByLocation`$$
CREATE PROCEDURE `transitdata`.`GetClosestStopsByLocation` (
IN StopLat DOUBLE,
IN StopLon DOUBLE,
IN Rnd INT,
IN Distance INT,
IN NumStops INT
)
BEGIN
SET @StopLat = StopLat;
SET @StopLon = StopLon;
SET @Rnd = Rnd;
SET @Distance = Distance;
SET @NumStops = NumStops;
PREPARE STMT FROM
'SELECT `stop_id`, `stop_name`, ROUND(((ACOS( SIN(? * PI()/180 ) * SIN(stop_lat * PI()/180 )
+ COS(? * PI()/180 ) * COS(stop_lat * PI()/180 ) * COS((? - stop_lon) * PI()/180))*180/PI())*60*1.1515), ?)
AS DISTANCE FROM transitdata.stops
HAVING distance <= ?
ORDER BY DISTANCE ASC LIMIT ?';
EXECUTE STMT USING @StopLat, @StopLat, @StopLon, @Rnd, @Distance, @NumStops;
END$$
DELIMITER ;
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment