WebSVN – eFlore/Projets.eflore-projets – Diff – /trunk/scripts/modules/ifn/Ifn.php


<?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");
			}
		}
		
	}
	
	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 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_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);
	}
}
?>

Subversion Repositories eFlore/Projets.eflore-projets

(root)/trunk/scripts/modules/ifn/Ifn.php – Rev 1003 → 1006

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