将用户的位置存储在数据库中，并为他找到最近的用户 [英] Storing user's location in DB and find the nearest users for him

问题描述

我试图实现一个用户系统，其中一个系统需求是查找您附近的其他用户。

位置将按国家进行过滤，但不会由城市，所以用户可以从同一个国家，但来自不同城市的其他用户。

问题是如何计算城市之间的距离？它不一定是100％准确的，但它必须优先考虑更接近的用户。

Google地理位置API会满足这些要求吗？什么是替代方案？

任何想法如何实现它，而不是一个非常繁重的计算？

我想到询问用户它的国家和城市只是为了写在他的个人资料中，并从它的IP中获取它的地理位置，或者导入包含所有城市和他们的地理位置的数据库。

请指导我。

 <$ c $ （cos（弧度（？））* cos（弧度（lat））* cos（弧度（lng） - 弧度（弧度） ？））+ sin（弧度（？））* sin（弧度（lat））））AS距离距离mytable HAVING距离<？ORDER BY距离LIMIT 0,20）; 
 //赋值参数
 $ stmt-> bindParam（1，$ center_lat）; //用户的纬度坐标
 $ stmt-> bindParam（2，$ center_lng）; // lng用户坐标
 $ stmt-> bindParam（3，$ center_lat）; 
 $ stmt-> bindParam（4，$ radius）; //以英里为单位的半径
  

城市数据库可以在 Geonames

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.

解决方案

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

这篇关于将用户的位置存储在数据库中，并为他找到最近的用户的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！