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

问题描述

我正在尝试实现一个用户系统，系统要求之一是找到您附近的其他用户.

位置将按国家/地区过滤，而不是按城市过滤，因此用户可以找到来自同一国家但来自不同城市的其他用户.

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

Google 地理位置 API 会满足这些要求吗?有哪些替代方案?

知道如何在不进行大量计算的情况下实现它吗?

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

请指导我.

解决方案

你可以使用 Haversine 公式在数据库中搜索最近的用户.

$stmt = $dbh->prepare("SELECT name, lat, lng, ( 3959 * acos( cos( radians(?) ) * cos( radians( lat ) ) * cos( radians( lng) - 弧度(?) ) + sin( 弧度(?) ) * sin( 弧度( lat ) ) ) AS 距离从mytable HAVING 距离< ? ORDER BY distance LIMIT 0 , 20");//赋值参数$stmt->bindParam(1,$center_lat);//用户的纬度坐标$stmt->bindParam(2,$center_lng);//用户lng坐标$stmt->bindParam(3,$center_lat);$stmt->bindParam(4,$radius);//以英里为单位的半径

可以在 地名上找到城市数据库/p>

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屋！