Master EAU Travaux Pratiques Création et peuplement d`une base

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 :
sam[email protected]
nikolay.sirak[email protected]tp2.fr
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 com-
mandes 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étacom-
mandes 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 nom-
merez 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 soi-
mê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 / postgresq l /9 .1/ c on trib/
postgis - 2.0 / po stgis. sql
Shell$ psql -d nom_base -f /usr /share / postgresq l /9 .1/ c on trib/
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 propre-
ment 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’at-
tribut 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
.
Commune
.
nomCom : string
numINSEE : int
surfaceCom : double
geometrie : Geometry
.
*
.
est_traversee_par
.
1..*
.
Parcelle
.
numCadastre : int
surfParc : double
geometrie : Geometry
.
1
.
est_composée_de
.
1..*
.
ParcelleConstructible
.
COS : float
.
ParcelleAgricole
.
typeCulture : string
.
Batiment
.
nomBati : string
surfaceBati : double
typeBati : string
geometrie : Geometry
.
1..*
.
est_situe_sur
.
1
Figure 1 – Schéma conceptuel de la base de données.
5