Qui sommes nous ?

Julien Cabieces Developpeur C++/Python QGIS Core committer @troopa81 @CabiecesJ @CabiecesJ@mapstodon.space

Julien Moura Developpeur Python Pigiste @ GeoTribu @geojulien @geojulien@mapstodon.space

Cet atelier présente...

• Les relations
• PostgreSQL Notify
• Traitement vers la base de données
• Gérer la base de données depuis l'explorateur
• Sauver un projet en base de données
• Log SQL

Relations

Cas d'utilisation

• NM Relation

• Les aéroports ont des companies aériennes...

• et les companies aériennes ont des aéroports

• Voir QGIS doc pour plus d'informations

Initialiser le modele de données

Créer la base

createdb pgqgis_tips

Vérifier que l'on peut s'y connecter

psql pgqgis_tips

Récupérer le fichier airports.sql et chargez le

psql pgqgis_tips -f airports.sql

👀 Jetons un oeil au modèle de données

Et maintenant dans QGIS ...

• On crée la connexion : Explorateur > PostgreSQL > Nouvelle connexion...
  • Nom: pgqgis_tips
  • Hôte: 127.0.0.1
  • port: 5432
  • Base de données: pgqgis_tips
  • Authentification si pas en trust sur connexion locale
  • ⚠️Cocher Lister les tables sans géométries

Et initialiser le projet ...

• Selectionner les 3 tables
• Puis Ajouter les tables sélectionnées au projet
• BONUS Ajouter une couche OpenStreetMap
  • et changer le CRS du projet : 3857

Découvrir les relations

• Projet > Propriétés > Relations
• Découvrir les relations
• Tout selectionner > OK

Ouvrir formulaire airports/airlines

Qu'observez-vous ?

Configurer formulaire airports

Ajouter une compagnie

Essayer d'ajouter KLM (à Amsterdam) depuis le formulaire enfant de l'aéroport Toulouse

Qu'observez-vous?

Ajouter une compagnie

• La couche airports_airlines est passé en édition aussi
  • Pas dans toutes les versions de QGIS
  • Fonctionne uniquement depuis le formulaire
• Impossible de faire le lien avec nextval('locations.airports_airlines_id_seq'::regclass)

Evaluer les clés primaires

• Relations ont besoin de PK valides, pas temporaire
  • i.e. nextval('locations.airports_id_seq'::regclass)
• ⚠️Pas suffisant...
  • On veut éditer toutes les couches de données liées
  • Sauver dans le bon ordre (référencées en premier!)
  • Et si y a des erreurs!

Groupe de transaction automatique

Re-Ajouter une compagnie

Re-Essayer d'ajouter KLM (à Amsterdam) depuis le formulaire enfant de l'aéroport Toulouse

⚠️NE PAS SAUVEGARDER⚠️

Qu'observez-vous?

Re-Ajouter une compagnie

Depuis une console psql

pgqgis_tips=# select * from locations.airlines;

Qu'observez vous?

Puis relancer la même requête aprés sauvegarde.

Modifier un aéroport

• Modifier le nom de l'aéroport Toulouse Blagnac
• ⚠️NE PAS SAUVEGARDER⚠️
• Depuis une console psql

pgqgis_tips=# update locations.airports set airport_name = 'Toulouse or not to lose!' where id = 1;

Qu'observez vous?

Puis sauvegardez.

Groupe de transaction automatique

• Ouvre une transaction en base ➡ Fermée lors de la sauvegarde
• TOUTES les couches de données éditées en même temps
• 👍 Evalue les triggers
• 👎 Transaction longue ➡ Problèmes
  • Verrouillage sur les lignes modifiées ➡ bloque les autres transactions

Groupe de transactions mises en mémoire tampon ⭐ Nouveau en 3.26 ⭐

On y retourne...

Re-Re-Ajouter une compagnie

Re-Re-Essayer d'ajouter Turkish Airlines (à Istanbul) depuis le formulaire enfant de l'aéroport de Lyon

⚠️NE PAS SAUVEGARDER⚠️

Qu'observez-vous?

Re-Re-Ajouter une compagnie

Depuis une console psql

pgqgis_tips=# select * from locations.airlines;

Qu'observez vous?

