Upload
others
View
6
Download
0
Embed Size (px)
Citation preview
MASTER 2 STEP – SPECIALITE SIG ET GESTION DE L’ESPACE PARCOURS PROFESSIONNEL
MIGRATION DE LA BASE DE DONNEES DE LA
DIRECTION DEPARTEMENTALE DES TERRITOIRES DE L’ISÈRE VERS UN SGBD SPATIAL « POSTGRES/POSTGIS »
Création des scripts d’import/export et de mise à jour des données, sous Postgres/PostGis
Réalisé par
NAÏMA BELOUCIF Septembre 2015
Organisme d’accueil : Direction Départementale des Territoires de l‟Isère38
Membres du jury :
Christophe Badol (Responsable du Bureau SIG central de la DDT 38) Thierry Joliveau (Responsable du Master 2 STEP Spécialité SIG et Gestion de l‟Espace)
Christine Jacqueminet (Maitre de conférences. Spécialité : Géographie physique, humaine) Bernard Etlicher (Enseignant-chercheur. Spécialité : Géographie physique, humaine)
Migration de la base de données de la DDT38 sous un SGBDS
2
REMERCIEMENTS
Je tiens tout d‟abord à remercier Christophe Badol, qui a encadré quotidiennement ce stage, pour ses nombreux conseils, sa réflexion et la confiance qu‟il m‟a accordée. Je le remercie aussi pour sa grande gentillesse, son écoute et pour avoir fait que ce stage se passe dans les meilleures conditions pour moi. Je tiens à remercier tout spécialement Monsieur Thierry Joliveau, responsable du M2 STEP/SIG à l‟Université Jean Monnet de Saint-Etienne et tuteur universitaire de ce stage de m‟avoir accordé la chance de suivre le master 2 SIG. Un grand merci à mes collègues du SIG central, avec qui j‟ai eu un réel plaisir à travailler durant ces cinq mois pour leur bonne humeur, leur générosité, leur compréhension qui m‟ont permis de réaliser ce stage dans des conditions optimales : - Annie Baydon pour sa générosité son ouverture d‟esprit et sa gentillesse. - Sandrine pour sa grande gentillesse et sa sensibilité. - Véronique pour son énergie et sa gentillesse. - Et Thierry pour son calme et sa sympathie. A titre plus personnel, je remercie Achraf AKRIMI pour ses encouragements et son soutien. Un grand merci à mes parents et mes sœurs pour leurs soutiens malgré la distance qui nous sépare. Enfin, je remercie la DDT38 pour son accueil au sein de leur structure.
Migration de la base de données de la DDT38 sous un SGBDS
3
GLOSSAIRES
Batch : est un fichier texte portant l’extension « .bat » qui regroupe un ensemble de lignes de commande écrites en ms-dos (langage de commande spécifique à Windows). L’ouverture du fichier exécute ligne par ligne toutes les commandes écrites.
CNIG : Conseil National de l’Information Géographique.
COVADIS : Commission de la Validation des Données pour l’Information Spatialisée.
DDT38 : Direction départementale des territoires de l’Isère.
GB_CONS : base de données qui stocke les couches en consultation au niveau de la
DDT38.
GB_PROD : base de données qui stocke les couches en production au niveau de la DDT38.
Geobase : nom de la base de données géographique au niveau de la DDT38.
GDAL : (Geospatial Data Abstraction Library) est une bibliothèque libre permettant de lire et
de traiter un très grand nombre de format d'images géographiques (notamment GeoTIFF et
ECW) depuis des langages de programmation tels que C, C++, C sharp / .Net, Java, Ruby,
VB6, Perl, Python.
Ogr2ogr : c’est un utilitaire qui peut être utilisé pour convertir des données simple dans des
formats de fichiers tout en réalisant des opérations diverses pendant le processus, comme
des sélections spatiales ou attributaires, la réduction d’ensemble d’attributs, la définition du
système de coordonnées en sortie ou même la reprojection des objets pendant la
translation.
PgAdmin : interface graphique d’utilisation de postgres/Postgis.
Postgres : Systéme de Gestion de Base de Données relationnelle (SGBD)
Postgis : Extension de postgres qui traite la donnée spatiale.
SIG : Système d’Information Géographique.
SQL : Select Query Langage : est un langage informatique normalisé servant à exploiter des bases de données relationnelles. La partie langage de manipulation des données de SQL permet de rechercher, d'ajouter, de modifier ou de supprimer des données dans les bases de données relationnelles.
Script : Ensemble de commandes capables d'automatiser certaines tâches d'un
programme.
Migration de la base de données de la DDT38 sous un SGBDS
4
Table des matières PARTIE I : PRESENTATION DU CADRE DU STAGE ET ELABORATION D‟UN PLAN
D‟ACTION ............................................................................................................................. 8
1.Présentation de l‟organisme d‟accueil ................................................................................ 8
1.1. Organisation du Système d‟Information Géographique à la DDT38 ................................ 8
1.1.1. La Direction Départementale des Territoires de l‟Isère ................................................ 8
1.1.2. Les préconisations nationales pour une infrastructure commune autour de l‟information
géographique…. ...................................................................................................................10
1.2. Présentation de l‟organisation et de l‟administration des données au niveau du service
SIG Central (SIGC) de la DDT38 .........................................................................................11
1.3.Organisation du SIG central (SIGC)...............................................................................11
1.3.1. Ressources disponibles et besoins .............................................................................11
2. Définition du sujet du stage ..............................................................................................15
2 .1 Les tâches principales de mon travail ...........................................................................15
2.1.1. Les tâches secondaires ..............................................................................................15
PARTIE II : PLAN D‟ACTION ..............................................................................................16
3.1. Outils et méthodes .........................................................................................................16
3.1.1. Le choix du langage de programmation pour l‟automatisation de la migration de la
Géobase………………………………………………………………………………………………16
3.1.2. Matériel nécessaire au fonctionnement du batch : ......................................................17
4. Préparation et paramétrage du serveur Postgres/Postgis ...............................................17
4.1. Gestion des accès à la base : création de rôles PostgreSQL ........................................20
4.2. Préparation des schémas sous Postgres/Postgis ..........................................................20
4.3. Création de tables spécifique pour la bascule vers le serveur en consultation ...............21
5. Mise en place du batch d‟import des couches ..................................................................22
5.1. Le principe de fonctionnement du batch d‟import ...........................................................22
5.1.1. L‟outil d‟import : „ogr2ogr.exe‟ .....................................................................................22
5.1.2. Les requêtes SQL .......................................................................................................23
6. Mise en place du batch de bascule « export » des tables .................................................25
6.1. Le principe du fonctionnement du batch d‟export ...........................................................26
7. La migration des données des documents d‟urbanisme ..................................................29
7.1. Le principe du fonctionnement du batch d‟import des documents d‟urbanisme ..............31
Migration de la base de données de la DDT38 sous un SGBDS
5
7 .2. Contrôle des données des documents d‟urbanisme après l‟import vers
Postgres/Postgis ..................................................................................................................32
7.2.1. Les requêtes pour le contrôle des données attributaires .............................................32
7.2.2. Les requêtes pour le contrôle de la géométrie ............................................................33
8. Autres tâches assignées lors du stage .............................................................................33
8.1. Guide utilisation du script d‟import .................................................................................33
8.2. Configuration de la connexion à Postgres/Postgis et de la mise à jour des tables via
QGIS ....................................................................................................................................33
8.3. Création de tables sous Postgres/Postgis .....................................................................33
8.4. Création de formulaire sous QGIS .................................................................................35
Conclusion ...........................................................................................................................36
Table des Figures .................................................................................................................37
Bibliographie ........................................................................................................................38
ANNEXE N° 1 ......................................................................................................................39
ANNEXE N° 2 ......................................................................................................................40
ANNEXE N° 3 ......................................................................................................................44
ANNEXE N° 5 ......................................................................................................................49
ANNEXE N° 6 ......................................................................................................................56
ANNEXE N° 7 ......................................................................................................................66
ANNEXE N° 8 ......................................................................................................................72
ANNEXE N° 9 ......................................................................................................................76
Migration de la base de données de la DDT38 sous un SGBDS
6
Introduction
Toute organisation des données exige un système et des normes bien définies et
appropriées à chaque organisme selon les besoins des utilisateurs de la donnée. La
tendance actuelle de partage et de disponibilité des données sur le web exige une
homogénéisation des données.
Dans le but de favoriser l'échange et l'utilisation des informations géographiques
(convergence des outils et méthodes) au sein des différents services de l‟état, la COVADIS
(Commission de la Validation des Données pour l‟Information Spatialisée) a mis en place un
dispositif qui comprend une liste de geostandards qui consiste à établir des normes
nationales de représentation de données géographiques. Ces geostandards sont des
modèles adaptés aux usages ministériels et transposables au modèle de la directive
Européenne INSPIRE. La Directive européenne INSPIRE, a pour objectif de veiller au
respect d‟interopérabilité des données afin de faciliter et organiser la mise à disposition, la
diffusion et le partage des données géographiques détenues par une autorité publique.
Afin d‟améliorer la maîtrise de l‟information géographique et pour disposer de données de
qualité, harmonisées, standardisées le patrimoine des données géographiques de la DDT de
l‟Isère est structuré selon la norme COVADIS.
Les standards COVADIS offrent un cadre technique décrivant en détail la façon d'ordonner et de stocker, au sein d'une base de données géographiques exploitable par un outil SIG, l'ensemble des informations selon les thématiques. C‟est dans ce contexte que, depuis 2010, le service SIG Central (SIGC) le principal administrateur de la base de données au niveau de la Direction Départementale des Territoires de l‟Isère (DDT38) s‟est engagé sur le vaste chantier d‟organisation et d‟administration de leurs données. Vu le caractère de multi-utilisateurs de la base de données au niveau de la DDT38, cette dernière est souvent contrainte à des disfonctionnements. A ce stade le besoin de réorganiser leur base données actuelle (fichiers à plat ESRI shapefile et Mapinfo) dans un Système de Gestion de Base de Données plus performant est devenu une nécessité. L‟idée d‟administrer la base de données sous Postgres/PostGis date de 2012. Mais ce n‟est qu‟à partir de 2014 que le service SIG central a commencé officiellement la migration progressive de sa base de production dans un Système de Gestion de Base de Données Postgres/PostGis sous un serveur Postgres dans une base de données nommée « geobase38 ». Ce stage se concentre donc sur ces nouveaux enjeux, afin de mettre en place, au sein du Service SIGC de la DDT38, les outils nécessaires pour faciliter la migration de la base de données actuelle vers un SGBD. Le principal objectif étant d‟automatiser autant que possible les procédures de migration de la base de données et de mise à jour des données pour gagner en efficacité.
La migration de la base de données actuelle (fichiers à plat ESRI Shapefile et Mapinfo) vers un système de gestion de base de données (SGBD) optimisera grandement la gestion et l‟usage des données au sein de la DDT38.
Migration de la base de données de la DDT38 sous un SGBDS
7
Dans un premier temps je présenterai le fonctionnement général de la DDT de l‟Isère et l‟organisation du service SIGC de la DDT de l‟Isère où j‟ai effectué mon stage et je listerai les différentes tâches qui m‟ont été assignées. Puis dans une deuxième partie, après avoir présenté brièvement la méthodologie de travail, je présenterai en détail les étapes suivies pour répondre à l‟objectif du stage, à savoir : la procédure d‟import et d‟export de la base de données, la mise en place technique qu‟elle nécessite, ainsi que le contrôle des données après l‟import vers le SGBD et certaines tâches secondaires que j‟ai dû effectuer. Je terminerai cette partie par la présentation des difficultés rencontrées. Je conclurai mon rapport par un bilan des tâches réalisées et les éventuelles critiques et limites de notre mode opératoire.
Migration de la base de données de la DDT38 sous un SGBDS
8
PARTIE I : PRESENTATION DU CADRE DU STAGE ET ELABORATION
D’UN PLAN D’ACTION
1. Présentation de l’organisme d’accueil
1.1. Organisation du Système d’Information Géographique à la
DDT38
1.1.1. La Direction Départementale des Territoires de l’Isère
Les Directions Départementales des Territoires sont des services interministériels déconcentrés de l‟Etat, issues de la fusion, des Directions Départementales de l‟Equipement (DDE), des Directions Départementales de l‟Agriculture et des Forêts (DDAF) et des services environnement des préfectures (janvier 2010). Placées sous l‟autorité du préfet de département, elles mettent en œuvre les politiques publiques d‟aménagement et de développement durable des territoires lancées par le MEDDE et le Ministère de l‟Agriculture, de l‟Agro-alimentaire et des Forêts (MAAF). La DDT de l‟Isère regroupe environ 350 agents répartis sur 8 services. (Annexe n°1) (Source : http://www.isere.gouv.fr)
Les six services fonctionnels responsables de domaines d’activité Le service logement et construction en charge des politiques de l‟habitat, du financement du logement social, de la réhabilitation du parc de logement privé, de la rénovation urbaine, de la conduite des opérations de constructions publiques et de la promotion de la qualité de la construction. Le service agriculture et développement rural en charge de la gestion des aides publiques aux agriculteurs, aux projets des exploitations et au développement rural, de la valorisation des filières agricoles et agro-alimentaires ainsi que de la gestion des crises agricoles. Le service environnement en charge de la gestion de la ressource en eau, de l‟aménagement des cours d‟eau, ainsi que de la préservation des espaces naturels et forestiers, de la faune et de la flore. Le service de prévention des risques en charge de l‟élaboration des plans de prévention des risques naturels, miniers et technologiques, et de l‟information sur les risques. Le service sécurité des transports en charge de l‟animation de la politique locale de sécurité routière, et de l‟éducation routière. Le service études et territoires qui élabore les diagnostics de territoires et études générales, les porter à connaissance et avis de l‟État sur les documents d‟urbanisme.
Les deux services aménagement
Les Services Aménagement Nord-Ouest (SANO) et Sud-Est (SASE), dont les agents, répartis sur le territoire, sont chargés d‟accompagner l‟élaboration des documents d‟urbanisme, par l‟instruction des permis de construire, et le conseil aux communes. Les communes du département sont réparties par zone géographique entre les différents chargés d‟aménagement (4 au SANO et 7 au SASE). Ces deux services sont activement mis à contribution pour la dématérialisation des documents d‟urbanisme, notamment sur les contrôles dits « d‟interprétation » concernant la conformité des zonages par rapport au code de l‟urbanisme.
Migration de la base de données de la DDT38 sous un SGBDS
9
Figure 1: Les antennes de la DDT dans le département de l’Isère (Source : http://www.isere.gouv.fr)
Le secrétariat général (SG)
C‟est au sein du SG, basé au siège de Grenoble, que se regroupe les services administratifs de la DDT : le pôle informatique (SIDSIC), les ressources humaines, les affaires juridiques…et les Bureaux du SIG central directement concernés par la politique nationale pour l‟information géographique.
Migration de la base de données de la DDT38 sous un SGBDS
10
1.1.2. Les préconisations nationales pour une infrastructure
commune autour de l’information géographique
Avec la création des DDT, le MEDDE et le MAAF ont mis en place, pour les services déconcentrés, une infrastructure géomatique qui comporte des outils, des règles de gestion des données et des méthodes généralisées. Ce cadre de travail vient en cohérence avec la directive européenne Inspire, et même s‟il subsiste des évolutions en interne, propre à chaque DDT, les deux ministères ont défini un cadre pour progressivement converger vers une infrastructure unique. Ce cadre repose sur : - Des référentiels géographiques centralisés Ils constituent le socle élémentaire de données partagées. Les plus utilisés étant ceux de L‟Institut National de l‟information Géographique et forestière (IGN) dans le cadre d‟un protocole pluriannuel et interministériel (BD TOPO, BD ORTHO, BD PARCELLAIRE,…), mais d‟autres référentiels sont employés, comme les données PCI Vecteur (Plans Cadastraux numérisés) validées par la Direction Générale des Finances Publiques. Nous verrons qu‟ils rentrent aussi dans la procédure de vérification des documents d‟urbanisme selon COVADIS.
- Des règles nationales pour la production et l’organisation des données métiers Un cadrage national est défini par la Commission de Validation des Données pour l’Information Spatialisé (COVADIS) qui a été mis en place par les deux ministères pour définir les standards et les méthodes à utiliser. Une arborescence pour le classement des données a été validée, arborescence qui s'applique à tous les services de chacun des deux ministères. Sur cette base, la commission a engagé le processus de standardisation des données du patrimoine commun, débouchant sur les geostandards COVADIS. Les données géographiques des DDT doivent être stockées dans les GéoBases, qui respectent les règles d'organisation (arborescence), de structuration et de nommage des données définies par la COVADIS.. - Des outils de mise à disposition et de catalogage des données Deux outils web sont actuellement proposés pour la DDT38 :
GéoRhôneAlpes (http://www.georhonealpes.fr/) : est un dictionnaire et un catalogue de
données directement lié à la GéoBASE. Il contient les fiches de métadonnées validées par la COVADIS, et permet à un service de préciser ses fiches, mais pas d'en créer pour les données non validées. C‟est l‟outil privilégié de diffusion des métadonnées en interne et entre services.
Géoportail de l'urbanisme (http://www.geoportail-urbanisme.gouv.fr/): nouveau
portail national de l‟urbanisme pour la mise à disposition des fichiers cartographiques des documents d‟urbanisme.
Pour accéder aux données, les utilisateurs doivent disposer des droits d'accès suffisants en fonction de l'origine de leur connexion et ils doivent accepter les conditions d'utilisation des données. Ils peuvent ensuite télécharger les données et/ou consulter les cartes choisies. Ils peuvent accéder à des données privées d'un service ou d'un groupe en s'authentifiant.
Migration de la base de données de la DDT38 sous un SGBDS
11
1.2 Présentation de l’organisation et de l’administration des données
au niveau du service SIG Central (SIGC) de la DDT38
L‟organisation des données géographiques au niveau de la DDT38 est assurée par le service SIGC. Actuellement le service SIGC est le principal et le seul administrateur du patrimoine commun des données géographiques de toute la DDT38.
Dans le but de faciliter et surtout d‟optimiser la gestion, le stockage et le suivi des données régulièrement mises à jour, le service SIG a commencé progressivement la migration d‟une partie de sa base appelé Géobase vers un Système de Gestion de base de Données (SGBD).
La Géobase regroupe les couches de référence et métier définies nationalement par la COVADIS. C‟est le socle de toute l’infrastructure SIG, répartie sur trois serveurs propres à la DDT sous forme de lecteurs réseau accessibles par l'ensemble des postes de travail de la DDT38 à partir de l'icône « poste de travail ». À chaque lecteur réseau est attribué une lettre. Il devient alors disponible dans le poste de travail, au même titre que les disques locaux, sur lesquels sont gérés les droits d‟accès. Ces lecteurs sont présentés comme suit : « S : GB_CONS » en consultation (lecture seule des fichiers), « T : GB: PROD » en production (lecture et écriture sur les fichiers) et « R : GB :REF » pour les données de références. Ils sont régulièrement synchronisés pour maintenir à jour les données validées en production avec les données en consultation.
Figure 2: La répartition de la base de données locale ( La Géobase)
1.3 Organisation du SIG central (SIGC)
1.3.1. Ressources disponibles et besoins
L’équipe du SIG central L‟équipe du Bureau du SIG central est actuellement composée de 4 personnes : - Christophe Badol : responsable du bureau, administrateur de données, et tuteur pour ce stage.
- Annie Baydon : opératrice d‟Information Géographique, chargée de mise à disposition des documents d‟urbanisme sur le réseau FTP et leur publication sur le Géoportail de l‟urbanisme
- Véronique Janes et Thierry Collet : géomaticiens et opérateurs SIG, production et valorisation des données. - Sandrine Trienbach-Jouve : chargée du catalogage et de la mise à disposition sur GéoRhône-Alpes.
Migration de la base de données de la DDT38 sous un SGBDS
12
Les missions Les principales missions confiées aux agents du SIG central sont : - Administrer le patrimoine commun de données géographiques - Développer l'utilisation de l'information géographique - Valoriser les données sous forme de cartes à la demande des services - Disposer de compétences pointues pour produire des données géographiques complexes - Animer le réseau des opérateurs et géomaticiens - Assurer l'interface avec les services départementaux et régionaux, les plates-formes et les portails cartographiques - Coordonner les SIG métier de la DDT
Les utilisateurs SIG au sein de la DDT38 Un rapport de janvier 2011, du réseau des CMSIG qui assure le management des différentes cellules SIG de l‟Etat, identifie 4 catégories d’utilisateurs des SIG: • Les utilisateurs de base: il s'agit potentiellement de tous les agents de la DDT, qui ont besoin d'accéder aux données géographiques avec un outil de consultation simple. • Les utilisateurs intermédiaires: ils se distinguent des utilisateurs de base par leur besoin de mettre en forme les données de façon personnalisée (ajout de couches, analyses thématiques, représentations...). • Les utilisateurs avancés: ils ont besoin de travailler les données, et d'en créer, afin de communiquer sur les enjeux du territoire. • Les géomaticiens ou opérateurs géomatiques: ils utilisent les fonctions avancées du SIG et participent à l'administration des données.
Les outils SIG en place - Logiciels SIG :
Le basculement vers les logiciels libres Depuis 2010, deux logiciels cartographiques sont maintenus à la DDT: MapInfo, largement utilisé au niveau local et national, et plus au moins le logiciel ArcView. Vu le budget important pour l‟achat des licences et la non adaptation du logiciel MapInfo aux nouveau besoins, les services de l‟état bascule progressivement vers le logiciel libre QGIS. L‟adoption du logiciel QGIS, parmi les outils proposés, par les ministères, à leurs services et aux DDT en complément de MapInfo et des outils web , a été décidée par la Commission de Coordination de l‟Information Géographique (CCIG)en date du 25 mai 2011. Au niveau de la DDT38, les utilisateurs du SIG commencent timidement à utiliser QGIS au détriment du logiciel MapInfo. Le SIG central possède également une licence pour le logiciel FME qui est un ETL (Extract, Transform and Load) utilisé dans l‟extraction, la transformation et le chargement de données géographiques vectorielles et images issues des données référentielles tel que le Plan Cadastral Informatisé issu de la Direction Départementale des Finances Publiques (DGFIP) et les (BD TOPO, BD ORTHO, BD PARCELLAIRE,…), issues de l‟Institut National de l‟information Géographique et forestière (IGN). Ainsi le SIG central et la DDT38 dispose de 12 licences MapInfo, 10 licences Arcview, 1 licence Arcinfo, QGIS installé sur 30 postes, et 1 Licence FME.
Migration de la base de données de la DDT38 sous un SGBDS
13
- Un serveur PostgreSQL 9.2 (SGBD open source) : Sa mise en place concerne pour le moment un besoin interne au SIG central, notamment pour la gestion des fichiers Cadastre (parcellaire, bâti,…) et les fichiers fonciers « Majic » contenant les noms des propriétaires. La gestion des données se fait via l‟application PgAdmin (Interface visuelle pour PostgreSQL). - Un serveur local PostgreSQL 9.3 avec l’extension spatiale Postgis 2.1 a été installé en
local sur une machine du SIG central. Cependant aucun package officiel n‟existe encore pour ce logiciel et la réflexion sur son déploiement global à la DDT commence seulement à émerger.
Migration de la base de données de la DDT38 sous un SGBDS
14
Migration de la base de données de la DDT38 sous un SGBDS
15
2. Définition du sujet du stage
L‟optimisation des procédures, par l‟automatisation des traitements de la migration des couches validées dans un système de gestion de base de données comme PostgreSQL, offre tant des avantages que des conditions de travail faisant bénéficier tous les acteurs impliqués. Le choix de ce SGBDS, n‟est pas anodin, il offre une capacité de stockage et de gestion des
données très importante, l‟interopérabilité des données et une homogénéisation de celle-ci.
Ce système facilite aussi l‟intégration des données externes à la DDT et offre une sécurité
d‟accès : selon l‟identité des utilisateurs (administrateur, production, consultation). A cela
s‟ajoute son statut d‟open data qui est une valeur non négligeable d‟un point de vue
économique (licence libre).
L‟intérêt de migrer des données dans un SGBD comme Postgis est de pouvoir optimiser la gestion, l‟utilisation et le suivi des données régulièrement mises à jour. Les scripts de migration des données doivent être accessibles aux utilisateurs responsables de la production au niveau du Service SIGC.
Suite au choix du basculement de la base production vers un SGBDS il a été choisi d‟utiliser le logiciel QGIS pour la visualisation et la mise à jour des couches, car son interface devrait permettre une utilisation complète de la base à savoir la visualisation, l‟édition, et la modification de la donnée géographique.
2 .1. Les tâches principales de mon travail
Automatiser la procédure de migration de base de données actuellement stockées sous le serveur «T : GB_PROD » vers un système de gestion de base de données Postgres/Postgis (SGBDS) afin de stocker les données produites par la DDT38 et ensuite exporter ces données vers le serveur actuel de consultation « S : GB_CONS », soit par un script synchronisé pour une bascule nocturne quotidienne soit par un script pour une bascule immédiate.
Un script d‟import spécifique pour les couches des documents d‟urbanisme vient en continuité du contrôle de conformité. Une fois validées, les données doivent être intégrées dans le patrimoine commun en production.
La mise en place des requêtes de vérification de la qualité et de l‟exactitude des données importées.
2.1.1. Les tâches secondaires
D‟autres tâches sont apparues suite à cette migration des données. Telles que :
Configurer la connexion du serveur Postgres/Postgis vers QGIS pour la mise à jour et la consultation des couches.
Réalisation de formulaires sur QGIS en forme de menu déroulant pour les attributs dont les données sont déjà définies. Ce formulaire a pour but de faciliter la saisie et d‟éviter les erreurs lors de la mise à jour des couches par les agents de la DDT38.
Un guide pour l‟utilisation du script d‟import, en commençant par l‟ouverture du
serveur Postgres via l‟interface pgAdmin, jusqu‟à la configuration de la connexion
des tables, stockées dans Postgres/Postgis pour une consultation et une mise à jour
sous Qgis, en passant par la création de formulaire (menu déroulant) pour certains
attributs.
Migration de la base de données de la DDT38 sous un SGBDS
16
PARTIE II : PLAN D’ACTION
3. Méthodologie du travail
Mon travail principal consiste à la migration de la base de données locale vers un système
de gestion de base de données Spatial (Postgres/Postgis).
La première étape est de définir le modèle de données à respecter dans la base pour ensuite la mettre en place. Dans un deuxième temps, les procédures d‟import, d‟export et de synchronisation avec la base en consultation seront mises en place pour automatiser les opérations de gestion de la Géobase. Il existe plusieurs façons de faire l‟import/export des données vers ou via le système de
gestion de base de données Postgres/Postgis. A titre d‟exemple la fonction (sql2shp), qui ne
prend en compte que des fichiers de forme « .shp ». L‟outil Spatialite intégré au logiciel
QGIS offre lui aussi des fonctions d‟import vers Postgres/Postgis, mais il est limité à l‟import
d‟une couche à la fois.
Vue la diversité des formats des données qui existent au niveau de la DDT38, le plus pratique est de proposer une migration en utilisant l‟outil og2ogr de la bibliothèque GDAL.
Les scripts de migrations de la base de données sont assurés par un fichier Batch qui permet d‟automatiser les différentes manipulations de migration en utilisant les applications ogr2ogr et psql.
3.1. Outils et méthodes
3.1.1. Le choix du langage de programmation pour l’automatisation de
la migration de la Géobase
La solution choisie pour la migration de la Géobase locale de la DDT38 s‟est tournée vers l‟élaboration d‟un script batch, centralisant toutes les étapes nécessaires, pour que les tables et couches spatiales soit migrées avec un même standard en utilisant les applications ogr2ogr et psql. Il nous parut pertinent de nous orienter vers une solution utilisant des procédures de l‟utilitaire ogr2ogr. En effet, cette application peut facilement s‟adapter et s‟automatiser. L‟application ogr2ogr, va nous permettre d‟introduire certains paramètres et de faire des modifications lors de l‟import, afin d‟avoir des données cohérentes.
Le choix d‟automatisation de la migration de la Géobase en utilisant le langage batch, revient à l‟utilisation de ce programme par la DDT38 depuis déjà un an.
Le rôle primitif d‟un batch est d'enchaîner des commandes (de les exécuter les unes à la suite des autres). Son intérêt est d'automatiser des tâches répétitives effectuées sous DOS. Un fichier batch est un fichier texte portant l‟extension « .bat » et qui regroupe un ensemble de lignes de commande écrites en MS-DOS (langage de commande originel de MS/DOS, puis Windows). L‟ouverture du fichier exécute ligne par ligne toutes les commandes écrites.
Migration de la base de données de la DDT38 sous un SGBDS
17
3.1.2. Matériel nécessaire au fonctionnement du batch :
Les logiciels
- Notepad++ (édition de script) - PostgresSQL version >= 9.2 (SGBD opensource) - Postgis version >= 2.1.1 (extension spatiale PgSQL) - pgAdminIII version >= 1.18.0 (Interface utilisateur PqSQL) - Bibliothèque GDAL version >= 1.10.0 (Librairie de fonctions spatiales dont ogr2ogr) - Qgis version >= 2.6 (SIG opensource)
Les applications utilisées sont
- ogr2ogr.exe pour l‟import/export de données vers Postresql. - psql.exe terminal pour PostgreSQL pour le lancement des requêtes SQL. - pgsql2shp.exe pour l‟export des tables erreurs, Postgis vers couche Shape.
4. Préparation et paramétrage du serveur Postgres/Postgis
Le modèle de donnée étant déjà établi par la COVADIS seules quelques adaptations ont été faites pour faciliter la gestion des couches. Tel que la configuration du champ « id_map » comme clé primaire. Lors de l‟import de la base de production on doit respecter l‟arborescence de la Géobase existante où les données sont stockées selon le modèle de donnée déjà établi par COVADIS sous un dossier et un sous-dossier pour chaque métier (thématiques).
La base actuelle est stockée dans19 répertoires qui regroupent au total 107 sous-répertoires
répartis entre 3 à 10 dans chaque répertoire selon la thématique. Dans l‟organisation de la
nouvelle base sous Postgres/Postgis elle reprendra la même répartition que la base
existante en créant des schémas dont le nom comprend le nom du répertoire et le nom du
sous-répertoire. (Figure : 3).
Figure 3: Schéma de la base de données dans le serveur actuel et dans le serveur Postgres/Postgis
Migration de la base de données de la DDT38 sous un SGBDS
18
Figure 4: Exemple de représentation de la base sous Postgres/Postgis
Figure 5: Exemple de représentation de la base sous la Géobase Production
Migration de la base de données de la DDT38 sous un SGBDS
19
L’organisation de la base spatiale Postgres/Postgis : En SGBD nous ne parlons plus de couche, comme dans un SIG ordinaire, mais de table.
Chaque table est composée de plusieurs colonnes attributaires et doit posséder
obligatoirement une clé primaire qui ne doit pas avoir de données nulles. Ce qui permet
d‟assurer l‟unicité des objets insérés dans chaque table.
Grâce à l‟extension spatiale PostGis, le SGBD devient SGBDS en associant les données
spatiales (géographiques), et permet ainsi la gestion et le traitement de cette dernière au
travers de fonctions complètes. PostGis stocke l‟information spatiale dans la même table en
lui associant une colonne géométrique appelé par défaut « the_geom » et il associe à
chaque table une projection stockée dans une autre table appelée « spatial_ref_sys » (cette
table stocke tous les systèmes de projection des données existantes dans la base). Une
autre table appelée « Geometry Column » est créée automatiquement dans le schéma public
après la création de l‟extension Postgis (figure 6), elle sert à stoker toutes les informations
par rapport aux données spatiales enregistrées dans la base de données.
Figure 6: Exemple table Geometry Column
Migration de la base de données de la DDT38 sous un SGBDS
20
4.1. Gestion des accès à la base : création de rôles PostgreSQL
Une fois les rôles spécifiques créés, les droits d‟accès peuvent être affinés au niveau des tables, schémas ou bases pour répartir les droits selon l‟utilisateur
Concernant la gestion des droits sur la base donnée de la DDT38, trois groupes de rôle ont été définis sur PostgreSQL depuis sa mise en place en 2014.
Lors des connexions à PostgreSQL via Qgis, ces rôles sont pris en compte lors de l‟identification de l‟utilisateur dans les paramètres de connexion. Ainsi Qgis peut constituer la plateforme principale de travail pour les données Postgis. - « admin » le super-utilisateur ayant tout les droits en lecture et écriture sur les bases. Il peut créer des bases de données ainsi que les rôles de connexion, c‟est le rôle privilégié pour les géomaticiens du service SIGC. - « prod » qui a des droits en lecture et écriture seulement sur certaine base métier (différents rôles pourront ensuite être rattachés à un service). C‟est le rôle de connexion de tous les utilisateurs et producteurs de donnée de la DDT extérieurs au SIG central.
- « cons » qui possède seulement les droits en lecture sur les données, il ne peut modifier le contenu des tables ou les objets géométriques. Ce rôle est réservé aux utilisateurs occasionnels recherchant juste des informations. Concernant notre base, le groupe « cons » peut uniquement se connecter à la base et sélectionner des objets. « prod » peut se connecter, sélectionner des objets mais aussi créer ou modifier les données.
4.2 Préparation des schémas sous Postgres/Postgis
Comme mentionné ci-dessus dans la base sous Postgres/Postgis les répertoires et sous- répertoires sont représentés sous forme de schémas.
Actuellement les schémas ne sont pas tous créés, ils seront créés au fur à mesure de l‟import
des couches. Cette étape de création de schéma n‟a pas pu être intégrée directement dans le
script d‟import. De ce fait avant d‟exécuter le script d‟import Il faut vérifier si le schéma existe,
dans Postgres, dans le cas contraire il faut procéder à la création d‟un nouveau schéma.
La requête type de création des schémas en associant les droits selon les utilisateurs.
-- Schéma: nom du répertoire__nom du sous répertoire
DROP SCHEMA nom du répertoire__nom du sous répertoire
CREATE SCHEMA nom du répertoire__nom du sous répertoire AUTHORIZATION
gb_adm;
GRANT ALL ON SCHEMA nom du répertoire__nom du sous répertoire TO
geobase38_administrateurs;
GRANT USAGE ON SCHEMA nom du répertoire__nom du sous répertoire TO
geobase38_consultation;
GRANT USAGE ON SCHEMA nom du répertoire__nom du sous répertoire TO
geobase38_production;
Migration de la base de données de la DDT38 sous un SGBDS
21
4.3. Création de tables spécifique pour la bascule vers le serveur en
consultation
Afin de pouvoir basculer la geobase38 „production‟ stockée sous le SGBDS vers le serveur Consultation (T : GB_CONS), en automatisant le basculement de plusieurs tables à la fois, j‟ai eu recours à une méthode qui est en version teste au niveau de la DDT38. La méthode consiste à stocker les noms des tables à basculer dans des tables spécifiques pour ensuite les rappeler lors de l‟exécution du batch d‟export. De ce fait, deux tables ont été créées par l‟administrateur de données dans la base de
données « géobase38 » dans un schéma spécifique appelé « gestion_bdd ». Elles sont
nommées comme ceci : « export_immediat » pour la bascule immédiate et la table
« export_nocturne » pour la bascule quotidienne synchronisée. Des droits sont attribués à
ces deux tables afin qu‟elles soient modifiables, et mises à jour que par les utilisateurs qui
sont dans la catégorie « production » et bien évidement tous les droits pour l‟utilisateur de
catégorie « administrateur ».
Figure 7: tables spécifiques pour la bascule vers le serveur en consultation
Migration de la base de données de la DDT38 sous un SGBDS
22
5. Mise en place du batch d’import des couches
En effet, l‟objectif est de créer un fichier Batch permettant de se connecter à la base de
données en tant qu‟utilisateur ayant les droits d‟éditions sur les tables ciblées et d‟effectuer
l‟import des couches vers le serveur Postgres/Postgis. Le script est fait de façon à permettre
d'importer n'importe quelle couche dans n'importe quel répertoire, en modifiant juste les
variables d'environnement qui sont facilement repérables dans le script, (les variables
d'environnement facilitent la modification et évitent de modifier le code afin de faire face au
bug).
Les variables sont précédées par le terme "set" et on leur fait appel dans le batch en mettant
la variable entre le symbole %.
5.1. Le principe de fonctionnement du batch d’import
Nous présenterons ici les principales fonctions contenues dans le script « Batch» d‟import
5.1.1. L’outil d’import : ‘ogr2ogr.exe’
C‟est une fonction disponible dans la bibliothèque Open Source Geospatial Data Abstraction Library (Gdal). Il peut être utilisé pour importer des données simples en les convertissant dans plusieurs formats de fichiers tout en réalisant des opérations diverses pendant le processus comme la définition du système de coordonnées en sortie ou même la reprojection des objets pendant la translation ainsi que pour renommer la couche en sortie.
Les tables sont importées dans la base via une commande ogr2ogr.
Commande ogr2ogr pour l’import des couches
%OGR% -gt 65536 --config PGCLIENTENCODING UTF8 -f "PostgreSQL"
PG:"host=%host% user=%user% dbname=%base% port=%port% password=%pass%
active_schema=%schema%" -a_srs EPSG:2154 -skipfailures -nlt %geometrie% -nlt
PROMOTE_TO_MULTI -lco GEOMETRY_NAME=the_geom -lco DIM=2 -overwrite -nln
%nomTable% "%prod%\%nomCouche%"
Voici une petite explication des termes utilisés par l’utilitaire ogr2ogr :
-gt 65536 permet de grouper les géométries par groupe de 65 536 géométries avant de les commiter. Cela évite de commiter les géométries les unes après les autres. La valeur utilisée est celle préconisée par la documentation ; --config qui force l‟encodage des données sources (variable PGCLIENTENCODING) ; -lco définit le nom de la colonne géométrie (GEOMETRY_NAME) et les attributs textes en „character varying‟ (PRECISION) ; -f définit le format en sortie « PostgreSQL » ; host, user, dbase,password et active_schema sont les variables contenant les paramètres de connexion à la base sous postgres/ postgis (geobase38) ; -s_srs et –t_srs définissent le système de projection en Lambert 93 (EPSG : 2154) ; -nlt définit le type de géométrie en sortie ; -overwrite écrase les données de la table cible ; -nln définit le nom de la couche dans PostgreSQL.
N.B : les noms entre le symbole % sont des variables d‟environnement prédéfinies tout au
début du script.
Migration de la base de données de la DDT38 sous un SGBDS
23
rem--REQUETES DE SUPPRESSION DU CHAMP ogc_fid DANS LA TABLE
IMPORTER ET ATTRIBUTION DE CLE PRIMAIRE AU CHAMP id_map :Configuration_Cle
set /p champ_id_map= " VOULEZ VOUS CONFIGURER LE CHAMP id_map COMME
CLE PRIMAIRE? (o/n) : "
if "%champ_id_map%"=="o" %PSQL% -h %host% -p %port% -U %user% -d %base% -c
"ALTER table %schema%.%nomTable% DROP COLUMN ogc_fid" && %PSQL% -h
%host% -p %port% -U %user% -d %base% -c "ALTER table %schema%.%nomTable%
ADD CONSTRAINT id_map PRIMARY KEY (id_map)"
5.1.2. Les requêtes SQL
Suppression du champ ogc_fid et attribution de clé primaire au champ Id_map:
Lors de l‟import de la couche vers la base postgres/postgis, un nouveau champ appelé
« ogc_fid » se crée automatiquement et prend le rôle d‟une clé primaire qui correspond à un
index unitaire.
Afin de ne pas encombrer nos tables avec de nouveaux champs, il est préférable de définir
le champ «id_map» comme clé primaire vu qu‟il est utilisé déjà comme un identifiant dans
les couches. Mais étant donné que l‟unicité de ce champ n‟est pas forcement respectée nous
avons mis en place une requête qui vérifie si le champ « id_map » ne contenant pas de
doublons.
La requête de vérification de l’existence de doublons dans le champ « id_map »
rem VERIFICATION DE L'EXISTENCE DE DOUBLON DANS id_map
%PSQL% -h %host% -p %port% -U %user% -d %base% -c "SELECT Count(*), id_map
FROM %schema%.%nomTable% GROUP BY id_map HAVING Count(*) >1"
if "Count(*)"==1 goto Configuration_Cle
Si la condition d‟unicité existe on répond à la question suivante par oui afin de supprimer le
champ « ogc_fid ». Dans le cas contraire il faut répondre par un non et on continue l‟import.
Les modifications seront réalisées après l‟import.
La requête de suppression du champ ogc_fid dans la table importé et attribution de clé
primaire au champ « id_map »
Migration de la base de données de la DDT38 sous un SGBDS
24
rem – REQUETES D’AJOUT DES DROITS SUR LA NOUVELLE TABLE echo. echo -- AJOUT DES DROITS SUR LA TABLE %nomTable% %PSQL% -h %host% -p %port% -U %user% -d %base% -c "ALTER table %schema%.%nomTable% OWNER TO gb_adm; GRANT ALL ON TABLE %schema%.%nomTable% TO %base%_administrateurs; GRANT SELECT,INSERT,UPDATE,DELETE ON TABLE %schema%.%nomTable% TO %base%_production; GRANT SELECT ON TABLE %schema%.%nomTable% TO %base%_consultation"
Ajout des droits sur la nouvelle table
Pour que la table importée soit utilisable et modifiable par « les utilisateurs» j'ai rajouté des
droits en utilisant une requête SQL dans le script. Cette requête s'exécute automatiquement
lors de l‟exécution du script.
La requête d’ajout des droits sur la nouvelle table
Ajout de Champs de production dans la table importée (s’ils n’existent pas
dans la couche initiale).
Selon la norme COVADIS, certains champs doivent exister dans les couches stockées dans
le serveur production en respectant le nommage, le type ainsi que leurs tailles déjà
prédéfinis. Elles renseignent sur le nom du producteur, la date de la dernière modification.
Lors de l‟exécution du script une question s‟affiche pour le rajout ou pas des quarte champs
(gb_iduser, gb_ddmod, gb_datver, gb_verrou).
Voici la question qui s‟affiche dans la fenêtre d‟exécution (cmd): VOULEZ VOUS CREER
LES QUATRE CHAMPS SPECIFIQUES A GB_PROD ? (o/n)
Si on répond avec "o" qui signifie "oui" une requête sql s‟exécute automatiquement afin
d'ajouter les quarte champs.
La requête de création des quarte champs
rem --REQUETES CREATION DES QUATRES CHAMPS
echo.
echo -- AJOUT DE CHAMPS
set /p choix="VOULEZ VOUS CREER LES QAUTRES CHAMPS SPECIFIQUE A
GB_PROD (gb_iduser, gb_ddmod date, etc..) ? (o/n) :"
if "%choix%"=="o" (%PSQL% -h %host% -p %port% -U %user% -d %base% -c "ALTER
table %schema%.%nomTable% ADD column gb_iduser char(16) , ADD column gb_ddmod
date, ADD column gb_datver date, ADD column gb_verrou char(16)")
Migration de la base de données de la DDT38 sous un SGBDS
25
Le détail de ce script est précisé dans l’Annexe n° 2. Son élaboration est le fruit d‟un travail de recherche conséquent. En effet, nous avons été confrontés à de nombreux problèmes, tous résolus soit par des séries de tests ou par des aides externes. Ainsi, avant d‟arriver à un script abouti, des vérifications récurrentes de l‟efficacité de celui-ci ont été réalisées. Progressivement, le script est devenu de plus en plus élaboré afin de répondre à nos objectifs : • automatiser la tâche d‟import; • ne pas altérer les tables; • garder la projection en RGF93 ; Dès lors que ces objectifs furent respectés, nous avons pu passer à la tâche de la bascule vers le serveur en consultation sur lequel les données sont continuellement consultées.
Configuration de la table d’export nocturne
Afin de préparer la bascule synchronisée de ces tables vers la Géobase de consultation, j‟ai
ajouté deux commandes qui seront exécutables que si l‟on répond Oui à la fin du script.
Si on répond "oui", une requête sql s‟exécute automatiquement afin de configurer l‟export
nocturne dans la table que nous avons créée au préalable dans Postgres.
L‟export nocturne sera paramétré pour une exécution soit hebdomadaire ou bien
quotidienne selon le besoin de la DDT.
6. Mise en place du batch de bascule « export » des tables
Nous présenterons ici le fonctionnement du script « Batch» de la bascule des tables de la
géobase38 sous Postgres/Postgis vers le serveur en consultation (GB_CONS).
La bascule de la Géobase38 production installée dans Postgres/Postgis, vers le serveur en
consultation (T : GB_CONS), peut se faire de deux manières : soit en bascule nocturne
(Annexe n°3) soit en bascule immédiate (Annexe n°4).
La différence entre ces deux manipulations consiste dans la configuration de la bascule.
Soit une « Bascule Nocturne» directement après l‟import vers Postgres/Postgis si notre
couche est à jour et ne nécessite aucune modification au préalable. Dans ce cas de figure il
suffit d‟accepter de configurer la table « export_nocturne » lors de l‟exécution du batch
d‟import. En répondant avec la lettre « o » qui signifie « oui » (figure 8).
Figure 8: exemple de fenêtre d'exécution "cmd" pour la configuration de l'export nocturne
Migration de la base de données de la DDT38 sous un SGBDS
26
rem -- REQUETES DE CONFIGURATION DE L'EXPORT
echo -- CONFIGURER LA TABLE EXPORT NOCTURNE
set /p EXPORT= " VOULEZ VOUS CONFIGURER L'EXPORT AUTOMATIQUE
NOCTURNE VERS LA GEOBASE DE CONSULTATION? (o/n) : "
if "%EXPORT%"=="o" (%PSQL% -h %host% -p %port% -U %user% -d %base% -c
"INSERT INTO gestion_bdd.export_nocturne (nom_table,nom_schema) VALUES
(lower('%nomTable%'),lower('%schema%'))")
La requête SQL pour configurer la table d’export nocturne
Dans ce cas la bascule vers le serveur en consultation va se faire automatiquement selon la
programmation du planificateur de tâche.
La planification de cette tâche est réalisée par l‟administrateur des données, monsieur
Christophe Badol, responsable du service SIGC.
Pour le cas de bascule immédiate, la configuration de la table « export_immediat » est
intégrée dans le batch de la bascule immédiate.
La requête SQL pour configurer la table d’export immédiat
rem CONFIGURATION DE LA TABLE BASCULE IMMEDIAT
echo Configuration de la table bascule immediat
%PSQL% -h %host% -p %port% -U %user% -d %base% -c "INSERT INTO gestion_bdd.bascule_immediat (nom_table,nom_schema) VALUES (lower('%nom_table%'),lower('%nom_schema%'))"
6.1. Le principe du fonctionnement du batch d’export
Pour la bascule vers le serveur en consultation, nous avons mis en place deux scripts.
Ces deux scripts contiennent les mêmes fonctions, la seule différence est que l‟export
nocturne sera synchronisé, comme une tâche qui sera exécutée automatiquement, tandis
que la bascule immédiate se fait en exécutant manuellement le script.
Les deux scripts exportent les tables en deux formats. Le format « Shp » et le format
« tab ».
Le batch d‟export comprend trois grandes parties que j‟explique brièvement ci-dessous
La première étape consiste à lister les tables, les champs ainsi que les schémas où
se trouvent les tables et ensuite les enregistrer dans un fichier .txt.
Migration de la base de données de la DDT38 sous un SGBDS
27
Pour l‟automatisation de cette liste voici la requête mise en place qui va générer un
fichier .txt :
La requête de création de la liste des tables à exporter
Echo Creation de la liste des tables a exporter...
rem creation de la liste des tables présentes dans la table Export_Nocturne :
"Liste_table_export_nocturne_%date%.txt"
%PSQL% -U %user% -d %base% -h %host% -p %port% -c "copy (SELECT
UPPER(table_name), UPPER(Split_part(export_nocturne.nom_schema,
'__',1)),UPPER(Split_part (export_nocturne.nom_schema, '__',2)),
string_agg(table_name||'.'||column_name,',') as appel_colonnes FROM
information_schema.columns, gestion_bdd.export_nocturne WHERE table_name =
nom_table and column_name not like 'ogc_fid' and column_name not like 'gb_iduser' and
column_name not like 'gb_ddmod' and column_name not like 'gb_datver' and column_name
not like 'gb_verrou' group by table_name, export_nocturne.nom_schema) to STDOUT" >
%cd%\Liste_table_export_nocturne_%date%.txt
Dans cette requête on demande de découper le schéma, où se trouve nos tables en deux en les séparant par un espace en utilisant la fonction « split », afin d‟avoir deux noms différents qui correspondent respectivement au nom du répertoire et sous-répertoire de stockage des couches exportées. Nous avons spécifié avec la fonction UPPER » d‟afficher ces noms en majuscule car dans notre base de stockage les noms des répertoires et sous-répertoire sont en majuscule. La fonction «string_agg », nous l‟avons utilisé pour agréger le nom de la table et nom de la colonne qui correspond, afin d‟associé à chaque colonne la table qui lui correspond. Pour chercher les noms des tables, colonnes et schéma, nous avons utilisé la fonction « from » .
Le schéma « information_schema » se trouve dans le catalogue de chaque base de données du serveur postgres . « Columns », est le nom de la table qui se trouve dans ce schéma, elle sera utilisée afin de récupérer les noms des colonnes pour chaque table. Au final nous avons un « fichier.txt » qui comprend les noms des schémas, tables et colonnes à exporter. Comme mentionné plus haut, la liste est faite de sorte à découper le schéma en deux parties pour correspondre au nom du répertoire et sous-répertoire de stockage après l‟export, suivi du nom de la table et enfin le nom des colonnes. Au final on se retrouve avec plusieurs lignes et dans chaque ligne on a quatre éléments séparés par une tabulation (en respectant l‟ordre hiérarchique).
Migration de la base de données de la DDT38 sous un SGBDS
28
echo Export de la couche...
echo.
echo **export de %%a.shp ...
SET PGCLIENTENCODING=UTF8
%OGR% -gt 65536 -f "ESRI Shapefile" %CONS%\%%b\%%c\%%a.shp -a_srs EPSG:2154 -overwrite -
skipfailues PG:"host=%host% user=%user% dbname=%base% password=%pass%" -sql "SELECT %%d
from %%b__%%c.%%a"
echo.
echo **export de %%a.tab ...
SET PGCLIENTENCODING=LATIN1
%OGR% -gt 65536 -f "Mapinfo File" %CONS%\%%b\%%c\%%a.TAB -a_srs %cd%\00-SHP_VERS_TAB.prj
-overwrite -skipfailues PG:"host=%host% user=%user% dbname=%base% password=%pass%" -sql
"SELECT %%d from %%b__%%c.%%a"
)
La deuxième partie du script comprend la boucle d‟export : Après la création de notre fichier.txt qui liste les tables à exporter nous avons associé 4 variables pour chaque élément. La boucle d’export rem --BOUCLE D'EXPORT DES FICHIERS DANS Liste_table_export_%date%.txt" for /f " tokens=1,2,3,4 " %%a in ('type %cd%\Liste_table_export_%date%.txt') do ( echo %%a echo %%b echo %%c echo %%d Nous avons associé : 1 : la variable a : qui correspond au nom de la couche en Majuscule. 2 : la variable b : correspond à la première partie du nom du schéma étant le nom du répertoire de la base de stockage après l‟export (base consultation). 3 : la variable c : correspond à la seconde partie du schéma étant le sous-répertoire de la base de stockage après l‟export (base consultation) . 4 : la variable d : correspond à la liste de tous les champs de la table La troisième partie du script consiste à l‟export des tables avec la commande
ogr2ogr. Nous avons réalisé deux commandes afin d‟exporter en format SHP et en format TAB.
La requête d’export des tables en format Shp et format « Tab »
Migration de la base de données de la DDT38 sous un SGBDS
29
7. La migration des données des documents d’urbanisme
Convaincu du levier que représente la numérisation des documents d‟urbanisme, l‟Etat, par l‟intermédiaire de la direction départementale des territoires de l‟Isère, s‟est associé à la démarche et à la procédure de numérisation, notamment en fournissant les documents en sa possession et en participant à la conduite du marché et à son exécution. L‟Etat souhaite impulser et participer à la dynamique départementale autour de la dématérialisation puis de la mise à jour permanente des documents d‟urbanisme numériques. Pour avoir des documents d‟urbanisme uniformisés par rapport à la norme COVADIS, la
DDT38 a mis en place un système de contrôle de ces documents avant de les intégrer dans
son patrimoine commun.
La migration des données des documents d‟urbanisme vient en continuité du contrôle de conformité. Une fois validées les données doivent être intégrées dans la Géobase.
Pour l‟import des données du document d‟urbanisme sous le serveur Postgres/Postgis il fallait établir un script spécifique pour introduire ces données en respectant le modèle conceptuel déjà mis en place par la COADIS. L‟existence du modèle conceptuel a permis d‟introduire facilement les contraintes affectées à certains champs.
Migration de la base de données de la DDT38 sous un SGBDS
30
Figure 9: modèle conceptuel des tables des documents d’urbanismes
Migration de la base de données de la DDT38 sous un SGBDS
31
7.1. Le principe du fonctionnement du batch d’import des documents
d’urbanisme
La procédure d‟import des couches des documents d‟urbanisme est la même que pour l‟import des autres couches, les spécificités concernent l‟ajout de certaines contraintes telles que la configuration de clé primaire pour le champ « id_map » avec la forme suivante : (code INSEE+ auto incrémentation) pour toutes les couches et une clé étrangère pour certaines couches. Ceci nous a amenés à réaliser un batch d‟import spécifique aux documents d‟urbanismes. (Annexe n° 5).
Figure 10: exemple des documents d'urbanismes dans Postgres/Postgis
Migration de la base de données de la DDT38 sous un SGBDS
32
7 .2. Contrôle des données des documents d’urbanisme après l’import
vers Postgres/Postgis
Suite à l‟import des données des documents d‟urbanisme (PLU, POS, CC) de la Geobase (GB_Prod) vers la base Postgis, j‟ai effectué certaines vérifications sur la qualité et l‟exactitude de ces données, afin d‟identifier les erreurs susceptibles d‟exister dans les objets des 14 tables importées. En effet, les valeurs saisies dans les champs ne respectant pas toutes les syntaxes définies dans le standard malgré que les tables soient bien structurées.
Certaines erreurs ont été détectées plus au moins lors de l'import. Telle que l'absence de certaines données signalées lors de la configuration de la clé étrangère pour certaines couches. Ou bien des objets non importés suite à des erreurs de l'encodage, signalés dans la fenêtre d'exécution du batch (cmd) lors de l'import.
Certaines erreurs dans les données attributaires pourraient bien être identifiées lors de l‟import des données en introduisant la contrainte de vérification(CHEK) et la contrainte de valeurs nulles interdites (NOT NULL). Mais cette démarche reste très laborieuse car elle nécessite une vérification des enregistrements, un par un, avant l‟import. De ce fait le plus simple et le plus pratique est de faire des requêtes de vérification après l‟import afin de détecter plus exactement les objets incohérents.
Afin d‟identifier et de corriger toutes les erreurs j‟ai réalisé les requêtes de vérification des données attributaires et des requêtes de vérification de la validité des données géométriques. Suite à l‟import des données des documents d‟urbanisme de la géobase (GB_Prod) vers la base Postgis (geobase38), nous allons effectuer certaines vérifications sur la qualité et l‟exactitude de ces données. Afin d‟identifier les erreurs susceptibles d‟exister dans les objets des tables importées.
7.2.1. Les requêtes pour le contrôle des données attributaires
Dans le but de corriger les incohérences j‟ai stocké les résultats des requêtes dans des vues
dans un schéma appelé «verif_plu» avec attribution des droits uniquement pour l‟utilisateur (gb_adm) propriétaire de la geobase 38. (Annexe n°6). Les requêtes sont effectuées pour
les vérifications suivantes :
- Vérification des occurrences pour les colonnes dont les valeurs sont prédéfinies et
dont la valeur vide est interdite ;
- Vérification des doublons pour les colonnes dont les valeurs doubles sont interdites,
cela concerne essentiellement les champs clé primaire ;
- Vérification de la forme des champs : consiste à vérifier si les enregistrements ont le
même nombre de caractères (par exemple code insee);
- Identifications des erreurs qui empêchent l‟attribution de clé étrangère: Lors de
l‟import des couches des documents d‟urbanisme, j‟ai associé directement des
contraintes de clés (clé étrangère et clé primaire) , mais un message d‟erreur s‟
affiche lors de l‟exécution de la requête d‟attribution de clé étrangère ;
- Confirmer si certaines données sont obsolètes.
Migration de la base de données de la DDT38 sous un SGBDS
33
7.2.2. Les requêtes pour le contrôle de la géométrie
La vérification de la géométrie se fait sur plusieurs critères. En premier lieu l‟identification
des objets sans géométrie, vérifier le type de la géométrie, ensuite la vérification de
l‟existence des doublons pour les géométries existantes et enfin, identifier la géométrie invalide, et la corriger si possible. (Annexe n°7).
8. Autres tâches assignées lors du stage
8.1. Guide utilisation du script d’import
J‟ai mis en place un guide pour l‟utilisation du script en commençant par l‟ouverture du
serveur Postgres via l‟interface pgAdmin, jusqu‟à l‟exécution du batch.(Annexe n°8).
8.2. Configuration de la connexion à Postgres/Postgis et de la mise à
jour des tables via QGIS
Afin de visualiser et de mettre à jour les tables importées vers Postgres/Postgis, il a été
choisi d‟utiliser le logiciel QGIS, il donne la possibilité de se connecter à une base Postgres
afin de visualiser et de modifier la donnée.
Pour cela il est nécessaire, une fois QGIS démarré, de se connecter au préalable à la
Geobase38 via l‟outil «d’ajout de table PostGis». Cet outil permet de lier notre espace de
travail à n‟importe quelle base de données Postgres.
Pour accompagner les deux géomaticiens du service SIGC pour se connecter à
Postgres/Postgis et mettre à jour les données via QGIS j‟ai mis en place un guide
utilisateur. (Annexe n°9).
8.3. Création de tables sous Postgres/Postgis
Après que la base ait complètement migré vers le SGBDS (Postgres/Postgis), il est impératif
de créer les tables directement dans ce système. Ceci m‟a amenée à mettre à disposition
des utilisateurs un mode d‟emploi, expliquant les deux possibilités de création de tables
sous le SGBDS, le paramétrage des contraintes sur certains champs (clé primaire, clé
étrangère…) et l‟attribution des droits sur l‟utilisation des tables (les droits sur les tables sont
toujours les mêmes selon les utilisateurs).
Méthode manuelle
J‟ai mis en place des imprimes écran des étapes de création et de paramétrage de
tables.
Figure 11: exemple de création de table sous Postgres
Migration de la base de données de la DDT38 sous un SGBDS
34
Requête en utilisant le langage SQL.
Voici un exemple de requête pour la création et le paramétrage des tables.
La requête de création des tables sous Postgres/Postgis
CREATE TABLE nom du schema.nom de la table
(
the_geom geometry(le type de couche,2154),
id_map character varying(20) NOT NULL PRIMARY KEY ,
libelle character varying(254),
insee character varying(5),
creation character varying(10),
departemen character varying(3),
type_etat character varying(1),
gb_iduser character(16),
gb_ddmod date,
gb_datver date,
gb_verrou character(16)
);
ALTER TABLE nom du schema.nom de la table
OWNER TO gb_adm;
GRANT ALL ON TABLE nom de la table.shp TO geobase38_administrateurs;
GRANT SELECT,INSERT,DELETE ON TABLE nom de la table.shp TO
geobase38_production;
GRANT SELECT ON TABLE nom de la table.shp TO geobase38_consultation;
Selon le type de couche que vous souhaitez créer vous modifiez la ligne suivante : the_geom geometry(MultiPolygon,2154): pour les couches polygone the_geom geometry(point,2154): pour les couches point the_geom geometry((MultiLineString,2154): pour les couches ligne Voici le résultat de la requête : Une table avec les champs vides à remplir à partir de QGIS.
Migration de la base de données de la DDT38 sous un SGBDS
35
8.4. Création de formulaire sous QGIS
Outre la migration de la base de données vers un SGBDS. J‟ai réalisé un guide pratique pour la création de formulaire pour les projets sous QGIS qui est l‟interface pour la visualisation des données enregistrées sous le SGBDS. En effet, la DDT profite de cette réorganisation sous QGIS pour mettre en place un système facilitant ainsi aux utilisateurs du SIG la saisie et l‟usage des données. Pour cela j‟ai mis en place des formulaires de saisie (menu déroulant) pour certains champs et un projet en version test regroupant les couches de base fréquemment utilisées tel que le plan cadastral informatisé issu des données de DGFIP et l‟ortho photo de l‟IGN en format (wmts). J‟ai mis en place un guide détaillé pour expliquer les étapes à suivre afin de mettre en place des formulaires pour les données en production. J‟ai proposé plusieurs méthodes pour le stockage du formulaire d‟une couche. La méthode retenue est choisie par rapport à la facilité du chargement du formulaire pour l‟utilisation dans une couche sous QGIS. Le second critère de choix du formulaire était basé sur la facilité de création. De ce fait la méthode retenue est l‟enregistrement du formulaire dans un fichier de style au format « .qml », afin de pouvoir le charger lorsqu‟on souhaite utiliser la couche hors du projet.
Voici en résumé les méthodes que j‟ai explorées avant de choisir la méthode qui s‟adapte
le mieux au besoin et au niveau de connaissance des utilisateurs. Pour mettre en place un
formulaire utilisable par tous les agents qui produisent de la donnée à la DDT38.
- En premier lieu j‟ai proposé un formulaire à stocker dans Postgres/Postgis, dans le but de
regrouper toutes les tables de la production dans le SGBDS.
Le premier inconvénient est que le formulaire s‟enregistre automatiquement dans une table
sous le schéma public. Vu le nombre important de formulaires à mettre en place cette
méthode est moins appréciée car elle peut mener à confondre les formulaires entre eux.
Ce qui est préférable est de stocker les formulaires dans les mêmes schémas où se
trouvent les couches. Pour répondre à cette particularité, j‟ai proposé de déplacer la table
vers le schéma correspondant. Cette pratique n‟est pas judicieuse car lors de l‟appel de ce
formulaire sous Qgis, ce dernier ne la trouve pas, car dans sa mémoire il a gardé le chemin
où la table est enregistrée la première fois.
- La deuxième contrainte de cette méthode est l‟obligation d‟ajouter des droits d‟usages à
chaque formulaire.
La seconde méthode porte sur l‟enregistrement du formulaire dans la base locale. Ceci est
une méthode très limitée car l‟usage du formulaire peut se faire juste sur l‟ordinateur sur
lequel le formulaire est créé.
Migration de la base de données de la DDT38 sous un SGBDS
36
Conclusion Le service SIG central de la DDT 38 se trouve au cœur d‟un vaste chantier mobilisant une grande partie des ressources du service. L‟optimisation des procédures, par l‟automatisation de la migration de leurs données SIG vers un Système de Gestion de Base de Données Spatiale (SGBDS) Postgres/Postgis, offre tant des avantages techniques que des conditions de travail faisant bénéficier tous les acteurs impliqués. C‟est donc ce à quoi je me suis attelée durant ces 5 mois au sein du SIG central de la DDT de Grenoble. Les résultats espérés ont été concluants pour une grande partie du stage. La création de script a été une de mes premières expériences de programmation et je suis satisfaite d‟avoir pu répondre aux attentes de ma mission sur ce point.
Le déploiement de nouveaux outils opensource comme QGIS ou Postgres représente un intérêt certain d‟un point de vue économique (licence libre) mais aussi en matière de gain en efficacité dans l‟administration des données du patrimoine de la DDT38. Cela soulève cependant le sujet de la formation des utilisateurs SIG à ces nouveaux outils, afin qu‟ils soient déployés plus largement au sein du SIG central et de l‟ensemble de la DDT.
Lors de la réalisation de la migration de la base de données j‟ai dû faire fasse à plusieurs
difficultés. Les difficultés étaient essentiellement d‟ordre technique.
Plusieurs erreurs suite à l‟utilisation de l‟utilitaire ogr2ogr. Ceci est dû à un mauvais paramétrage du logiciel QGIS lors de l‟installation. Comme le service SIGC ne possède pas les droits d‟installation, à chaque installation j‟étais contrainte d‟attendre le service informatique qui n‟est pas toujours disponible.
Certains bugs lors de l‟import des couches vers Postgres/Postgis, sont souvent dus à l‟encodage, car la base est paramétrée pour l‟encodage en UTF8 exigé par la norme COVADIS. Alors que les données sont souvent issues des données externes à la DDT38 ne respectant pas forcement cet encodage.
Le script de migration de la base de données est largement optimisable et pourrait s‟étendre à d‟autres organismes. Le résultat de mon travail répond d‟une façon satisfaisante à la demande de la DDT38. J‟ai pu, comme je l‟espérais, monter en compétence dans le domaine de la programmation. C‟est un domaine que je maitrisais peu et qui me semblait plus que nécessaire de développer pour arriver sur le marché du travail.
Dans la gestion de base de données tout comme en programmation, pouvoir travailler au sein d‟une infrastructure d‟information géographique, m‟a permis de prendre confiance en mes compétences techniques et ma capacité à m‟adapter. Venant du monde des sciences de l‟environnement, j‟ai pu avec plaisir m‟immerger dans celui de l‟informatique qui me paraissait encore lointain. Riche de cette expérience, je suis plus que motivée pour continuer sur la voie que j‟ai choisie, et vivre encore plus d‟expériences au travers de la géomatique.
Migration de la base de données de la DDT38 sous un SGBDS
37
Table des Figures
Figure 1: Les antennes de la DDT dans le département de l’Isère (Source : http://www.isere.gouv.fr) 9
Figure 2: La répartition de la base de données locale ( La Géobase) .................................................... 11
Figure 3: Schéma de la base de données dans le serveur actuel et dans le serveur Postgres/Postgis . 17
Figure 4: Exemple de représentation de la base sous Postgres/Postgis .............................................. 18
Figure 5: Exemple de représentation de la base sous la Géobase Production ..................................... 18
Figure 6: Exemple table Geometry Column .......................................................................................... 19
Figure 7: tables spécifique pour la bascule vers le serveur en consultation ......................................... 21
Figure 8: exemple de fenêtre d'exécution "cmd" pour la configuration de l'export nocturne............ 25
Figure 9: modèle conceptuel des tables des documents d’urbanismes ............................................... 30
Figure 10: exemple des documents d'urbanismes dans Postgres/Postgis............................................ 31
Figure 11: exemple de création de table sous Postgres……………………………………………………………… ……33
Migration de la base de données de la DDT38 sous un SGBDS
38
Bibliographie
Sites internet
http://www.postgis.fr/chrome/site/docs/workshop-foss4g/doc/loading_data.html http://mesange.educagri.fr/htdocs/sigea/supports/QGIS/distance/perfectionnement/M05_SQLBDD_gen_web/co/10_N2_Gestion_Tables_Postgis.html http://postgresql.developpez.com/cours/ http://www.postgresql.org/docs/9.3/static/app-psql.html http://docs.postgresql.fr/7.4/tutorial.html http://www.btsinfogap.org/cours/s2/ig2/triggers_postgres_plpgsql.html http://www.portailsig.org/content/fme-levons-le-voile http://www.isere.gouv.fr
http://www.certu.fr/
http://georezo.net/blog/inspire/2014/01/16/standards-covadis-ou-cnig-obligatoires/ http://archives.cnig.gouv.fr/Front/index.php?RID=120 http://cnig.gouv.fr/?page_id=2732
Ouvrages consultés
- Regina O.Obe, Leo S.Hsu, « Postgis in action », Manning,2ème édition, 643 p. - Mark Leslie, Paul Ramsey, “Introduction to postgis”, version 1.0, 24/10/13, 147 p. - Peter Shaw, « GIS, Succintly », Synfusion Inc, 2013, 106 p. -RONALD R. PLOW & RYAN K. STEPHENS, SQL, CAMPUSPRESS. 330PP, Mars 2001; 243p
Migration de la base de données de la DDT38 sous un SGBDS
39
ANNEXE N°1
Migration de la base de données de la DDT38 sous un SGBDS
40
ANNEXE N° 2
Batch d’import des couches vers Postgres/Psotgis
rem Realisée par Naima Beloucif version du 21/08/2015
rem =====================================
@echo off
rem =============================
rem -- CHEMIN VERS LA BASE PRODUCTION
color F2
rem -- IL FAUT RENSEIGNER LES PARAMETRES CI-DESSOUS EN MINISCULE:
rep, sousrep, nomTable, extension,
rem =============================
rem Indiquer le repertoire Géobase de destination (en miniscule)
set rep=nature_paysage_biodiversite
rem Indiquer le sous-repertoire Géobase de destination (en miniscule)
set sousrep=n_chasse
rem Indiquer le chemin vers la couche à importer (ne pas mettre de / à la fin du
chemin)
rem dans Gb_Prod lors d'une bascule de T:/ vers QGIS :
set prod=Q:\PRODUCTION\%rep%\%sousrep%
rem Indiquer le nom de la table (en miniscule)
set nomTable=l_poste_agrenage_p_038
rem Indiquer l'extension de la couche à importer (en miniscule)
set extension=shp
rem indiquer l'encodage source de la couche ( soit UTF8, soit LATIN1)
set PGCLIENTENCODING=UTF8
rem set PGCLIENTENCODING=LATIN1
rem indiquer le type de la geometrie de la couche soit MULTIPOLYGON, soit
MultiLineString, soit point
rem set geometrie=MULTIPOLYGON
rem set geometrie=MULTILINESTRING
set geometrie=POINT
Migration de la base de données de la DDT38 sous un SGBDS
41
rem ============================================================
rem -- A PARTIR D'ICI NE CHANGEZ AUCUN PARAMETRES DANS LE SCRIPT
rem ============================================================
rem --CHEMIN VERS:BASE POSTGRESQL
set host=D38-SOMA2
set user=gb_adm
set port=5432
set base=geobase38
set pass=gb_adm
rem =============================
set schema=%rep%__%sousrep%
set nomCouche=%nomTable%.%extension%
set PGPASSWORD=%pass%
rem =============================
rem --Chemin des application selon le bureau
rem poste bureau 228
set OGR="C:\OSGeo4W64\bin\ogr2ogr.exe"
set PSQL="C:\OSGeo4W64\bin\psql.exe"
rem =============================
rem --CODE IMPORT DES COUCHES
echo.
echo --IMPORT DES COUCHES OGR2OGR
echo Import de la couche %nomCouche%
%OGR% -gt 65536 --config PGCLIENTENCODING UTF8 -f "PostgreSQL"
PG:"host=%host% user=%user% dbname=%base% port=%port%
password=%pass% active_schema=%schema%" -a_srs EPSG:2154 -skipfailures -nlt
%geometrie% -nlt PROMOTE_TO_MULTI -lco GEOMETRY_NAME=the_geom -lco
DIM=2 -overwrite -nln %nomTable% "%prod%\%nomCouche%"
rem =============================
Migration de la base de données de la DDT38 sous un SGBDS
42
rem =============================
rem VERIFICATION DE L'EXISTENCE DE DOUBLON DANS id_map
%PSQL% -h %host% -p %port% -U %user% -d %base% -c "SELECT Count(*),
id_map FROM %schema%.%nomTable% GROUP BY id_map HAVING Count(*) >1"
if "Count(*)"==1 goto Configuration_Cle
rem =============================
rem -- REQUETES DE SUPPRESSION DU CHAMP ogc_fid dans la table importer et
attribution de clé primaire au champ Id_map.
:Configuration_Cle
set /p champ_id_map= " VOULEZ VOUS CONFIGURER LE CHAMP id_map
COMME CLE PRIMAIRE? (o/n) : "
if "%champ_id_map%"=="o" %PSQL% -h %host% -p %port% -U %user% -d
%base% -c "ALTER table %schema%.%nomTable% DROP COLUMN ogc_fid" &&
%PSQL% -h %host% -p %port% -U %user% -d %base% -c "ALTER table
%schema%.%nomTable% ADD CONSTRAINT id_map PRIMARY KEY (id_map)"
rem =============================
rem -- REQUETES D'AJOUT DES DROITS SUR LA NOUVELLE TABLE
echo -- AJOUT DES DROITS SUR LA TABLE %nomTable%
%PSQL% -h %host% -p %port% -U %user% -d %base% -c "ALTER table
%schema%.%nomTable% OWNER TO gb_adm; GRANT ALL ON TABLE
%schema%.%nomTable% TO %base%_administrateurs; GRANT
SELECT,INSERT,UPDATE,DELETE ON TABLE %schema%.%nomTable% TO
%base%_production; GRANT SELECT ON TABLE %schema%.%nomTable% TO
%base%_consultation"
rem =============================
rem --REQUETES CREATION DES QUATRES CHAMPS
echo.
echo -- AJOUT DE CHAMPS
set /p choix="VOULEZ VOUS CREER LES QAUTRES CHAMPS SPECIFIQUE A
GB_PROD (gb_iduser, gb_ddmod date, etc..) ? (o/n) :"
if "%choix%"=="o" (%PSQL% -h %host% -p %port% -U %user% -d %base% -c
"ALTER table %schema%.%nomTable% ADD column gb_iduser char(16) , ADD
column gb_ddmod date, ADD column gb_datver date, ADD column gb_verrou
char(16)")
rem =============================
Migration de la base de données de la DDT38 sous un SGBDS
43
rem -- REQUETES DE CONFIGURATION DE L'EXPORT
echo -- CONFIGURER LA TABLE EXPORT NOCTURNE
set /p EXPORT= " VOULEZ VOUS CONFIGURER L'EXPORT AUTOMATIQUE
NOCTURNE VERS LA GEOBASE DE CONSULTATION? (o/n) : "
if "%EXPORT%"=="o" (%PSQL% -h %host% -p %port% -U %user% -d %base% -c
"INSERT INTO gestion_bdd.export_nocturne (nom_table,nom_schema) VALUES
(lower('%nomTable%'),lower('%schema%'))")
echo.
echo FIN
pause
Migration de la base de données de la DDT38 sous un SGBDS
44
rem Realisée par Naima Beloucif version du 21/08/2015
rem =============================
@echo off
rem chemin du répertoire courant:
rem =============================
rem --CHEMIN VERS BASE POSTGRESQL
set host=D38-SOMA2
set user=gb_adm
set port=5432
set base=geobase38
set pass=gb_adm
set PGPASSWORD=gb_adm
rem =============================
rem --Chemin des application selon le bureau
rem poste bureau 228
set OGR="C:\OSGeo4W64\bin\ogr2ogr.exe"
set PSQL="C:\OSGeo4W64\bin\psql.exe"
rem =============================
rem date du jour sans "/"
set date=%date:/=%
rem =============================
rem --CHEMIN VERS GB_CONS :
set CONS=Q:/CONSULTATION
rem =============================
ANNEXE N° 3
Batch de la Bascule Nocturne
Migration de la base de données de la DDT38 sous un SGBDS
45
echo Creation de la liste des tables a exporter...
rem creation de la liste des tables présentes dans la table Export_Nocturne :
"Liste_table_export_nocturne_%date%.txt"
%PSQL% -U %user% -d %base% -h %host% -p %port% -c "copy (SELECT UPPER(table_name),
UPPER(Split_part(export_nocturne.nom_schema, '__',1)),UPPER(Split_part
(export_nocturne.nom_schema, '__',2)), string_agg(table_name||'.'||column_name,',') as
appel_colonnes FROM information_schema.columns, gestion_bdd.export_nocturne WHERE
table_name = nom_table and column_name not like 'ogc_fid' and column_name not like
'gb_iduser' and column_name not like 'gb_ddmod' and column_name not like 'gb_datver' and
column_name not like 'gb_verrou' group by table_name, export_nocturne.nom_schema) to
STDOUT" > %cd%\Liste_table_export_nocturne_%date%.txt
Pause
rem =============================
rem --BOUCLE D'EXPORT DES FICHIERS DANS Liste_table_export_nocturne_%date%.txt"
for /f " tokens=1,2,3,4 " %%a in ('type %cd%\Liste_table_export_nocturne_%date%.txt') do (
echo %%a
echo %%b
echo %%c
echo %%d
pause
echo Export de la couche...
echo.
echo **export de %%a.shp ...
SET PGCLIENTENCODING=UTF8
%OGR% -gt 65536 -f "ESRI Shapefile" %CONS%\%%b\%%c\%%a.shp -a_srs EPSG:2154 -overwrite
-skipfailues PG:"host=%host% user=%user% dbname=%base% password=%pass%" -sql "SELECT
%%d from %%b__%%c.%%a"
echo.
echo **export de %%a.tab ...
SET PGCLIENTENCODING=LATIN1
%OGR% -gt 65536 -f "Mapinfo File" %CONS%\%%b\%%c\%%a.TAB -a_srs %cd%\00-
SHP_VERS_TAB.prj -overwrite -skipfailues PG:"host=%host% user=%user% dbname=%base%
password=%pass%" -sql "SELECT %%d from %%b__%%c.%%a"
)
echo.
echo FIN
pause
Migration de la base de données de la DDT38 sous un SGBDS
46
Rem realise par Naima Beloucif version du 21/08/2015
@echo off
color F2
rem -- IL FAUT REMPLIR LES PARAMETRES CI DESSOUS EN MINISCULE: nomTable, schema,dans la
fênetre d'exécution
:nom_tabel_schema
set /p nom_table= Rentrer le nom de la table a exporter en miniscule:
set /p nom_schema= Rentrer le nom du schema ou se trouve la table a exporter en miniscule:
rem affichage des informations saisiées:
echo.
echo Le nom de la table est: %nom_table%
echo Le nom du schema : %nom_schema%
echo.
rem confirmation des informations saisiées:
set /p donnees= Confirmer les donnees saisiees ? (o/n) :
if "%donnees%"=="o" (goto donneesOK) else goto nom_tabel_schema
:donneesOK
rem =============================
rem --CHEMIN VERS BASE POSTGRESQL BASE D'ORIGINE
set host=D38-SOMA2
set user=gb_adm
set port=5432
set base=geobase38
set pass=gb_adm
set cons= Q:\CONSULTATION
set PGPASSWORD=%pass%
rem =============================
rem --Chemin des application selon le bureau
rem poste bureau 228
set OGR="C:\OSGeo4W64\bin\ogr2ogr.exe"
set PSQL="C:\OSGeo4W64\bin\psql.exe"
rem =============================
ANNEXE N°4
Batch de la Bascule immédiate
Migration de la base de données de la DDT38 sous un SGBDS
47
rem =============================
rem date du jour sans "/"
set date=%date:/=%
rem =============================
rem CONFIGURATION DE LA TABLE BASCULE IMMEDIAT
echo Configuration de la table bascule immediat
echo DEBUG %PSQL% -h %host% -p %port% -U %user% -d %base% -c "INSERT INTO
gestion_bdd.bascule_immediat (nom_table,nom_schema) VALUES
(lower('%nom_table%'),lower('%nom_schema%'))"
%PSQL% -h %host% -p %port% -U %user% -d %base% -c "INSERT INTO
gestion_bdd.bascule_immediat (nom_table,nom_schema) VALUES
(lower('%nom_table%'),lower('%nom_schema%'))"
rem =============================
rem Creation de la liste des données de la table présentes dans la table bascule_immediat :
"Liste_table_bascule_immediat_%date%.txt"
echo Creation de la liste des donnees de la table a exporter...
echo DEBUG %PSQL% -U %user% -d %base% -h %host% -p %port% -c "copy (SELECT
UPPER(table_name), UPPER(Split_part(bascule_immediat.nom_schema, '__',1)),UPPER(Split_part
(bascule_immediat.nom_schema, '__',2)), string_agg(table_name||'.'||column_name,',') as
appel_colonnes FROM information_schema.columns, gestion_bdd.bascule_immediat WHERE
table_name = nom_table and column_name not like 'ogc_fid' and column_name not like
'gb_iduser' and column_name not like 'gb_ddmod' and column_name not like 'gb_datver' and
column_name not like 'gb_verrou' group by table_name, bascule_immediat.nom_schema) to
STDOUT" > %cd%\Liste_table_bascule_immediat_%date%.txt
%PSQL% -U %user% -d %base% -h %host% -p %port% -c "copy (SELECT UPPER(table_name),
UPPER(Split_part(bascule_immediat.nom_schema, '__',1)),UPPER(Split_part
(bascule_immediat.nom_schema, '__',2)), string_agg(table_name||'.'||column_name,',') as
appel_colonnes FROM information_schema.columns, gestion_bdd.bascule_immediat WHERE
table_name = nom_table and column_name not like 'ogc_fid' and column_name not like
'gb_iduser' and column_name not like 'gb_ddmod' and column_name not like 'gb_datver' and
column_name not like 'gb_verrou' group by table_name, bascule_immediat.nom_schema) to
STDOUT" > %cd%\Liste_table_bascule_immediat_%date%.txt
pause
rem =============================
Migration de la base de données de la DDT38 sous un SGBDS
48
rem --BOUCLE D'EXPORT DES FICHIERS DANS Liste_table_bascule_immediat_%date%.txt"
for /f " tokens=1,2,3,4 " %%a in ('type %cd%\Liste_table_bascule_immediat_%date%.txt') do (
echo %%a
echo %%b
echo %%c
echo %%d
echo.
rem -- CODE D'EXPORT DE LA COUCHE EN FORMAT shp et TAB
echo Export de la couche...
echo.
echo **export de %%a.shp ...
SET PGCLIENTENCODING=UTF8
%OGR% -gt 65536 -f "ESRI Shapefile" %cons%\%%b\%%c\%%a.shp -a_srs EPSG:2154 -overwrite -
skipfailues PG:"host=%host% user=%user% dbname=%base% password=%pass%" -sql "SELECT
%%d from %%b__%%c.%%a"
echo.
echo **export de %%a.tab ...
SET PGCLIENTENCODING=LATIN1
%OGR% -gt 65536 -f "Mapinfo File" %cons%\%%b\%%c\%%a.TAB -overwrite -skipfailues
PG:"host=%host% user=%user% dbname=%base% password=%pass%" -sql "SELECT %%d from
%%b__%%c.%%a"
)
rem =============================
rem --SUPPRESSION DU FICHIER "LISTE_TABLE_ BASCULE_EXPORT_%DATE%.TXT"
del Liste_table_bascule_immediat_%date%.txt
rem --SUPPRESSION DES DONNES DANS LA TABLE "BASCULE IMMEDIAT"
%PSQL% -h %host% -p %port% -U %user% -d %base% -c "TRUNCATE TABLE
gestion_bdd.bascule_immediat"
echo.
echo FIN
pause
Migration de la base de données de la DDT38 sous un SGBDS
49
Rem realisé par Naima Beloucif version du 21/08/2015
@echo off
rem =============================
rem -- CHEMIN VERS LA BASE PRODUCTION
color F9
rem =============================
rem --CHEMIN VERS:BASE POSTGRESQL
set host=D38-SOMA2
set user=gb_adm
set port=5432
set base=geobase_test
set pass=gb_adm
set schema=public
set PGPASSWORD=%pass%
set
prod=C:\Users\adminseven\Desktop\travail_naima\AMENAGEMENT_URBANISME\N_ZONAGES_PLA
NIFICATION
set encodingclient=UTF8
rem =============================
rem --CHEMIN VERS UTILITAIRES
rem poste bureau 228
set OGR="C:\OSGeo4W64\bin\ogr2ogr.exe"
set PSQL="C:\OSGeo4W64\bin\psql.exe"
rem =============================
rem poste bureau 227
rem set OGR="C:\Program Files\QGIS\bin\ogr2ogr.exe"
rem set PSQL="C:\Program Files\QGIS\bin\psql.exe"
ANNEXE N°5
Batch d’import des documents d’urbanisme
Migration de la base de données de la DDT38 sous un SGBDS
50
rem =============================
rem --CODE IMPORT DES COUCHES
echo.
echo --IMPORT DES COUCHES OGR2OGR
echo Import des couches %nomCouche%
%OGR% -gt 65536 --config PGCLIENTENCODING UTF8 -f "PostgreSQL" PG:"host=%host%
user=%user% dbname=%base% port=%port% password=%pass% active_schema=%schema%" -
a_srs EPSG:2154 -skipfailures -nlt PROMOTE_TO_MULTI -lco GEOMETRY_NAME=the_geom -lco
DIM=2 -overwrite -nln N_N_DOCUMENT_URBA_038 "%prod%\N_DOCUMENT_URBA_038.SHP"
%OGR% -gt 65536 --config PGCLIENTENCODING UTF8 -f "PostgreSQL" PG:"host=%host%
user=%user% dbname=%base% port=%port% password=%pass% active_schema=%schema%" -
a_srs EPSG:2154 -skipfailures -nlt MULTIPOLYGON -nlt PROMOTE_TO_MULTI -lco
GEOMETRY_NAME=the_geom -lco DIM=2 -overwrite -nln N_N_DOCUMENT_URBA_COM_038
"%prod%\N_DOCUMENT_URBA_COM_038.SHP"
%OGR% -gt 65536 --config PGCLIENTENCODING UTF8 -f "PostgreSQL" PG:"host=%host%
user=%user% dbname=%base% port=%port% password=%pass% active_schema=%schema%" -
a_srs EPSG:2154 -skipfailures -lco GEOMETRY_NAME=the_geom -lco DIM=2 -overwrite -nln
N_N_ZONE_URBA_038 "%prod%\N_ZONE_URBA_038.SHP"
%OGR% -gt 65536 --config PGCLIENTENCODING UTF8 -f "PostgreSQL" PG:"host=%host%
user=%user% dbname=%base% port=%port% password=%pass% active_schema=%schema%" -
a_srs EPSG:2154 -skipfailures -nlt MULTIPOLYGON -nlt PROMOTE_TO_MULTI -lco
GEOMETRY_NAME=the_geom -lco DIM=2 -overwrite -nln N_N_SECTEUR_CC_038
"%prod%\N_SECTEUR_CC_038.SHP"
%OGR% -gt 65536 --config PGCLIENTENCODING UTF8 -f "PostgreSQL" PG:"host=%host%
user=%user% dbname=%base% port=%port% password=%pass% active_schema=%schema%" -
a_srs EPSG:2154 -skipfailures -nlt MULTIPOLYGON -nlt PROMOTE_TO_MULTI -lco
GEOMETRY_NAME=the_geom -lco DIM=2 -overwrite -nln N_N_PRESCRIPTION_SURF_038
"%prod%\N_PRESCRIPTION_SURF_038.SHP"
%OGR% -gt 65536 --config PGCLIENTENCODING UTF8 -f "PostgreSQL" PG:"host=%host%
user=%user% dbname=%base% port=%port% password=%pass% active_schema=%schema%" -
a_srs EPSG:2154 -skipfailures -nlt PROMOTE_TO_MULTI -lco GEOMETRY_NAME=the_geom -lco
DIM=2 -overwrite -nln N_N_PRESCRIPTION_PCT_038 "%prod%\N_PRESCRIPTION_PCT_038.SHP"
%OGR% -gt 65536 --config PGCLIENTENCODING UTF8 -f "PostgreSQL" PG:"host=%host%
user=%user% dbname=%base% port=%port% password=%pass% active_schema=%schema%" -
a_srs EPSG:2154 -skipfailures -nlt PROMOTE_TO_MULTI -lco GEOMETRY_NAME=the_geom -lco
DIM=2 -overwrite -nln N_N_PRESCRIPTION_LIN_038 "%prod%\N_PRESCRIPTION_LIN_038.SHP"
%OGR% -gt 65536 --config PGCLIENTENCODING UTF8 -f "PostgreSQL" PG:"host=%host%
user=%user% dbname=%base% port=%port% password=%pass% active_schema=%schema%" -
a_srs EPSG:2154 -skipfailures -nlt MULTIPOLYGON -nlt PROMOTE_TO_MULTI -lco
GEOMETRY_NAME=the_geom -lco DIM=2 -overwrite -nln N_N_INFO_SURF_038
"%prod%\N_INFO_SURF_038.SHP"
Migration de la base de données de la DDT38 sous un SGBDS
51
%OGR% -gt 65536 --config PGCLIENTENCODING UTF8 -f "PostgreSQL" PG:"host=%host%
user=%user% dbname=%base% port=%port% password=%pass% active_schema=%schema%" -
a_srs EPSG:2154 -skipfailures -nlt PROMOTE_TO_MULTI -lco GEOMETRY_NAME=the_geom -lco
DIM=2 -overwrite -nln N_N_INFO_PCT_038 "%prod%\N_INFO_PCT_038.SHP"
%OGR% -gt 65536 --config PGCLIENTENCODING UTF8 -f "PostgreSQL" PG:"host=%host%
user=%user% dbname=%base% port=%port% password=%pass% active_schema=%schema%" -
a_srs EPSG:2154 -skipfailures -nlt PROMOTE_TO_MULTI -lco GEOMETRY_NAME=the_geom -lco
DIM=2 -overwrite -nln N_N_INFO_LIN_038 "%prod%\N_INFO_LIN_038.SHP"
%OGR% -gt 65536 --config PGCLIENTENCODING UTF8 -f "PostgreSQL" PG:"host=%host%
user=%user% dbname=%base% port=%port% password=%pass% active_schema=%schema%" -
a_srs EPSG:2154 -skipfailures -nlt PROMOTE_TO_MULTI -lco GEOMETRY_NAME=the_geom -lco
DIM=2 -overwrite -nln N_N_HABILLAGE_SURF_038 "%prod%\N_HABILLAGE_SURF_038.SHP"
%OGR% -gt 65536 --config PGCLIENTENCODING UTF8 -f "PostgreSQL" PG:"host=%host%
user=%user% dbname=%base% port=%port% password=%pass% active_schema=%schema%" -
a_srs EPSG:2154 -skipfailures -nlt PROMOTE_TO_MULTI -lco GEOMETRY_NAME=the_geom -lco
DIM=2 -overwrite -nln N_N_HABILLAGE_PCT_038 "%prod%\N_HABILLAGE_PCT_038.SHP"
%OGR% -gt 65536 --config PGCLIENTENCODING UTF8 -f "PostgreSQL" PG:"host=%host%
user=%user% dbname=%base% port=%port% password=%pass% active_schema=%schema%" -
a_srs EPSG:2154 -skipfailures -nlt PROMOTE_TO_MULTI -lco GEOMETRY_NAME=the_geom -lco
DIM=2 -overwrite -nln N_N_HABILLAGE_LIN_038 "%prod%\N_HABILLAGE_LIN_038.SHP"
%OGR% -gt 65536 --config PGCLIENTENCODING UTF8 -f "PostgreSQL" PG:"host=%host%
user=%user% dbname=%base% port=%port% password=%pass% active_schema=%schema%" -
a_srs EPSG:2154 -skipfailures -nlt PROMOTE_TO_MULTI -lco GEOMETRY_NAME=the_geom -lco
DIM=2 -overwrite -nln N_N_HABILLAGE_TXT_038 "%prod%\N_HABILLAGE_TXT_038.SHP"
rem =============================
rem --creation cle primaire pour document urba
echo configurer idurba comme cle primaire pour la couche N_DOCUMENT_URBA_038
echo.
%PSQL% -h %host% -p %port% -U %user% -d %base% -c "ALTER table
%schema%.N_N_DOCUMENT_URBA_038 DROP COLUMN ogc_fid" && %PSQL% -h %host% -p
%port% -U %user% -d %base% -c "ALTER table %schema%.N_N_DOCUMENT_URBA_038 ADD
CONSTRAINT IDURBA PRIMARY KEY (IDURBA)"
rem --modification du id_map de toutes les couches et creation des id_map en format
38xxx_incrementation
Migration de la base de données de la DDT38 sous un SGBDS
52
echo creer un nouveau id_map pour toutes les couches en format 38xxx_incrementer
echo.
%PSQL% -h %host% -p %port% -U %user% -d %base% -c "ALTER table
%schema%.N_N_DOCUMENT_URBA_COM_038 ADD column id_map character varying(20)" &&
%PSQL% -h %host% -p %port% -U %user% -d %base% -c "update
%schema%.N_N_DOCUMENT_URBA_COM_038 set id_map=RPAD(idurba,5) || (ogc_fid)"
%PSQL% -h %host% -p %port% -U %user% -d %base% -c "ALTER table
%schema%.N_N_DOCUMENT_URBA_COM_038 DROP COLUMN ogc_fid"
%PSQL% -h %host% -p %port% -U %user% -d %base% -c "ALTER table
%schema%.N_N_DOCUMENT_URBA_COM_038 ADD CONSTRAINT id_map0 PRIMARY KEY (id_map)"
%PSQL% -h %host% -p %port% -U %user% -d %base% -c "ALTER table
%schema%.N_N_ZONE_URBA_038 ALTER column id_map type character varying(20)" && %PSQL% -
h %host% -p %port% -U %user% -d %base% -c "update %schema%.N_N_ZONE_URBA_038 set
id_map=RPAD(idurba,5) || (ogc_fid)"
%PSQL% -h %host% -p %port% -U %user% -d %base% -c "ALTER table
%schema%.N_N_ZONE_URBA_038 DROP COLUMN ogc_fid"
%PSQL% -h %host% -p %port% -U %user% -d %base% -c "ALTER table
%schema%.N_N_ZONE_URBA_038 ADD CONSTRAINT PK_id_map1 PRIMARY KEY (id_map)"
%PSQL% -h %host% -p %port% -U %user% -d %base% -c "ALTER table
%schema%.N_N_SECTEUR_CC_038 ALTER column id_map type character varying(20)" && %PSQL% -
h %host% -p %port% -U %user% -d %base% -c "update %schema%.N_N_SECTEUR_CC_038 set
id_map=RPAD(idurba,5) || (ogc_fid)"
%PSQL% -h %host% -p %port% -U %user% -d %base% -c "ALTER table
%schema%.N_N_SECTEUR_CC_038 DROP COLUMN ogc_fid"
%PSQL% -h %host% -p %port% -U %user% -d %base% -c "ALTER table
%schema%.N_N_SECTEUR_CC_038 ADD CONSTRAINT id_map12 PRIMARY KEY (id_map)"
%PSQL% -h %host% -p %port% -U %user% -d %base% -c "ALTER table
%schema%.N_N_PRESCRIPTION_SURF_038 ALTER column id_map type character varying(20)" &&
%PSQL% -h %host% -p %port% -U %user% -d %base% -c "update
%schema%.N_N_PRESCRIPTION_SURF_038 set id_map=(INSEE) ||(ogc_fid)"
%PSQL% -h %host% -p %port% -U %user% -d %base% -c "ALTER table
%schema%.N_N_PRESCRIPTION_SURF_038 DROP COLUMN ogc_fid"
%PSQL% -h %host% -p %port% -U %user% -d %base% -c "ALTER table
%schema%.N_N_PRESCRIPTION_SURF_038 ADD CONSTRAINT PK_id_map2 PRIMARY KEY (id_map)"
Migration de la base de données de la DDT38 sous un SGBDS
53
%PSQL% -h %host% -p %port% -U %user% -d %base% -c "ALTER table
%schema%.N_N_PRESCRIPTION_PCT_038 ALTER column id_map type character varying(20)" &&
%PSQL% -h %host% -p %port% -U %user% -d %base% -c "update
%schema%.N_N_PRESCRIPTION_PCT_038 set id_map=(INSEE) ||(ogc_fid)"
%PSQL% -h %host% -p %port% -U %user% -d %base% -c "ALTER table
%schema%.N_N_PRESCRIPTION_PCT_038 DROP COLUMN ogc_fid"
%PSQL% -h %host% -p %port% -U %user% -d %base% -c "ALTER table
%schema%.N_N_PRESCRIPTION_PCT_038 ADD CONSTRAINT PK_id_map3 PRIMARY KEY (id_map)"
%PSQL% -h %host% -p %port% -U %user% -d %base% -c "ALTER table
%schema%.N_N_PRESCRIPTION_LIN_038 ALTER column id_map type character varying(20)" &&
%PSQL% -h %host% -p %port% -U %user% -d %base% -c "update
%schema%.N_N_PRESCRIPTION_LIN_038 set id_map=(INSEE) ||(ogc_fid)"
%PSQL% -h %host% -p %port% -U %user% -d %base% -c "ALTER table
%schema%.N_N_PRESCRIPTION_LIN_038 DROP COLUMN ogc_fid"
%PSQL% -h %host% -p %port% -U %user% -d %base% -c "ALTER table
%schema%.N_N_PRESCRIPTION_LIN_038 ADD CONSTRAINT PK_id_map4 PRIMARY KEY (id_map)"
%PSQL% -h %host% -p %port% -U %user% -d %base% -c "ALTER table
%schema%.N_N_INFO_SURF_038 ALTER column id_map type character varying(20)" && %PSQL%
-h %host% -p %port% -U %user% -d %base% -c "update %schema%.N_N_INFO_SURF_038 set
id_map=(INSEE) ||(ogc_fid)"
%PSQL% -h %host% -p %port% -U %user% -d %base% -c "ALTER table
%schema%.N_N_INFO_SURF_038 DROP COLUMN ogc_fid"
%PSQL% -h %host% -p %port% -U %user% -d %base% -c "ALTER table
%schema%.N_N_INFO_SURF_038 ADD CONSTRAINT PK_id_map5 PRIMARY KEY (id_map)"
%PSQL% -h %host% -p %port% -U %user% -d %base% -c "ALTER table
%schema%.N_N_INFO_PCT_038 ALTER column id_map type character varying(20)" && %PSQL% -h
%host% -p %port% -U %user% -d %base% -c "update %schema%.N_N_INFO_PCT_038 set
id_map=(INSEE) ||(ogc_fid)"
%PSQL% -h %host% -p %port% -U %user% -d %base% -c "ALTER table
%schema%.N_N_INFO_PCT_038 DROP COLUMN ogc_fid"
rem %PSQL% -h %host% -p %port% -U %user% -d %base% -c "Create sequence
%schema%.sequence_info_pct" && %PSQL% -h %host% -p %port% -U %user% -d %base% -c
"update %schema%.N_INFO_PCT_038 set id_map=(INSEE)||'_'|| nextval('sequence_info_pct')"
%PSQL% -h %host% -p %port% -U %user% -d %base% -c "ALTER table
%schema%.N_N_INFO_PCT_038 ADD CONSTRAINT PK_id_map6 PRIMARY KEY (id_map)"
Migration de la base de données de la DDT38 sous un SGBDS
54
%PSQL% -h %host% -p %port% -U %user% -d %base% -c "ALTER table
%schema%.N_N_INFO_LIN_038 ALTER column id_map type character varying(20)" && %PSQL% -h
%host% -p %port% -U %user% -d %base% -c "update %schema%.N_N_INFO_LIN_038 set
id_map=(INSEE) ||(ogc_fid)"
%PSQL% -h %host% -p %port% -U %user% -d %base% -c "ALTER table
%schema%.N_N_INFO_LIN_038 DROP COLUMN ogc_fid"
%PSQL% -h %host% -p %port% -U %user% -d %base% -c "ALTER table
%schema%.N_N_INFO_LIN_038 ADD CONSTRAINT PK_id_map7 PRIMARY KEY (id_map)"
%PSQL% -h %host% -p %port% -U %user% -d %base% -c "ALTER table
%schema%.N_N_HABILLAGE_SURF_038 ALTER column id_map type character varying(20)" &&
%PSQL% -h %host% -p %port% -U %user% -d %base% -c "update
%schema%.N_N_HABILLAGE_SURF_038 set id_map=(INSEE) ||(ogc_fid)"
%PSQL% -h %host% -p %port% -U %user% -d %base% -c "ALTER table
%schema%.N_N_HABILLAGE_SURF_038 DROP COLUMN ogc_fid"
%PSQL% -h %host% -p %port% -U %user% -d %base% -c "ALTER table
%schema%.N_N_HABILLAGE_SURF_038 ADD CONSTRAINT PK_id_map8 PRIMARY KEY (id_map)"
%PSQL% -h %host% -p %port% -U %user% -d %base% -c "ALTER table
%schema%.N_N_HABILLAGE_PCT_038 ALTER column id_map type character varying(20)" &&
%PSQL% -h %host% -p %port% -U %user% -d %base% -c "update
%schema%.N_N_HABILLAGE_PCT_038 set id_map=(INSEE) ||(ogc_fid)"
%PSQL% -h %host% -p %port% -U %user% -d %base% -c "ALTER table
%schema%.N_N_HABILLAGE_PCT_038 DROP COLUMN ogc_fid"
%PSQL% -h %host% -p %port% -U %user% -d %base% -c "ALTER table
%schema%.N_N_HABILLAGE_PCT_038 ADD CONSTRAINT PK_id_map9 PRIMARY KEY (id_map)"
%PSQL% -h %host% -p %port% -U %user% -d %base% -c "ALTER table
%schema%.N_N_HABILLAGE_LIN_038 ALTER column id_map type character varying(20)" &&
%PSQL% -h %host% -p %port% -U %user% -d %base% -c "update
%schema%.N_N_HABILLAGE_LIN_038 set id_map=(INSEE) ||(ogc_fid)"
%PSQL% -h %host% -p %port% -U %user% -d %base% -c "ALTER table
%schema%.N_N_HABILLAGE_LIN_038 DROP COLUMN ogc_fid"
%PSQL% -h %host% -p %port% -U %user% -d %base% -c "ALTER table
%schema%.N_N_HABILLAGE_LIN_038 ADD CONSTRAINT PK_id_map10 PRIMARY KEY (id_map)"
%PSQL% -h %host% -p %port% -U %user% -d %base% -c "ALTER table
%schema%.N_N_HABILLAGE_TXT_038 ALTER column id_map type character varying(20)" &&
%PSQL% -h %host% -p %port% -U %user% -d %base% -c "update
%schema%.N_N_HABILLAGE_TXT_038 set id_map=(INSEE) ||(ogc_fid)"
%PSQL% -h %host% -p %port% -U %user% -d %base% -c "ALTER table
%schema%.N_N_HABILLAGE_TXT_038 DROP COLUMN ogc_fid"
%PSQL% -h %host% -p %port% -U %user% -d %base% -c "ALTER table
%schema%.N_N_HABILLAGE_TXT_038 ADD CONSTRAINT PK_id_map11 PRIMARY KEY (id_map)"
Migration de la base de données de la DDT38 sous un SGBDS
55
rem =============================
rem -- REQUETES D'attribution de clé étrangere 'champ IDURBA' aux deux couches
(N_ZONE_URBA_038 et N_DOCUMENT_URBA_COM_038)
rem echo attribution de cle etrangere 'champ IDURBA' aux deux couches (N_ZONE_URBA_038 et
N_DOCUMENT_URBA_COM_038)
%PSQL% -h %host% -p %port% -U %user% -d %base% -c "ALTER table
%schema%.N_N_ZONE_URBA_038 ADD CONSTRAINT fk_N_N_DOCUMENT_URBA_038_IDURBA
FOREIGN KEY (IDURBA) REFERENCES %schema%.N_N_DOCUMENT_URBA_038(IDURBA)"
%PSQL% -h %host% -p %port% -U %user% -d %base% -c "ALTER table
%schema%.N_N_DOCUMENT_URBA_COM_038 ADD CONSTRAINT
fk_N_N_DOCUMENT_URBA_038_IDURBA FOREIGN KEY (IDURBA) REFERENCES
%schema%.N_N_DOCUMENT_URBA_038(IDURBA)"
%PSQL% -h %host% -p %port% -U %user% -d %base% -c "ALTER table
%schema%.N_N_SECTEUR_CC_038 ADD CONSTRAINT fk_N_N_DOCUMENT_URBA_038_IDURBA
FOREIGN KEY (IDURBA) REFERENCES %schema%.N_N_DOCUMENT_URBA_038(IDURBA)"
rem =============================
rem -- REQUETES D'AJOUT DES DROITS SUR LA NOUVELLE TABLE
REM echo -- AJOUT DES DROITS SUR LA TABLE %nomTable%
%PSQL% -h %host% -p %port% -U %user% -d %base% -c "ALTER TABLE
%schema%.N_N_ZONE_URBA_038 OWNER TO postgres; GRANT ALL ON TABLE
%schema%.N_N_ZONE_URBA_038 TO postgres; GRANT ALL ON TABLE
%schema%.N_N_ZONE_URBA_038 TO admin; GRANT SELECT,INSERT,UPDATE,DELETE ON TABLE
%schema%.N_N_ZONE_URBA_038 TO gb_prod; GRANT SELECT ON TABLE
%schema%.N_N_ZONE_URBA_038 TO consultation"
rem %PSQL% -h %host% -p %port% -U %user% -d %base% -c "ALTER table
%schema%.%nomTable% OWNER TO gb_adm; GRANT ALL ON TABLE %schema%.%nomTable% TO
%base%_administrateurs; GRANT SELECT,INSERT,UPDATE,DELETE ON TABLE
%schema%.%nomTable% TO %base%_production; GRANT SELECT ON TABLE
%schema%.%nomTable% TO %base%_consultation"
echo.
echo FIN
pause
Migration de la base de données de la DDT38 sous un SGBDS
56
ANNEXE N° 6
I. Les requêtes pour le contrôle des données attributaires I.1.Vérification des occurrences pour les colonnes dont les valeurs sont prédéfinies et
dont la valeur vide est interdite :
I.1.1 table n_zone_urba_038
Requête pour les occurrences dans la colonne « typezone , qui ne devrait pas être (vide) et dont les valeurs possibles sont une nomenclature prédéfinie : (U, AUc, AUs, A, N, Nh, Nd).
CREATE OR REPLACE VIEW verif_plu.occurence_typezone_ZU AS
SELECT typezone,id_map
FROM amenagement_urbanisme__n_zonage_planification.n_n_zone_urba_038
where typezone not in ('U', 'AUc', 'AUs', 'A', 'N', 'Nh', 'Nd') GROUP BY typezone, id_map;
Le résultat de la requête fait ressortir 938 objets mal renseignés (des valeurs qui n‟existent
pas dans la nomenclature prédéfinie).
Requête pour les occurrences dans la colonne « destdomi ». Ce champ renseigne sur la vocation (l‟usage) principale de la zone. Il ne doit pas être vide et les valeurs possibles ont une nomenclature prédéfinie : ('00', '01', '02', '03', '04', '05', '07','08', '09', '10', '99').
CREATE OR REPLACE VIEW verif_plu.occurence_destdomi_ZU AS
SELECT
n_n_zone_urba_038.destdomi,
n_n_zone_urba_038.id_map
FROM amenagement_urbanisme__n_zonage_planification.n_n_zone_urba_038
where destdomi not in ('00', '01', '02', '03', '04', '05', '07','08', '09', '10', '99')
GROUP BY destdomi, id_map
order by destdomi;
Le résultat de la requête ressort 541 objets mal renseignés.
Requête de détection de valeurs nulles dans la colonne « libelle ». Elle comprend le nom court de la zone tel qu‟il apparaît sur le plan de zonage. Elle ne devrait pas être vide (null).
CREATE OR REPLACE VIEW verif_plu.libelle_ZU AS
SELECT
n_n_zone_urba_038.libelle,
n_n_zone_urba_038.id_map
FROM amenagement_urbanisme__n_zonage_planification.n_n_zone_urba_038
where libelle is null ;
La requête ne renvoie aucune ligne de ce fait le champ « libelle » est correct.
Migration de la base de données de la DDT38 sous un SGBDS
57
I.1.2 table n_document_urba_038
Le champ « idurba » ne devrait pas être nul. Cette contrainte est vérifiée lors de l‟import car
ce champ est prédéfini comme clé primaire au moment de l‟import. De ce fait il est
systématiquement non null. Il est composé du code INSEE et de la date d‟approbation du
document opposable.
Requête pour les occurrences dans la colonne « etat ». Elle renseigne sur l‟état juridique du document d‟urbanisme. Celle-ci ne devrait pas être (vide) et les valeurs possibles ont une nomenclature prédéfinie : (03, 05).
La valeur 03 indique que le document est opposable et le 05 indique que le document a été
remplacé.
CREATE OR REPLACE VIEW verif_plu.occurence_etat_DU AS
select etat,idurba
from amenagement_urbanisme__n_zonage_planification.n_n_document_urba_038
where etat not in ('03', '05')
group by etat,idurba
order by etat;
La requête ne renvoie aucune ligne de ce fait le champ «etat» est bien renseigné ( pas
d‟erreur de saisie).
Requête de vérification de l‟existence des valeurs vides dans le champ « datappro ». Cet attribut ne devrait pas être null au moment où le document est opposable. Il correspond à la date d‟approbation de la dernière procédure administrative ayant fait évoluer le document d‟urbanisme. Cette date correspond à la date d‟approbation du document intervenue suite à une procédure de révision, d‟élaboration, de modification, de mise à jour ou de mise en compatibilité (et même si elle ne concerne que la partie écrite du règlement).
CREATE OR REPLACE VIEW verif_plu.datappro_DU AS
SELECT
datappro,idurba
FROM amenagement_urbanisme__n_zonage_planification.n_n_document_urba_038
where datappro is null ;
Aucune erreur n‟a été détectée.
Requête de vérification de l‟existence des valeurs vides dans le champ «datefin» si l‟état du document prend la valeur « remplacé » (le terme remplacé est signalé dans le champ «etat» par le nombre „05‟).
Voici une requête qui fait ressortir les enregistrements null dans «datefin» lorsque l‟etat du
document est = 05 (remplacé)
SELECT
datefin, etat
FROM amenagement_urbanisme__n_zonage_planification.n_n_document_urba_038
where datefin is null and etat ='05'
La requête ne renvoie aucune erreur
Migration de la base de données de la DDT38 sous un SGBDS
58
Dans le champ «etat», la valeur 05 est ambiguë car elle signifie que le document est
remplacé, dans ce cas les tables (couches) géographiques ne devraient pas contenir des
données concernant le document.
Requête pour vérifier si les données confédérées comme remplacées n‟existent plus dans la couche zone_urba. Pour cette vérification la requête consiste à vérifier
s‟il existe des idurba dans la couche « zone_urba » représentant les données dont
l‟etat est marqué comme remplacé (05)
La requête ne renvoie aucune erreur.
select n_n_zone_urba_038.idurba as idurba_zone, n_n_document_urba_038.idurba,
n_n_document_urba_038.etat
from amenagement_urbanisme__n_zonages_planification.n_n_document_urba_038,
amenagement_urbanisme__n_zonages_planification.n_n_zone_urba_038
where n_n_document_urba_038.etat='05' and
n_n_document_urba_038.idurba=n_n_zone_urba_038.idurba
I.1.3 table n_prescription_surf_038
Requête pour les occurrences dans la colonne « type ». Ce champ renseigne sur le type de la prescription dans une liste prédéfinie. Celle-ci ne devrait pas être (vide) et les valeurs possibles ont une nomenclature prédéfinie : (01,02,03,04,05,06,07,08,09,1011,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,99). chaque valeur correspond à une zone, espace, terrain ou secteur bien défini.
CREATE OR REPLACE VIEW verif_plu.occurence_typepsc_PS AS
SELECT
typepsc,id_map
FROM amenagement_urbanisme__n_zonage_planification.n_n_prescription_surf_038
where typepsc not in
('01','02','03','04','05','06','07','08','09','10','11','12','13','14','15','16','17','18','19','20','21','22','23',
'24','25','26','27','99') GROUP BY typepsc,id_map
order by typepsc;
La requête renvoie 30 erreurs.
I.1.4 table n_secteur_cc_038
Requête de vérification d‟occurrence dans la colonne « typesect »
CREATE OR REPLACE VIEW verif_plu.occurence_typesect_CC AS
SELECT
typesect,id_map
FROM amenagement_urbanisme__n_zonage_planification.n_n_secteur_cc_038
where typesect not in ('01','02','03','99') GROUP BY typesect,id_map
order by typesect;
La requête renvoie 24 lignes d‟erreurs. (Des lignes vides).
Migration de la base de données de la DDT38 sous un SGBDS
59
Requête de vérification d‟occurrence dans la colonne «destdomi»
CREATE OR REPLACE VIEW verif_plu.occurence_destdomi_CC AS
SELECT
destdomi,id_map
FROM amenagement_urbanisme__n_zonage_planification.n_n_secteur_cc_038
where destdomi not in ('00','01','02','07','08','99') GROUP BY destdomi,id_map
order by destdomi;
Requête de vérification de l‟existence des valeurs vides dans le champ « libelle»
CREATE OR REPLACE VIEW verif_plu.libelle_CC AS
SELECT libelle,id_map
FROM amenagement_urbanisme__n_zonage_planification.n_n_secteur_cc_038
where libelle is null ;
I.2.Vérification des doublons pour les colonnes dont les valeurs doubles sont
interdites :
Cela concerne essentiellement les champs « clé primaire ».
I.2.1 table n_zone_urba_038
Requête de vérification des doublons selon le id_map, insee create or replace view verif_plu.doublon_id_map_ZU as
select id_map, insee, count(insee) as doublon from
amenagement_urbanisme__n_zonage_planification.n_n_zone_urba_038 group by id_map,
insee having count (insee)>1;
La requête ne renvoie aucune ligne. De ce fait le champ id_map n‟a pas de doublon.
Requête vérification des doublons à partir de la colonne «insee »
CREATE OR REPLACE VIEW verif_plu.nb_id_urba_par_insee AS
SELECT n_n_zone_urba_038.insee,
n_n_zone_urba_038.datappro
FROM amenagement_urbanisme__n_zonages_planification.n_n_zone_urba_038
GROUP BY n_n_zone_urba_038.insee, n_n_zone_urba_038.datappro
ORDER BY n_n_zone_urba_038.insee, n_n_zone_urba_038.datappro;
CREATE OR REPLACE VIEW verif_plu.doublons_n_zone_urba AS
SELECT nb_id_urba_par_insee.insee,
count(nb_id_urba_par_insee.insee) AS count
FROM verif_plu.nb_id_urba_par_insee
GROUP BY nb_id_urba_par_insee.insee HAVING count(nb_id_urba_par_insee.insee) > 1;
La requête renvoie 2 doublons
Migration de la base de données de la DDT38 sous un SGBDS
60
I.2.2 table n_document_urba_038
Requête de vérification des doublons selon le id_map
create or replace view verif_plu.doublon_id_map_DU as
select id_map, count(id_map) as doublon from
amenagement_urbanisme__n_zonage_planification.n_n_document_urba_038 group by
id_map having count (id_map)>1;
Requête d‟identification de doublon et d‟erreurs de saisie ( idurba=idurbaprec). Cette requête permet d‟identifier les erreurs de saisie dans les deux colonnes « idurba »
et «idurbaprec», car elles ont la même structure (insee_date). La différence est dans la
date d‟approbation. Car la colonne « idurba » renseigne sur le dernier document
opposable et la colonne « idurbaprec » renseigne sur le document précédent devenu
obsolète.
create or replace view verif_plu.idurba_egal_idurbaprec_DU as
select idurbaprec ,idurba, count(idurba) as doublon from
amenagement_urbanisme__n_zonage_planification.n_n_document_urba_038 where
idurba= idurbaprec group by idurbaprec, idurba
I.2.3 table n_prescription_surf_038
Requête de vérification de l‟existence de doublon selon id_map
create or replace view verif_plu.id_map_PS as
select id_map, count(id_map) as doublon from
amenagement_urbanisme__n_zonage_planification.n_n_prescription_surf_038 group by
id_map having count (id_map)>1;
La requête ne retourne aucune ligne. Absence de doublon.
Requête de vérification de l‟existence de doublon à partir du champ insee
create or replace view verif_plu.nb_insee_ps as
SELECT n_n_prescription_surf_038.insee,
n_n_prescription_surf_038.datappro
FROM amenagement_urbanisme__n_zonages_planification.n_n_prescription_surf_038
GROUP BY n_n_prescription_surf_038.insee,n_n_prescription_surf_038.datappro
having count(n_n_prescription_surf_038.insee)>1;
CREATE OR REPLACE VIEW verif_plu.doublons_n_secteur_cc AS
SELECT nb_insee_ps.insee,
count(nb_insee_ps.insee) AS count
FROM verif_plu.nb_insee_ps
GROUP BY nb_insee_ps.insee
HAVING count(nb_insee_ps.insee) > 1;
Migration de la base de données de la DDT38 sous un SGBDS
61
I.2.4 table n_secteur_cc_038
Requête de vérification de l‟existence de doublon selon id_map
Create or replace view verif_plu.doublon_id_map_CC as
select id_map, count(id_map) as doublon from
amenagement_urbanisme__n_zonage_planification.n_n_secteur_cc_038 group by id_map
having count (id_map)>1;
La requête ne retourne aucune ligne. Absence de doublon.
Requête de vérification de l‟existence de doublon à partir du champ insee
CREATE OR REPLACE VIEW verif_plu.nb_id_urba_par_insee_cc AS
SELECT n_n_secteur_cc_038.insee,
n_n_secteur_cc_038.datappro
FROM amenagement_urbanisme__n_zonages_planification.n_n_secteur_cc_038
GROUP BY n_n_secteur_cc_038.insee, n_n_secteur_cc_038.datappro
ORDER BY n_n_secteur_cc_038.insee, n_n_secteur_cc_038.datappro;
CREATE OR REPLACE VIEW verif_plu.doublons_n_secteur_cc AS
SELECT nb_id_urba_par_insee_cc.insee,
count(nb_id_urba_par_insee_cc.insee) AS count
FROM verif_plu.nb_id_urba_par_insee_cc
GROUP BY nb_id_urba_par_insee_cc.insee
HAVING count(nb_id_urba_par_insee_cc.insee) > 1;
La requête renvoie une erreur.
I .3.Vérification de la forme des champs
Consiste à vérifier si les enregistrements ont le même nombre de caractères par exemple
code insee, idurba…… I.3.1 table n_zone_urba_038
Requête de vérification du format du champ insee
create or replace view verif_plu.insee_fromat_ZU as
select insee from amenagement_urbanisme__n_zonage_planification.n_n_zone_urba_038
where insee not like '38___'
La requête ne renvoie aucune ligne de ce fait il n'y a pas d‟erreur de saisie.
Requête de vérification du format du champ idurba
create or replace view verif_plu.idurba_fromat_ZU as
select idurba from amenagement_urbanisme__n_zonage_planification.n_n_zone_urba_038
where idurba not like '38____________'
La requête ne renvoie aucune ligne.
Migration de la base de données de la DDT38 sous un SGBDS
62
I.3.2 table n_document_urba_038
Requête de vérification du format du champ idurba
create or replace view verif_plu.idurba_fromat_DU as
select idurba from
amenagement_urbanisme__n_zonage_planification.n_n_document_urba_038 where idurba
not like '38____________'
La requête ne renvoi aucune ligne.
Requête de vérification du format du champ id_map
create or replace view verif_plu.id_map_fromat_DU as
select id_map from
amenagement_urbanisme__n_zonage_planification.n_n_document_urba_038 where
id_map not like '38___'
La requête renvoie une ligne d‟erreur
Requête de vérification du format du champ idurbaprec
J‟ai associé à la requête le champ « id_map » afin de distinguer facilement où se trouve
l‟erreur afin de la corriger.
create or replace view verif_plu.idurbaprec_fromat_DU as
select id_map, idurbaprec from
amenagement_urbanisme__n_zonage_planification.n_n_document_urba_038 where
idurbaprec not like '38____________'
La requête renvoie plusieurs lignes d‟erreurs.
Requête de vérification du format du champ siren
create or replace view verif_plu.siren_fromat_DU as
select id_map, siren from
amenagement_urbanisme__n_zonage_planification.n_n_document_urba_038 where siren
not like '2138_____'
La requête renvoi 9 lignes d‟erreurs
I.3.3 table n_prescription_surf_038
Requête de vérification du format du champ insee
create or replace view verif_plu.insee_fromat_PS as
select insee from amenagement_urbanisme__n_zonage_planification.n_n_zone_urba_038
where insee not like '38___'
La requête ne renvoie aucune ligne d‟erreur.
Migration de la base de données de la DDT38 sous un SGBDS
63
I.3.4 table n_secteur_cc_038
Requête de vérification du format du champ insee
create or replace view verif_plu.insee_fromat_CC as
select insee from
amenagement_urbanisme__n_zonage_planification.n_n_secteur_cc_038 where
insee not like '38___'
La requête ne renvoie aucune ligne d‟erreur.
I.4 Identifications des erreurs qui empêchent l’attribution de la colonne (idurba)
comme clé étrangère:
Lors de l‟import des couches des documents d‟urbanisme, j‟ai associé directement des contraintes de clés (clé étrangère et clé primaire) , mais un message d‟erreur s‟ affiche lors
de l‟exécution de la requête d‟attribution de clé étrangère (idurba) . (les «idruba » suivants
ne sont pas dans le champ (idurba) de la table n_document... qui contient la clé primaire)
Pour savoir d‟où vient le problème j‟ai réalisé deux requêtes.
I.4.1. table n_zone_urba_038
Requête pour vérifier les données « idurba » qui existent dans table « zone_urba » et qui
n‟existent pas dans la table « document_urba ». Leurs existences empêchent la création de
clé étrangère (idurba) dans la table « zone_urba ».
Cette requête nous renseigne sur l‟existence des données qui sont peut être obsolètes
Requête pour vérifier les enregistrements « idurba » qui existent dans table
« zone_urba » et qui n‟existent pas dans la table «document_urba »
create or replace view verif_plu.idurba_zone_urba_non_exist_doc_urba as
select distinct Z.idurba as zone_urba from
amenagement_urbanisme__n_zonage_planification.n_n_zone_urba_038 Z
where not exists( select D.idurba as document_urba from
amenagement_urbanisme__n_zonage_planification.n_n_document_urba_038 as D where
z.idurba= D.idurba)
Migration de la base de données de la DDT38 sous un SGBDS
64
La requête renvoie deux lignes d‟erreurs.
Pour confirmer si ces « idurba » sont obsolètes, j‟ai comparé les « idurba » de cette table
avec les « idurbaprec » de la table « document_urba ».
Cette requête peut aussi nous renseigner sur l‟existence des idurba obsolètes mais qui ne
sont pas effacés dans idurba de la table zone_urba
create or replace view verif_plu.compar_idurba_zone_urba_idurbaprec_document_urba as
select distinct Z.idurba as zone_urba, D.idurbaprec as document_urba from
amenagement_urbanisme__n_zonage_planification.n_n_document_urba_038 D,
amenagement_urbanisme__n_zonage_planification.n_n_zone_urba_038 Z
where Z.idurba=D.idurbaprec
la requête ressort 5 lignes d‟erreur
I.4.2 table n_document_urba_038
Requête pour vérifier l‟existence des mêmes idurba dans le champ idurbaprec et
dans idurba de la table document_urba
create or replace view verif_plu.compar_idurba_idurbaprec_DU as
select distinct idurba, idurbaprec from
amenagement_urbanisme__n_zonage_planification.n_n_document_urba_038
where idurba=idurbaprec
Migration de la base de données de la DDT38 sous un SGBDS
65
I.4.3 table n_secteur_cc_038
Requête pour vérifier les enregistrements « idurba » qui existent dans la table secteur
et qui n‟existent pas dans document_urba
create or replace view verif_plu.idurba_CC_non_exist_doc_urba as
select distinct c.idurba as secteur_cc from
amenagement_urbanisme__n_zonage_planification.n_n_secteur_cc_038 as c
where not exists( select D.idurba as document_urba from
amenagement_urbanisme__n_zonage_planification.n_n_document_urba_038 as D where
c.idurba= D.idurba)
La requête renvoie une ligne d‟erreur.
Requête pour vérifier l‟existence des mêmes enregistrements « idurba » dans le
champ » idurbaprec » de la table (document_urba) et dans le champ « idurba » de la
table secteur.
select distinct c.idurba as secteur_cc , D.idurbaprec as document_urba from
amenagement_urbanisme__n_zonage_planification.n_n_document_urba_038 as D,
amenagement_urbanisme__n_zonage_planification.n_n_secteur_cc_038 as c
where c.idurba=D.idurbaprec
Aucune ligne d‟erreur
Migration de la base de données de la DDT38 sous un SGBDS
66
ANNEXE N°7
II. Les requêtes pour le contrôle de la géométrie
La vérification de la géométrie se fait sur plusieurs critères.
En premier lieu, identifier les objets sans géométrie, le type de la géométrie, ensuite
l‟existence des doublons pour les géométries existantes, identifier la géométrie invalide et
la corriger si possible, identifier les géométries moins de 4 points. II.1. Vérification de l’absence de géométrie dans certains enregistrements
Requête pour identifier des enregistrements sans géométrie.
Nos requêtes vont se baser sur le champ de géométrie appelé dans nos tables (the_geom)
II.1.1 table n_zone_urba_038
Requête d‟indentifications des enregistrements sans géométrie
select the_geom, idurba, id_map from
amenagement_urbanisme__n_zonage_planification.n_zone_urba_038
where the_geom is null
IL existe aussi une autre requête qui affiche les objets sans géométrie :
create or replace view verif_plu.sansgeom_ZU as
select id_map, idurba, ST_geometryType(the_geom) from
amenagement_urbanisme__n_zonage_planification.n_n_zone_urba_038 where
ST_geometryType (the_geom) is null
La requête renvoie 41 enregistrements sans géométrie. Ils appartiennent à la même
commune(38567)
II.1.2 table n_prescription_surf_038
Requête d‟indentifications des enregistrements sans géométrie
create or replace view verif_plu.absen_geom_PS as
select the_geom, id_map from
amenagement_urbanisme__n_zonage_planification.n_n_prescription_surf_038
where the_geom is null
La requête ne renvoie aucune ligne (pas d‟absence de géométrie)
Migration de la base de données de la DDT38 sous un SGBDS
67
II.2. vérifier le type de la géométrie
II.2.1 table n_zone_urba_038
Requête d‟identification des enregistrements qui ne sont pas des multipolygones
select idurba, ST_geometryType(the_geom) from
amenagement_urbanisme__n_zonage_planification.n_n_zone_urba_038 where the_geom
not like 'multipolygone';
II.2.2 table n_prescription_surf_038
Requête d‟identification des enregistrements qui ne sont pas des multipolygones
select id_map, ST_geometryType(the_geom) from
amenagement_urbanisme__n_zonage_planification.n_n_prescription_surf_038 where
ST_geometryType(the_geom) not like 'ST_MultiPolygon'
II.3. vérifier l’existence des géométries en double
II.3.1 table n_zone_urba_038
Requête d‟identification des enregistrements qui ont des géométries doubles
select insee, count(*),the_geom
from amenagement_urbanisme__n_zonage_planification.n_n_zone_urba_038
group by the_geom,insee having count (the_geom)>1
Impossible d‟utiliser le « id_map » car chaque enregistrement a un « id_map » différent, de
ce fait il n‟affiche aucun résultat ;
II.3.2 table n_prescription_surf_038
Requête d‟identification des enregistrements qui ont des géométries doubles.
Pour identifier facilement les objets doubles, j‟ai utilisé le libellé. Car avec “id_map” on n‟ a
aucun résultat.
create or replace view verif_plu.doubl_geom_PS as
select insee, count(*),the_geom, libelle
from amenagement_urbanisme__n_zonage_planification.n_n_prescription_surf_038
group by the_geom,insee, libelle having count (the_geom)>1
Il ya eu 1849 lignes qui représentent des doublons.
Migration de la base de données de la DDT38 sous un SGBDS
68
II.3.3 table n_secteur_cc_038
Requête d‟identification des enregistrements qui ont des géométries doubles
--create or replace view verif_plu.doubl_geom_CC as
select insee, count(*),the_geom
from amenagement_urbanisme__n_zonage_planification.n_n_secteur_cc_038
group by the_geom,insee having count (the_geom)>1
La requête n‟envoie aucune ligne (pas de doublon géométrique)
II.4. vérification de géométrie invalide
II.4.1 table n_zone_urba_038
Requête d‟identification de géométrie invalide
create or replace view verif_plu.geom_invalid_ZU as
SELECT id_map, idurba, the_geom as "poly_invalid", ST_IsValidReason(the_geom) as
"invalid_reason" FROM
amenagement_urbanisme__n_zonage_planification.n_n_zone_urba_038 where
ST_isValid(the_geom)=false
II.4.2 table n_prescription_surf_038
Requête d‟identification de géométrie invalide
create or replace view verif_plu.geom_invalid_PS as
SELECT id_map, the_geom as "poly_invalid", ST_IsValidReason(the_geom) as
"invalid_reason" FROM
amenagement_urbanisme__n_zonage_planification.n_n_prescription_surf_038 where
ST_isValid(the_geom)=false
Migration de la base de données de la DDT38 sous un SGBDS
69
II.4.3 table n_secteur_cc_038
Requête d‟identification de géométrie invalide
create or replace view verif_plu.geom_invalid_CC as
SELECT id_map, the_geom as "poly_invalid", ST_IsValidReason(the_geom) as
"invalid_reason" FROM
amenagement_urbanisme__n_zonage_planification.n_n_secteur_cc_038 where
ST_isValid(the_geom)=false
La requête ne renvoie aucune ligne.
II.5. correction de la géométrie invalide
Requête de correction de la géométrie invalide. Elle comprend deux parties : une partie pour
identifier les erreurs et la deuxième pour corriger les erreurs. Pour vérifier si la correction est
réalisée on relance aprés la requête d‟identification de la géométrie invalide et en principe la
requête ne retourne aucune ligne.
II.5.1 table n_zone_urba_038
Requête correction de géométrie
update amenagement_urbanisme__n_zonage_planification.n_n_zone_urba_038 set
the_geom= ST_MakeValid(the_geom)
Where not ST_IsValid (the_geom);
II.5.2 table n_prescription_surf_038
Requête correction de géométrie
update amenagement_urbanisme__n_zonage_planification.n_n_prescription_surf_038 set
the_geom= ST_MakeValid(the_geom)
Where not ST_IsValid (the_geom);
Migration de la base de données de la DDT38 sous un SGBDS
70
II.6. identification des objets non importés suite au message d’erreur lors de l’import
Lors de l‟import de la table zone_urba en format (tab) les données ne sont pas
complètement remontées. Et le message d‟erreur qui s‟affiche indique que les erreurs sont
dus à l‟encodage.
De ce fait j'ai procédé à une comparaison des données entre les deux tables afin d'identifier
les objets non remonté et d'en déduire ainsi la spécificité de ces données.
J‟ai fait un import des tables en format shp. Il n‟y a pas eu de message d‟erreur et en
vérifiant le nombre d‟objets entre les tables importées et les tables originales le compte est
bon. A cet effet j‟ai réalisé une requête qui compare les données entre les tables de formats
différents. Ceci m‟a permis de voir d‟où vient l‟erreur. Effectivement c‟est une erreur
d‟encodage, car après identification des objets qui ne sont pas remontés il s‟avère que ces
dernières ont des informations non lisibles (les caractères avec des accents sont en forme
de symboles).
create or replace view verif_plu.donnees_non_importe_ZU as
select insee, idurba, id_map from public.n_n_zone_urba_038
where public.n_n_zone_urba_038.insee not in (select insee from
amenagement_urbanisme__n_zonage_planification.n_n_zone_urba_038)
Migration de la base de données de la DDT38 sous un SGBDS
71
Cette requête nous donne les codes « insee », « idurba » et « id_map » qui ne sont pas
remontés (il reste à voir en détail quels sont ces objets). nous avons au total 328 objets. et on peut aussi avoir juste les codes insee en utilisant la requête suivante:
Il ya aussi le même message pour la table habillage_text
II.7 requête pour identifier les objets qui sont contenues dans d’autres objets :
select P.idurba, B.idurba,
ST_Contains (P.the_geom, B.the_geom)
ST_INtersects (P.the_geom, B.the_geom)
from amenagement_urbanisme__n_zonage_planification.n_n_zone_urba_038 P,
amenagement_urbanisme__n_zonage_planification.n_n_zone_urba_038 B
where ST_Contains (P.the_geom, B.the_geom) or ST_INtersects (P.the_geom,
B.the_geom) is false
Au total j’ai réalisé 24 vues qui stockent les erreurs des trois tables principales :
Migration de la base de données de la DDT38 sous un SGBDS
72
ANNEXE N° 8
Guide d’utilisation du script d’import
I. Étapes pour la connexion à la geobase38 :
configuration du serveur qui stocke la geobase38
1-ouvrir l‟interface PGAdmin : en cliquant sur
2- clic sur
3- Saisir les informations suivantes dans cette fenêtre : Nom du serveur, Hôte, Port TCP,
Base maintenance, Nom utilisateur, Mot de passe ces deux derniers paramètres dépendent
du Groupe d‟utilisateurs soit : administrateur, production ou consultation.
Et il faut cocher (Enregistrer le mot de passe),
4- clic sur ok
Migration de la base de données de la DDT38 sous un SGBDS
73
A partir d’ici vous accédez aux données stockées dans Postgres, et vous pouvez les
exploiter selon votre groupe d’utilisateurs ce qui signifie selon les droits assigner à
chaque groupe d’utilisateurs.
II. Script d'import:
II.1. Création d’un schéma sous Postgres :
Voici les étapes à suivre pour la création du schèma :
Aprés avoir cliqué sur Démarrer
a-ouvrir l‟interface PGAdmin : en cliquant sur
b- dans la fenêtre PgAdmin clic sur l‟interpréteur de requête SQL de Pg_admin
c- copier-coller la requête ci-dessous dans la fenêtre qui s‟ouvre, ensuite modifier le nom
du schéma.
d- clic sur le bouton exécuter
Migration de la base de données de la DDT38 sous un SGBDS
74
Requêtes de création de schéma:
La requête permet de créer du schéma et des droits sur l‟usage du schéma. Au niveau des
schémas de la geobase38, les droits sont les mêmes pour tous les schémas :
-Copier la requête suivante dans la fenêtre SQL de postgres et modifier le nom du schéma:
II.2. Paramétrage du script import : Ouvrir le script nommé « import_gb_prod » avec
« notepad++ » afin de paramétrer les différentes variables.
Ce script est fait d‟une façon à permettre d'importer n'importe quelle couche dans n'importe
quel répertoire, En modifiant juste les variables d'environnements qui sont facilement
repérables dans le script, (les variables d'environnements facilitent la modification et évitent
de modifier le code afin d‟éviter le bug).
Les variables sont précédées par le terme "set".
Les variables a modifié sont:
- Le nom du répertoire et sous-répertoire dans lequel se trouve notre couche au départ
(tout en minuscule sans accents et sans espaces).
- Le nom de la table : on donne le même nom que celui de la couche à importer (tout en
minuscule sans accents et sans espaces).
- L’extension (shp où TAB).
- Le chemin vers la base production : à paramétrer une seule fois
Voici un exemple de représentation des variables à modifier dans le script
set rep=agriculture set sousrep=n_sante_vegetale set nomTable=n_chrysomele_zsup_038 set extension=TAB
-- Schéma: nom du répertoire__nom du sous répertoire
DROP SCHEMA nom du répertoire__nom du sous répertoire;
CREATE SCHEMA nom du répertoire__nom du sous répertoire
AUTHORIZATION gb_adm;
GRANT ALL ON SCHEMA nom du répertoire__nom du sous répertoire TO
geobase38_administrateurs;
GRANT USAGE ON SCHEMA nom du répertoire__nom du sous répertoire TO
geobase38_consultation;
GRANT USAGE ON SCHEMA nom du répertoire__nom du sous répertoire TO geobase38_production;
Migration de la base de données de la DDT38 sous un SGBDS
75
Les paramètres pour designer le chemin vers la geobase38 dans Postgres.
rem --CHEMIN VERS:BASE POSTGRESQL : host, user,port, base, pass set host=D38-SOMA2: nom du serveur set user=gb_adm: l‟utilisateur de la base set port=5432: le port sur le quel est installé le serveur Postgres qui héberge la base c‟est souvent 5432 set base=geobase38: nom de la base
set pass=gb_adm: mot de passe
NB : ne pas mettre d’espace ni accents dans les noms des chemins, et dans les
noms des répertoires et ne pas laisser d’espace à la fin d’une ligne (pour vérifier ça
sélectionner chaque ligne)
III. L’exécution du Script d’import :
Le script est exécutable soit à partir de la fenêtre CMD, soit en cliquant directement sur le
fichier.bat.
Migration de la base de données de la DDT38 sous un SGBDS
76
ANNEXE N° 9
La Configuration de la connexion à Postgres/Postgis et mise à jour
des tables via QGIS
I. Ouverture de la couche dans QGIS
La base de données peut être consultée via le logiciel SIG Quantum Gis. Ce logiciel est
gratuit et open source. Son interface devrait permettre une utilisation complète de la base à
savoir la visualisation, l‟édition, et la modification de la donnée géographique.
Afin de visualiser la donnée, il a été choisi d‟utiliser le logiciel QGis, il donne la possibilité de
se connecter à une base Postgrès et de visualiser la donnée sur notre client cartographique.
1- Une fois Qgis démarré, il faut se connecter au préalable à sa base de données via
l‟outil «d’ajout de table PostGis», présent dans la barre de menu du Qgis . Cet outil permet de lier notre espace de travail à n‟importe quelle base de données Postgrès.
2- A l‟ouverture de cette fenêtre Clic sur l‟icône Nouveau ou Éditer, une nouvelle
fenêtre s‟ouvre
3- Ensuite on s‟identifie via la fenêtre ci-dessous, en saisissant les informations telles
que ci-dessous :
Nom: c‟est le nom du serveur. Hôte: est l‟adresse IP. Port: 5432. Base de données:
geobase38. Nom d‟utilisateur :gb_prod, le mot de passe : gb_prod.( selon le groupe
d‟utilisateurs)
Migration de la base de données de la DDT38 sous un SGBDS
77
-Il faut cocher les deux cases: (Enregistrer le nom d’utilisateur, et Sauvegarder le mot
de passe) ceci nous permet de garder nous sessions ouverture et de pas réinitialiser
la configuration à chaque connexion.
NB: vous pouvez aussi cocher la case «lister les tables sans géométrie», si vous
souhaitez charger des tables qui n’ont pas d’objet géographique.
Validez par OK, la fenêtre suivante s‟affiche pour avertir que le mot de passe est
sauvegardé
Clic sur OK.
4- Une fois la connexion configurée dans QGIS Une nouvelle fenêtre s‟affiche comme ci-
dessous. Clic sur connecter, les noms des schémas s‟affichent. Clic sur le nom du
schéma qui héberge votre couche. Une liste des tables avec le nom des colonnes
s‟affiche, Choisissez les tables que vous souhaitez charger. Clic sur ajouter
Migration de la base de données de la DDT38 sous un SGBDS
78
NB: (le nom des schémas qui s’affichent dépend des droits attribués au groupe
d’utilisateurs. Par exemple si vous êtes connectés comme utilisateur «consultation» il
y a certains schémas ou tables qui sont dans la base mais qui ne s’affichent pas dans
votre fenêtre). Selon votre groupe d’utilisation vous allez avoir des droits différents
sur l’utilisation des couches (soit juste pour consultation ou bien des droits pour la
modification, création), vous pouvez revoir les droits d’utilisation de la table dans la
requête d’ajout des droits sur la nouvelle table ci-dessus.