A custom MySQL function to calculate the Haversine distance?

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 w开发者_JAVA技巧ould 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.

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).