Le portail francophone de la géomatique

Bonsoir,

Utilisez une jointure (LEFT JOIN) entre les tables:

select ... 
from  relevés_oiseaux r LEFT JOIN commentaire c on r.id = c.relevés_oiseaux_ref_id

Vous aurez ainsi TOUS les records de la table relevés_oiseaux (sauf si vous ajoutez une clause WHERE), avec une valeur nulle pour les colonnes de la table commentaire pour les lignes qui ne joignent pas.

Je vous invite à ne pas utiliser d'accents dans vos noms de table !

Nicolas

Salut,

rien n'interdit d'inclure plusieurs fois une même table dans une requête.
Il faut juste les distinguer en leur donnant un alias afin que le moteur de base de données
sache s'y retrouver (et la personne qui écrit la requête aussi).

D'autre part votre requête ne nécessite pas de joindre plusieurs fois la table releves_oiseaux.
Une jointure s'applique à l'expression de table qui se trouve à sa gauche.
Ainsi, dans la requête :

SELECT * FROM releve_oiseau  r
LEFT JOIN commentaire c ON r.id = c.releve_oiseau_id
LEFT JOIN table2 t2 ON r.id = t2.releve_oiseau_id

Le LEFT JOIN table2 ne s'applique pas à la table commentaire mais au resultat de la jointure
releve_oiseau LEFT JOIN commentaire.

On peut écrire la requête de manière non ambiguë, en rajoutant des parenthèses,
(certains moteurs SQL l'imposent d'ailleurs)

SELECT * FROM 
( releve_oiseau  r LEFT JOIN commentaire c ON r.id = c.releve_oiseau_id )
LEFT JOIN table2 t2 ON r.id = t2.releve_oiseau_id

Vous pouvez donc chainer vos jointures assez facilement,
il faut juste faire attention au fait que  celles ci s'appliquent au résultat
de l'expression de table précédente.
Ainsi si vous restreignez les résultats avec un INNER JOIN dans une première jointure,
ceux ci n'apparaitront pas dans les résultats
et ne seront pas utilisés dans les jointures ultérieures.

Bonjour,

