Haversine计算:显示集合内的用户 [英] Haversine calculation: Show users within set
问题描述
- 前5名附近用户,根据主要用户的长/纬度而定,从最近到最远订购
- 仅限于主要用户500米以内的长/长
SQL数据库包含每隔5分钟更新一次的每个在线用户的长/经纬度。
但是我认为我可能在寻找错误的东西。任何指导都非常感谢。
以下SQL查询使用 余弦的球面法则 来计算坐标和表格中坐标之间的距离。 b
$ blockquote
d = acos(sin(lat1).sin(lat2)+ cos(lat1).cos(lat2).cos(lng2-lng1)).R
查询使用 SQL数学函数
require(dbinfo。 php); //数据库参数
& center_lat = primaryLat;
$ center_lng = primarylng;
$ radius = 0.5; // 500米
$ arr = array();
//连接数据库
$ dbh = new PDO(mysql:host = $ host; dbname = $ database,$ username,$ password);
$ dbh-> setAttribute(PDO :: ATTR_ERRMODE,PDO :: ERRMODE_EXCEPTION);
try {
//准备语句
$ stmt = $ dbh-> prepare(SELECT name,lat,lng,(3959 * acos(cos(radians(?))* cos (弧度(lat))* cos(弧度(lng) - 弧度(θ))+ sin(弧度(θ))* sin(弧度(lat))))AS距离距离gbstn HAVING距离<?ORDER BY distance LIMIT 0,5);
//赋值参数
$ stmt-> bindParam(1,$ center_lat);
$ stmt-> bindParam(2,$ center_lng);
$ stmt-> bindParam(3,$ center_lat);
$ stmt-> bindParam(4,$ radius);
//执行查询
$ stmt-> setFetchMode(PDO :: FETCH_OBJ);
$ stmt-> execute();
//显示结果
while($ obj = $ stmt-> fetch()){
$ arr [] = $ obj;
}
if(count($ arr)> = 1)
{
echo'{marker:'。json_encode($ arr)。'}';
} else {
echo'{marker:[{name:No Results,lat:'。$ center_lat。',lng:'。$ center_lng。' , 距离:0}]}';
}
}
catch(PDOException $ e){
echo对不起,我怕你可以'不要那样做。 $ e-> getMessage(); //在测试后删除或修改
file_put_contents('PDOErrors.txt',date('[Ymd H:i:s]')。,mapSelect.php。 $ e-> getMessage()。\r\\\
,FILE_APPEND);
}
//关闭连接
$ dbh = null;
?>
使用 PDO 而不是弃用 mysql_
函数。
您将需要修改该陈述以适应。调试完成后,还要删除catch块中的回显。
Building a carpooling app for my local community built on PHP and SQL. While I'm usually ofay with php coding, I'm stumped looking for the mathematical formula needed to list:
- Top 5 nearby users, ordered from closest to furthest given the long / lat of the primary user
- Limited to those within 500 meters of the primary users long / lat
The SQL database contains the long / lat of every online user that is updated at 5 minute intervals.
Have searched around, but think I may be looking for the wrong thing. Any guidance is greatly appreciated.
The following SQL query uses Spherical Law of Cosines to calculate the distance between a coordinate and coordinates in a table.
d = acos( sin(lat1).sin(lat2) + cos(lat1).cos(lat2).cos(lng2-lng1) ).R
The query uses SQL Math functions
require("dbinfo.php");//database parameters
¢er_lat = primaryLat;
$center_lng = primarylng;
$radius = 0.5;//500 meters
$arr = array();
//Connect to database
$dbh = new PDO("mysql:host=$host;dbname=$database", $username, $password);
$dbh->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
try {
// Prepare statement
$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 , 5");
// Assign parameters
$stmt->bindParam(1,$center_lat);
$stmt->bindParam(2,$center_lng);
$stmt->bindParam(3,$center_lat);
$stmt->bindParam(4,$radius);
//Execute query
$stmt->setFetchMode(PDO::FETCH_OBJ);
$stmt->execute();
//Show the results
while($obj = $stmt->fetch()) {
$arr[] = $obj;
}
if (count($arr) >= 1)
{
echo '{"marker":'.json_encode($arr).'}';
}else{
echo '{"marker":[{"name":"No Results","lat":'.$center_lat.',"lng":'.$center_lng.',"distance":0}]}';
}
}
catch(PDOException $e) {
echo "I'm sorry I'm afraid you can't do that.". $e->getMessage() ;// Remove or modify after testing
file_put_contents('PDOErrors.txt',date('[Y-m-d H:i:s]').", mapSelect.php, ". $e->getMessage()."\r\n", FILE_APPEND);
}
//Close the connection
$dbh = null;
?>
Using PDO instead of deprecated mysql_
functions.
You will require to modify the statement to suit . Also remove the echo in catch block after debugging.
这篇关于Haversine计算:显示集合内的用户的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!