Storing user's location in DB and find the nea

问题:

I am trying to implement a user system, one of the system requirements is find other users that are near you.

Location will be filtered by countries but not by cities, so users can get other users from the same country but from different city.

The question is how to calculate distances between cities? It doesn't have to be 100% accurate but it have to give some priority for closer users.

Will Google Geo location API will satisfy those requirements? What are the alternatives?

Any idea how to implement it without a very heavy calculation?

I thought of asking the user it's country and city just for writing in his profile, and get it's geo location from it's IP OR import a database which contains all cities and their geo location.

Please direct me.

回答1:

You can use the Haversine formula to search for nearest user in a database.

$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 mytable HAVING distance < ? ORDER BY distance LIMIT 0 , 20");
    // Assign parameters
    $stmt->bindParam(1,$center_lat);//lat coordinate of user
    $stmt->bindParam(2,$center_lng);//lng coordinate of user
    $stmt->bindParam(3,$center_lat);
    $stmt->bindParam(4,$radius);//Radius in miles 

A database of cities can be found at Geonames