当您需要确定新洞附近的坑洞时,以下代码可以以米为单位执行此操作.它在使用POD进行错误检查的MySQL查询中使用
Haversine Formula.
$lat& $lng是新坑坑的坐标$radius是以米为单位的搜索半径. 6378160是赤道处以米为单位的地球半径.
为了确保该级别的准确性,数据库中的坐标必须至少具有4个小数位See Wiki
编辑
try {
// Prepare search statement
$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");
// Assign parameters
$stmt1->bindParam(1,$lat);
$stmt1->bindParam(2,$lng);
$stmt1->bindParam(3,$lat);
$stmt1->bindParam(4,$radius);
//Execute query
$stmt1->setFetchMode(PDO::FETCH_ASSOC);
$stmt1->execute();
if ($stmt1->rowCount()>0) {//Existing pothole
// fetch row
$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;
// Prepare UPDATE statement existing pothole
$stmt2 = $dbh->prepare("UPDATE `potholes` SET `lat` = ?,`cnt` = ? WHERE `potholes`.`id` = ? LIMIT 1");
// Assign parameters
$stmt2->bindParam(1,$meanLat);
$stmt2->bindParam(2,$cnt);
$stmt2->bindParam(3,$id);
$stmt2->execute();
// }
}else{//New pothole
// Prepare INSERT statement new pothole
$stmt3 = $dbh->prepare("INSERT INTO `potholes` (`id`, `lat`, `lng`, `cnt`) VALUES (NULL, ?, ?, '1')");
// Assign parameters
$stmt3->bindParam(1,$lat);
$stmt3->bindParam(2,$lng);
$stmt3->execute();
}
echo json_encode($data);//Echo to calling page if required
}
catch(PDOException $e) {
echo "Error Message.". $e->getMessage() ;// Remove or modify after testing
file_put_contents('PDOErrors.txt',date('[Y-m-d H:i:s]').", data2c.php, ". $e->getMessage()."\r\n", FILE_APPEND);
}
//Close the connection
$dbh = null;