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

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!

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!)

If each location in the db has lat/lon fields then do the distance calculation in the db. The following stored procedure would need some tweaking to suit your db because we do not know the schema - but it assumes a table called places with a field with the location's latitude and a field with the longitude.

CREATE PROCEDURE `spNearestPlaces`(IN `_latitude` double, IN `_longitude` double, IN `_radius` INT)
    LANGUAGE SQL
    NOT DETERMINISTIC
    CONTAINS SQL
    SQL SECURITY DEFINER
    COMMENT ''
proc:begin
    declare strsql varchar(10000);
    declare lat double default 0;
    declare lng double default 0;
    declare radius float default 0;
    declare earth_radius integer default 0;
    declare lon1 float;
    declare lat1 float;
    declare lon2 float;
    declare lat2 float;



    set @lat=_latitude;
    set @lng=_longitude;

    set @radius=cast(_radius as unsigned);
    set @earth_radius=3956;

    set @lon1 = @lng - @radius/ceil( cos( radians( @lat ) ) * 69 );
    set @lon2 = @lng + @radius/ceil( cos( radians( @lat ) ) * 69 );
    set @lat1 = @lat - ( @radius/69 );
    set @lat2 = @lat + ( @radius/69 );




    set @strsql=concat("select
            p.`id`,
            p.`latitude`,
            p.`longitude`,
            ROUND( @earth_radius * 2 * ASIN( SQRT( POWER( SIN( (@lat - p.`latitude`) * pi()/180 / 2), 2) +COS(@lat * pi()/180) * COS(p.`latitude` * pi()/180) *POWER(SIN((@lng - p.`longitude`) * pi()/180 / 2), 2) ) ),2) AS 'distance'
            from `places` p
            where   
                    p.`latitude` between @lat1 and @lat2
                    and 
                    p.`longitude` between @lon1 and @lon2
            having `distance` <= @radius 
            order by `distance`;");

    prepare stmt from @strsql;
    execute stmt;
    deallocate prepare stmt;
end

You would call the stored procedure from php like:-

$lat=56.564;
$lng=-2.5465;
$radius=5;

call `spNearestPlaces`($lat,$lng,$radius);