Nous utilisons des cookies pour vous garantir la meilleure expérience sur notre site. Si vous continuez à utiliser ce dernier, nous considèrerons que vous acceptez l'utilisation des cookies. J'ai compris ! ou En savoir plus !.
Nom d'utilisateur    Mot de passe              Toujours pas inscrit ?   Mot de passe oublié ?

#1 jeu. 26 juillet 2018 18:39

Brice73
Membre
Date d'inscription: 28 juin 2018
Messages: 10

[Postgis] Créer une vue basé sur plusieurs tables

Bonjour

Je voudrais créer des vues contenant les champs de plusieurs tables (reliées entre elles par des clés étrangères).

Le soucis, c'est que les données d'une table ne sont pas forcément liées aux données d'une autre table.

Par exemple, j'ai une table "relevés_oiseaux" et une autre table "commentaire", mais toutes les données d'oiseaux n'ont pas forcément de commentaire.

Dans ma vue, je voudrais afficher les champs de la table "relevés_oiseaux" et de la table "commentaire". Mais en utilisant la clause

WHERE relevés_oiseaux.id = commentaire.relevés_oiseaux_ref_id


la requête ne me retourne que les données d'oiseaux qui comprennent des commentaires. Or je voudrais qu'elle me retourne aussi les données d'oiseaux ne comprenant pas de commentaire.

Comment puis-je faire ?

Merci de votre aide

Hors ligne

 

#2 jeu. 26 juillet 2018 19:26

Nicolas Ribot
Moderateur
Lieu: Toulouse
Date d'inscription: 9 sept. 2005
Messages: 1111

Re: [Postgis] Créer une vue basé sur plusieurs tables

Bonsoir,

Utilisez une jointure (LEFT JOIN) entre les tables:

Code:

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

Hors ligne

 

#3 jeu. 26 juillet 2018 21:07

Brice73
Membre
Date d'inscription: 28 juin 2018
Messages: 10

Re: [Postgis] Créer une vue basé sur plusieurs tables

Bonsoir

Merci de votre réponse rapide !

En effet, en cherchant, j'avais vu que l'on pouvait faire avec LEFT JOIN.

Le problème, c'est que dans la clause FROM, si j'ai bien compris, une table ne peut être mentionnée qu'une seule fois.
Et ma table "releves_oiseaux" est liée à plusieurs autres tables sur lesquelles j'ai aussi besoin de récupérer des champs pour ma vue.
Donc, comment fait-on pour appliquer LEFT JOIN entre par exemple : table_1 et table_2 ; table_1 et table_3 ; table_2 et table_4 ?

Pour les accents, merci de votre conseil, oui j'ai pris soin de ne pas en mettre dans la base de donnée. C'était juste pour l'exemple.

Brice

Hors ligne

 

#4 ven. 27 juillet 2018 09:40

tumasgiu
Membre
Lieu: Ajaccio
Date d'inscription: 5 juil. 2010
Messages: 678

Re: [Postgis] Créer une vue basé sur plusieurs tables

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 :

Code:

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)

Code:

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.

Hors ligne

 

#5 ven. 27 juillet 2018 12:04

Brice73
Membre
Date d'inscription: 28 juin 2018
Messages: 10

Re: [Postgis] Créer une vue basé sur plusieurs tables

Bonjour

Ok très bien merci, ça fonctionne !

Autre question : Sur ma vue, j'ai des champs de type booleen. Quand j'ouvre ma vue dans Qgis et que je vais dans la table attributaire, les champs booleen renvoie les valeurs 'f' ou 't'.
Y a t-il un moyen pour que les valeurs affichées soient 'VRAI' ou 'FAUX' ? Faut-il que j'utilise une fonction SI dans la requête SELECT ? Ou y a-t-il un moyen plus simple ?

Brice

Hors ligne

 

#6 sam. 28 juillet 2018 11:42

Mathieu Denat
Membre
Lieu: Montpellier
Date d'inscription: 5 mai 2010
Messages: 78

