Reputation: 8296
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?
Upvotes: 1
Views: 1503
Reputation: 115660
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).
Upvotes: 3