/trunk/services/modules/0.1/osm/ZoneAdmin.php |
---|
32,6 → 32,7 |
private $lat = null; |
private $lon = null; |
private $zone = null; |
public function __construct(Bdd $bdd) { |
$this->bdd = $bdd; |
42,6 → 43,7 |
$this->verifierParametres(); |
$this->lat = $this->parametres['lat']; |
$this->lon = $this->parametres['lon']; |
$this->zone = isset($this->parametres['zone']) ? $this->parametres['zone'] : null; |
$zoneTrouveeInfos = $this->localiserPointLatLon(); |
$corps = $this->formaterResultats($zoneTrouveeInfos); |
92,6 → 94,7 |
private function localiserPointLatLon() { |
$osmIdsInClause = $this->getOsmIdsDesCentresAProximites(); |
$zone = isset($this->zone) ? $this->bdd->proteger($this->zone) : null; |
$requete = 'SELECT id_zone_geo AS codeZoneGeo, intitule, code_iso_3166_1 AS codeIso31661, '. |
'code_iso_3166_2 AS codeIso31662, code_insee AS codeInsee, code_nuts as codeNuts, '. |
98,6 → 101,7 |
'nom_en AS nomEn, nom_es AS nomEs, wikipedia, niveau '. |
'FROM osm_zones_admin '. |
"WHERE st_within(GEOMFROMTEXT('POINT($this->lon $this->lat)'), polygone) = 1 ". |
(isset($zone) ? "AND zone = $zone " : ''). |
"AND osm_id IN ($osmIdsInClause) ". |
' -- '.__FILE__.' : '.__LINE__; |
$resultat = $this->bdd->recupererTous($requete); |
126,10 → 130,12 |
} |
private function construireRequeteUnion() { |
$zone = isset($this->zone) ? $this->bdd->proteger($this->zone) : null; |
$requeteTpl = 'SELECT osm_id, '. |
"(($this->lon - centre_lng) * ($this->lon -centre_lng) + ($this->lat - centre_lat) * ($this->lat - centre_lat)) AS distance ". |
'FROM osm_zones_admin '. |
'WHERE niveau = %s '. |
(isset($zone) ? "AND zone = $zone " : ''). |
'ORDER BY distance ASC '. |
"LIMIT %s "; |
$niveaux = array(2,3,4,5,6,7,8); |
/trunk/scripts/modules/osm/ZonesAdmin.php |
---|
66,7 → 66,7 |
"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), '. |
'shape, shape_centroid, Y(shape_centroid), X(shape_centroid), zone, '. |
'admin_level, iso3166_1, iso3166_2, ref_insee, ref_nuts, '. |
'name, name_fr, name_en, name_es, wikipedia '. |
"FROM {$this->bddOsmNom}.multipolygons_ref ". |
/trunk/scripts/modules/osm/config.ini |
---|
1,5 → 1,5 |
; Base de données contenant les infos provenant d'OSM |
bdd_osm_nom = "osm" |
bdd_osm_nom = "tb_osm" |
; Ajouter les nouvelles version à la suite dans versions et versionsDonnees. |
version = "2.0" |
/trunk/docs/projets/osm/bdd/OSM.mwb |
---|
Cannot display: file marked as a binary type. |
svn:mime-type = application/octet-stream |
/trunk/docs/projets/osm/bdd/CommunesFrance.sql |
---|
File deleted |
/trunk/docs/projets/osm/bdd/osm.mdb.mwb |
---|
Cannot display: file marked as a binary type. |
svn:mime-type = application/octet-stream |
/trunk/docs/projets/osm/bdd/osm2.pdf |
---|
Cannot display: file marked as a binary type. |
svn:mime-type = application/octet-stream |
/trunk/docs/projets/osm/bdd/osm_communes.pdf |
---|
Cannot display: file marked as a binary type. |
svn:mime-type = application/octet-stream |
/trunk/docs/projets/osm/bdd/osm_communes.pdf |
---|
New file |
Property changes: |
Added: svn:mime-type |
+application/octet-stream |
\ No newline at end of property |
/trunk/docs/projets/osm/bdd/osm.mwb.bak |
---|
Cannot display: file marked as a binary type. |
svn:mime-type = application/octet-stream |
/trunk/docs/projets/osm/bdd/osm.mwb.bak |
---|
New file |
Property changes: |
Added: svn:mime-type |
+application/octet-stream |
\ No newline at end of property |
/trunk/docs/projets/osm/bdd/osm.mwb |
---|
Cannot display: file marked as a binary type. |
svn:mime-type = application/octet-stream |
/trunk/docs/projets/osm/bdd/osm.mwb |
---|
New file |
Property changes: |
Added: svn:mime-type |
+application/octet-stream |
\ No newline at end of property |
/trunk/docs/projets/osm/bdd/osm_communes.sql |
---|
New file |
0,0 → 1,93 |
SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0; |
SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0; |
SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='TRADITIONAL,ALLOW_INVALID_DATES'; |
CREATE SCHEMA IF NOT EXISTS `tb_eflore` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci ; |
USE `tb_eflore` ; |
-- ----------------------------------------------------- |
-- Table `tb_eflore`.`osm_noeuds` |
-- ----------------------------------------------------- |
DROP TABLE IF EXISTS `tb_eflore`.`osm_noeuds` ; |
CREATE TABLE IF NOT EXISTS `tb_eflore`.`osm_noeuds` ( |
`id_noeud` BIGINT NOT NULL , |
`lat` DOUBLE NOT NULL , |
`long` DOUBLE NOT NULL , |
PRIMARY KEY (`id_noeud`) ) |
ENGINE = InnoDB; |
-- ----------------------------------------------------- |
-- Table `tb_eflore`.`osm_chemin_a_noeuds` |
-- ----------------------------------------------------- |
DROP TABLE IF EXISTS `tb_eflore`.`osm_chemin_a_noeuds` ; |
CREATE TABLE IF NOT EXISTS `tb_eflore`.`osm_chemin_a_noeuds` ( |
`id_chemin` BIGINT NOT NULL , |
`id_noeud` BIGINT NOT NULL , |
`ordre` INT NOT NULL , |
PRIMARY KEY (`id_chemin`, `id_noeud`, `ordre`) , |
INDEX `id_noeud` (`id_noeud` ASC) , |
INDEX `id_chemin` (`id_chemin` ASC) , |
CONSTRAINT `avoir_noeud` |
FOREIGN KEY (`id_noeud` ) |
REFERENCES `tb_eflore`.`osm_noeuds` (`id_noeud` ) |
ON DELETE NO ACTION |
ON UPDATE NO ACTION) |
ENGINE = InnoDB; |
-- ----------------------------------------------------- |
-- Table `tb_eflore`.`osm_commune` |
-- ----------------------------------------------------- |
DROP TABLE IF EXISTS `tb_eflore`.`osm_commune` ; |
CREATE TABLE IF NOT EXISTS `tb_eflore`.`osm_commune` ( |
`id_relation` BIGINT NOT NULL , |
`nom` VARCHAR(255) NULL , |
`code_insee` VARCHAR(5) NULL , |
`polygone` MULTIPOLYGON NULL , |
`centre` POINT NULL , |
`notes` TEXT NULL , |
PRIMARY KEY (`id_relation`) , |
INDEX `code_insee` (`code_insee` ASC) , |
INDEX `commune_polygone` USING BTREE (`polygone` ASC, `centre` ASC) ) |
ENGINE = InnoDB; |
-- ----------------------------------------------------- |
-- Table `tb_eflore`.`osm_relation_a_chemins` |
-- ----------------------------------------------------- |
DROP TABLE IF EXISTS `tb_eflore`.`osm_relation_a_chemins` ; |
CREATE TABLE IF NOT EXISTS `tb_eflore`.`osm_relation_a_chemins` ( |
`id_relation` BIGINT NOT NULL , |
`id_chemin` BIGINT NOT NULL , |
`role` VARCHAR(20) NOT NULL , |
`ordre` INT NOT NULL , |
`sens` VARCHAR(15) NOT NULL , |
`nbre_poly` INT NOT NULL , |
PRIMARY KEY (`id_relation`, `id_chemin`) , |
INDEX `role` (`role` ASC) , |
INDEX `sens` (`sens` ASC) , |
INDEX `ordre` (`ordre` ASC) , |
INDEX `nbre_poly` (`nbre_poly` ASC) , |
CONSTRAINT `avoir_chemin_a_noeuds` |
FOREIGN KEY () |
REFERENCES `tb_eflore`.`osm_chemin_a_noeuds` () |
ON DELETE NO ACTION |
ON UPDATE NO ACTION, |
CONSTRAINT `avoir_commune` |
FOREIGN KEY () |
REFERENCES `tb_eflore`.`osm_commune` () |
ON DELETE NO ACTION |
ON UPDATE NO ACTION) |
ENGINE = InnoDB; |
USE `tb_eflore` ; |
SET SQL_MODE=@OLD_SQL_MODE; |
SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS; |
SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS; |