Subversion Repositories eFlore/Projets.eflore-projets

Compare Revisions

No changes between revisions

Ignore whitespace Rev 1071 → Rev 1072

/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;
/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);