1

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.

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?

Community
  • 1
  • 1
Codecraft
  • 8,291
  • 4
  • 28
  • 45

1 Answers1

3

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

ypercubeᵀᴹ
  • 113,259
  • 19
  • 174
  • 235
  • Perfect. I've had a play around now that I had somewhere to start, and with a bit of trial and error i've come up with the full function - and edited it into your post. Hopefully others can make use of it too. – Codecraft Jun 17 '11 at 17:45
  • me either, I guess my edit was rejected - but I have idea why this would be. The function appears again in another question it generated: http://stackoverflow.com/questions/6389859/this-stored-function-for-calculating-haversine-is-returning-different-results-to – Codecraft Jun 18 '11 at 09:27