Puis relancer la même requête aprés sauvegarde.

Re-Modifier un aéroport

• Re-Modifier le nom de l'aéroport Toulouse Blagnac
• ⚠️NE PAS SAUVEGARDER⚠️
• Depuis une console psql

pgqgis_tips=# update locations.airports set airport_name = 'Toulouse or not to lose!' where id = 1;

Qu'observez vous?

Puis sauvegardez.

Groupe de transactions mises en mémoire tampon

• Toutes les modifications mises en tampons ➡ rejouées lors de la sauvegarde
  • Dans le bon ordre
  • Dans une seule transaction
• 👎 Pas d'évaluation des triggers live (fait lors de la sauvegarde)
• Le dernier qui sauve a gagné!

PostgreSQL Notify

• Déclencher un rafraichissement de la donnée sur un "évènement" PostgreSQL
• Insert, Update, Delete, ...

Créer un aéroport depuis la base

à Nantes!

pgqgis_tips=# insert into locations.airports (airport_name, geom) values( 'Nantes', st_geomfromtext('POINT(-1.5546 47.2191)', 4326));

ou supprimer

delete from locations.airports where id = 6

Rien ne se passe: on doit rafraichir ou se déplacer dans la carte!

PostgreSQL configuration

CREATE FUNCTION notify_modified_airports() RETURNS trigger
LANGUAGE plpgsql
AS $$ 
BEGIN 
NOTIFY qgis, 'airports_modified';
RETURN NULL;
END; 
$$;


CREATE TRIGGER notify_modified_airports 
AFTER INSERT OR UPDATE OR DELETE OR TRUNCATE ON locations.airports
FOR EACH STATEMENT EXECUTE PROCEDURE notify_modified_airports();

QGIS configuration

Modifier la base

QGIS se met à jour tout seul!

Pas seulement pour le rafraichissement

• Comment ? Avec un peu de magie Python 🪄
• Ou ? Plugin / Script de démarrage / Macros / console Python
• Pour faire quoi ? ✨ Ce que vous voulez !!!*

Par exemple: depuis la console Python (Extensions > Console Python)

def onNotify():
   iface.messageBar().pushInfo("Notification", "Les aéroports ont été modifiés 😱 !!")

iface.activeLayer().dataProvider().notify.connect(onNotify)

*Dans les limites de ce que propose l'API QGIS

📝 Exercice

Ajouter une anotation pour chaque nouvelle compagnie aérienne

Réponse

Envoie l'id de la nouvelle ligne...

CREATE FUNCTION notify_new_airlines() RETURNS trigger
LANGUAGE plpgsql
AS $$
DECLARE
  v_txt text;
BEGIN
  v_txt := format('new_airlines %s', NEW.id);
PERFORM pg_notify('qgis', v_txt);
RETURN NULL;
END; 
$$;


CREATE TRIGGER notify_modified_airports 
AFTER INSERT ON locations.airlines
FOR EACH ROW EXECUTE PROCEDURE notify_new_airlines();

Pour chaque ligne

Réponse

Un petit peu de Python et d'API QGIS

def createAnnotationOnNotify(msg):
    id = int(msg[13:len(msg)])
    airlines = QgsProject.instance().mapLayersByName("airlines")[0]
    new_feat = airlines.getFeature(id)
    
    annotation = QgsTextAnnotation()
    annotation.setDocument(QTextDocument("✨✨Nouvelle compagnie {} ici !! ✈✈ ".format(new_feat["airline_name"])))
    annotation.setMapPosition(new_feat.geometry().asPoint())
    annotation.setMapPositionCrs(airlines.sourceCrs())
    annotation.setFrameSizeMm(QSizeF(50,25))

    QgsProject.instance().annotationManager().addAnnotation( annotation );
    
QgsProject.instance().mapLayersByName("airlines")[0].dataProvider().notify.connect(createAnnotationOnNotify)

Réponse

et c'est bon

pgqgis_tips=# insert into locations.airlines (airline_name, geom) values( 'Iberia', st_geomfromtext('POINT(2.1512 41.3894)', 4326));

Rafraichir les autres couches ?

• Cas d'utilisation :
  • Gérer 1 réseau de noeuds et lignes
  • Rafraichir les noeuds en fonction de lignes qui s'intersectent en utilisant des triggers
