WebSVN – eFlore/Projets.eflore-projets – Diff – /trunk/scripts/modules/osm/ZonesAdmin.php


<?php
/**
 * Création de la table osm_communes.
 * /opt/lampp/bin/php cli.php osm -a communes -m manuel
 *
 */
class ZonesAdmin {
 
	private $conteneur;
	private $bdd;
	private $messages;
	private $mode;
 
	private $bddOsmNom;
 
	public function __construct($conteneur) {
		$this->conteneur = $conteneur;
		$this->bdd = $this->conteneur->getBdd();
		$this->bddOsmNom = $this->conteneur->getParametre('bdd_osm_nom');
		$this->messages = $this->conteneur->getMessages();
		$this->mode = $this->conteneur->getParametre('m');
	}
 
	public function executer() {
		// Lancement de l'action demandée
		$cmd = $this->conteneur->getParametre('a');
		switch ($cmd) {
			case 'za' :
				$this->supprimerTableOsmZonesAdmin();
				$this->conteneur->getEfloreCommun()->chargerStructureSql();
				$this->creerTableOsmZonesAdmin();
				$this->corrigerTableOsmZonesAdmin();
				$this->mettreAJourIdZoneGeo();
				break;
			case 'zaCreer' :
				$this->creerTableOsmZonesAdmin();
				break;
			case 'zaCorriger' :
				$this->corrigerTableOsmZonesAdmin();
				break;
			case 'zaMajIdZG' :
				$this->mettreAJourIdZoneGeo();
				break;
			case 'zaSupprimer' :
				$this->supprimerTableOsmZonesAdmin();
				break;
			default :
				$this->messages->traiterErreur('Erreur : la commande "%s" n\'existe pas!', array($cmd));
		}
 
	}
 
	private function supprimerTableOsmZonesAdmin() {
		$requete = 'DROP TABLE IF EXISTS osm_zones_admin '.
			' -- '.__FILE__.' : '.__LINE__;
		$this->bdd->requeter($requete);
	}
 
	private function creerTableOsmZonesAdmin() {
		$requete = 'INSERT INTO osm_zones_admin '.
			"SELECT if(ref_insee != '' AND admin_level = 8, CONCAT('INSEE-C:',ref_insee), ".
			"if(ref_insee != '' AND admin_level = 6, CONCAT('INSEE-D:',ref_insee), ".
			"if(ref_insee != '' AND admin_level = 4, CONCAT('INSEE-R:',ref_insee), ".
			"if(iso3166_2 != '', CONCAT('ISO-3166-2:',iso3166_2), ".
			"if(iso3166_1 != '', CONCAT('ISO-3166-1:',iso3166_1), ".
			"CONCAT('OSM-ID:',osm_id)))))), ".
			'osm_id, osm_version, osm_timestamp, '.
			"if(name_fr != '', name_fr, name), ".
			'shape, shape_centroid, Y(shape_centroid), X(shape_centroid), '.
			'admin_level, iso3166_1, iso3166_2, ref_insee, ref_nuts, '.
			'name, name_fr, name_en, name_es, wikipedia '.
			"FROM {$this->bddOsmNom}.multipolygons_ref ".
			' -- '.__FILE__.' : '.__LINE__;
		$this->bdd->requeter($requete);
	}
 
	private function corrigerTableOsmZonesAdmin() {
		$requete = 'SELECT osm_id, other_tags '.
			"FROM {$this->bddOsmNom}.multipolygons_ref ".
			"WHERE iso3166_2 IS NULL AND other_tags LIKE '%\"iso3166-2\"%' ".
			' -- '.__FILE__.' : '.__LINE__;
		$resultat = $this->bdd->recupererTous($requete);
		if ($resultat) {
			foreach ($resultat as $infos) {
				$tags = $this->getTags($infos['other_tags']);
				$iso_3166_2 = $this->bdd->proteger($tags['iso3166-2']);
				$osm_id = $this->bdd->proteger($infos['osm_id']);
 
				$requete = 'UPDATE osm_zones_admin '.
					"SET code_iso_3166_2 = $iso_3166_2 ".
					"WHERE osm_id = $osm_id ".
					' -- '.__FILE__.' : '.__LINE__;
				$this->bdd->requeter($requete);
			}
		}
	}
 