De mémoire (c'est donc à vérifier) c'est QGIS qui choisit d'afficher un 'f' et 't' dans le champ booléen.
De la même manière qu'il est possible de choisir d'afficher "valeure nulle" au lieu de NULL, il doit être possible de changer cet affichage dans les options de QGIS.

Attention à bien vérifier que le champ concerné est bien interprété comme un champ booléen par Qgis (onglet champs dans les propriétés de la couche). Les champs booléens ne sont pas supportés pour tes les formats, de mémoire ils ne sont supportés que pour les BDD (Spatialite, PostGIS), lors de l'export vers d'autres formats, ils sont convertis en texte.

Enfin, tenter d'insérer du texte dans un champ booléen ne fonctionnera pas en BDD.
Une solution serait de créer  un champ texte (à la création de la vue par exemple) et remplir ce champ en utilisant IF ou CASE WHEN (personnellement je n'utilise jamais IF). D'autres pourront dire laquelle des deux méthodes est la moins gourmande en ressources.

J'aurais fait un truc du style:

SELECT blabla,
      CASE
      WHEN champ_booleen IS TRUE THEN 'VRAI'::text
      WHEN champ_booleen IS FALSE THEN 'FAUX'::text
      ELSE 'réponse de normand (le champ n'est ni vrai ni faux!)'::text
      END
FROM blabla

Bonne journée,

Salut,

il me semblait aussi que l'on pouvait modifier la représentation des valeurs booléennes dans QGIS,
mais je n'ai pas trouvé non plus.

La solution proposée par Mathieu est parfaitement valide, mais c'est quelque-chose
que j'aurais tendance à faire directement dans le visualiseur, ici QGIS, ce afin de dissocier la requête
de la manière dont elle se présente.

Pour votre question sur le type text, la documentation dit que sa taille est illimitée.
Il y a certainement une limite, assez difficile à atteindre je suppose.
Si ce champ EWKT est un champ de table, et pas juste un champ calculée dans une vue,
il faut cependant avoir l'esprit la façon dont Postgres stocke les informations des tables :

Les données d'une même table sont stockées dans une série de fichier ou les lignes s'enregistrent
séquentiellement, ces fichiers sont appelés pages.
La taille par défaut d'une page est de 8KB, et une ligne ne peut pas commencer sur une page et terminer
dans la suivante.
Cette contrainte implique donc qu'une ligne ne peut pas faire plus de 8KB, et que le nombre de page
va exploser si beaucoup de lignes sont volumineuses sans excéder toutefois la limite de 8KB
(Postgres fixe ce critère de taille à 2KB)

Pour contourner cette difficulté, PostgreSQL a deux techniques :
compresser les champs volumineux, ou les stocker dans un fichier à part. Ce mécanisme est appelé TOAST.

SI vos géométries sont vraiment grosses, elles ont des chances de se retrouver dans la TOAST TABLE,
ce qui en soit n'est pas une mauvaise chose, mais ce stockage alternatif induit un coût lors de la récupération
de ces données pendant les requêtes (on appelle cela de-toaster une ligne), qui peut être non négligeable.

Sinon, un petit comparatif sympa sur les différents types de chaines de caractère (en anglais):
https://www.depesz.com/2010/03/02/charx … r-vs-text/

Dernière modification par tumasgiu (Wed 08 August 2018 10:02)

Je voulais dire que j'aurai créé des colonnes calculées dans QGIS pour gérer
la représentation textuelle des colonnes booléennes, pour dissocier application
et présentation.
Mais j'avoue, c'est du chipotage de développeur.

Dernière modification par tumasgiu (Wed 08 August 2018 23:07)

Bonjour,

Propriété de la couche > Champs > Cliquer sur Edition de texte et choisir Boite à cocher
et dans représentation d'un état coché mettre VRAI et représentation d'un état décoche mettre FAUX.

Cordialement,

Dernière modification par lejedi76 (Mon 13 August 2018 08:45)

Oui si vous ouvrez votre vue dans plusieurs projets QGIS, il vous faudra à chaque fois recréer ces colonnes.
Toutefois, je ne suis pas un pro de QGIS, mais je crois que si vous enregistrez la définition de la couche, vous avez la possibilité
d'enregistrer aussi les colonnes virtuelles.
Une fois enregistrée, vous utilisez le ficier qlr pour charger votre vue dans votre projet au lieu de charger la couche postgis.

Récapitulatif :

a INNER JOIN  b ON p
  -> renvoie toutes les combinaison de la table a X la table b quand le prédicat p est vrai.

a OUTER LEFT JOIN b ON p
->
  renvoie toutes les combinaison de la table a X la table b quand le prédicat p est vrai
   ainsi que les lignes de a n'ayant pas satisfait p;
   Pour ces lignes de a qui n'ont pas été associées à b,
    les champs de b sélectionnées dans la clause SELECT valent tous NULL

a OUTER RIGHT JOIN b ON p
->
  idem que la jointure précédente sauf que ce sont les lignes de b n'ayant pas satisfait p qui sont renvoyées.

a FULL OUTER JOIN b ON p
  -> équivaut à LEFT JOIN b ON p UNION a RIGHT JOIN b ON p

Dernière modification par tumasgiu (Tue 14 August 2018 14:02)

Effectivement ca n'est pas la bonne solution, la requête rame car il y a explosion combinatoire, ce que vous faites
revient presque faire un CROSS JOIN privé des lignes qui concordent.

Ce n'est pas les prédicat de jointure qu'il faudrait changer, mais autre chose.

Merci pour votre réponse Nicolas, je ne savais pas que l'on pouvait faire des anti join.

Je suis maintenant sur la problématique d'édition des vues. Pour pouvoir mettre à jour les vues, j'ai créé des triggers. Mais pour écrire les requêtes de mises à jour des vues, je rencontre le problème suivant :
J'ai une table "de référence" (nommée "taxref"), complétée avec des données officielles qu'il ne faut pas que je modifie. Dans cette table, j'ai par exemple, les champs "cd_nom" (qui est la clé primaire) et "nom_scien".
J'ai une autre table ("table_2") contenant des données que je peux modifier et qui fait référence à la table "taxref" sur sa clé primaire. J'ai par exemple les champs "caracteristique", "nombre", "cd_nom_ref_id" (clé étrangère faisant référence à la table "taxref")
Sur ma vue, j'affiche les champs de "table_2" et le champ "nom_scien" de "taxref".

Comment écrire dans ma requête update, que quand une valeur du champ "nom_scien" est modifiée, il faut modifier la valeur dans le champ "cd_nom_ref_id" en prenant (dans la table taxref) la valeur du champ "cd_nom" correspondante à la valeur mise à jour (qui doit forcément être une valeur déjà contenue dans la table "taxref") ?
Je ne sais pas non plus comment faire pour les requête create et delete...

Maintenant j'attends le post suivant qui va dire
que çà n'est pas une bonne idée d'utiliser une clef naturelle,
de surcroit textuelle, comme clef primaire

Bonjour,

Ben non, les règles sont faites pour avoir des exceptions

Quelque chose comme çà (fautes de syntaxe incluses):

CREATE OR REPLACE FUNCTION upd_view() RETURNS TRIGGER AS
$$
DECLARE
  new_nom TEXT; --variable servant à stocker la nouvelle valeur cd_nom
BEGIN
  IF OLD.nom_scien  <> NEW.nom_scien THEN
        SELECT cd_nom INTO new_nom FROM taxref WHERE nom_scien = NEW.nom_scien
        IF new_nom IS NULL THEN
             -- si aucun nom scientifique ne correspond, on ne met rien à jour.
             RAISE EXCEPTION  'Nom scientifique incorrect : %', NEW.nom_scien;
        END IF;
        UPDATE table_2 SET cd_nom = new_nom WHERE id_table_2 = NEW.id_table_2;
  END IF;
END;
$$ LANGUAGE plpgsql

Dernière modification par tumasgiu (Wed 24 October 2018 14:14)

De ce que j'ai compris, vous joignez à votre table table_2 des informations relative à l'espece d'oiseau concernée par chacune des lignes ?
Si c'est le cas, votre vue comportera exactement une fois chaque ligne de table_2.
Dans ce cas pourquoi ne pas uitiliser l'identifiant de table_2 à la place de row_number ?

Autre question à laquelle je n'arrive pas à trouver de réponse (même en ayant passé quelques heures à chercher sur internet) : dans une fonction trigger, avant le END; final de la requête, apparemment, il faut mettre RETURN 'new', 'old', 'null' ou un autre attribut. Savez-vous à quoi sert ce RETURN ?

Oubli de ma part.
RETURN est l'instruction PL/PGSQL qui détermine quelle valeur sera retournée par la fonction,
ce qui est un des concepts de base de l'algorithmique.
Ici en l’occurrence la fonction doit retourner un objet du type event_trigger.
Les variables OLD et NEW sont de ce type
On peut se référer à la documentation de postgresql pour savoir quelle valeur renvoyer.
Dans le cas d'un trigger INSTEAD OF, la seule rêgle est que NULL doit être renvoyé si
aucune mise à jour n'est effectuée, NEW pour les instructions INSERT & DELETE,
et OLD, pour DELETE.
cf. https://doc.postgresql.fr/10/plpgsql-trigger.html

Pour approfondir:
https://doc.postgresql.fr/10/plpgsql.htm

La doc officielle est votre amie, même si elle est un peu austère.

Dernière modification par tumasgiu (Fri 26 October 2018 15:51)

Merci pour votre réponse précise sur l'instruction RETURN

Oui c'est bien cela, sauf que table_2 est aussi lié à une table contenant la géométrie des entités. Et une ligne de table_2 peut être liée à plusieurs entités géométriques donc à plusieurs lignes de la table contenant la géométrie.
Donc si je met l'id de la table_2 à la place de row_number dans la vue, cet id ne sera pas unique.


BenB, je suis d'accord, ça peut être super intéressant !

Ah oui, c'est intéressant en effet, pourquoi ne pas échanger sur un fil séparé plutôt ?
Ceux ci pourraient peut être aider d'autres personnes à faire les bons choix.
Ou rédiger un petit article après vos échanges et le poster je sais pas.
Enfin, simple suggestion.

Oui, je pense que çà devrait fonctionner, sans trop de problème.
Je vous conseille de faire tout de même quelques test avant de passer en production.