Weblabor

DELIMITER $$

CREATE PROCEDURE geodist_otp (
	IN userid INT,
	IN dist INT
) 
BEGIN
	DECLARE mylon DOUBLE;
	DECLARE mylat DOUBLE;
	DECLARE lon1 FLOAT;
	DECLARE lon2 FLOAT;
	DECLARE lat1 FLOAT;
	DECLARE lat2 FLOAT;
	-- get the original lon and lat for the userid
	SELECT longitude, latitude INTO mylon, mylat FROM users WHERE id=userid LIMIT 1;
	-- calculate lon and lat for the rectangle:
	SET lon1 = mylon-dist/ABS(COS(RADIANS(mylat))*69);
	SET lon2 = mylon+dist/ABS(COS(RADIANS(mylat))*69);
	SET lat1 = mylat-(dist/69);
	SET lat2 = mylat+(dist/69);
	SELECT destination.*, 6537 * 2 * ASIN(SQRT( POWER(SIN((origin.latitude - destination.latitude) * PI()/180 / 2), 2) + COS(origin.latitude * PI()/180) * COS(destination.latitude * PI()/180) * POWER(SIN((origin.longitude - destination.longitude) * PI()/180 / 2), 2) )) AS distance FROM atm_otp destination, users origin WHERE origin.id=userid AND destination.longitude BETWEEN lon1 AND lon2 AND destination.latitude BETWEEN lat1 AND lat2 HAVING distance < dist ORDER BY distance LIMIT 10;
END $$
DELIMITER;