CODEWITHSUNDEEP
sql
user3718549
user3718549

Reputation: 55

MYSQL select MIN distance beetween latitude and Longitude

I have a table that contains column called func_id , latitude and longitude. Im trying to get the min distance row and return the func_id by a function.

I tried:

 function Get_Min_func_id($lat, $long){
        $query = mysql_query("
 select func_id,
   MIN( 3959 * acos( cos( radians('$lat') ) 
          * cos( radians( latitude ) ) 
          * cos( radians( longitude ) - radians('$long') ) 
          + sin( radians('$lat') ) 
          * sin( radians( latitude ) ) ) )  AS distance 
from entregas 
");

  return @mysql_result($query, 0, 'func_id');
  }

But its not working fine. It is returning only the same func_id.

Upvotes: 1

Views: 1127

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269493

This is the query that you want:

 select func_id,
        ( 3959 * acos( cos( radians('$lat') ) 
          * cos( radians( latitude ) ) 
          * cos( radians( longitude ) - radians('$long') ) 
          + sin( radians('$lat') ) 
          * sin( radians( latitude ) ) ) )  AS distance 
 from entregas
 order by distance asc
 limit 1;

Your query will return the minimum distance, but an arbitrary value for func_id. As a general rule, you should always have the same columns in a group by as in the select, unless you are using aggregation functions. In this case, you have an aggregation query with no group by, so there should be no additional columns.

Upvotes: 2

Related Questions