calculate distance between 2 points using mysql and php

Question

i have a map page that have a forma for calculating distance but this contain 2 drop list that handle all the cities in the database to allow user to select the first point from the first drop list and the second point from the second drop list then to calculate the distance using the longitude and latitude stored in the database for each city.

village table:

 - id
 - village_name
 - lattitude
 - longiude
 - district_id

i have this query untill now but i do not know if it is right 

<?php 
    if(isset($_POST['calculate']))
    {
        $sql = mysql_query("SELECT ( 3959 * acos( cos( radians(37) ) * cos( radians( lattitude ) ) * cos( radians( longitude ) - radians(-122) ) + sin( radians(37) ) * sin( radians( lat ) ) ) )FROM village")or die(mysql_error());
    }

?>

the problem is that i do not know how to calculate distance between 2 points using the latitude and longitude stored in the database

i did read about the haversine formula but i do not know how to use it here

i do not want anyone to do it for me but to explain to me and help me can anyone help me with this ????

http://stackoverflow.com/questions/1006654/fastest-way-to-find-distance-between-two-lat-long-points — Drew, May 13 '13 at 21:24
Possible duplicate: http://stackoverflow.com/questions/574691/mysql-great-circle-distance-haversine-formula — kittycat, May 13 '13 at 21:25

score 1 · Answer 1 · answered May 13 '13 at 21:37

In my opinion you should calculate it using php.

First of all fetch lat and lon from database (i guess they are in float format)

$LonS - Start lon
$LonE - End lon
$LatS - Start lat
$LatE - End lat

This is the great circle formula http://en.wikipedia.org/wiki/Haversine_formula

dot (.) means multiplication, in php '*'. So the final code in php:

$R = 6371; //km
$A = pow(sin(($LatE - $LatS)/2), 2) + cos($LatS) * cos($LatE) * pow(sin(($LonE - $LonS)/2) , 2);
$C = 2 * atan2(sqrt($A), sqrt(1 - $A));
$D = $R * $C;

calculate distance between 2 points using mysql and php

1 Answers1