为什么该MySQL存储函数给出的结果与查询中的计算结果不同? [英] Why does this MySQL stored function give different results than to doing the calculation in the query?

问题描述

这是一个有关使用haversine公式计算地球上两个纬度和经度之间的距离的问题，用于需要查找我最近的"功能的项目中.

This is a question about calculating the distance between two points of latitude and longitude on the earth using a haversine formula, for use in projects where you need to have a 'find my nearest' function.

在MySQL中，这篇文章中已经很好地讨论并解决了Haversine公式

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

然后我问了这个问题将该函数存储到存储函数中，以便将来的项目可以使用它，而不必查找，记住该表达式或以长格式重新键入该表达式.

I then asked this question about turning it into a stored function so that its available for future projects without having to lookup, remember or re-type the formula in its long form.

一切都很好.除了我的函数的结果(略有不同)以外，其他条件相同时，直接在查询中直接键入公式即可.为什么会这样?

Its all good. Except my function differs in results (slightly) to just typing the formula directly into the query, all other things being equal. Why is this?

这是我写的函数:

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's a set of test queries set to find the distance between the London Eye and Buckingham Palace, just for an example. Obviously normally you'd substitute the destination with fields from your database of geo-located 'things' that you want to compare with.

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

在示例中，我们使用英里-因此我们将半径(测试中的 @milesModifier ，函数中的 radius )精确设置为3959.

In the example, we're using miles - so we've set the radius (@milesModifier in the test, radius in the function) to 3959 exactly.

我得到的结果很有趣(在MySQL 5.2.6社区版上)，亮点:

The result I got back was interesting (on MySQL 5.2.6 community edition), highlights:

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

longFormat 是在查询中完成的数学运算， distanceMiles 是该函数的结果.

longFormat is the maths done in the query, distanceMiles is the result of the function.

结果是不同的...确定，所以就在项目中使用该函数而言，这是微不足道的，但是我很想知道函数内部或外部的相同公式如何产生不同的结果.

The results are different... OK, so its an insignificance as far as using the function in a project, but i'm interested to know how the same formula inside or outside of the function have different results.

我猜想这与FLOAT的长度有关-在函数中未指定长度，我尝试指定它们(最多30,15)，以便为我拥有的所有图形留出足够的空间和我期望的输出-但结果仍然略有不同.

I'm guessing that its to do with lengths of the FLOAT - they're not specified in the function, I have tried specifying them (right up to 30,15) to give plenty of room for all the figures I have and the output I expect - but the results still differ slightly.

推荐答案

FLOAT是一种近似的数据类型-请参见:

FLOAT is an approximate data type - see:

浮点值问题
数值类型

尝试将FLOAT更改为DECIMAL(30,15)，以确保您具有正确的精度.

Try changing FLOAT to DECIMAL(30,15) to ensure that you have the correct precision.

如果您想深入讨论浮点，可以尝试这篇文章:

If you want an in-depth discussion of floating point, you could try this article:

每个计算机科学家应了解的浮点知识算术

这篇关于为什么该MySQL存储函数给出的结果与查询中的计算结果不同?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！