由于您需要在新的坑洞附近识别坑洞,以下代码可以以米为单位完成此操作。它在使用POD进行错误检查的MySQL查询中使用Haversine公式。
$lat和$lng是新坑洞的坐标,$radius是以米为单位的搜索半径。 6378160是赤道上地球的半径(以米为单位)。
为确保在此级别上的准确性,数据库中的坐标必须至少有4个小数位{{link2:请参见维基百科}}
编辑
try {
$stmt1 = $dbh->prepare("SELECT id, lat, lng, cnt, ( 6378160 * acos( cos( radians(?) ) * cos( radians( lat ) ) * cos( radians( lng ) - radians(?) ) + sin( radians(?) ) * sin( radians( lat ) ) ) ) AS distance FROM potholes HAVING distance < ? ORDER BY distance LIMIT 0,10");
$stmt1->bindParam(1,$lat);
$stmt1->bindParam(2,$lng);
$stmt1->bindParam(3,$lat);
$stmt1->bindParam(4,$radius);
$stmt1->setFetchMode(PDO::FETCH_ASSOC);
$stmt1->execute();
if ($stmt1->rowCount()>0) {
$row = $stmt1->fetch();
$id = $row['id'];
$lat1 = $row['lat'];
$lng1 = $row['lng'];
$cnt = $row['cnt'];
$meanLat = (($lat1*$cnt)+$lat)/($cnt+1);
$meanLng = (($lng1*$cnt)+$lng)/($cnt+1);
++$cnt;
$stmt2 = $dbh->prepare("UPDATE `potholes` SET `lat` = ?,`cnt` = ? WHERE `potholes`.`id` = ? LIMIT 1");
$stmt2->bindParam(1,$meanLat);
$stmt2->bindParam(2,$cnt);
$stmt2->bindParam(3,$id);
$stmt2->execute();
}else{
$stmt3 = $dbh->prepare("INSERT INTO `potholes` (`id`, `lat`, `lng`, `cnt`) VALUES (NULL, ?, ?, '1')");
$stmt3->bindParam(1,$lat);
$stmt3->bindParam(2,$lng);
$stmt3->execute();
}
echo json_encode($data);
}
catch(PDOException $e) {
echo "Error Message.". $e->getMessage() ;
file_put_contents('PDOErrors.txt',date('[Y-m-d H:i:s]').", data2c.php, ". $e->getMessage()."\r\n", FILE_APPEND);
}
$dbh = null;