2.1 Notions PostgreSQL/PostGIS

La base de données (BDD) est au centre des dispositifs informatiques de collecte, mise en forme, stockage et utilisation d'informations.

PostgreSQL est un Système de Gestion de Bases de Données (SGBD) open source qui permet d'administrer une ou plusieurs BDD regroupées dans un cluster avec une gestion fine des utilisateurs (rôles, groupes).

Nous allons découvrir dans cette partie comment fonctionne théoriquement une BDD PostgreSQL et comment faire pour se connecter à une base (cf. 2.1.1). Puis nous verrons comment activer l'extension PostGIS et découvrirons les spécificités d'une BDD spatiale (cf. 2.1.2).

2.1.1 SGBD PostgreSQL

1. Eléments clés d'une BDD

Chaque BDD s'organise en 1 ou plusieurs schémas. Ils regroupent des tables de données et d’autres objets tels que les vues, les séquences (utilisées par exemple pour générer des identifiants uniques pour les lignes d'une table), les fonctions, etc.

Une BDD dans PostgreSQL contient donc principalement des tables qui, elles-mêmes sont organisées en colonnes (champs) et lignes (enregistrements) où sont stockées les valeurs (données).

Les champs des tables peuvent être de plusieurs types : texte (varchar), nombre entier, nombre réel (flottant), date, booléen (oui/non, vrai/faux, 0/1), etc. (cf. doc QGIS).

SQL (Structured Query Language) est un langage informatique normalisé servant à exploiter des BDD relationnelles, dont voici quelques principales commandes : CONNECT (se connecter à une BDD), CREATE (créer une BDD, une table...), INSERT (ajouter des données), UPDATE (modifier) et DELETE (supprimer), SELECT (rechercher/sélectionner). Nous allons le découvrir et le manipuler dans la partie 2.3.1.

La vue est la représentation "virtuelle" d'une requête de sélection SELECT sous la forme d'une table (sans coût de stockage supplémentaire). Cette vue s'actualise automatiquement à chaque UPDATE, INSERT ou DELETE dans la ou les tables requêtées. Attention, on ne peut pas directement modifier les données à partir d'une vue !

2. Comment se connecter à une BDD PostgreSQL ?

psql est une interface en ligne de commande permettant de créer/manipuler une BDD PostgreSQL à travers la saisie de requêtes SQL, directement ou par l'utilisation de procédures stockées (cf. doc PostgreSQL).

Pour faciliter la gestion, il existe des outils d'administration graphique pour PostgreSQL tels que pgAdmin 4 (distribué selon les termes de la licence PostgreSQL) et DBeaver, plus "léger" que nous utiliserons en TP (cf. liste clients - doc PostgreSQL).

Nous verrons également qu'il est possible de se connecter à une BDD PostgreSQL/PostGIS via QGIS (cf. 2.2) ainsi que d'autres logiciels SIG, serveurs ou clients web cartographiques tels que Geoserver [cf. tuto ANF SIST 2018 Geoserver et tuto sigterritoires.fr], Mapserver, ArcServer, API javascript OpenLayers/Leaflet (cf. 3.5), etc.

3. Comment créer une BDD PostgreSQL ?

Il est possible de créer une base de données soit en ligne de commande via psql soit via une interface graphique telle que celle de DBeaver qui permet de générer le code SQL adéquat.

Nous allons le pratiquer dans la section suivante. Voici pour information la requête générique de création d'une BDD en SQL : (source doc PostgreSQL) :

CREATE DATABASE nom
    [ [ WITH ] [ OWNER [=] nom_utilisateur ]
               [ TEMPLATE [=] modèle ]
               [ ENCODING [=] codage ]
               [ LC_COLLATE [=] lc_collate ]
               [ LC_CTYPE [=] lc_ctype ]
               [ TABLESPACE [=] tablespace ]
               [ ALLOW_CONNECTIONS [=] connexion_autorisee ]
               [ CONNECTION LIMIT [=] limite_connexion ]
               [ IS_TEMPLATE [=] est_template ] ]

Pour créer une BDD, il faut être superutilisateur ou avoir le droit spécial CREATEDB.

2.1.2 Extension spatiale PostGIS

