Ouch.

This is a portion of code that is used in a MySQL database to assist in finding a series of near-by co-ordinates out of a table.

It’s currently in use in an active project for my work, Euro RSCG 4D Toronto and was a derivative from some PHP code that was written by a monkey. It was ‘ported’ to a mysql function for speed and proved to be quite useful — and quite evil when there was a small bug in it.

DROP FUNCTION IF EXISTS calculateDistance;
DELIMITER //

CREATE FUNCTION calculateDistance (x1 DECIMAL(16,12), y1 DECIMAL(16,12), x2 DECIMAL(16,12), y2 DECIMAL(16,12))
RETURNS DECIMAL(16,12)
DETERMINISTIC
BEGIN
DECLARE rx1 DECIMAL(16,12);
DECLARE ry1 DECIMAL(16,12);
DECLARE rx2 DECIMAL(16,12);
DECLARE ry2 DECIMAL(16,12);
DECLARE rdist DECIMAL(16,12);
DECLARE ydiff DECIMAL(16,12);
DECLARE z DECIMAL(16,12);
DECLARE miles DECIMAL(16,12);
DECLARE km DECIMAL(16,12);

SET rx1 = RADIANS(x1);
SET ry1 = RADIANS(y1);
SET rx2 = RADIANS(x2);
SET ry2 = RADIANS(y2);

SET ydiff = ABS(ry2 – ry1);

SET z = SIN(rx1) * SIN(rx2) + COS(rx1) * COS(rx2) * COS(ydiff);

SET rdist = ATAN(-z / SQRT(-z * z + 1)) + 2 * ATAN(1);

SET miles = rdist * 3958.754;

SET km = miles * 1.609344;

return km;
END; //

DELIMITER ;

SELECT calculateDistance(’43.145300′, ‘-80.289100′, geoLat, geoLong) as distance FROM VIEW_Store ORDER BY distance ASC LIMIT 5;