CODEWITHSUNDEEP
mysql
Pushpendra Kuntal
Pushpendra Kuntal

Reputation: 6196

Is my function correct to calculate distance between two latitude and longitude?

i am woking in mysql.

I want to make a function which returns distance between two coordinates.

I want to get this distance in miles.

this is my code:-

DROP FUNCTION `checkDistance`//

CREATE DEFINER=`root`@`localhost` FUNCTION `checkDistance`(

lat1 DOUBLE(18,15),

lon1 DOUBLE(18,15),

lat2 DOUBLE(18,15),

lon2 DOUBLE(18,15)

) RETURNS double(18,15)

DETERMINISTIC

BEGIN

                DECLARE delta_lat DOUBLE(18,15);

                DECLARE delta_lon DOUBLE(18,15);

                DECLARE temp1 DOUBLE(18,15);

                DECLARE EARTH_RADIUS DOUBLE(18,15);

                DECLARE distance DOUBLE(18,15);

                SET lat1 = RADIANS(lat1);

                SET lon1 = RADIANS(lon1);

                SET lat2 = RADIANS(lat2);

                SET lon2 = RADIANS(lon2);

                SET delta_lat = lat2 - lat1;

                SET delta_lon = lon2 - lon1;

                SET temp1 = POW(SIN(delta_lat/2.0),2) + COS(lat1) * COS(lat2) * 

POW(SIN(delta_lon/2.0),2);

                SET EARTH_RADIUS = 3956.0;

                SET distance = EARTH_RADIUS * 2 * ATAN2(SQRT(temp1),SQRT(1-temp1));

                RETURN distance;

    END

whenever i provide these argument for testing :-

lat1=26.860524000000000 lon1= 75.762005000000000

lat2= 26.860084000000000 lon2=75.761745000000000

then this output is generated:- 0.008681244150493

but when i provide above argument some other formulas then i get another results like this:- miles = 0.034364904109861

please tell me which one is correct ? I have searched so many times on net, and there are so many formula to calculate this distance. please check my above code and suggest me if there are some issues. I want very accurate result.

Thank you in advance. You are welcome to post your suggestions.

Upvotes: 0

Views: 314

Answers (1)

Cybercartel
Cybercartel

Reputation: 12592

This looks like the mercantor projection and the harvesine formula. It looks right to me. With your data I get 'Distance: 0.05531 km'. Can you try to set EARTH_RADIUS to the correct value (6371). I'm not sure why you pick the half radius. Here is my source: http://www.movable-type.co.uk/scripts/latlong.html.

Upvotes: 1

Related Questions