Get Locations Nearest to Longest Using Php?

Question

Hi In my data base I have an total 100 locations in this 50 locations are nearest to me another 50 locations are longest to me so i want to sort data using this order so here i calculate the distance

$latt=$_REQUEST['latt'];
$long=$_REQUEST['long'];

$start=$_REQUEST['start'];
 function distancefind($lat1, $lon1, $lat2, $lon2, $unit) { 

  $theta = $lon1 - $lon2; 
  $dist = sin(deg2rad($lat1)) * sin(deg2rad($lat2)) +  cos(deg2rad($lat1)) * cos(deg2rad($lat2)) * cos(deg2rad($theta)); 
  $dist = acos($dist); 
  $dist = rad2deg($dist); 
  $miles = $dist * 60 * 1.1515;
  $unit = strtoupper($unit);

  if ($unit == "K") {
    return ($miles * 1.609344); 
  } else if ($unit == "N") {
      return ($miles * 0.8684);
    } else {
        return $miles;
      }
}



 $c=0;
    $limt=10;
    if($start!=null)
    {
    $query="select * from  tbl_MapDetails LIMIT $start,$limt";
    }
$result=mysql_query($query);
if($result)
{
while($row=mysql_fetch_array($result))
{

$distance1=distancefind($latt,$long,$row['Latitude'],$row['Longitude'],"");


$message[$c]=array("ID"=>$row['LocationID'],"Address"=>$address,"City"=>$row['City']=($row['City'] != null)?$row['City']:"","State"=>$row['State']=($row['State'] !=null)?$row['State']:"","Country"=>$row['Country']=($row['Country']!=null)?$row['Country']:"","Zip"=>$row['Zip']=($row['Zip'] !=null)?$row['Zip']:"","Country"=>$row['Country']=($row['Country']!=null)?$row['Country']:"","Distance"=>$distance1=($distance1==null)?"0":$distance1,"Latitude"=>$row['Latitude']=($row['Latitude']!=null)?$row['Latitude']:"","Longitude"=>$row['Longitude']=($row['Longitude']!=null)?$row['Longitude']:"","Pic"=>$pic,"rating"=>$row1[0]=($row1[0]!=null)?$row1[0]:"","name"=>$row['LocationName']=($row['LocationName']!=null)?$row['LocationName']:"","note"=>$row['Note']=($row['Note']!=null)?$row['Note']:"","feature1"=>$row['FeatureIcon1']=($row['FeatureIcon1']!=null)?$row['FeatureIcon1']:"","feature2"=>$row['FeatureIcon2']=($row['FeatureIcon2']!=null)?$row['FeatureIcon2']:"","feature3"=>$row['FeatureIcon3']=($row['FeatureIcon3']!=null)?$row['FeatureIcon3']:"","selectLogo"=>$row['SelectIcon']=($row['SelectIcon']!=null)?$row['SelectIcon']:"");
$c++;
}

so here i get each time i get 10 locations so message array data start with nearest to longest how can i check please guide me

Thanks for advance

you can do it directly by sql query. http://stackoverflow.com/questions/4645490/get-nearest-places-google-maps-mysql-spatial-data check this link, you need to remove having clause from query. — Gaurav, Jan 10 '12 at 06:02

score 1 · Accepted Answer · answered Jan 10 '12 at 06:06

1

If you want to do the ordering first and the limiting second (ie pick the $limt closest things (offset by $start) to ($lat,$long)), you can do it within MySQL:

$query = "SELECT *,
 ((ACOS(  SIN($lat*PI()/180)*SIN(Latitude*PI()/180) 
        + COS($lat*PI()/180)*COS(Latitude*PI()/180)*COS(($long-Longitude)*PI()/180
        )
  ) * 180/PI()
 )*60 * 1.1515) AS dist
FROM  tbl_MapDetails
ORDER BY dist 
LIMIT $start,$limt";

But if you want to do the $limt first and then sort by distance (so pick $limt items and then order them by distance -- may not contain the closest location though), use @SergeyRatnikov's answer, or you need to do a nested select as:

SELECT * from
 (SELECT *,
   ...[distancecalculation]... AS dist
   FROM tbl_MapDetails
   LIMIT $start,$limt)
ORDER BY dist

answered Jan 10 '12 at 06:06

mathematical.coffee

55,977
11
154
194

execute this query but it's not working.. i get #1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '*PI()/180)*SIN(Latitude*PI()/180) + COS(*PI()/180)*COS(Latitude*PI()/180)*COS((8' at line 1 – Chithri Ajay Jan 10 '12 at 06:42
This works for me when I type it into a mysql prompt. Do you have unbalanced brackets or something? Can you try building the query up bit-by-bit (i.e. first do `SIN($lat*PI()/180)`, then add in each expression one-by-one) and see what step it goes wrong at? – mathematical.coffee Jan 10 '12 at 07:17

score 0 · Answer 2 · answered Jan 10 '12 at 05:48

0

This code sort array $message by Distance

function cmp($a, $b) {
    if ($a["Distance"] == $b["Distance"]) {
        return 0;
    }
    return (floatval($a["Distance"]) < floatval($b["Distance"])) ? -1 : 1;
}

uasort($message, 'cmp');

answered Jan 10 '12 at 05:48

Sergey Ratnikov

1,296
8
12

Try `usort` instead `uasort`. This function assigns new keys to the elements in array. It will remove any existing keys that may have been assigned, rather than just reordering the keys. – Sergey Ratnikov Jan 10 '12 at 07:23

Get Locations Nearest to Longest Using Php?

2 Answers2