find records with latitude and longitude [duplicat

问题:

Possible Duplicate:
Fastest Way to Find Distance Between Two Lat/Long Points

I have locations table which consists of all the locations with their latitude and longitude values. I am getting the particular locations lat. logn. and radius. Now, I want to find all the reocords within this radius and for that given lat. and long. values. I am using the following query. But I am not sure whether it gives me the correct results or not.

From given lat and long I am finding out lat.min and max as -

$lng_min = $longitude - $radius / abs(cos(deg2rad($latitude)) * 69);
$lng_max = $longitude + $radius / abs(cos(deg2rad($latitude)) * 69);
$lat_min = $latitude - ($radius / 69);
$lat_max = $latitude + ($radius / 69);

and then use the following query -

SELECT * FROM  merchant_branches,locations 
where locations."coorX" BETWEEN $lng_min AND $lng_max 
AND  
locations."coorY" BETWEEN $lat_min AND $lat_max 
and merchant_branches.location_id = locations.id

where location id is the foreign key in merchant_brach table.

I am confused about the results I am getting. Plz help me.

回答1:

You have to use something like this to get the nearest results to a given location

$query = sprintf(
         "SELECT foo,
                  6371 * ACOS( Cos(RADIANS(lat)) * COS(RADIANS(%f))
                      * COS(RADIANS(%f) - RADIANS(lng)) + SIN(RADIANS(lat))
                      * SIN(RADIANS(%f)) ) * 1000 AS distance
            FROM `%s`
        ORDER BY distance",
        $lat, $lag, $lng, $table
    );

You have to set $lat, $lng and $table according to your table structure and maybe set a limit to the result.

You get a detailed explantation here Geo Distance Search with MySQL.