Master EAU Travaux Pratiques Création et peuplement d`une base
publicité
Master EAU Travaux Pratiques Création et peuplement d’une base de données spatiales T. Libourel S. Andrés N. Sirakov 2013-2014 Contacts : [email protected] [email protected] Introduction PostGIS est une extension du SGBD relationnel-objet PostgreSQL. Il entre dans ce qu’on peut appeler les « SGBD Spatiaux ». PostGIS permet de manipuler des données vectorielles stockées dans les tables des bases de données. Pour cela, il propose des nouveaux types géographiques (points, lignes, polygones) pour les colonnes des tables. PostGIS seul ne permet pas de visualiser la géométrie des données stockées, mais seulement leur description en format texte WKT. Pour afficher les objets graphiquement, il faut utiliser des applications complémentaires capables de se connecter aux bases de données (comme QGIS) ou bien exporter les données dans un format qui peut être affiché avec un logiciel adapté (par exemple le format shapefile avec un SIG bureautique ou le format KML avec Google Earth). Objectifs et organisation du TP L’objectif principal de ce TP est de se familiariser avec la notion de base de données spatiales, en utilisant le SGBD PostgreSQL et sa cartouche spatiale PostGIS. Plusieurs points vont être abordés : 1. Construction et peuplement d’une base de données contenant des tables ayant une colonne géométrique. 2. Export des tables créées dans des fichiers vectoriels shapefile. 3. Visualisation de la base de données et des fichiers shapefile avec le SIG QGIS. 4. Création de nouvelles tables et import de nouvelles données à partir de fichiers shapefile. Pré-requis Pour réaliser le TP, une machine virtuelle pré-configurée a été installée sur les postes. Il s’agit d’un système d’exploitation qui va fonctionner en mode autonome à l’intérieur d’une fenêtre du système « normal ». Nous avons choisi de mettre en place une machine virtuelle basée sur le système Ubuntu 12.04. Tous les outils nécessaires pour le TP ont déjà été installés dans ce système. Commencez par lancer le logiciel VirtualBox dans votre système normal. Pour cela, ouvrez un terminal depuis le menu principal : Système > Terminal. Puis entrez mavirt. Laissez le temps au système virtuel pour démarrer (environ une minute). Un mot de passe est demandé pour démarrer la session : tapez tppostgis. Vous devriez ensuite arriver sur le bureau Ubuntu. 1 Toutes les opérations qui suivent dans ce TP doivent être réalisées dans cet environnement. Pour plus de commodité et éviter de confondre le système normal et le système virtuel, vous pouvez mettre le système virtuel en plein écran en cliquant dans sa fenêtre puis en utilisant la combinaison de touches CTRL droite + f. Pour revenir en mode fenêtré, utiliser la même combinaison de touches. Les données du TP doivent être téléchargées depuis l’adresse indiquée par le professeur en utilisant le navigateur internet inclus dans l’environnement virtuel. Pendant tout le TP, vous aurez besoin de deux terminaux. Le premier servira à taper les commandes Unix dont vous aurez besoin (accès à la base, exécution d’un script, import/export de données). Il sera précisé dans les instructions par le préfixe Shell$. Le second terminal servira à vous connecter au client en ligne de commande psql pour utiliser la base de données (métacommandes PostgreSQL et requêtes SQL). Il sera précisé dans les instructions par le préfixe psql>. Il est donc souhaitable d’ouvrir les 2 terminaux dès le début du TP (menu Applications > Accessoires > Terminal). Vous utiliserez les paramètres suivants pour vous connecter à la base de données : nom du serveur : localhost port : 5432 nom de la base : tppostgis1 nom d’utilisateur : postgres mot de passe : postgres Références et supports en ligne Sites concernant PostgreSQL et PostGIS – Client PostgreSQL en ligne de commande (psql) : http://www.linux-france.org/article/ serveur/psql/guide-utilisateur/app-psql.html – Client graphique pour PostgreSQL (PgAdmin) : http://www.pgadmin.org – Manuel utilisateur de PostGIS (en anglais) : http://postgis.net/docs/, http://www.gis. unbc.ca/help/software/postgis/postgis.pdf – Sites officiels de PostGIS : http://www.postgis.fr/, http://postgis.refractions.net/ (en anglais) SIG libre QGIS (pour la visualisation) – Site officiel : http://qgis.org – Documentation officielle : http://download.osgeo.org/qgis/doc/manual/qgis-1.7.2_user_ guide_fr.pdf – Fiches d’aide à l’utilisation de QGIS : http://sigea.educagri.fr/fileadmin/user_upload/ doc_prof/guerreiro/QGis/Fiches_QGIS_V1_8_0.pdf – Astuces QGIS : http://sig974.free.fr/FAQ/index.php?static3/toutes-les-docs Commandes et métacommandes du client PostgreSQL Ces commandes vont vous être utiles pour utiliser votre base de données. Commandes PostGreSQL (depuis le shell Unix) Accès à la base : Shell$ psql -d [nom_base] -h [nom_serveur] -U [ nom_utilisateur] -W Shell$ password : [mot_de_passe] Exécution d’un script : Shell$ psql -d [nom_base] -h [nom_serveur] -U [ nom_utilisateur] -f [cheminvers/script] -W Shell$ password : [mot_de_passe] 2 Exécution d’une commande SQL seule : Shell$ psql -d [nom_base] -h [nom_serveur] -U [ nom_utilisateur] -c 'commande_SQL ' -W \\ Shell$ password : [mot_de_passe] Métacommandes PostGreSQL (depuis le client psql) \d : Liste les tables d’une base. \d nom_table : Liste les colonnes de la table spécifiée. \d * : Liste l’information de toutes les tables et colonnes de chaque table. \da : Liste tous les agrégats disponibles. \dd object : Liste la description depuis pg_description d’un objet spécifié, qui peut être une table, colonne, type, opérateur, ou agrégat. \df : Liste les fonctions. \di : Liste les index. \do : Liste les opérateurs. \ds : Liste les séquences. \dS : Liste les tables système et les index. \dt : Liste les tables non système. \q : Quitte le programme psql. \ ! [ command ] : Bascule vers un shell unix séparé (on en sort grâce à la commande exit) ou exécute la commande unix passée en paramètre. 1 Construction d’une base de données spatiales Plusieurs manipulations sont nécessaires avant de pouvoir utiliser la base de données spatiales. Dans un premier temps, il s’agit de créer une nouvelle base avec la commande createdb. Ensuite il faut activer l’extension PostGIS dans cette base grâce à une requête SQL. 1.1 Création et vérification de la base Dans un terminal unix, exécutez la commande de création d’une nouvelle base que vous nommerez tppostgis1 : Shell$ createdb -h localhost -U postgres -W tppostgis1 Vérifiez que la base existe en vous y connectant dans le terminal avec la commande psql. Vérifiez qu’il n’y a aucune table contenue dans la base. 1.2 Prise en compte de l’extension PostGIS dans la base Depuis la version 9.1 de PostgreSQL (celle qui est installée sur votre machine virtuelle), la procédure d’ajout de l’extension PostGIS dans une base a été simplifiée par rapport aux versions précédentes. Dans le terminal psql connecté à votre base, tapez simplement la requête suivante : psql > CREATE EXTENSION postgis ; Vous pouvez aussi (au choix) exécuter cette requête directement depuis le terminal unix de la manière suivante : Shell$ psql -d [nom_base] -h [nom_serveur] -U [ nom_utilisateur] -c 'CREATE EXTENSION postgis ;' -W Ce nouveau raccourci exécute automatiquement plusieurs commandes qu’il fallait exécuter soimême dans les versions précédentes de PostgreSQL. Il est bon de les connaître (ne les exécutez pas puisque c’est déjà fait !), les voici pour information : 3 Shell$ createlang plpgsql -d nom_base Shell$ psql -d nom_base -f /usr/share/postgresql /9.1/ contrib/ postgis -2.0/ postgis.sql Shell$ psql -d nom_base -f /usr/share/postgresql /9.1/ contrib/ postgis -2.0/ spatial_ref_sys.sql La commande createlang langName -d nom_base définit un nouveau langage procédural qui sera utilisé par PostgreSQL sur la base de données spécifiée. Ici c’est le langage plpgsql qui est chargé car il permet d’exécuter des fonctions couramment utilisées par PostGIS. La commande psql -d nom_base -f /cheminvers/postgis.sql permet de charger les types de colonnes, les fonctions géographiques et les tables associées qui sont utilisées par PostGIS. La commande psql -d nom_base -f /cheminvers/spatial_ref_sys.sql permet de charger dans la base de données, la table spatial_ref_sys qui contient environ 300 systèmes de références spatiales et les détails nécessaires aux transformations de coordonnées géographiques dans ces systèmes. Maintenant que vous avez toutes les cartes en main, vous allez pouvoir démarrer le TP proprement dit. 2 Création de la structure et peuplement de la base La base de données que nous allons créer et peupler correspond en partie à celle que vous avez modélisé lors du TD de modélisation conceptuelle. Nous vous redonnons ci-dessous le schéma conceptuel correspondant (Figure 1). Pour gérer la généralisation/spécialisation, nous avons le choix d’utiliser la deuxième solution c’est à dire que les trois classes concernant les parcelles ne vont donner qu’une seule table parcelle dans la base avec tous les attributs numCadastre, surfaceParc, geometrie, COS et typeCulture. 2.1 Création et peuplement des tables La création des tables contenant des objets géographiques se fait en 2 étapes : 1. création des tables et attributs classiques sans la composante spatiale ; 2. ajout de la composante spatiale des objets géographiques : pour chaque table, création d’une colonne géométrique. Dans ce TP, vous allez créer et peupler la base de données de deux manières : 1. directement en tapant la commande dans un terminal ; 2. en exécutant des scripts regroupant plusieurs commandes. 2.1.1 Création des tables sans composante spatiale Créez la table commune en tapant la commande SQL correspondante dans le client psql. L’attribut numINSEE sera la clé primaire de la table, l’attribut nomCom sera une chaîne de longueur maximale 256 caractères et l’attribut surfaceCom de type real (c’est le type qui correspond à un nombre à virgule dans PostgreSQL). Attention l’attribut geometrie ne doit pas être ajouté pour le moment. Rappel : Pour créer une table en SQL, il faut utiliser la commande create table [nom_table] (att1 type [not null primary key], att2 type, . . .); Vérifiez la structure de la table commune, puis exécutez le script Creation.sql pour créer les autres tables dans la base. Listez les tables qui viennent d’être créées dans la base. 4 . Riviere numRiviere : int nomRiviere : string longueur : double geometrie : Geometry * est_traversee_par 1..* Commune nomCom : string numINSEE : int surfaceCom : double geometrie : Geometry est_composée_de 1 1..* ParcelleConstructible COS : float Parcelle numCadastre : int surfParc : double geometrie : Geometry ParcelleAgricole typeCulture : string 1 est_situe_sur 1..* Batiment nomBati : string surfaceBati : double typeBati : string geometrie : Geometry Figure 1 – Schéma conceptuel de la base de données. 5 2.1.2 Création de la composante spatiale : ajout des colonnes géographiques En vous aidant de l’exemple donné en cours, ajoutez la colonne géographique dans la table commune en indiquant les caractéristiques suivantes : – le nom de la colonne est the_geom ; – le SRID pour cette table est 2154 ; – le type de géométrie est MULTIPOLYGON ; – la dimension associée à la géométrie est 2. Rappel : la commande PostGIS d’ajout d’une colonne géométrique est AddGeometryColumn. Exécuter ensuite le script Geom.sql pour ajouter les colonnes géographiques aux autres tables. 2.1.3 Peuplement des tables Exécuter le script Insertion.sql pour ajouter les enregistrements aux tables. Ajouter un enregistrement dans la table batiment, contenant une géométrie, tel que : – le nom du bâtiment n’est pas connu (valeur NULL), – la surface du bâtiment est égale à 964.8706251980000000, – le type du bâtiment est bia, – la parcelle sur laquelle se trouve le bâtiment porte le numéro 4515, – la géométrie du bâtiment est définie par la valeur : ST_GeometryFromText( 'MULTIPOLYGON(((769986.7073358211 6290779.744180864, 769989.6601997252 6290799.728719401, 770038.4111935201 6290792.355767068, 770035.4775371914 6290773.581503133, 769986.7073358211 6290779.744180864)))',2154) Question : Dans quel format la déclaration de l’objet géographique que l’on vient d’insérer est-elle exprimée ? 2.1.4 Création des index Les index servent à accélérer les recherches et permettent ainsi d’utiliser de manière optimale la base de données avec de grandes quantités de données. Les données sont organisées dans un arbre qui peut être parcouru afin de trouver rapidement un enregistrement particulier. Pour des colonnes géographiques, on utilise de préférence des index de type GIST (arbre de recherche généralisé). La syntaxe pour créer un index GIST sur une colonne géographique est la suivante : CREATE INDEX nomIndex ON nomTable USING GIST (nomColGeom GIST_ GEOMETRY_OPS_2D); Créez un index de type GIST nommé batiment_geom_idx pour la table batiment. Exécuter le script Index.sql pour créer les index des tables parcelle, commune et rivière. 2.2 Exploration de la base de données Utiliser les commandes PostgreSQL pour explorer la base de données ainsi que les méta-tables PostGIS geometry_columns et spatial_ref_sys. 3 Visualisation avec Quantum GIS Pour lancez le logiciel QGIS, il y a deux possibilités : – en tapant dans la console unix : qgis & – depuis le menu Ubuntu Applications > Sciences > Quantum GIS Desktop Pour commencer, vous allez devoir définir une connexion vers votre base PostGIS dans QGIStp. Une fois cette connexion créée, elle sera mémorisée dans QGIS et pourra être réutilisée autant de fois que nécessaire, même après un redémarrage du logiciel. 6 3.1 Création d’une nouvelle connexion PostGIS Dans QGIS, cliquez sur le menu : Couche > Ajouter une couche PostGIS (ou bien cliquez sur l’icône Ajouter une couche PostGIS). Cliquez sur le bouton Nouveau, puis renseignez les informations relatives à la connexion : Nom : Base PostGIS TP1 Hôte : localhost Port : 5432 Base de données : tppostgis1 Nom d’utilisateur : postgres Mot de passe : postgres Pour plus de commodité, vous pouvez cocher Enregistrer le nom d'utilisateur et Sauvegarder le mot de passe. Le bouton Tester la connexion vous permet de vérifier que les informations sont correctes et que la connexion fonctionne. Si tout va bien, cliquez sur le bouton OK. Vous devriez voir apparaître la nouvelle connexion dans la liste déroulante au-dessus du bouton Nouveau. C’est ici que sont listées toutes les connexions précédemment mémorisées. 3.2 Visualisation des couches PostGIS Sélectionnez la connexion Base PostGIS TP1 dans la liste, puis cliquez sur le bouton Connecter. La liste des couches géographiques disponibles dans la base s’affiche. Sélectionnez toutes les couches listées (sauf raster_columns qui n’est pas sélectionnable), puis cliquez en bas de la fenêtre sur le bouton Ajouter. Les objets géographiques contenus dans les quatre couches commune, parcelle, batiment et riviere de votre base PostGIS s’affichent dans la fenêtre principale de QGIS. Dans la boîte Couches à gauche de l’interface, vous pouvez masquer temporairement des couches et modifier leur ordre d’affichage (avec un cliquer/glisser). Remontez la couche batiment au-dessus des couches parcelle et commune pour rendre visible son contenu. Pour afficher des informations sur les objets, vous pouvez utiliser l’outil Identifier les entités puis cliquer sur un objet d’une couche. L’identification des objets se fait du haut vers le bas dans l’ordre des couches c’est à dire que si plusieurs objets superposés se trouvent au point où vous avez cliqué, c’est celui de la couche la plus haut qui sera affiché en premier dans la fenêtre d’identification. 4 Import/export de données 4.1 Export des données de la base en fichiers de formes Vous pouvez consulter la page http://manpages.ubuntu.com/manpages/precise/man1/pgsql2shp. 1.html. Les tables de la base de données PostGIS peuvent être exportées au format shape avec l’outil pgsql2shp qui est fourni avec PostGIS. Dans la console unix, exécutez la structure commande suivante pour les quatre tables géographiques de votre base : Shell$ pgsql2shp -f nomCouche.shp -h host -u user -P password -p 5432 nomBD nomTable Question : Pour chacune des tables, combien de fichiers obtenez-vous ? Ouvrez dans QGIS les fichiers de forme obtenus : menu Couche > Ajouter une couche vecteur (ou l’icône Ajouter une couche vecteur). 7 En superposant avec les couches issues de la connexion directe à la base, vérifiez que les objets géographiques correspondent. 4.2 Import de données shape dans la base Vous pouvez consulter la page http://manpages.ubuntu.com/manpages/precise/man1/shp2pgsql. 1.html La commande shp2pgsql permet d’importer des fichiers de forme dans une base PostGIS. Il y a deux façons d’utiliser cette commande : soit en l’exécutant depuis une console unix, soit à travers une extension de QGIS. 4.2.1 Utilisation directe de la commande shp2pgsql La commande shp2pgsql peut être utilisée d’une des deux manières suivantes (au choix) : 1. Création d’un fichier de commandes SQL intermédiaire à partir du fichier shape puis exécution du fichier SQL au niveau de la base de données. Shell$ shp2pgsql -option -s 2154 [cheminVers/fichier_shape.shp] [nom_table] > [nom_fichier_sql.sql] Shell$ psql -h [nom_serveur] -U [nom_utilisateur] -d [nom_base] -f [nom_fichier_sql.sql] 2. Création de commandes SQL à partir du fichier shape et exécution directe via un pipe (pas de fichier SQL intermédiaire). Shell$ shp2pgsql -option -s 2154 [cheminVers/fichier_shape.shp] [nom_table] | psql -h [nom_serveur] -U [nom_utilisateur] -d [nom_base] La commande possède différents modes opératoires suivant l’option qui est passée en premier paramètre, à savoir : -c : Crée une nouvelle table et y insère les données contenues dans le fichier shape. -d : Supprime la table de la base si elle existe avant la création de la nouvelle table et l’insertion des données contenues dans le fichier shape. -a : Ajoute les données contenues dans le fichier shape dans une table qui existe dans la base de données. Il faut souvent spécifier le nom de la colonne géométrique existante avec l’option -g (inutile si celle-ci s’appelle geom). -p : Seule la création de la table est effectuée, les données ne sont pas insérées. -s : Crée et remplit les tables géométriques avec le SRID spécifié. Importez les fichiers suivants en utilisant l’option précisée : occsol.shp : création d’une nouvelle table ; commune.shp : ajout de données dans une table existante ; batiment.shp : suppression de la table existante et création d’une nouvelle table. Avec QGIS, chargez la nouvelle table occsol puis vérifiez les changements dans les tables commune et batiment en décochant puis en cochant à nouveau les couches. 4.2.2 Import avec QGIS Pour importer des fichiers shape dans la base, il est possible d’utiliser une extension de QGIS qui s’appelle SPIT. Cet outil utilise en arrière-plan la commande shp2pgsql. L’avantage de cette solution c’est qu’il n’y a pas besoin de taper la commande à la main, tout est réalisé grâce à une interface graphique. Vous allez importer le fichier de forme batiq.shp de cette manière. Pour commencer, il faut ajouter l’extension SPIT qui n’est pas encore installée dans QGIS. Cliquez sur le menu Extension > Gestionnaire d'extensions. Dérouler la liste des extensions et cochez SPIT puis cliquez sur le bouton OK. Cliquez sur la nouvelle icône représentant un éléphant bleu qui est apparu sur l’interface de QGIS. 8 Dans la nouvelle fenêtre, sélectionnez votre base PostGIS dans la liste déroulante, cliquez sur le bouton Connecter. Décochez la case Utiliser le SRID par défaut et saisissez 2154 pour la valeur du SRID. En bas de la fenêtre, cliquez sur le bouton Ajouter puis sélectionnez le fichier batiq.shp. Cliquez sur le bouton OK en bas. Pour vérifier l’import, ouvrez la table batiq contenue dans la base PostGIS et comparez avec le fichier de forme d’origine. Déconnectez vous du client psql avec la métacommande \q et fermez les deux consoles. Si vous le souhaitez, vous pouvez sauvegarder votre projet QGIS avec le menu Fichier > Sauvegarder le projet. Fermez QGIS et arrêtez la machine virtuelle. 9
