Subversion Repositories eFlore/Projets.eflore-projets

Rev

Rev 1001 | Go to most recent revision | Details | Compare with Previous | Last modification | View Log | RSS feed

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