Geek asks and answers

MySQL custom function for calculating the haversin distance?

I create a “find the closest” script through which my client provided me with a list of their locations. After some research, I decided that the way to do this is to geocode the address / postcode specified by the user and use the Haversin formula to calculate the distance.

The formula is wise, I got the answer I was looking for from this question (unfortunately guys). Therefore, I will not repeat the long query / formula here.

What I would like to do, though as an example, is something like:

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

It would be easier to remember, easier to read later, and more reusable by copying the function into future projects without retraining / integrating a large formula. In addition, the last argument can help with the ability to return distances in different units.

Is it possible to create a custom MySQL function / procedure for this and how do I do this? (I guess this is what they are for, but I never used them!)

Will she offer any speed difference (anyway) for the long version?

+1
source share
1 answer

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

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 ;

, - , , : , , (, 2 , ) ).

+3

More articles:


All Articles