IdentifiantMot de passe
Loading...
Mot de passe oublié ?Je m'inscris ! (gratuit)
Navigation

Inscrivez-vous gratuitement
pour pouvoir participer, suivre les réponses en temps réel, voter pour les messages, poser vos propres questions et recevoir la newsletter

Requêtes PostgreSQL Discussion :

Relations pour traitement SIG


Sujet :

Requêtes PostgreSQL

  1. #1
    Membre du Club
    Homme Profil pro
    Urbaniste
    Inscrit en
    Septembre 2014
    Messages
    51
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : France, Aveyron (Midi Pyrénées)

    Informations professionnelles :
    Activité : Urbaniste
    Secteur : Conseil

    Informations forums :
    Inscription : Septembre 2014
    Messages : 51
    Points : 49
    Points
    49
    Par défaut Relations pour traitement SIG
    Bonjour,

    Avant toutes choses, je vous demande pardon si j'emploie des termes inappropriés (qui peuvent d'ailleurs porter à confusion...), mais je ne suis pas spécialiste de l'univers SQL (et des bases de données en général en fait...). En revanche, je suis intéressé par ce domaine (dans lequel il faudrait vraiment que je me plonge sérieusement parce que ce que j'ai pu entrevoir permettrait de gagner pas mal de temps dans mon travail) et j'aurais voulu avoir votre avis sur une requête que j'ai élaborée.

    Pour planter le décors, je suis urbaniste et je cherche à faire le lien entre un jeu de données MAJIC et un cadastre EDIGEO.
    Niveau logiciel, j'utilise principalement QGIS pour la partie géographique (et notamment avec le plugin cadastre qui permet d'importer les fichiers MAJIC ainsi que les cadastres EDIGEO dans une base de données Postgis), et j'ai également installé PostgreSQL 11.2 ainsi que PostGIS 2.5.1.

    Voici mon besoin : Je souhaite utiliser les relations existant entre les différentes tables composant les données MAJIC afin de rassembler les informations correspondant aux noms des propriétaires pour chaque parcelle ainsi que la présence (ou non) de locaux vacants et, le cas échéant, leur nombre, leur type, et leur niveau d'entretien.
    Afin que ce soit simple à utiliser par la suite je me suis dit que j'allais faire une seule et unique requête, mais ça rame pas mal et je me demande si, d'une, la rédaction de la requête est bonne (comme je ne connais pas le langage SQL, j'ai essayé de rassembler différentes choses que j'ai pus trouver sur internet mais j'ai le sentiment que ça pourrait être optimisé), ou s'il ne vaut mieux pas passer par des "tables temporaires" afin d'alléger un peu les traitements et limiter la quantité de donnée placée en mémoire tampon...

    Pour info, voici la requête que j'ai tentée :
    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    20
    21
    22
    23
    24
    25
    26
    27
    28
    29
    30
    31
    32
    33
    34
    35
    36
    37
    38
    39
    40
    41
    42
    43
    44
    45
    46
    47
    48
    49
    50
    51
    52
    53
    54
    55
    56
    57
    SELECT	-- Liste des champs à récupérer dans ma table
    	"parcelle".parcelle,	-- Indispensable pou faire une jointure sur QGIS
    	"parcelle".ccodep AS dept,
    	"parcelle".ccocom AS com,
    	"parcelle".comptecommunal,
    	"parcelle".voie AS id_voie,
    	"parcelle".dvoilib AS voie,
    	count("proprietaire".proprietaire) AS nb_prop,	-- Utile pour identifier les terrains régimes de propriété particuliers
    	string_agg("proprietaire".proprietaire , ', ') AS proprietaire,
    	string_agg("proprietaire".ccodro, ', ') AS ccodro,
    	string_agg("proprietaire".gdesip , ', ') AS gdesip,
    	string_agg("proprietaire".gtoper, ', ')AS gtoper,
    	max("proprietaire".dnatpr) AS dnatpr,
    	max("proprietaire".ccogrm) AS ccogrm,
    	max("proprietaire".dsglpm) AS dsglpm,
    	max("proprietaire".dforme) AS dforme,
    	max("proprietaire".dformjur) AS dformjur,
    	string_agg("proprietaire".ddenom, ', ') AS ddenom,	-- Concaténation du (des) nom(s) de propriétaire(s)
    	max("proprietaire".dlign6) AS dlign6,
    	max("proprietaire".dlign5) AS dlign5,
    	max("proprietaire".dsiren) AS dsiren,
    	string_agg("proprietaire".dnomus, ', ') AS dnomus,
    	string_agg("proprietaire".dprnus, ', ') AS dprnus,
    	max("temp1".local10),
    	max("temp1".invar),
    	max("temp1".pev),
    	string_agg("temp1".ccoaff, ', '),	-- Usage du local (logement, commerce, artisanat,...)
    	string_agg(CAST ("temp1".dcetlc AS TEXT), ', '),	-- Entretien du local
    	max("temp1".ccthp),	-- Type d'occupation du local (vacant,...)
    	count("temp1".ccthp),	-- Une fois agrégé, permet de connaître le nombre de logements vacants par parcelle
    	avg("temp1".dvltpe)
    FROM
    	"majic_2018"."parcelle",
    	"majic_2018"."proprietaire",	-- Ces parcelles ont une relation directe : comptecommunal
    	(SELECT
    		"local10".parcelle,
    		"local10".local10,
    		"local10".ccodep AS dept,
    		"local10".ccocom AS com,
    		"local10".invar,
    		"pev".pev,
    		"pev".ccoaff,
    		"pev".dcetlc,
    		"pev".ccthp,
    		"pev".dvltpe
    	FROM
    		"majic_2018"."local10",	-- Pas de relation directe de "pev" à "parcelle", donc je dois passer par "local10"
    		"majic_2018"."pev"
    	WHERE
    		"local10".local10 = "pev".local10
    	 AND "pev".ccthp = 'V'	-- J'en profite pour ne garder que les locaux vacants
    	) "temp1"
    WHERE
    	("parcelle".comptecommunal = "proprietaire".comptecommunal)
    OR
    	("temp1".parcelle = "parcelle".parcelle)
    GROUP BY "parcelle".parcelle ;
    J'ai essayé d'annoter un peu tout ça pour m'y retrouver le jour où je reviendrais dessus (parce que je n'en traite pas tous les jours non plus de ces fichiers-là) et ça m'a permis de voir quelque incohérences aussi...
    Pour info, là je n'ai fait que la requête, mais je dois encore ajouter la commande pour créer une table à partir de cette requête, mais ça ne semble pas trop compliqué d'après ce que j'ai vu (j'espère...).

    Donc si vous avez des conseils, astuces, ou autres informations qui pourraient m'aider dans ma quête de la requête simple et efficace, je suis preneur ! D'avance merci pour vos lumières !

  2. #2
    Rédacteur

    Avatar de SQLpro
    Homme Profil pro
    Expert bases de données / SQL / MS SQL Server / Postgresql
    Inscrit en
    Mai 2002
    Messages
    21 736
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : France, Var (Provence Alpes Côte d'Azur)

    Informations professionnelles :
    Activité : Expert bases de données / SQL / MS SQL Server / Postgresql
    Secteur : Conseil

    Informations forums :
    Inscription : Mai 2002
    Messages : 21 736
    Points : 52 447
    Points
    52 447
    Billets dans le blog
    5
    Par défaut
    D'abord les jointures se font avec l'opérateur JOIN depuis la norme de 1992, soit 27 ans…

    Ensuite vos double quote ne servent à rien.

    Mettez des alias de table

    Tout ce qui n'est pas dans un calcul d'agrégat doit figurer dans le groupage…

    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    20
    21
    22
    23
    24
    25
    26
    27
    28
    29
    30
    31
    32
    33
    34
    35
    36
    37
    38
    39
    40
    41
    42
    43
    44
    45
    SELECT	-- Liste des champs à récupérer dans ma table
    	PC.parcelle,	-- Indispensable pou faire une jointure sur QGIS
    	PC.ccodep AS dept,
    	PC.ccocom AS com,
    	PC.comptecommunal,
    	PC.voie AS id_voie,
    	PC.dvoilib AS voie,
    	count(PP.proprietaire) AS nb_prop,	-- Utile pour identifier les terrains régimes de propriété particuliers
    	string_agg(PP.proprietaire , ', ') AS proprietaire,
    	string_agg(PP.ccodro, ', ') AS ccodro,
    	string_agg(PP.gdesip , ', ') AS gdesip,
    	string_agg(PP.gtoper, ', ')AS gtoper,
    	max(PP.dnatpr) AS dnatpr,
    	max(PP.ccogrm) AS ccogrm,
    	max(PP.dsglpm) AS dsglpm,
    	max(PP.dforme) AS dforme,
    	max(PP.dformjur) AS dformjur,
    	string_agg(PP.ddenom, ', ') AS ddenom,	-- Concaténation du (des) nom(s) de propriétaire(s)
    	max(PP.dlign6) AS dlign6,
    	max(PP.dlign5) AS dlign5,
    	max(PP.dsiren) AS dsiren,
    	string_agg(PP.dnomus, ', ') AS dnomus,
    	string_agg(PP.dprnus, ', ') AS dprnus,
    	max(L.local10),
    	max(L.invar),
    	max(PV.pev),
    	string_agg(PV.ccoaff, ', '),	-- Usage du local (logement, commerce, artisanat,..)
    	string_agg(CAST (PV.dcetlc AS TEXT), ', '),	-- Entretien du local
    	max(PV.ccthp),	-- Type d'occupation du local (vacant,..)
    	count(PV.ccthp),	-- Une fois agrégé, permet de connaître le nombre de logements vacants par parcelle
    	avg(PV.dvltpe)
    FROM 
        majic_2018.parcelle AS P
    	JOIN majic_2018.PP AS PP ON PC.comptecommunal = PP.comptecommunal	-- Ces parcelles ont une relation directe : comptecommunal
        JOIN majic_2018.local10 AS L On L.parcelle = PC.parcelle
        JOIN majic_2018.pev AS PV ON  L.local10 = PV.local10
    WHERE 
        PV.ccthp = 'V'	-- J'en profite pour ne garder que les locaux vacants
    GROUP BY 
    	PC.parcelle,
    	PC.ccodep,
    	PC.ccocom,
    	PC.comptecommunal,
    	PC.voie,
    	PC.dvoilib;
    voici votre requête récrite.

    Pour en savoir plus, lisez mon livre :
    Nom : SQL.jpg
Affichages : 698
Taille : 47,4 Ko

    A +
    Frédéric Brouard - SQLpro - ARCHITECTE DE DONNÉES - expert SGBDR et langage SQL
    Le site sur les SGBD relationnels et le langage SQL: http://sqlpro.developpez.com/
    Blog SQL, SQL Server, SGBDR : http://blog.developpez.com/sqlpro
    Expert Microsoft SQL Server - M.V.P. (Most valuable Professional) MS Corp.
    Entreprise SQL SPOT : modélisation, conseils, audit, optimisation, formation...
    * * * * * Expertise SQL Server : http://mssqlserver.fr/ * * * * *

  3. #3
    Membre du Club
    Homme Profil pro
    Urbaniste
    Inscrit en
    Septembre 2014
    Messages
    51
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : France, Aveyron (Midi Pyrénées)

    Informations professionnelles :
    Activité : Urbaniste
    Secteur : Conseil

    Informations forums :
    Inscription : Septembre 2014
    Messages : 51
    Points : 49
    Points
    49
    Par défaut
    Merci pour ces informations !

    Effectivement, c'est beaucoup plus rapide avec cette rédaction ! Et ça prouve encore une fois que je dois vraiment me pencher sérieusement sur le SQL parce que ça pourrait réellement faciliter certains traitements...

    Juste une petite question du coup, est-il possible, à partir de cette requête, de garder toutes les lignes de ma table parcelle (au nombre de 22 227) et de ne pas réduire leur nombre en fonction des locaux vacants (ccthp='V', ce qui ne laisse que 270 lignes) ? En gros, est-il possible de renvoyer chaque ligne de ma table parcelle en précisant si, oui ou non, il y a une occurrence pour ccthp = 'V' ?

    Encore merci !

  4. #4
    Modérateur
    Avatar de Waldar
    Homme Profil pro
    Customer Success Manager @Vertica
    Inscrit en
    Septembre 2008
    Messages
    8 452
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Âge : 46
    Localisation : France, Val de Marne (Île de France)

    Informations professionnelles :
    Activité : Customer Success Manager @Vertica
    Secteur : High Tech - Éditeur de logiciels

    Informations forums :
    Inscription : Septembre 2008
    Messages : 8 452
    Points : 17 814
    Points
    17 814
    Par défaut
    Oui, supprimez votre condition WHERE, et ajoutez une colonne comme ceci :
    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    max(case PV.ccthp when 'V' then 1 else 0 end) as vacant

  5. #5
    Membre du Club
    Homme Profil pro
    Urbaniste
    Inscrit en
    Septembre 2014
    Messages
    51
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : France, Aveyron (Midi Pyrénées)

    Informations professionnelles :
    Activité : Urbaniste
    Secteur : Conseil

    Informations forums :
    Inscription : Septembre 2014
    Messages : 51
    Points : 49
    Points
    49
    Par défaut
    Super, grâce à vos réponses j'ai pu faire ce que je voulais. Ma requête finale ressemble à ça :
    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    20
    21
    22
    23
    24
    25
    26
    27
    28
    29
    30
    31
    32
    33
    34
    35
    36
    37
    38
    39
    40
    41
    42
    -- -----------------------------------------------------------------------------------------------------------
    -- Pour plus d'infos sur les données MAJIC : http://piece-jointe-carto.developpement-durable.gouv.fr/NAT004/DTerNP/html3/_ff_descriptif_tables_image.html#lotslocaux
    -- -----------------------------------------------------------------------------------------------------------
     
    SELECT	-- Liste des champs à récupérer dans ma table
    	PC.parcelle,	-- Référence cadastrale : Indispensable pour faire une jointure sur QGIS
    	max(PC.ccodep) AS dept,	-- Code département
    	max(PC.ccocom) AS com,	-- Code commune
    	max(PC.comptecommunal) AS comptecommunal,	-- Identifiant de ou des propriétaire(s) : Indispensable pour intégrer les infos des propriétaires
    	max(PC.voie) AS id_voie,
    	max(PC.dvoilib) AS voie,	-- Nom de la voie (donne notamment des infos sur le hameau)
    	count(PP.proprietaire) AS nb_prop,	-- Nombre de propriétaires : Utile pour identifier les terrains régimes de propriété particuliers (copropriété,...)
    	string_agg(PP.proprietaire , ', ') AS proprietaire,	-- Identifiant d'un propriétaire unique (personne physique ou morale)
    	string_agg(PP.ccodro, ', ') AS ccodro,	-- Droit du propriétaire (usufruit,...)
    	string_agg(PP.gdesip , ', ') AS gdesip,	-- Indicayeur du destinataire de l'avis d'imposition
    	string_agg(PP.gtoper, ', ')AS gtoper,	-- Personne physique (1) ou morale (2)
    	max(PP.dnatpr) AS dnatpr,	-- Nature de personne physique ou morale
    	max(PP.ccogrm) AS ccogrm,	-- Code groupe de personne morale (Etat, Région, coproprietaire,...)
    	max(PP.dsglpm) AS dsglpm,	-- Sigle de personne morale
    	max(PP.dforme) AS dforme,	-- Forme juridique abrégée majic2
    	max(PP.dformjur) AS dformjur,	-- Forme juridique
    	string_agg(PP.ddenom, ', ') AS ddenom,	-- Concaténation du (des) nom(s) de propriétaire(s)
    	max(PP.dlign6) AS dlign6,	-- Code postal ou, le cas échéant, pays de résidence
    	max(PP.dlign5) AS dlign5,	-- Ville de résidence (si pays étranger)
    	max(PP.dsiren) AS dsiren,	-- Numéro SIREN (personne morale)
    	string_agg(PP.dnomus, ', ') AS dnomus,	-- Nom d'usage (personne physique)
    	string_agg(PP.dprnus, ', ') AS dprnus,	-- Prénom d'usage (personne physique)
    	max(L.local10) AS local10,	-- Identifiant du local
    	max(L.invar) AS invar,	-- Identifiant du batiment
    	max(PV.pev) AS pev,	-- Identifiant pev
    	string_agg(PV.ccoaff, ', ') AS usage_loc,	-- Usage du local (logement, commerce, artisanat,..)
    	string_agg(CAST (PV.dcetlc AS TEXT), ', ') AS dcetlc,	-- Entretien du local
    	string_agg(PV.ccthp, ', ') AS typ_occup,	-- Type d'occupation du local (vacant,..)
    	count(PV.ccthp) filter (where ccthp = 'V') AS nb_vac,	-- Une fois agrégé, permet de connaître le nombre de logements vacants par parcelle
    	avg(PV.dvltpe) AS val_loc	-- Valeur locative moyenne d'un local (en fonction de la valeur locative de chaque local du bâtiment)
    FROM 
        majic_2018.parcelle AS PC
    	JOIN majic_2018.proprietaire AS PP ON PC.comptecommunal = PP.comptecommunal	-- Ces parcelles ont une relation directe : comptecommunal
        LEFT JOIN majic_2018.local10 AS L On L.parcelle = PC.parcelle	-- Il faut d'abord créer la relation entre local10 et parcelle...
        LEFT JOIN majic_2018.pev AS PV ON  L.local10 = PV.local10	-- ...Pour pouvoir, au final, créer une relation entre pev et parcelle (via local10)
    GROUP BY 
    	PC.parcelle;	-- On ne souhaite garder qu'une seule ligne par parcelle en agrégeant les données
    Du coup je me suis dit que garder l'information sur le type d'occupation des locaux pouvais être utile, mais je souhaitait compter le nombre de locaux vacants dans un bâtiment. Du coup j'ai appliqué un filter sur le count afin de ne compter que les locaux vacants. J'ai ensuite dérivé ce principe pour connaître le nombre de logements vacants (en ajoutant une condition de ccoaff=H (on parle d'habitation) et de dnupev=001 (on ne considère que les parties principales d'habitation)).

    Ensuite, j'ai remarqué que, par défaut, la jointure ne conservait que les lignes pour lesquelles il y avait une correspondance. Pour conserver toutes mes lignes parcelle (parce que j'ai quand même des propriétaires sur toutes mes parcelles), j'ai remplacé les JOIN par des LEFT JOIN.
    Et voilà, j'ai le résultat dont je rêvais. Encore merci pour vos explications !

  6. #6
    Membre du Club
    Homme Profil pro
    Urbaniste
    Inscrit en
    Septembre 2014
    Messages
    51
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : France, Aveyron (Midi Pyrénées)

    Informations professionnelles :
    Activité : Urbaniste
    Secteur : Conseil

    Informations forums :
    Inscription : Septembre 2014
    Messages : 51
    Points : 49
    Points
    49
    Par défaut
    Bonjour,

    Je reviens sur le sujet parce que je me suis rendu compte qu'il y avait une perte d'information dans ce traitement qui m'est utile, mais je sais pourquoi. Malheureusement, je n'ai pas réussi à trouver de solution pour éviter cette perte...
    Au début de la requête, j'ai quelque agrégations en comptage (nombre de logements, nombre de locaux, nombre de logements vacants,...). Sauf que je me suis rendu compte que ces comptages étaient faux.

    En fait l'erreur vient de la table proprietaire qui va "ajouter des lignes" à la table parcelle dès le début de l'opération. En effet, il y a parfois plusieurs propriétaires (ou plutôt de 1 à N propriétaires) pour une parcelle, donc je me retrouve avec autant de lignes (1 à N donc) pour un même code parcelle qu'il y a de propriétaires sur cette parcelle.
    Du coup quand je viens faire ma jointure avec les tables local10 puis pev, j'ai des valeurs qui se multiplient (au lieu d'avoir 1 fois chaque parcelle, je l'ai 1 à N fois, et mes valeurs local 10 et pev se trouvent copiées 1 à N fois), et lorsque je les compte, mon résultat est faussé (par exemple, j'ai 2775 logements d'après la table non traitée et j'en compte 4605 en sortie de traitement).

    Du coup je voulais savoir s'il existait une astuce pour pallier à ce genre de problèmes ?
    En sachant que la première réponse que j'envisage serait de faire le traitement en 2 temps : d'abord je fais la jointure entre mes parcelles et les proprietaires et je groupe par parcelle ; ensuite je joins les tables local10 puis pev au résultat précédent et c'est fini, je conserve ma donnée (pour des raisons d'efficience je vais faire ça dans un premier temps, mais je reste à l'écoute pour une solution "all in one").

    D'avance merci pour vos lumières !

  7. #7
    Membre du Club
    Homme Profil pro
    Urbaniste
    Inscrit en
    Septembre 2014
    Messages
    51
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : France, Aveyron (Midi Pyrénées)

    Informations professionnelles :
    Activité : Urbaniste
    Secteur : Conseil

    Informations forums :
    Inscription : Septembre 2014
    Messages : 51
    Points : 49
    Points
    49
    Par défaut
    Bonjour,

    J'ai finalement trouvé une solution assez simple. Mettre mes 2 requêtes l'une à la suite de l'autre... (tellement simple que je n'y avais même pas pensé... )

    Comme je souhaite créer mes tables directement et que je ne veux pas m'embêter avec ce qui existe, je voulais passer par un DROP TABLE, mais pour une raison que j'ignore postgresql me retourne systématiquement une erreur de syntaxe lorsque je le met...

    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    1
    2
    3
    4
    5
    6
    7
    ERROR:  ERREUR:  erreur de syntaxe sur ou près de « DROP »
    LINE 5: DROP TABLE IF EXISTS majic1;
            ^
     
     
    SQL state: 42601
    Character: 491
    Mais je dois dire que je ne sais absolument pas pourquoi ça bloque... En parallèle, j'ai aussi cette erreur sur une autre requête (qui n'a rien à voir avec celle-ci, c'est pour un import de fichier csv) lorsque je souhaite créer des colonnes dans une table (l'erreur me dit qu'il y a une erreur de syntaxe sur le type de données dans la colonne). Sauriez-vous d'où peut venir cette erreur .

Discussions similaires

  1. [XL-2010] Transformation d'un tableau pour traitement dans SIG
    Par martin.porret dans le forum Excel
    Réponses: 2
    Dernier message: 24/05/2014, 18h07
  2. API ou OCX gratuites pour traitement d'image
    Par uranium-design dans le forum VB 6 et antérieur
    Réponses: 9
    Dernier message: 02/09/2006, 17h03
  3. Réponses: 4
    Dernier message: 21/07/2006, 14h27
  4. [ADO.Net][C# 1.1]Comment réaliser relation pour 2 Dataset ?
    Par misa dans le forum Accès aux données
    Réponses: 2
    Dernier message: 07/01/2006, 07h55
  5. Programmation pour traitement de données
    Par benbois dans le forum Langages de programmation
    Réponses: 16
    Dernier message: 19/10/2005, 18h01

Partager

Partager
  • Envoyer la discussion sur Viadeo
  • Envoyer la discussion sur Twitter
  • Envoyer la discussion sur Google
  • Envoyer la discussion sur Facebook
  • Envoyer la discussion sur Digg
  • Envoyer la discussion sur Delicious
  • Envoyer la discussion sur MySpace
  • Envoyer la discussion sur Yahoo