Why does this MySQL stored function give different results than doing calculations in a query?

It is a question of calculating the distance between two latitude and longitude points on Earth using the Haversin formula for use in projects where you need to find the “closest” function.

The haversine formula is well discussed and solved in MySQL in this post .

Then I asked this question about turning it into a stored function so that it is available for future projects without the need to search, remember or re-enter the formula in its long form.

Things are good. In addition, my function differs in small results (simply), simply by typing the formula directly in the request, ceteris paribus. Why is this?

So here is what I wrote:

DELIMITER $$

DROP FUNCTION IF EXISTS haversine $$

CREATE FUNCTION `haversine`
    (fromLatitude FLOAT,
     fromLongitude FLOAT,
     toLatitude FLOAT,
     toLongitude FLOAT,
     unit VARCHAR(20)
     )
    RETURNS FLOAT
    DETERMINISTIC    
    COMMENT 'Returns the distance on the Earth between two known points of longitude and latitude'
    BEGIN
    DECLARE radius FLOAT;
    DECLARE distance FLOAT;

    IF unit = 'MILES' THEN SET radius = '3959';
    ELSEIF (unit = 'NAUTICAL_MILES' OR unit='NM') THEN SET radius = '3440.27694';   
    ELSEIF (unit = 'YARDS' OR unit='YD') THEN SET radius = '6967840';
    ELSEIF (unit = 'FEET' OR unit='FT') THEN SET radius = '20903520';
    ELSEIF (unit = 'KILOMETRES' OR unit='KILOMETERS' OR unit='KM') THEN SET radius = '6371.3929';
    ELSEIF (unit = 'METRES' OR UNIT='METERS' OR unit='M') THEN SET radius = '6371392.9';
    ELSE SET radius = '3959'; /* default to miles */
    END IF;

    SET distance = (radius * ACOS(COS(RADIANS(fromLatitude)) * COS(RADIANS(toLatitude)) * COS(RADIANS(toLongitude) - RADIANS(fromLongitude)) + SIN(RADIANS(fromLatitude)) * SIN(RADIANS(toLatitude))));

    RETURN distance;
    END$$

DELIMITER ;

Here is a set of test queries set to find the distance between the London Eye and Buckingham Palace, just for example. Obviously, you usually replace the destination with fields from your database of “things” that you want to compare with them.

SET @milesModifier = 3959;

SET @myLat = 51.503228;
SET @myLong = -0.119703;

SET @destLat = 51.501267;  
SET @destLong = -0.142697;

SELECT  @kilometerModifier AS radius,
    @myLat AS myLat,
    @myLong AS myLong,
    @destLat AS destLat,
    @destLong AS destLong,
    (@milesModifier * ACOS(COS(RADIANS(@myLat)) * COS(RADIANS(@destLat)) * COS(RADIANS(@destLong) - RADIANS(@myLong)) + SIN(RADIANS(@myLat)) * SIN(RADIANS(@destLat)))) AS longFormat,
    haversine(@myLat,@myLong,@destLat,@destLong,'MILES') AS distanceMiles,
    haversine(@myLat,@myLong,@destLat,@destLong,'NAUTICAL_MILES') AS distanceNautical,
    haversine(@myLat,@myLong,@destLat,@destLong,'KM') AS distanceKm,
    haversine(@myLat,@myLong,@destLat,@destLong,'METRES') AS distanceMetres,    
    haversine(@myLat,@myLong,@destLat,@destLong,'YARDS') AS distanceYards,
    haversine(@myLat,@myLong,@destLat,@destLong,'FEET') AS distanceFeet,
    haversine(@myLat,@myLong,@destLat,@destLong,'') AS distanceDefault

In the example, we use miles - so we set the radius ( @milesModifier in the test radius in function) to 3959 for sure.

The result I got back was interesting (in the MySQL community version 5.2.6), emphasizes:

| longFormat       | distanceMiles   |
|------------------|-----------------|
| 0.99826000106148 | 0.9982578754425 |

longFormat is the math executed in the request, distanceMiles is the result of the function.

... , , , .

, FLOAT - , ( 30,15), , - .