Re: [Postgis] Créer une vue basé sur plusieurs tables

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:

Code:

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,


Mathieu
C'est en forgeant qu'on devient forgeron

Hors ligne

 

#7 mar. 07 août 2018 14:59

Brice73
Membre
Date d'inscription: 28 juin 2018
Messages: 10

Re: [Postgis] Créer une vue basé sur plusieurs tables

Bonjour

Oui, en effet, j'ai trouvé dans les options de Qgis comment changer l'affichage des valeurs nulles par contre, je n'ai pas trouvé comment changer l’affichage d'un champ booléen...

Après vérification, Qgis interprète bien le champ concerné comme un type booléen.

En effet, la solution CASE WHEN marche très bien.
J'ai vu que vous aviez mis le type du résultat en text. Savez-vous combien de caractères peut stocker le type text ?
Car sur un autre champ, je stocke des géométries en format EWKT et ces géométries peuvent être assez volumineuses. J'ai mis le champ en type text mais je ne sais pas si cela peut poser problème pour de très grosses entités géométriques.

Merci

Hors ligne

 

#8 mer. 08 août 2018 10:01

tumasgiu
Membre
Lieu: Ajaccio
Date d'inscription: 5 juil. 2010
Messages: 678

Re: [Postgis] Créer une vue basé sur plusieurs tables

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 (mer. 08 août 2018 10:02)

Hors ligne

 

#9 mer. 08 août 2018 21:11

Brice73
Membre
Date d'inscription: 28 juin 2018
Messages: 10

Re: [Postgis] Créer une vue basé sur plusieurs tables

Bonjour

Je ne comprends pas bien ce que vous entendez par

mais c'est quelque-chose que j'aurais tendance à faire directement dans le visualiseur


C'est à dire ? comment faire cela depuis Qgis ?

Merci pour vos explications précises sur le type text, je comprends un peu mieux. Et j'avais déjà vu ce fameux terme 'TOAST' dans PgAdmin, je me demandais ce que c'était. J'ai maintenant la réponse smile

Hors ligne

 

#10 mer. 08 août 2018 22:56

tumasgiu
Membre
Lieu: Ajaccio
Date d'inscription: 5 juil. 2010
Messages: 678

Re: [Postgis] Créer une vue basé sur plusieurs tables

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 (mer. 08 août 2018 23:07)

Hors ligne

 

#11 jeu. 09 août 2018 09:53

Nicolas Ribot
Moderateur
Lieu: Toulouse
Date d'inscription: 9 sept. 2005
Messages: 1111

Re: [Postgis] Créer une vue basé sur plusieurs tables

Bonjour,

Puis-je vous demander pkoi vous stockez les geom au format texte ?
Ca prend tellement de place: si vous avez deja la geom en geometry, mieux vaut appeler la représentation text avec st_astext(geom) quand vous en avez besoin.

Nicolas

Hors ligne

 

#12 lun. 13 août 2018 08:43

lejedi76
Membre
Date d'inscription: 8 août 2013
Messages: 264

Re: [Postgis] Créer une vue basé sur plusieurs tables

Bonjour,

tumasgiu a écrit:

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.


Brice73 a écrit:

C'est à dire ? comment faire cela depuis Qgis ?


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 (lun. 13 août 2018 08:45)

Hors ligne

 

#13 mar. 14 août 2018 10:54

Brice73
Membre
Date d'inscription: 28 juin 2018
Messages: 10

Re: [Postgis] Créer une vue basé sur plusieurs tables

Bonjour

Merci pour vos réponses.

Tumasgiu, si je créé des colonnes calculés dans Qgis, le soucis, c'est qu'il faut les recréer à chaque fois que l'on rouvre la vue dans Qgis. Ou alors, il y a un moyen qu'elles se créent automatiquement ?

Nicolas, les données récupérées dans la BDD sont issues d'une application Android qui stocke toutes les données (points, lignes et polygones) sous forme de simple coordonnées GPS. Donc passer par le format EWKT me paraissait être la chose la plus simple pour passer des coordonnées GPS au type geometry de Postgis.

