Relations pour traitement SIG

Version imprimable

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:

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 !

1 pièce(s) jointe(s)

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:

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 :
Pièce jointe 463066

A +

02/04/2019, 10h26
Nighthawk

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 !
02/04/2019, 13h07
Waldar

Oui, supprimez votre condition WHERE, et ajoutez une colonne comme ceci :

Code:

max(case PV.ccthp when 'V' then 1 else 0 end) as vacant

Super, grâce à vos réponses j'ai pu faire ce que je voulais. Ma requête finale ressemble à ça :

Code:

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 !

04/04/2019, 17h02
Nighthawk

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. :aie:

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 !
15/04/2019, 09h47
Nighthawk
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é... :oops:)

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:

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 .