Return Nearest Place available to Users location u

问题:

I want to implement Nearest Place available to User algorithm in Java or MySQL.

I have Stations table in MySQL database which has around 100K records of Stations with Latitude and Longitude. If User gives his latitude and longitude as x and y, then I want to return nearest stations available from User's location.

So please suggest me any algorithm available in Java or MySQL.

I tried with the following query, but it seems slower in performance -

SELECT *,3956*2*ASIN(SQRT(POWER(SIN((user_lat-abs(st.station_lat))*pi()/180/2 ), 2) + COS(user_lat*pi()/180)*COS(abs(st.station_lat) *pi()/180)*POWER(SIN((user_lon-
st.station_lon)*pi()/180/2 ),2))) AS distance FROM Stations st HAVING distance < 10 ORDER BY distance;

Thanks in advance.

回答1:

I use the Haversine formula in the following PHP PDO query. It pulls data from a table with 2.7K records and displays them on a MAP in less than 1 second with geocoding.. It defaults cleanly if searching outside range of database(Paris 25 miles).

Use 6357 in formula if kilometers are required instead of miles.

$stmt = $dbh->prepare("SELECT  name, lat, lng, ( 3959 * acos( cos( radians(?) ) * cos( radians( lat ) ) * cos( radians( lng ) - radians(?) ) + sin( radians(?) ) * sin( radians( lat ) ) ) ) AS distance FROM gbstn HAVING distance < ? ORDER BY distance LIMIT 0 , 20");
    // Assign parameters
    $stmt->bindParam(1,$center_lat);
    $stmt->bindParam(2,$center_lng);
    $stmt->bindParam(3,$center_lat);
    $stmt->bindParam(4,$radius);