Lejedi76, en effet la solution de changer l'outil d'édition dans les propriétés de la couche fonctionne mais la modification de l'outil d'édition n'est pris en compte que lorsque l'on édite les données. Donc après avoir fait la modification que tu as décrite, tant que l'on ouvre juste la table attributaire sans modifier les attributs en question, les valeurs affichées restent 't' et 'f'...

Autre question : J'ai une vue affichant les données de 3 tables : table oiseaux, table point et table ligne. Sachant que des données d'oiseaux peuvent être liés à un point et une ligne ou seulement à un point.
Comment n'afficher dans la vue que les données liés à un point mais n'étant pas liés à une ligne ?
Concrètement, ce que je n'arrive pas à faire, c'est demander d'afficher les données de ma table1 ne devant pas avoir de lien avec la table2.

Hors ligne

 

#14 mar. 14 août 2018 14:00

tumasgiu
Membre
Lieu: Ajaccio
Date d'inscription: 5 juil. 2010
Messages: 678

Re: [Postgis] Créer une vue basé sur plusieurs tables

Brice73 a écrit:

Bonjour
Tumasgiu, si je créé des colonnes calculés dans Qgis, le soucis, c'est qu'il faut les recréer à chaque fois que l'on rouvre la vue dans Qgis. Ou alors, il y a un moyen qu'elles se créent automatiquement ?


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.


Brice73 a écrit:

Autre question : J'ai une vue affichant les données de 3 tables : table oiseaux, table point et table ligne. Sachant que des données d'oiseaux peuvent être liés à un point et une ligne ou seulement à un point.
Comment n'afficher dans la vue que les données liés à un point mais n'étant pas liés à une ligne ?
Concrètement, ce que je n'arrive pas à faire, c'est demander d'afficher les données de ma table1 ne devant pas avoir de lien avec la table2.


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 (mar. 14 août 2018 14:02)

Hors ligne

 

#15 mer. 22 août 2018 20:03

Brice73
Membre
Date d'inscription: 28 juin 2018
Messages: 10

Re: [Postgis] Créer une vue basé sur plusieurs tables

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.


Ok merci. Je ne m'étais jamais intéressé à ce type de fichier. J'ignorais ce qu'étais un fichier de définition de couche. Et, en effet, ça a l'air très pratique !

Merci pour le rappel des fonctions JOIN. Je pense maintenant avoir bien compris leur fonctionnement.
Mais ce que je cherche à faire est l'inverse de INNER JOIN. C'est à dire de renvoyer toutes les combinaison de la table a X la table b quand le prédicat p n'est pas vrai (j'applique le prédicat sur les champs permettant de joindre les 2 tables). J'ai déjà essayé d'utiliser l'opérateur : '<>', mais quand j'actualise les données de la vues, ça se met à ramer... Donc je pense que ce n'est pas bonne solution.
Pour l'instant, je fais autrement, j'ai créé 2 vues différentes.

Hors ligne

 

#16 mer. 22 août 2018 20:45

tumasgiu
Membre
Lieu: Ajaccio
Date d'inscription: 5 juil. 2010
Messages: 678

Re: [Postgis] Créer une vue basé sur plusieurs tables

J'ai déjà essayé d'utiliser l'opérateur : '<>', mais quand j'actualise les données de la vues, ça se met à ramer... Donc je pense que ce n'est pas bonne solution.


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.

Hors ligne

 

#17 jeu. 23 août 2018 00:11

Nicolas Ribot
Moderateur
Lieu: Toulouse
Date d'inscription: 9 sept. 2005
Messages: 1111

Re: [Postgis] Créer une vue basé sur plusieurs tables

Bonsoir,

Pour matérialiser les conditions négatives, une solution efficace sont les anti join:

Code:

select ....
from table t 
where not exists (
    select null
    from t1
    where <condition vraie pour t et t1>
)

Nicolas

Hors ligne

 

Pied de page des forums

Powered by FluxBB

Partagez  |