	private function getTags($tagsInfos) {
		$tags = array();
		$clesValeurs = explode(',', $tagsInfos);
		foreach ($clesValeurs as $cleValeur) {
			list($cle, $val) = explode('=>', $cleValeur);
			$tags[trim($cle, '"')] = trim($val, '"');
		}
		return $tags;
	}
 
	private function mettreAJourIdZoneGeo() {
		$requete = 'UPDATE osm_zones_admin '.
			"SET id_zone_geo = if(code_insee != '' AND niveau = 8, CONCAT('INSEE-C:',code_insee), ".
			"if(code_insee != '' AND niveau = 6, CONCAT('INSEE-D:',code_insee), ".
			"if(code_insee != '' AND niveau = 4, CONCAT('INSEE-R:',code_insee), ".
			"if(code_iso_3166_2 != '', CONCAT('ISO-3166-2:',code_iso_3166_2), ".
			"if(code_iso_3166_1 != '', CONCAT('ISO-3166-1:',code_iso_3166_1), ".
			"CONCAT('OSM-ID:',osm_id)))))) ".
			' -- '.__FILE__.' : '.__LINE__;
		$this->bdd->requeter($requete);
	}
}

Subversion Repositories eFlore/Projets.eflore-projets

(root)/trunk/scripts/modules/osm/ZonesAdmin.php – Rev 1056 → 1060

