Stored Procedures for querying GFTS data from the State of Delaware in a MySQL database.

dartfirststate_de_us_procs.sql

-- ----------------------------------------------------------------------------------------------------
-- 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 ;