| 1001 |
delphine |
1 |
<?php
|
|
|
2 |
/** Exemple lancement:
|
|
|
3 |
* /opt/lampp/bin/php -d memory_limit=3500M cli.php ifn -a chargerTous
|
|
|
4 |
* Options :
|
|
|
5 |
* -t : Permet de tester le script sur un jeux réduit de données (indiquer le nombre de lignes).
|
|
|
6 |
*/
|
|
|
7 |
class Ifn extends EfloreScript {
|
|
|
8 |
|
|
|
9 |
public function executer() {
|
|
|
10 |
// Lancement de l'action demandée
|
|
|
11 |
try {
|
|
|
12 |
$this->initialiserProjet('ifn');
|
|
|
13 |
|
|
|
14 |
$cmd = $this->getParametre('a');
|
|
|
15 |
switch ($cmd) {
|
|
|
16 |
case 'chargerTous' :
|
|
|
17 |
$this->chargerStructureSql();
|
|
|
18 |
$this->chargerDonnees("documentationFlore");
|
|
|
19 |
$this->chargerDonneesAnnuelles();
|
|
|
20 |
include_once dirname(__FILE__)."/bibliotheque/proj4php/proj4php.php";
|
|
|
21 |
$this->genererCoordonneesWgs('placettesForet');
|
|
|
22 |
$this->genererCoordonneesWgs('placettesPeupleraie');
|
| 1003 |
mathias |
23 |
$this->genererCodeInsee('placettesForet');
|
|
|
24 |
$this->genererCodeInsee('placettesPeupleraie');
|
|
|
25 |
$this->creerVueTapir();
|
|
|
26 |
$this->ajouterTupleEfloreOntologies();
|
| 1001 |
delphine |
27 |
break;
|
|
|
28 |
case 'chargerStructure' :
|
|
|
29 |
$this->chargerStructureSql();
|
|
|
30 |
break;
|
|
|
31 |
case 'chargerDonnees' :
|
|
|
32 |
$this->chargerDonnees("documentationFlore");
|
|
|
33 |
$this->chargerDonneesAnnuelles();
|
|
|
34 |
break;
|
|
|
35 |
case 'genererCoordWgs' :
|
|
|
36 |
include_once dirname(__FILE__)."/bibliotheque/proj4php/proj4php.php";
|
|
|
37 |
$this->genererCoordonneesWgs('placettesForet');
|
|
|
38 |
$this->genererCoordonneesWgs('placettesPeupleraie');
|
|
|
39 |
break;
|
|
|
40 |
case 'genererCodeInsee' :
|
|
|
41 |
$this->genererCodeInsee('placettesForet');
|
| 1003 |
mathias |
42 |
$this->genererCodeInsee('placettesPeupleraie');
|
| 1001 |
delphine |
43 |
break;
|
|
|
44 |
case 'creerVueTapir' :
|
|
|
45 |
$this->creerVueTapir();
|
|
|
46 |
$this->ajouterTupleEfloreOntologies();
|
|
|
47 |
break;
|
|
|
48 |
case 'supprimerTous' :
|
|
|
49 |
$this->supprimerTous();
|
|
|
50 |
break;
|
|
|
51 |
default :
|
|
|
52 |
throw new Exception("Erreur : la commande '$cmd' n'existe pas!");
|
|
|
53 |
}
|
|
|
54 |
} catch (Exception $e) {
|
|
|
55 |
$this->traiterErreur($e->getMessage());
|
|
|
56 |
}
|
|
|
57 |
}
|
|
|
58 |
|
|
|
59 |
|
|
|
60 |
private function chargerDonneesAnnuelles() {
|
|
|
61 |
$categories = explode(",",Config::get('categories'));
|
|
|
62 |
$annees = explode(",",Config::get('versions'));
|
|
|
63 |
foreach ($categories as $categorie) {
|
|
|
64 |
foreach ($annees as $annee) {
|
|
|
65 |
$this->chargerDonnees($categorie, $annee."/");
|
|
|
66 |
Debug::printr($categorie." ".$annee."\n");
|
|
|
67 |
}
|
|
|
68 |
}
|
|
|
69 |
|
|
|
70 |
}
|
|
|
71 |
|
|
|
72 |
private function chargerDonnees($categorie, $annee = '') {
|
|
|
73 |
$chemin = Config::get('dossierTsv').$annee.Config::get('fichiers.'.$categorie);
|
|
|
74 |
$table = Config::get('tables.'.$categorie);
|
|
|
75 |
$requete = "LOAD DATA INFILE '$chemin' ".
|
|
|
76 |
"REPLACE INTO TABLE $table ".
|
|
|
77 |
'CHARACTER SET utf8 '.
|
|
|
78 |
'FIELDS '.
|
|
|
79 |
" TERMINATED BY ';' ".
|
|
|
80 |
" ENCLOSED BY '' ".
|
|
|
81 |
" ESCAPED BY '\\\' ".
|
|
|
82 |
'IGNORE 1 LINES';
|
|
|
83 |
$this->getBdd()->requeter($requete);
|
|
|
84 |
}
|
|
|
85 |
|
|
|
86 |
private function genererNumNomBdtfx() {
|
|
|
87 |
$table = Config::get('tables.flore');
|
|
|
88 |
$placettes = $this->recupererTuplesPrChpCoordonnees($table);
|
|
|
89 |
$this->preparerTablePrCoordWgs($table);
|
|
|
90 |
foreach ($placettes as $placette) {
|
|
|
91 |
$placette = $this->transformerCoordL93enWgs84($placette);
|
|
|
92 |
$this->remplirChpCoordonnees($table, $placette);
|
|
|
93 |
}
|
|
|
94 |
}
|
|
|
95 |
|
|
|
96 |
private function creerVueTapir() {
|
| 1006 |
delphine |
97 |
$requete = "DROP VIEW IF EXISTS ifn_tapir; CREATE VIEW ifn_tapir AS ".
|
| 1001 |
delphine |
98 |
"SELECT f.idp as observation_id, b.nom_sci as nom_scientifique_complet, b.num_nom, ".
|
|
|
99 |
" fo.lieu_commune_code_insee, fo.lieu_station_latitude, fo.lieu_station_longitude,".
|
|
|
100 |
" 'WGS84' AS geodeticDatum, e.dateeco as observation_date, '' AS observateur_nom_complet".
|
| 1018 |
delphine |
101 |
" FROM `bdtfx_v2_01` b, ifn_flore f, ifn_ecologie e, ifn_placettes_foret fo".
|
| 1001 |
delphine |
102 |
" WHERE f.idp = e.idp".
|
|
|
103 |
" AND e.idp = fo.idp".
|
| 1018 |
delphine |
104 |
" AND b.cd_nom = f.cd_ref";
|
| 1001 |
delphine |
105 |
$this->getBdd()->requeter($requete);
|
|
|
106 |
}
|
|
|
107 |
|
|
|
108 |
private function ajouterTupleEfloreOntologies(){ // pour la légende
|
|
|
109 |
$requete = "INSERT INTO `eflore_ontologies`(id,`classe_id`, `nom`, `description`, `code`, `complements`) VALUES ".
|
|
|
110 |
"(24,10,'IFN',".
|
|
|
111 |
"'données issues des données brutes mises en ligne de l\'Inventaire Forestier National',".
|
|
|
112 |
"'ifn','legende=#F2B148')";
|
|
|
113 |
$this->getBdd()->requeter($requete);
|
|
|
114 |
}
|
|
|
115 |
|
|
|
116 |
private function genererCoordonneesWgs($categorie) {
|
|
|
117 |
$table = Config::get('tables.'.$categorie);
|
|
|
118 |
$placettes = $this->recupererTuplesPrChpCoordonnees($table);
|
|
|
119 |
$this->preparerTablePrCoordWgs($table);
|
|
|
120 |
foreach ($placettes as $placette) {
|
|
|
121 |
$placette = $this->transformerCoordL93enWgs84($placette);
|
|
|
122 |
$this->remplirChpCoordonnees($table, $placette);
|
|
|
123 |
}
|
|
|
124 |
}
|
|
|
125 |
|
|
|
126 |
private function transformerCoordL93enWgs84($coord) {
|
|
|
127 |
$proj4 = new Proj4php();
|
|
|
128 |
$projL93 = new Proj4phpProj('EPSG:2154',$proj4);
|
|
|
129 |
$projWGS84 = new Proj4phpProj('EPSG:4326',$proj4);
|
|
|
130 |
$projLI = new Proj4phpProj('EPSG:27571',$proj4);
|
|
|
131 |
$projLSud = new Proj4phpProj('EPSG:27563',$proj4);
|
|
|
132 |
$projL72 = new Proj4phpProj('EPSG:31370',$proj4);
|
|
|
133 |
|
|
|
134 |
$pointSrc = new proj4phpPoint($coord['xl93'],$coord['yl93']);
|
|
|
135 |
$pointDest = $proj4->transform($projL93,$projWGS84,$pointSrc);
|
|
|
136 |
$coord['longitude_wgs'] = '"'.number_format($pointDest->x, 6).'"';
|
|
|
137 |
$coord['latitude_wgs'] = '"'.number_format($pointDest->y, 6).'"';
|
|
|
138 |
return $coord;
|
|
|
139 |
}
|
|
|
140 |
|
|
|
141 |
private function recupererTuplesPrChpCoordonnees($table) {
|
|
|
142 |
$requete = 'SELECT idp, xl93, yl93 '.
|
|
|
143 |
"FROM {$table} ";
|
|
|
144 |
$resultat = $this->getBdd()->recupererTous($requete);
|
|
|
145 |
return $resultat;
|
|
|
146 |
}
|
|
|
147 |
|
|
|
148 |
private function remplirChpCoordonnees($table, $placette) {
|
|
|
149 |
$requete = "UPDATE {$table} ".
|
|
|
150 |
" SET lieu_station_longitude = {$placette['longitude_wgs']} , lieu_station_latitude = {$placette['latitude_wgs']} ".
|
|
|
151 |
" WHERE idp = {$placette['idp']} ";
|
|
|
152 |
$resultat = $this->getBdd()->requeter($requete);
|
|
|
153 |
if ($resultat === false) {
|
|
|
154 |
throw new Exception("Erreur d'insertion pour le tuple $idp");
|
|
|
155 |
}
|
|
|
156 |
}
|
|
|
157 |
|
|
|
158 |
private function preparerTablePrCoordWgs($table) {
|
|
|
159 |
$requete = "SHOW COLUMNS FROM {$table} LIKE 'lieu_station_latitude' ";
|
|
|
160 |
$resultat = $this->getBdd()->recuperer($requete);
|
|
|
161 |
if ($resultat === false) {
|
|
|
162 |
$requete = "ALTER TABLE {$table} ".
|
|
|
163 |
' ADD `lieu_station_latitude` DECIMAL( 9, 6 ),'.
|
|
|
164 |
' ADD `lieu_station_longitude` DECIMAL( 9, 6 )';
|
|
|
165 |
$this->getBdd()->requeter($requete);
|
|
|
166 |
}
|
|
|
167 |
}
|
|
|
168 |
|
|
|
169 |
private function genererCodeInsee($categorie) {
|
|
|
170 |
$table = Config::get('tables.'.$categorie);
|
|
|
171 |
$this->preparerTablePrChpCodeInsee($table);
|
|
|
172 |
$liste_coordonnees = $this->recupererTuplesPrChpCodeInsee($table);
|
|
|
173 |
foreach ($liste_coordonnees as $coordonnees) {
|
|
|
174 |
$code_insee = $this->chercherCodeCommune($coordonnees['latitude'], $coordonnees['longitude']);
|
|
|
175 |
if ($code_insee != "") {
|
|
|
176 |
$this->remplirChpCodeInsee($table, $coordonnees['latitude'], $coordonnees['longitude'], $code_insee);
|
|
|
177 |
}
|
|
|
178 |
}
|
|
|
179 |
}
|
|
|
180 |
private function preparerTablePrChpCodeInsee($table) {
|
|
|
181 |
$requete = "SHOW COLUMNS FROM {$table} LIKE 'lieu_commune_code_insee' ";
|
|
|
182 |
$resultat = $this->getBdd()->recuperer($requete);
|
|
|
183 |
if ($resultat === false) {
|
|
|
184 |
$requete = "ALTER TABLE {$table} ".
|
|
|
185 |
' ADD `lieu_commune_code_insee` VARCHAR(5);';
|
|
|
186 |
$this->getBdd()->requeter($requete);
|
|
|
187 |
}
|
|
|
188 |
}
|
|
|
189 |
private function recupererTuplesPrChpCodeInsee($table) {
|
|
|
190 |
$requete = 'SELECT distinct lieu_station_latitude as latitude, lieu_station_longitude as longitude '.
|
|
|
191 |
"FROM {$table} WHERE lieu_commune_code_insee IS NULL ";
|
|
|
192 |
$resultat = $this->getBdd()->recupererTous($requete);
|
|
|
193 |
return $resultat;
|
|
|
194 |
}
|
|
|
195 |
private function remplirChpCodeInsee($table, $latitude, $longitude, $code_insee) {
|
|
|
196 |
$requete = "UPDATE {$table} ".
|
|
|
197 |
" SET lieu_commune_code_insee = '{$code_insee}'".
|
|
|
198 |
" WHERE lieu_station_longitude = {$longitude} AND lieu_station_latitude = {$latitude} ";
|
|
|
199 |
$resultat = $this->getBdd()->requeter($requete);
|
|
|
200 |
if ($resultat === false) {
|
|
|
201 |
throw new Exception("Erreur d'insertion pour le tuple $idp");
|
|
|
202 |
}
|
|
|
203 |
}
|
|
|
204 |
|
|
|
205 |
private function chercherCodeCommune($latitude, $longitude) {
|
|
|
206 |
$code_insee = '';
|
|
|
207 |
if ($this->testerCoordonneesWgsFrance($latitude, $longitude)) {
|
|
|
208 |
$url_service = "api.tela-botanica.org/service:eflore:0.1/osm/nom-commune".
|
|
|
209 |
"?lat={$latitude}&lon={$longitude}";
|
|
|
210 |
$url_service = str_replace(',', '.', $url_service);
|
|
|
211 |
$ch = curl_init($url_service);
|
|
|
212 |
curl_setopt($ch, CURLOPT_RETURNTRANSFER, true);
|
|
|
213 |
$reponse = curl_exec($ch);
|
|
|
214 |
$reponse = json_decode($reponse);
|
|
|
215 |
if (isset($reponse->codeINSEE)) {
|
|
|
216 |
$code_insee = $reponse->codeINSEE;
|
|
|
217 |
}
|
|
|
218 |
curl_close($ch);
|
|
|
219 |
}
|
|
|
220 |
return $code_insee;
|
|
|
221 |
}
|
|
|
222 |
|
|
|
223 |
private function testerCoordonneesWgsFrance($latitude, $longitude) {
|
|
|
224 |
$coord_france = false;
|
|
|
225 |
if ($latitude != '' && $longitude != '') {
|
|
|
226 |
if ($latitude < 51.071667 && $latitude > 41.316667) {
|
|
|
227 |
if ($longitude < 9.513333 && $longitude > -5.140278) {
|
|
|
228 |
$coord_france = true;
|
|
|
229 |
}
|
|
|
230 |
}
|
|
|
231 |
}
|
|
|
232 |
return $coord_france;
|
|
|
233 |
}
|
|
|
234 |
|
|
|
235 |
private function supprimerTous() {
|
|
|
236 |
$requete = "DROP TABLE IF EXISTS `ifn_arbres_forets`, `ifn_arbres_peupleraie`, `ifn_couverts_foret`,
|
|
|
237 |
`ifn_documentation`, `ifn_documentation_flore`, `ifn_ecologie`, `ifn_flore`, `ifn_placettes_foret`,
|
|
|
238 |
`ifn_placettes_peupleraie`;
|
|
|
239 |
";
|
|
|
240 |
$this->getBdd()->requeter($requete);
|
|
|
241 |
}
|
|
|
242 |
}
|
|
|
243 |
?>
|