Rev 1056		Rev 1060
1	`<?php`	1	`<?php`
2	`/**`	2	`/**`
3	`* Création de la table osm_communes.`	3	`* Création de la table osm_communes.`
4	`* /opt/lampp/bin/php cli.php osm -a communes -m manuel`	4	`* /opt/lampp/bin/php cli.php osm -a communes -m manuel`
5	`*`	5	`*`
6	`*/`	6	`*/`
7	`class ZonesAdmin {`	7	`class ZonesAdmin {`
8		8
9	`private $conteneur;`	9	`private $conteneur;`
10	`private $bdd;`	10	`private $bdd;`
11	`private $messages;`	11	`private $messages;`
12	`private $mode;`	12	`private $mode;`
13		13
14	`private $bddOsmNom;`	14	`private $bddOsmNom;`
15		15
16	`public function __construct($conteneur) {`	16	`public function __construct($conteneur) {`
17	`$this->conteneur = $conteneur;`	17	`$this->conteneur = $conteneur;`
18	`$this->bdd = $this->conteneur->getBdd();`	18	`$this->bdd = $this->conteneur->getBdd();`
19	`$this->bddOsmNom = $this->conteneur->getParametre('bdd_osm_nom');`	19	`$this->bddOsmNom = $this->conteneur->getParametre('bdd_osm_nom');`
20	`$this->messages = $this->conteneur->getMessages();`	20	`$this->messages = $this->conteneur->getMessages();`
21	`$this->mode = $this->conteneur->getParametre('m');`	21	`$this->mode = $this->conteneur->getParametre('m');`
22	`}`	22	`}`
23		23
24	`public function executer() {`	24	`public function executer() {`
25	`// Lancement de l'action demandée`	25	`// Lancement de l'action demandée`
26	`$cmd = $this->conteneur->getParametre('a');`	26	`$cmd = $this->conteneur->getParametre('a');`
27	`switch ($cmd) {`	27	`switch ($cmd) {`
28	`case 'za' :`	28	`case 'za' :`
-		29	`$this->supprimerTableOsmZonesAdmin();`
-		30	`$this->conteneur->getEfloreCommun()->chargerStructureSql();`
-		31	`$this->creerTableOsmZonesAdmin();`
-		32	`$this->corrigerTableOsmZonesAdmin();`
-		33	`$this->mettreAJourIdZoneGeo();`
-		34	`break;`
-		35	`case 'zaCreer' :`
29	`$this->creerTableOsmZonesAdmin();`	36	`$this->creerTableOsmZonesAdmin();`
30	`break;`	37	`break;`
31	`case 'zaCorriger' :`	38	`case 'zaCorriger' :`
32	`$this->corrigerTableOsmZonesAdmin();`	39	`$this->corrigerTableOsmZonesAdmin();`
33	`break;`	40	`break;`
34	`case 'zaMajIdZG' :`	41	`case 'zaMajIdZG' :`
35	`$this->mettreAJourIdZoneGeo();`	42	`$this->mettreAJourIdZoneGeo();`
36	`break;`	43	`break;`
37	`case 'zaSupprimer' :`	44	`case 'zaSupprimer' :`
38	`$this->supprimerTableOsmZonesAdmin();`	45	`$this->supprimerTableOsmZonesAdmin();`
39	`break;`	46	`break;`
40	`default :`	47	`default :`
41	`$this->messages->traiterErreur('Erreur : la commande "%s" n\'existe pas!', array($cmd));`	48	`$this->messages->traiterErreur('Erreur : la commande "%s" n\'existe pas!', array($cmd));`
42	`}`	49	`}`
43		50
44	`}`	51	`}`
45		52
46	`private function supprimerTableOsmZonesAdmin() {`	53	`private function supprimerTableOsmZonesAdmin() {`
47	`$requete = 'DROP TABLE IF EXISTS osm_zones_admin '.`	54	`$requete = 'DROP TABLE IF EXISTS osm_zones_admin '.`
48	`' -- '.__FILE__.' : '.__LINE__;`	55	`' -- '.__FILE__.' : '.__LINE__;`
49	`$this->bdd->requeter($requete);`	56	`$this->bdd->requeter($requete);`
50	`}`	57	`}`
51		58
52	`private function creerTableOsmZonesAdmin() {`	59	`private function creerTableOsmZonesAdmin() {`
53	`$requete = 'INSERT INTO osm_zones_admin '.`	60	`$requete = 'INSERT INTO osm_zones_admin '.`
54	`"SELECT if(ref_insee != '' AND admin_level = 8, CONCAT('INSEE-C:',ref_insee), ".`	61	`"SELECT if(ref_insee != '' AND admin_level = 8, CONCAT('INSEE-C:',ref_insee), ".`
55	`"if(ref_insee != '' AND admin_level = 6, CONCAT('INSEE-D:',ref_insee), ".`	62	`"if(ref_insee != '' AND admin_level = 6, CONCAT('INSEE-D:',ref_insee), ".`
56	`"if(ref_insee != '' AND admin_level = 4, CONCAT('INSEE-R:',ref_insee), ".`	63	`"if(ref_insee != '' AND admin_level = 4, CONCAT('INSEE-R:',ref_insee), ".`
57	`"if(iso3166_2 != '', CONCAT('ISO-3166-2:',iso3166_2), ".`	64	`"if(iso3166_2 != '', CONCAT('ISO-3166-2:',iso3166_2), ".`
58	`"if(iso3166_1 != '', CONCAT('ISO-3166-1:',iso3166_1), ".`	65	`"if(iso3166_1 != '', CONCAT('ISO-3166-1:',iso3166_1), ".`
59	`"CONCAT('OSM-ID:',osm_id)))))), ".`	66	`"CONCAT('OSM-ID:',osm_id)))))), ".`
60	`'osm_id, osm_version, osm_timestamp, '.`	67	`'osm_id, osm_version, osm_timestamp, '.`
61	`"if(name_fr != '', name_fr, name), ".`	68	`"if(name_fr != '', name_fr, name), ".`
62	`'shape, shape_centroid, Y(shape_centroid), X(shape_centroid), '.`	69	`'shape, shape_centroid, Y(shape_centroid), X(shape_centroid), '.`
63	`'admin_level, iso3166_1, iso3166_2, ref_insee, ref_nuts, '.`	70	`'admin_level, iso3166_1, iso3166_2, ref_insee, ref_nuts, '.`
64	`'name, name_fr, name_en, name_es, wikipedia '.`	71	`'name, name_fr, name_en, name_es, wikipedia '.`
65	`"FROM {$this->bddOsmNom}.multipolygons_ref ".`	72	`"FROM {$this->bddOsmNom}.multipolygons_ref ".`
66	`' -- '.__FILE__.' : '.__LINE__;`	73	`' -- '.__FILE__.' : '.__LINE__;`
67	`$this->bdd->requeter($requete);`	74	`$this->bdd->requeter($requete);`
68	`}`	75	`}`
69		76
70	`private function corrigerTableOsmZonesAdmin() {`	77	`private function corrigerTableOsmZonesAdmin() {`
71	`$requete = 'SELECT osm_id, other_tags '.`	78	`$requete = 'SELECT osm_id, other_tags '.`
72	`"FROM {$this->bddOsmNom}.multipolygons_ref ".`	79	`"FROM {$this->bddOsmNom}.multipolygons_ref ".`
73	`"WHERE iso3166_2 IS NULL AND other_tags LIKE '%\"iso3166-2\"%' ".`	80	`"WHERE iso3166_2 IS NULL AND other_tags LIKE '%\"iso3166-2\"%' ".`
74	`' -- '.__FILE__.' : '.__LINE__;`	81	`' -- '.__FILE__.' : '.__LINE__;`
75	`$resultat = $this->bdd->recupererTous($requete);`	82	`$resultat = $this->bdd->recupererTous($requete);`
76	`if ($resultat) {`	83	`if ($resultat) {`
77	`foreach ($resultat as $infos) {`	84	`foreach ($resultat as $infos) {`
78	`$tags = $this->getTags($infos['other_tags']);`	85	`$tags = $this->getTags($infos['other_tags']);`
79	`$iso_3166_2 = $this->bdd->proteger($tags['iso3166-2']);`	86	`$iso_3166_2 = $this->bdd->proteger($tags['iso3166-2']);`
80	`$osm_id = $this->bdd->proteger($infos['osm_id']);`	87	`$osm_id = $this->bdd->proteger($infos['osm_id']);`
81		88
82	`$requete = 'UPDATE osm_zones_admin '.`	89	`$requete = 'UPDATE osm_zones_admin '.`
83	`"SET code_iso_3166_2 = $iso_3166_2 ".`	90	`"SET code_iso_3166_2 = $iso_3166_2 ".`
84	`"WHERE osm_id = $osm_id ".`	91	`"WHERE osm_id = $osm_id ".`
85	`' -- '.__FILE__.' : '.__LINE__;`	92	`' -- '.__FILE__.' : '.__LINE__;`
86	`$this->bdd->requeter($requete);`	93	`$this->bdd->requeter($requete);`
87	`}`	94	`}`
88	`}`	95	`}`
89	`}`	96	`}`
90		97
91	`private function getTags($tagsInfos) {`	98	`private function getTags($tagsInfos) {`
92	`$tags = array();`	99	`$tags = array();`
93	`$clesValeurs = explode(',', $tagsInfos);`	100	`$clesValeurs = explode(',', $tagsInfos);`
94	`foreach ($clesValeurs as $cleValeur) {`	101	`foreach ($clesValeurs as $cleValeur) {`
95	`list($cle, $val) = explode('=>', $cleValeur);`	102	`list($cle, $val) = explode('=>', $cleValeur);`
96	`$tags[trim($cle, '"')] = trim($val, '"');`	103	`$tags[trim($cle, '"')] = trim($val, '"');`
97	`}`	104	`}`
98	`return $tags;`	105	`return $tags;`
99	`}`	106	`}`
100		107
101	`private function mettreAJourIdZoneGeo() {`	108	`private function mettreAJourIdZoneGeo() {`
102	`$requete = 'UPDATE osm_zones_admin '.`	109	`$requete = 'UPDATE osm_zones_admin '.`
103	`"SET id_zone_geo = if(code_insee != '' AND niveau = 8, CONCAT('INSEE-C:',code_insee), ".`	110	`"SET id_zone_geo = if(code_insee != '' AND niveau = 8, CONCAT('INSEE-C:',code_insee), ".`
104	`"if(code_insee != '' AND niveau = 6, CONCAT('INSEE-D:',code_insee), ".`	111	`"if(code_insee != '' AND niveau = 6, CONCAT('INSEE-D:',code_insee), ".`
105	`"if(code_insee != '' AND niveau = 4, CONCAT('INSEE-R:',code_insee), ".`	112	`"if(code_insee != '' AND niveau = 4, CONCAT('INSEE-R:',code_insee), ".`
106	`"if(code_iso_3166_2 != '', CONCAT('ISO-3166-2:',code_iso_3166_2), ".`	113	`"if(code_iso_3166_2 != '', CONCAT('ISO-3166-2:',code_iso_3166_2), ".`
107	`"if(code_iso_3166_1 != '', CONCAT('ISO-3166-1:',code_iso_3166_1), ".`	114	`"if(code_iso_3166_1 != '', CONCAT('ISO-3166-1:',code_iso_3166_1), ".`
108	`"CONCAT('OSM-ID:',osm_id)))))) ".`	115	`"CONCAT('OSM-ID:',osm_id)))))) ".`
109	`' -- '.__FILE__.' : '.__LINE__;`	116	`' -- '.__FILE__.' : '.__LINE__;`
110	`$this->bdd->requeter($requete);`	117	`$this->bdd->requeter($requete);`
111	`}`	118	`}`
112	`}`	119	`}`