• NON !! : Préférer les dépendances de données

Traitement vers la base de données

Envoyer la sortie d'un traitement directement en base de données

Générer les polygones de Voronoï...

... sur les compagnies aériennes, directement en base

Pourquoi? Je sais pas mais c'est joli🌺🌸

Vérification en base

pgqgis_tips=# \d locations.voronoi
                           Table « locations.voronoi »
   Colonne    |          Type          | Collationnement | NULL-able | Par défaut 
--------------+------------------------+-----------------+-----------+------------
 id           | integer                |                 | not null  | 
 geom         | geometry(Polygon,4326) |                 |           | 
 airline_name | character varying      |                 |           | 
Index :
    "voronoi_pkey" PRIMARY KEY, btree (id)

pgqgis_tips=# select count(*) from locations.voronoi;
 count 
-------
     8
(1 ligne)

Gérer la base depuis l'explorateur

📝 Exercice

• Ajouter un champs texte à la table voronoi
• Supprimer ce champs
• Exécuter un requête pour récupérer les 2 premières compagnies aériennes
• ... Et la visualiser dans QGIS
• Exporter la table voronoi dans un fichier GeoPackage
• Supprimer la table voronoi
• Créer une table / un schéma

Et vérifier ce qui se passe en base

Est-ce que ça remplace le DB Manager ?

• Oui ... mais pas complètement
• Certains fonctionnalités manquent
  • Créer/gérer des contraintes
  • Créer une vue depuis une requête
  • Modifier une colonne
  • Historisation de table (Quelqu'un utilise ça?!!)
  • ...

DB Manager

• N'utilise pas l'API QGIS des fournisseurs de données
  • Complète duplication de code en Python
• Pas bien testé

Préférer l'accés depuis l'explorateur chaque fois que c'est possible!

Sauver le projet en base

Activer l'option

Projet > Enregistrer sous > PostgreSQL...

Projet > Ouvrir depuis > PostgreSQL...

Vérification en base

pgqgis_tips=# \d locations.qgis_projects
              Table « locations.qgis_projects »
 Colonne  | Type  | Collationnement | NULL-able | Par défaut 
----------+-------+-----------------+-----------+------------
 name     | text  |                 | not null  | 
 metadata | jsonb |                 |           | 
 content  | bytea |                 |           | 
Index :
    "qgis_projects_pkey" PRIMARY KEY, btree (name)

pgqgis_tips=# select name, metadata from locations.qgis_projects ; 
    name    |                                       metadata                                       
------------+--------------------------------------------------------------------------------------
 mon_projet | {"last_modified_time": "2023-03-09 10:44:45.330186", "last_modified_user": "julien"}
(1 ligne)

Fichiers externes

Vous pouvez embarquer un fichier externe dans le projet (SVG par exemple)

Log SQL ⭐ Nouveau en 3.26 ⭐

• Clic droit barre d'outils > Panneaux > Outils de debogage et développement
• Logue TOUTES les requêtes SQL (select, insert, update...)
• Logue aussi TOUTES les requêtes HTTP

Tester l'outil de log

• Activer le log
• Nettoyer
• Répéter certaines actions de l'atelier

Exploiter les fichiers de log

• Sauver les logs
• Utiliser jq en ligne de commande

Temps total d'exécution en ms

jq '[.[]."Temps total (ms)" | tonumber] | add' ~/test.json

Requêtes différentes

jq -r '.[]."SQL"' ~/test.json | grep -v "CLOSE qgis_"  |grep -v "FETCH FORWARD " | sed "s/\\\\//g" | sed "s/BEGIN READ ONLY.*CURSOR FOR //" | sort | uniq -c

Requêtes triées par temps d'exécution

jq -r '.[] | [ ."Temps total (ms)", ."SQL"]|join(" ")' ~/test.json | grep -v "CLOSE qgis_"  |grep -v "FETCH FORWARD " | sed "s/\\\\//g" | sed "s/BEGIN READ ONLY.*CURSOR FOR //" | sort -nr

Questions

Rencontres des Utilisateurs Francophones de QGIS
Clermont-Ferrand - 13/03/2023

Julien Cabieces / Julien Moura