L'extension PostGIS permet au SGBD PostgreSQL de stocker des données spatiales. D'autres SGBD spatiaux existent : Oracle spatial, Microsoft SQL server, Sqlite/SpatiaLite, DB2, MySQL/MyGIS, Esri ArcGIS, et apparition plus récente chez les BDD NoSQL (MongoDB, CouchDB, Solr…).

1. Caractéristiques générales des BDD spatiales

• Stockage attributaire et géométrique : en général 1 table (ou 1 vue) équivaut à 1 couche SIG,

• Requêtes SQL attributaires et spatiales (opérations SIG) dont la création de nouvelles géométries,

• Renvoi aux clients des données attributaires/spatiales selon différents formats,

• Données volumineuses et traitements longs/complexes pris en charge,

• Respect des standards existants de l’Open Geospatial Consortium (OGC) : Simple Features for SQL specifications (SFSQL, cf. figure ci-dessous) et SQL Multimedia Applications Spatial specification (SQL-MM).

Au même titre que les types de données classiques (texte varchar/text..., numérique integer/real..., booléen, date...), le SGBD spatial gère 4 types spatiaux (geometry, geography, raster, topology). Il stocke toutes les informations spatiales dans cette colonne spatiale ; il n’existe pas de tables d’entités annexes. Rassembler les informations spatiales dans un champ unique de type geometry par exemple simplifie l'accès aux données spatiales.

De plus, de nombreuses fonctions spatiales sont automatiquement installées lors de l'activation de PostGIS dans PostgreSQL.

Ainsi une BDD de données spatiale occupe une place centrale dans les systèmes d'information.

2. Comment activer l'extension PostGIS pour une BDD ?

Au moment de l'installation du SGBD PostgreSQL, pensez à installer également PostGIS. La documentation officielle de PostGIS permet d'expliquer l'installation selon l'OS de votre ordinateur.

Ensuite, il suffit d'activer l'extension une fois la BDD créée en utilisant la commande SQL :

CREATE EXTENSION "postgis";

/* 
* Dans le navigateur de BDD, faites un clic droit sur la connexion bdd_sist_solution,
* ouvrez l'éditeur SQL puis copiez-collez la commande ci-dessous
*/
CREATE DATABASE bdd_sist_tp 
  WITH
  OWNER = 'sist'
  ENCODING = 'UTF-8'
  TABLESPACE = pg_default ;

/* 
* Dans le navigateur de BDD, actualisez F5 la connexion pour voir apparaître la base
* faites un clic droit sur la base `bdd_sist_tp`, 
* ouvrez l'éditeur SQL puis copiez-collez la commande ci-dessous
*/
CREATE EXTENSION "postgis";

L'activation de PostGIS engendre dans le schéma public l'ajout d'un millier de fonctions, la création de la table spatial_ref_sys et de 2 vues geography_columns, geometry_columns. Par exemple la vue geometry_columns liste automatiquement chaque table spatiale vectorielle possédant un champ géométrique.

3. Spécificités d'une BDD spatiale

Cette partie détaille plus finement les spécificités d'une BDD spatiale évoquées en 2.1.2.1.

Qu’est-ce qu’une BDD spatiale a de plus qu’une BDD non spatiale ?

1) 4 types de données (et leurs fonctions spatiales "ST_xxx" associées) :

• géométrique : stocke la géométrie des objets spatiaux selon les standards SFSQL et SQL-MM (cf. figure OGC ci-dessus). PostGIS étend ce standard aux 3DZ (=2D + altitude), 3DM (=2D + mesure quelconque) et 4D (=XYZM ou 3DZM),
• géographique : stocke sous la forme de coordonnées géographiques (longitude/latitude), exprimées en degrés, pour les mêmes objets spatiaux que le type géométrique sauf les courbes. Ce type est très utile pour les mesures de grandes distances/aires/longueurs tenant compte de la courbure de la Terre (selon par exemple WGS 84). Plus de détails dans la doc PostGIS...,
• raster : stocke les données spatiales sous la forme d'une matrice de valeurs géopositionnée avec/sans attributs, dans une ou plusieurs bandes, avec la possibilité de mettre en place une organisation par dalles (tuiles) et de gérer des miniatures (overviews) selon une structure pyramidale. Il faut installer au préalable l’extension postgis_raster,
• topologique : exprime les relations spatiales entre des entités vectorielles connectées ou adjacentes dans un SIG pour effectuer certains types d’analyse spatiale, comme l’analyse de réseau (route, hydrographie). Pour gérer des objets topologiques, il faut installer au préalable l’extension postgis_topology dans un schéma dédié de la BDD.

