选择不同的经度和纬度值并找到它们的中位数

Question

I am creating a Phonegap application using Google Maps API V3 and jQuery.

The application stores the longitude and latitude values separately in a MySQL database when a pothole is detected. What I need to do is select any values which are relatively close and would most probably be the same pothole.

Is there any way in jQuery/PHP/SQL to get values which are relatively close, find their mean-point and then use the value to continue processing some other things?

Basically what I would need is that once a particular pothole is detected 5 times, it would be charted on a Google Map. However only once this threshold is met. The difficulty is that the same pothole might be reported under slightly different longitude and latitude values, depending on the accuracy of the device reporting it.

Answer 1

As you require to identify potholes near the new one the following code can do this in metres. It uses the Haversine Formula in a MySQL query using POD with error checking.

$lat & $lng are the coordinates of the new pothole & $radius is the search radius in metres. 6378160 is the radius of the earth in metres at equator.

To ensure accuracy at this level the coordinates in the database must have at least 4 decimal places See Wiki

EDIT

    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()."
", FILE_APPEND);  
 }
//Close the connection
$dbh = null;