Subversion Repositories eFlore/Projets.eflore-projets

<?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");
                        }
                }
                
        }
        
        protected 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=#FFBD00')";
                $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);
        }
}
?>