Type spatial	Champ	Type de données spatiales stockées	Fonctions associées (source postgis.net)
geometry	geom	coordonnées géométriques cartésiennes	Voir toutes les fonctions...
geography	geog	coordonnées géographiques (degrés)	Voir toutes les fonctions...
raster	rast	matrice	Voir toutes les fonctions...
topology		données de réseau	Voir toutes les fonctions...

Ces champs peuvent potentiellement coexister dans la même BDD, voire dans des colonnes d’une même table (cas pour "geom" et "geog").

Rem : les types geography, raster et topology ne sont pas abordés dans la suite de cette formation.

2) Des fonctions spatiales classées par grandes catégories :

• Conversion de données spatiales dans un format externe,
• Gestion d’informations relatives aux tables spatiales et à l’administration de PostGIS,
• Récupération de propriétés et de mesures d’une géométrie,
• Comparaison de 2 géométries en respectant leurs relations spatiales,
• Construction de nouvelles géométries à partir d’autres,
• Fonctions SIG classiques : resample, clip, reclass, intersection, union, projection, etc.,
• Fonctions spécifiques aux différents types raster, topologique, géométrique et géographique.

Nous allons en manipuler plusieurs dans la partie 2.3. Interroger une BDD dans QGIS.

Aussi les données spatiales stockées dans ces différents champs sont souvent illisibles (ex : format WKB Well-Known Binary). L'écriture à l'aide du standard WKT (Well-Known Text) inclut des informations lisibles sur le type d'objet spatial et ses coordonnées selon le système de coordonnées de la table. Voici un exemple d'écriture en WKT d'un polygone simple (source postgis.net) à l'aide de la fonction ST_AsText(geometry or geography) :

SELECT ST_AsText('01030000000100000005000000000000000000
000000000000000000000000000000000000000000000000
F03F000000000000F03F000000000000F03F000000000000F03
F000000000000000000000000000000000000000000000000');
/* 
 returns : 
 st_astext
--------------------------------
 POLYGON((0 0,0 1,1 1,1 0,0 0))
(1 row)
 */

Voici un exemple qui lit du GML et retourne du JSON :

SELECT 
    ST_AsGeoJSON(
        ST_GeomFromGML(
            '<gml:Point><gml:coordinates>1,1</gml:coordinates></gml:Point>'
        )
    ); 
/* 
 returns : 
 st_asgeojson
--------------------------------
{"type":"Point","coordinates":[1,1]}
(1 row)
 */

3) Indexation spatiale : accélère les recherches en organisant les données dans des arbres de recherche qui peuvent être parcourus efficacement pour retrouver une entité particulière. Les index spatiaux ne sont pas capables d’indexer des entités géométriques elles-mêmes, mais ils indexent leur étendue.

CREATE INDEX [indexname] ON [tablename] USING GIST ( [geometryfield] );

En savoir plus sur l'amélioration des performances de PostgreSQL/PostGIS via la doc QGIS...

2.1.3 À RETENIR

• PostgreSQL est un Système de Gestion de Bases de Données performant permettant de gérer des données "métiers" dans différents schémas sous la forme essentiellement de tables et de vues.
• Le langage SQL (Structured Query Language) sert à exploiter des BDD relationnelles.
• Pour faciliter la gestion des BDD, on peut utiliser un des outils d'administration graphique disponibles, dont pgAdmin 4 et DBeaver.
• Il faut obligatoirement activer l'extension PostGIS d'une BDD via un superutilisateur.
• Les 3 spécificités d'une BDD spatiale : jusqu'à 4 types de données (geometry, geography, topology, raster) gérés, des fonctions spatiales "ST_xxx" standardisées associées et une indexation spatiale sur ces types de données.