Subversion Repositories eFlore/Projets.eflore-projets

Rev

Rev 1138 | 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');
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
 
1202 delphine 72
	protected function chargerDonnees($categorie, $annee = '') {
1001 delphine 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".
1138 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',".
1202 delphine 112
				"'ifn','legende=#FFBD00')";
1001 delphine 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
?>