New file |
0,0 → 1,243 |
<?php |
/** Exemple lancement: |
* /opt/lampp/bin/php -d memory_limit=3500M cli.php ifn -a chargerTous |
* Options : |
* -t : Permet de tester le script sur un jeux réduit de données (indiquer le nombre de lignes). |
*/ |
class Ifn extends EfloreScript { |
|
public function executer() { |
// Lancement de l'action demandée |
try { |
$this->initialiserProjet('ifn'); |
|
$cmd = $this->getParametre('a'); |
switch ($cmd) { |
case 'chargerTous' : |
$this->chargerStructureSql(); |
$this->chargerDonnees("documentationFlore"); |
$this->chargerDonneesAnnuelles(); |
include_once dirname(__FILE__)."/bibliotheque/proj4php/proj4php.php"; |
$this->genererCoordonneesWgs('placettesForet'); |
$this->genererCoordonneesWgs('placettesPeupleraie'); |
$this->genererCodeInsee('placettesForet'); |
$this->genererCodeInsee('placettesPeupleraie'); |
$this->creerVueTapir(); |
$this->ajouterTupleEfloreOntologies(); |
break; |
case 'chargerStructure' : |
$this->chargerStructureSql(); |
break; |
case 'chargerDonnees' : |
$this->chargerDonnees("documentationFlore"); |
$this->chargerDonneesAnnuelles(); |
break; |
case 'genererCoordWgs' : |
include_once dirname(__FILE__)."/bibliotheque/proj4php/proj4php.php"; |
$this->genererCoordonneesWgs('placettesForet'); |
$this->genererCoordonneesWgs('placettesPeupleraie'); |
break; |
case 'genererCodeInsee' : |
$this->genererCodeInsee('placettesForet'); |
$this->genererCodeInsee('placettesPeupleraie'); |
break; |
case 'creerVueTapir' : |
$this->creerVueTapir(); |
$this->ajouterTupleEfloreOntologies(); |
break; |
case 'supprimerTous' : |
$this->supprimerTous(); |
break; |
default : |
throw new Exception("Erreur : la commande '$cmd' n'existe pas!"); |
} |
} catch (Exception $e) { |
$this->traiterErreur($e->getMessage()); |
} |
} |
|
|
private function chargerDonneesAnnuelles() { |
$categories = explode(",",Config::get('categories')); |
$annees = explode(",",Config::get('versions')); |
foreach ($categories as $categorie) { |
foreach ($annees as $annee) { |
$this->chargerDonnees($categorie, $annee."/"); |
Debug::printr($categorie." ".$annee."\n"); |
} |
} |
|
} |
|
private function chargerDonnees($categorie, $annee = '') { |
$chemin = Config::get('dossierTsv').$annee.Config::get('fichiers.'.$categorie); |
$table = Config::get('tables.'.$categorie); |
$requete = "LOAD DATA INFILE '$chemin' ". |
"REPLACE INTO TABLE $table ". |
'CHARACTER SET utf8 '. |
'FIELDS '. |
" TERMINATED BY ';' ". |
" ENCLOSED BY '' ". |
" ESCAPED BY '\\\' ". |
'IGNORE 1 LINES'; |
$this->getBdd()->requeter($requete); |
} |
|
private function genererNumNomBdtfx() { |
$table = Config::get('tables.flore'); |
$placettes = $this->recupererTuplesPrChpCoordonnees($table); |
$this->preparerTablePrCoordWgs($table); |
foreach ($placettes as $placette) { |
$placette = $this->transformerCoordL93enWgs84($placette); |
$this->remplirChpCoordonnees($table, $placette); |
} |
} |
|
private function creerVueTapir() { |
$requete = "DROP VIEW IF EXISTS ifn_tapir; CREATE VIEW ifn_tapir AS ". |
"SELECT f.idp as observation_id, b.nom_sci as nom_scientifique_complet, b.num_nom, ". |
" fo.lieu_commune_code_insee, fo.lieu_station_latitude, fo.lieu_station_longitude,". |
" 'WGS84' AS geodeticDatum, e.dateeco as observation_date, '' AS observateur_nom_complet". |
" FROM `bdtfx_v2_01` b, ifn_flore f, ifn_ecologie e, ifn_placettes_foret fo". |
" WHERE f.idp = e.idp". |
" AND e.idp = fo.idp". |
" AND b.cd_nom = f.cd_ref;"; |
$this->getBdd()->requeter($requete); |
} |
|
private function ajouterTupleEfloreOntologies(){ // pour la légende |
$requete = "INSERT INTO `eflore_ontologies`(id,`classe_id`, `nom`, `description`, `code`, `complements`) VALUES ". |
"(24,10,'IFN',". |
"'données issues des données brutes mises en ligne de l\'Inventaire Forestier National',". |
"'ifn','legende=#F2B148')"; |
$this->getBdd()->requeter($requete); |
} |
|
private function genererCoordonneesWgs($categorie) { |
$table = Config::get('tables.'.$categorie); |
$placettes = $this->recupererTuplesPrChpCoordonnees($table); |
$this->preparerTablePrCoordWgs($table); |
foreach ($placettes as $placette) { |
$placette = $this->transformerCoordL93enWgs84($placette); |
$this->remplirChpCoordonnees($table, $placette); |
} |
} |
|
private function transformerCoordL93enWgs84($coord) { |
$proj4 = new Proj4php(); |
$projL93 = new Proj4phpProj('EPSG:2154',$proj4); |
$projWGS84 = new Proj4phpProj('EPSG:4326',$proj4); |
$projLI = new Proj4phpProj('EPSG:27571',$proj4); |
$projLSud = new Proj4phpProj('EPSG:27563',$proj4); |
$projL72 = new Proj4phpProj('EPSG:31370',$proj4); |
|
$pointSrc = new proj4phpPoint($coord['xl93'],$coord['yl93']); |
$pointDest = $proj4->transform($projL93,$projWGS84,$pointSrc); |
$coord['longitude_wgs'] = '"'.number_format($pointDest->x, 6).'"'; |
$coord['latitude_wgs'] = '"'.number_format($pointDest->y, 6).'"'; |
return $coord; |
} |
|
private function recupererTuplesPrChpCoordonnees($table) { |
$requete = 'SELECT idp, xl93, yl93 '. |
"FROM {$table} "; |
$resultat = $this->getBdd()->recupererTous($requete); |
return $resultat; |
} |
|
private function remplirChpCoordonnees($table, $placette) { |
$requete = "UPDATE {$table} ". |
" SET lieu_station_longitude = {$placette['longitude_wgs']} , lieu_station_latitude = {$placette['latitude_wgs']} ". |
" WHERE idp = {$placette['idp']} "; |
$resultat = $this->getBdd()->requeter($requete); |
if ($resultat === false) { |
throw new Exception("Erreur d'insertion pour le tuple $idp"); |
} |
} |
|
private function preparerTablePrCoordWgs($table) { |
$requete = "SHOW COLUMNS FROM {$table} LIKE 'lieu_station_latitude' "; |
$resultat = $this->getBdd()->recuperer($requete); |
if ($resultat === false) { |
$requete = "ALTER TABLE {$table} ". |
' ADD `lieu_station_latitude` DECIMAL( 9, 6 ),'. |
' ADD `lieu_station_longitude` DECIMAL( 9, 6 )'; |
$this->getBdd()->requeter($requete); |
} |
} |
|
private function genererCodeInsee($categorie) { |
$table = Config::get('tables.'.$categorie); |
$this->preparerTablePrChpCodeInsee($table); |
$liste_coordonnees = $this->recupererTuplesPrChpCodeInsee($table); |
foreach ($liste_coordonnees as $coordonnees) { |
$code_insee = $this->chercherCodeCommune($coordonnees['latitude'], $coordonnees['longitude']); |
if ($code_insee != "") { |
$this->remplirChpCodeInsee($table, $coordonnees['latitude'], $coordonnees['longitude'], $code_insee); |
} |
} |
} |
private function preparerTablePrChpCodeInsee($table) { |
$requete = "SHOW COLUMNS FROM {$table} LIKE 'lieu_commune_code_insee' "; |
$resultat = $this->getBdd()->recuperer($requete); |
if ($resultat === false) { |
$requete = "ALTER TABLE {$table} ". |
' ADD `lieu_commune_code_insee` VARCHAR(5);'; |
$this->getBdd()->requeter($requete); |
} |
} |
private function recupererTuplesPrChpCodeInsee($table) { |
$requete = 'SELECT distinct lieu_station_latitude as latitude, lieu_station_longitude as longitude '. |
"FROM {$table} WHERE lieu_commune_code_insee IS NULL "; |
$resultat = $this->getBdd()->recupererTous($requete); |
return $resultat; |
} |
private function remplirChpCodeInsee($table, $latitude, $longitude, $code_insee) { |
$requete = "UPDATE {$table} ". |
" SET lieu_commune_code_insee = '{$code_insee}'". |
" WHERE lieu_station_longitude = {$longitude} AND lieu_station_latitude = {$latitude} "; |
$resultat = $this->getBdd()->requeter($requete); |
if ($resultat === false) { |
throw new Exception("Erreur d'insertion pour le tuple $idp"); |
} |
} |
|
private function chercherCodeCommune($latitude, $longitude) { |
$code_insee = ''; |
if ($this->testerCoordonneesWgsFrance($latitude, $longitude)) { |
$url_service = "api.tela-botanica.org/service:eflore:0.1/osm/nom-commune". |
"?lat={$latitude}&lon={$longitude}"; |
$url_service = str_replace(',', '.', $url_service); |
$ch = curl_init($url_service); |
curl_setopt($ch, CURLOPT_RETURNTRANSFER, true); |
$reponse = curl_exec($ch); |
$reponse = json_decode($reponse); |
if (isset($reponse->codeINSEE)) { |
$code_insee = $reponse->codeINSEE; |
} |
curl_close($ch); |
} |
return $code_insee; |
} |
|
private function testerCoordonneesWgsFrance($latitude, $longitude) { |
$coord_france = false; |
if ($latitude != '' && $longitude != '') { |
if ($latitude < 51.071667 && $latitude > 41.316667) { |
if ($longitude < 9.513333 && $longitude > -5.140278) { |
$coord_france = true; |
} |
} |
} |
return $coord_france; |
} |
|
private function supprimerTous() { |
$requete = "DROP TABLE IF EXISTS `ifn_arbres_forets`, `ifn_arbres_peupleraie`, `ifn_couverts_foret`, |
`ifn_documentation`, `ifn_documentation_flore`, `ifn_ecologie`, `ifn_flore`, `ifn_placettes_foret`, |
`ifn_placettes_peupleraie`; |
"; |
$this->getBdd()->requeter($requete); |
} |
} |
?> |