Subversion Repositories eFlore/Projets.eflore-projets

Compare Revisions

Ignore whitespace Rev 1000 → Rev 1001

/trunk/scripts/modules/ifn/Ifn.php
New file
0,0 → 1,239
<?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');
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 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_02` b, ifn_flore f, ifn_ecologie e, ifn_placettes_foret fo".
" WHERE f.idp = e.idp".
" AND e.idp = fo.idp".
" AND b.cd_ref = 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);
}
}
?>