找到2个坐标与存储在表格中的1个集合之间的最短距离

Question

I have a MySQL table with the following attributes: store_name lat lon

I have 1500 records.

I need to find the closest store to the user (based on IP).

I have figured the IP translation part using a 3rd party API. I know how to calculate the distance between 2 coordinates. However, I don't know how to return the store name that is the closest.

My function to calculate distance

function distance($lat1, $lon1, $lat2, $lon2) 
{
$minus = $lon1 - $lon2;
$dist = sin(deg2rad($lat1)) * sin(deg2rad($lat2)) +  cos(deg2rad($lat1))*cos(deg2rad($lat2)) * cos(deg2rad($minus));
$dist = acos($dist);
$dist = rad2deg($dist);
$miles = $dist * 60 * 1.1515;
return $miles;
}

I can find the distance between user and each store.

Note: $lat1 and $lat1 are obtained by a 3rd party API service.

$store = mysql_query("SELECT * FROM store") or die(mysql_error());
while($row=mysql_fetch_assoc($store))
{
$lat2 = $row["lat"];
$lon2 = $row["lon"];
$distance = distance($lat1, $lon1, $lat2,$lon2);
}

How do I calculate run the distance function and store the distance value in an array and then sort the distances to find the nearest store? Is this the correct methodology? And how do I proceed with the array code? I have no experience with arrays in PHP.

Thank you.

Answer 1

You could just do the math on the database side, order your result set by distance and select the one with the lowest value.

And I took the liberty of updating your database access methods. Don't, under any circumstances, use mysql_* functions.

$lat1 = 123.245;
$lon1 = 48.123;

$dbhost = "localhost";
$dbname = "database";
$username = "user";
$password = "pass";

$db = new PDO("mysql:host=$dbhost;dbname=$dbname", $username, $password);

$query = "SELECT *, DEGREES(ACOS(SIN(RADIANS(?)) * SIN(RADIANS(`lat`)) +  COS(RADIANS(?)) * COS(RADIANS(`lat`)) * COS(RADIANS(? - `lon`)))) * 60 * 1.1515 AS distance
    FROM store
    ORDER BY distance ASC
    LIMIT 1";
$stmt = $db->prepare($query);
$stmt->execute(array($lat1, $lat1, $lon1));
$result = $stmt->fetch(PDO::FETCH_ASSOC);
print_r($result);