用php找中位数,PHP-选择不同的经度和纬度值并找到它们的中位数

当您需要确定新洞附近的坑洞时,以下代码可以以米为单位执行此操作.它在使用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;