按计算的距离（未存储在DB中的距离）对MySQL结果进行排序

Question

I have "Places" stored in a database and I am using PHP to access them. What I want to do is to return all Places sorted by distance relative to a certain place.

This place will be dynamic from an Android application, i.e. I want to display all places closest to the user's location.

What would be the best way to do this? Would it be effective/efficient to retrieve ALL places in PHP into an array, calculate the distance for each place, and then sort that array by distance? Or is there an easier/faster way to accomplish what I need?

Thanks!

Answer 1

You can do it all in SQL, by calculating the distance on the fly. This is a rough approximation of the distance between stored lat/lon fields and supplied $lat/$lon

$dlat = "(`lat`-$lat)";
$dlon = "(`lon`-$lon)*".cos($lat*3.1415/180);
$dist_sql = "$dlat*$dlat+$dlon*$dlon";
$sql = "IF(`lat` IS NULL,1e20,$dist_sql)";

then use $sql as you would use any other field, e.g.

SELECT * FROM `table` ORDER BY $sql ASC

This is far from perfect but should get you started at least.

The cos() comes in because one degree of longitude is less distance as the (absolute) latitude increases.

To get from $sql to an actual value in km, divide by 90 and multiply by 10000 km. (again: very rough approximation!)