Calculating distance help needed

[smith.james0]

Hi

My site will display a list of shop sorted by distance, when you've put your post code in.

 

The guery to return the list of shops in distance order

$sql = "SELECT *, ((ACOS(SIN($_SESSION[lat] * PI() / 180) * SIN(lat * PI() / 180) + COS($_SESSION[lat] * PI() / 180) * COS(lat * PI() / 180) * COS(($_SESSION[lon] - lon) * PI() / 180)) * 180 / PI()) * 60 * 1.1515) AS `distance` FROM `table` WHERE Category = '$category2' ORDER BY `distance` ASC LIMIT $page, $limit";

 

I then use this code to display the distance

 

$postcode = $row[Postcode];

#Convert the post code to upper case and trim the variable
$postcode = strtoupper(trim($postcode));


#Remove any spaces
$postcode = str_replace(" ","",$postcode);


#Trim the last 3 characters off the end
$postcode_first = substr($postcode,0,strlen($postcode)-3);


$sql = "SELECT * FROM `outcodepostcodes` WHERE outcode = '$postcode_first' LIMIT 1";
$resultp = mysql_query($sql,$db) or die ("Invalid query1");
while($rowp = mysql_fetch_array($resultp)){ 


//Does the post code exist?
if ($rowp["outcode"] != ""){


#Assign variables
$code1_lat = $rowp["lat"];
$code1_long = $rowp["lng"];


}else{


#post code not found


}


}// end of while


#$earth = 6371; #km change accordingly
$earth = 3960; #miles


#Point 1 cords
$lat1 = deg2rad($_SESSION[lat]);
$long1= deg2rad($_SESSION[lon]);


#Point 2 cords
$lat2 = deg2rad($code1_lat);
$long2= deg2rad($code1_long);


#Haversine Formula
$dlong=$long2-$long1;
$dlat=$lat2-$lat1;


$sinlat=sin($dlat/2);
$sinlong=sin($dlong/2);


$a=($sinlat*$sinlat)+cos($lat1)*cos($lat2)*($sinlong*$sinlong);


$c=2*asin(min(1,sqrt($a)));


$miles=round($earth*$c);

 

The problem I have is that the two pieces of code return difference distances, this is the first four results displayed by distance

1)

You are 90 miles away

2)

You are 84 miles away

3)

You are 130 miles away

4)

You are 146 miles away

 

Can anyone help??

James

[Barand]

It looks a though your lat and long values come from two sources (table and outcodepostcode) . A better design would be a join on postcode so you only need them once. That way there is no possibility of different values in the two tables.

 

I cannot say this is the reason, just a possibility, without going through both formulae with a fine tooth comb.

 

Which one is correct?

[smith.james0]

The second is slightly more accurate. The Table source is where all the shop data is kept including Name Address and the Lat and Long of that shop. Outcodepostcode is a table to get your Lat Long from your postcode once you've entered it

 

James

[Barand]

Do the fields in both tables have the same format and precision?