mysql计算距离函数,一个自定义的MySQL函数来计算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.

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