3. PostGis et commandes SQL

3.1. PostGis

3.1.1. Présentation

PostGis est l’extension spatiale de la base de données PostGre.

http://www.postgis.fr/chrome/site/docs/workshop-foss4g/doc/index.html#

  • Stockage et manipulation des objets spatiaux

  • Gestion de l’accès aux données via des index spatiaux
    • Objectif : identifier rapidement la position des objets les uns / autres
    • Premier niveau de recherche via des rectangles de plus petite taille capable de contenir un objet géographique, « bounding boxes »
    ../../../_images/Bounding_boxes.png

    Il est plus simple de travailler sur des rectangles que sur des formes quelconques, qui donnent une première estimation de la position relative des objets.

  • Propose des fonctions spatiales : analyser les composants géographiques, déterminer les relations spatiales et manipuler les objets géographiques

    • Conversion : fonctions qui convertissent les données géographiques.
    • Gestion : fonctions qui permettent de gérer les informations relatives aux tables spatiales et l’administration de PostGIS.
    • Récupération : fonctions qui permettent de récupérer les propriétés et les mesures d’une géométrie.
    • Comparaison : fonctions qui permettent de comparer deux géométries en respectant leurs relations spatiales.
    • Construction : fonctions qui permettent de construire de nouvelles géométries à partir d’autres

3.1.2. Consultation et la manipulation de la base de données PostGis

La consultation et la manipulation de la base de données PostGis peut se faire via:

  • le programme PgAdmin PgAdmin
  • l’utilitaire de gestion de base de données de QGIS

3.1.2.1. PgAdmin PgAdmin

Le programme PgAdmin PgAdmin installé avec PostGis (privilégier PgAdmin III, disponible sur https://www.pgadmin.org/download/)

Lors de l’ouverture de PgAdmin, ce dernier se connecte à la base de données; les identifiants et mots de passe renseignés lors de l’installation sont disponibles dans le fichier C:\Users\{user}\AppData\Roaming\postgresql\pgpass.conf

../../../_images/PgAdmin_2.png

Note

A la création d’un projet hydra, une base est créée, qui comprend différents schémas, dont:

  • Un schéma Projet qui comprend les tables communes à tous les modèles du projet (hydrologie, scénarios, ...)
  • Un ou plusieurs schémas Modèles qui comprennent les tables de géométrie des objets de modélisation,
  • Un schéma Hydra qui comprend les différents types d’objets hydra (types de sections, ...)
  • Un schéma Work dans lequel il est possible d’importer des tables brutes (depuis des fichiers shape via l’utilitaire dédié, cf. Import shape), pour les pré-traiter et les importer dans un modèle.

3.1.2.2. Utilitaire de gestion de base de données de QGIS

La connexion à la base de données doit être préalablement faite via le menu Couches/Ajouter une couche/Ajouter une couche PostGis.

../../../_images/QGIS_couche_PostGis_1.png

Sélectionner Nouveau. Les identifiants et mots de passe renseignés lors de l’installation sont disponibles dans le fichier C:\Users\{user}\AppData\Roaming\postgresql\pgpass.conf

../../../_images/QGIS_couche_PostGis_2.png

Après validation, sélectionner Connecter; il est alors possible de sélectionner une couche de la base de données pour ‘afficher dans QGIS, et de se connecter à la base via le gestionnaire de bases de données.

../../../_images/QGIS_couche_PostGis_3.png

L’utilitaire de gestion de base de données de QGIS est accessible via le menu Base de données/Gestionnaire de bas de données de QGIS.

../../../_images/QGIS_BD.png

3.2. SQL (Structured Query Language)

3.2.1. Présentation

Langage informatique normalisé servant à exploiter des bases de données relationnelles :

  • manipulation des données : permet de rechercher, d’ajouter, de modifier ou de supprimer des données dans les bases de données relationnelles.
  • Définition des données : permet de créer et de modifier l’organisation des données dans la base de données
  • Contrôle de transaction : permet de commencer et de terminer des transactions
  • Contrôle des données : permet d’autoriser ou d’interdire l’accès à certaines données à certaines personnes

3.2.2. Principales commandes

Les paragraphes suivants présentent la syntaxes des commandes de base pour manipuler les tables et les données associées.

3.2.2.1. Actions sur les tables

  • Création d’une table

    CREATE TABLE [schema].[table] ([champ1] [type], [champ2] [type])

  • Suppression d’une table : DROP

    DROP TABLE [schema].[table]

  • Modifier la structure d’une table : ALTER

    ALTER TABLE [schema].[table] ADD COLUMN [champ] [type]

    ADD COLUMN

    DROP COLUMN

    RENAME COLUMN

3.2.2.2. Actions sur les données

  • Sélection : SELECT

    SELECT [champ] FROM [schema].[table] WHERE [condition]

    SELECT name FROM angkor.elem_2d_node where id<1000

  • Mise à jour d’une table : UPDATE

    UPDATE [schema].[table] SET [champ]=[valeur] WHERE [condition]

    UPDATE angkor.elem_2D_node SET name=‘toto’ WHERE id= 3138

  • Insertion de données dans une table : INSERT

    INSERT INTO [schema].[table] ([champ]) SELECT [valeur]

    INSERT INTO [schema].[table] ([champ]) SELECT [champ] FROM [source] WHERE [condition]

  • Supprimer des données : DELETE

    DELETE * FROM [schema].[table] WHERE [condition]

3.2.3. Exécuter des requêtes

Les requêtes peuvent être exécutées depuis:

  • le programme PgAdmin PgAdmin
  • l’utilitaire de gestion de base de données de QGIS

3.2.3.1. PgAdmin PgAdmin

Se positionner sur le schéma sur lequel la requête doit être effectuée, et sélectionner le bouton PgAdmin_sql.

Écrire la requête dans la fenêtre, puis l’exécuter via le bouton PgAdmin_play.

3.2.3.2. Utilitaire de gestion de base de données de QGIS

Après s’être connecté à la base, se positionner sur le schéma sur lequel la requête doit être effectuée, et sélectionner le bouton QGIS_sql.

Écrire la requête dans la fenêtre, puis l’exécuter via le bouton Exécuter.

Il est possible de charger le résultat de la requête (select) dans le gestionnaire de couche sous forme de vue; pour cela, sélectionner Créer une vue. Sélectionner le champ correspondant à la géométrie des objets, donner un nom à la vue et cliquer sur Charger la vue.