一个自定义的MySQL函数来计算Haversine距离? [英] A custom MySQL function to calculate the Haversine distance?

问题描述

我正在构建查找我最近的"脚本，据此我的客户向我提供了他们所在位置的列表.经过研究，我确定实现此目的的方法是对用户提供的地址/邮政编码进行地理编码，并使用Haversine公式计算距离.

I'm building a 'find my nearest' script whereby my client has provided me with a list of their locations. After some research, I determined that the way to do this was to geocode the address/postcode given by the user, and use the Haversine formula to calculate the distance.

明智地使用公式，我从这个问题(对你们表示敬意).因此，我在这里不再重复冗长的查询/公式.

Formula wise, I got the answer I was looking for from this question (kudos to you guys). So I won't repeat the lengthy query/formula here.

举例来说，我想做的事是这样的:

What i'd like to have been able to do though, as an example - is something like:

SELECT address, haversine(@myLat,@myLong,db_lat,db_long,'MILES') .....

通过将函数复制到将来的项目中，而不必重新学习/重新集成大公式，这将更容易记住，以后更易于阅读和更可重用.此外，最后一个参数可能有助于返回不同单位的距离.

This would be just easier to remember, easier to read later, and more re-usable by copying the function into future projects without having to relearn / re-integrate the big formula. Additionally, the last argument could help with being able to return distances in different units.

是否可以创建用户MySQL函数/过程来执行此操作，我将如何处理? (我想这就是它们的用途，但是我从来不需要使用它们！)

Is it possible to create a user MySQL function / procedure to do this, and how would I go about it? (I assume this is what they are for, but i've never needed to use them!)

与长版本相比，它会提供任何速度差异(两种方式)吗?

Would it offer any speed difference (either way) over the long version?

推荐答案

是的，您可以为此创建存储功能.像这样:

Yes, you can create a stored function for this purpose. Something like this:

DELIMITER //
  DROP FUNCTION IF EXISTS Haversine //
  CREATE FUNCTION Haversine
    ( myLat FLOAT
    , myLong FLOAT
    , db_lat FLOAT
    , db_long FLOAT
    , unit VARCHAR(20)
    )
    RETURNS FLOAT
      DETERMINISTIC
    BEGIN
      DECLARE haver FLOAT ;

      IF unit = 'MILES'                    --- calculations
        SET haver = ...                --- calculations

      RETURN haver ;
    END  //
DELIMITER ;

我认为它不会带来任何提速，但是出于您提到的所有其他原因，它还是有好处的:可读性，可重用性，易于维护(假设您在两年后发现错误，必须在(几百个地方).

I don't think it offers any speed gains but it's good for all the other reasons you mention: Readability, reusability, ease of maintenance (imagine you find an error after 2 years and you have to edit the code in a (few) hundred places).

这篇关于一个自定义的MySQL函数来计算Haversine距离?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！