| 29,36 → 29,34 |
|---|
| |
| public function obtenirPointsPourCentreEtRadius($lat_centre, $lon_centre, $radius) { |
| $requete = "SELECT ". |
| "id_observation, nom_sel, ( ". |
| "6371 * acos ( ". |
| "cos ( radians(".$lat_centre.") ) ". |
| "* cos( radians( latitude ) ) ". |
| "* cos( radians( longitude ) - radians(".$lon_centre.") ) ". |
| "+ sin ( radians(".$lat_centre.") ) ". |
| "* sin( radians( latitude ) ) ". |
| ") ". |
| ") AS distance, latitude, longitude, COUNT(id_observation) as nb_obs ". |
| "id_observation, nom_sel, latitude, longitude, COUNT(id_observation) as nb_obs ". |
| "FROM cel_obs ". |
| "WHERE latitude != 0 AND longitude != 0 ". |
| "GROUP BY latitude, longitude ". |
| "HAVING distance < ".$radius." ". |
| "ORDER BY distance "; |
| "AND ".$this->renvoyerDistanceSql($lat_centre, $lon_centre)." < ".$radius." ". |
| "GROUP BY latitude, longitude "; |
| |
| $points = Cel::db()->requeter($requete); |
| return $points; |
| } |
| |
| private function renvoyerDistanceSql($lat_centre, $lon_centre) { |
| $sous_requete = |
| "( ". |
| "6371 * acos ( ". |
| "cos ( radians(".$lat_centre.") ) ". |
| "* cos( radians( latitude ) ) ". |
| "* cos( radians( longitude ) - radians(".$lon_centre.") ) ". |
| "+ sin ( radians(".$lat_centre.") ) ". |
| "* sin( radians( latitude ) ) ". |
| ") ". |
| ") "; |
| return $sous_requete; |
| } |
| |
| public function obtenirPointPlusProche($lat_centre, $lon_centre) { |
| $requete = "SELECT ". |
| "id_observation, nom_sel, ( ". |
| "6371 * acos ( ". |
| "cos ( radians(".$lat_centre.") ) ". |
| "* cos( radians( latitude ) ) ". |
| "* cos( radians( longitude ) - radians(".$lon_centre.") ) ". |
| "+ sin ( radians(".$lat_centre.") ) ". |
| "* sin( radians( latitude ) ) ". |
| ") ". |
| ") AS distance, latitude, longitude, COUNT(id_observation) as nb_obs ". |
| "id_observation, nom_sel, ".$this->renvoyerDistanceSql($lat_centre, $lon_centre)." AS distance, ". |
| "latitude, longitude, COUNT(id_observation) as nb_obs ". |
| "FROM cel_obs ". |
| "WHERE latitude != 0 AND longitude != 0 ". |
| "GROUP BY latitude, longitude ". |