#1 Tue 05 April 2016 18:25
- Benoit91
- Participant assidu
- Date d'inscription: 2 Oct 2008
- Messages: 263
postgres/postgis jointure sur champ à valeurs multiples
Bonjour
Je rencontre un problème de requête sql avec jointure.
Je m'explique:
Je dispose de 2 tables, 1 table parcelle (structurée avec le plugin cadastre de QGIS) et 1 table des logements sociaux.
Dans ma table des logements, j'ai un champ "refcada2" qui liste les parcelles sur laquelle ou lesquelles se situe la résidence des logements. J'ai donc ceci
logement libelle_residence refcada2
logement1 resid1 AH0060
logement2 resid2 AN0235,AN0236,AN0237
logement3 resid2 AN0235,AN0236,AN0237
Je souhaite récupérer les parcelles (geom) qui localises les résidences (1 logement est dans une résidence qui se situe sur 1 ou plusieurs parcelles). J'ai tenté se type de requête (le row_number c'est pour un affichage dans qgis)
SELECT ROW_NUMBER() OVER() AS gid,
cada_parcelle.idu_id,
cada_parcelle.geom
FROM cadastre.cada_parcelle
INNER JOIN habitat.temp_rpls ON cada_parcelle.code_insee=insee
WHERE cada_parcelle.idu_temp IN (refcada2)
GROUP BY cada_parcelle.idu_id,cada_parcelle.geom
mais je ne récupère que les parcelles uniques dans le champ refcada2 soit ma resid1.
D'ailleurs je ne sais si c'est possible de pouvoir effectuer une jointure de ce type, sur un champ comprenant plusieurs valeurs.
Par avance, merci de votre aide.
Version de posgresql 9.4 et postgis 2.1
Dernière modification par Benoit91 (Tue 05 April 2016 18:27)
Hors ligne
#2 Tue 05 April 2016 18:53
- VianneyD
- Participant assidu
- Date d'inscription: 30 May 2011
- Messages: 153
Re: postgres/postgis jointure sur champ à valeurs multiples
Bonjour,
La solution doit se trouver du coté des regex.
Quelque chose de ce genre peut-être :
Code:
[...] WHERE cada_parcelle.idu_temp IN (SELECT regexp_split_to_table(refcada2,',') FROM habitat.temp_rpls) [...]
Je n'ai pas testé donc sans garantie ^^
Vianney Dugrain
Hors ligne
#3 Wed 06 April 2016 11:09
- Benoit91
- Participant assidu
- Date d'inscription: 2 Oct 2008
- Messages: 263
Re: postgres/postgis jointure sur champ à valeurs multiples
Bonjour Vianney
Merci c'est exactement çà au niveau de la formule.
Je ne pratique pas assez postgres/postgis mais je vois que j'ai tout intérêt à m’intéresser au requêtes avec regexp.
La requête modifiée en conséquence
Code:
WITH parcelles_rpls AS (SELECT CONCAT (insee,'000',regexp_split_to_table(refcada2,',')) as idur FROM habitat.temp_rpls),
pcada AS (SELECT CONCAT ('91',cada_parcelle.idu_id) as idup, cada_parcelle.geom FROM cadastre.cada_parcelle)
SELECT ROW_NUMBER() OVER() AS gid,
pcada.idup,
pcada.geom
FROM pcada
JOIN parcelles_rpls ON idup=idur
GROUP BY idup,geomCordialement.
Dernière modification par Benoit91 (Wed 06 April 2016 12:18)
Hors ligne
#4 Wed 31 October 2018 11:45
- BenB
- Participant actif
- Date d'inscription: 29 Dec 2015
- Messages: 120
Re: postgres/postgis jointure sur champ à valeurs multiples
Bonjour,
je me permets de remonter ce post car je suis sur la même problématique,
j'ai deux tables, 1 table habitat (t_habitat) et 1 table de référence de l'INPN (habref_cb)
Pour récupérer le libelle de l'habitat je dois faire une jointure sur un champs de la table t_habitat qui peut avoir plusieurs valeur séparés par une virgule :
t_habitat habref_cb
gid code_cb code_cb libelle
25 36.1 36.1 Prés salés continentaux
26 87.24 37.7 Fourrés halophiles semi-désertiques
27 37.7 42.21 Dunes
28 36.1,42.21 87.24 Prairies et landes des bancs de galets
Vue souhaitée
25 36.1 Prés salés continentaux
26 87.24 Prairies et landes des bancs de galets
27 37.7 Fourrés halophiles semi-désertiques
28 36.1,42.21 Prés salés continentaux, Dunes
Je n'arrive pas à intégrer l'expression suivante dans ma requête
SELECT regexp_split_to_table(code_cb,',') FROM pds_observatoire.t_habitat)
j'ai essayé en sous requête au niveau du LEFT JOIN en amont avec WITH.
il me manque aussi la deuxième partie de CONCATÉNATION des libelles
Je suis preneur de toute aide.
Merci !
Hors ligne
#5 Wed 31 October 2018 11:58
- Nicolas Ribot
- Membre
- Lieu: Toulouse
- Date d'inscription: 9 Sep 2005
- Messages: 1544
Re: postgres/postgis jointure sur champ à valeurs multiples
Bonjour,
Je vous conseillerais d'abord de changer la structure de la table t_habitat de sorte que les code_cb ne soient pas une liste de valeurs, mais des lignes séparées dans votre table, quitte à passer par une table de lien supplémentaire (lien n-m entre habitat et habref_cb).
Vous avez plusieurs possibilités pour traiter cette jointure, par ex:
• exploser code_cb en autant de ligne, puis faire une jointure sur habref_cb,
• ou construire un tableau de code_cb a partir de la liste texte de code_cb, et faire une jointure sur habref_cb en cherchahnt les code_cb contenus dans le tableau:
regex_split_to_array est utilisé dans la jointure pour construire un tableau de valeurs a partir d'une chaine texte.
element = any(array) renvoie vrai si element est présent dans le tableau: condition de jointure.
Code:
with t_habitat as (
select * from (values (25, '36.1'),
(26, '87.24'),
(27, '37.7'),
(28, '36.1,42.21')) as t(gid, code_cb)
), habref_cb as (
select *
from (values ('36.1', 'Prés salés continentaux'),
('37.7', 'Fourrés halophiles semi-désertiques'),
('42.21', 'Dunes'),
('87.24', 'Prairies et landes des bancs de galets')) as t(code_cb, libelle)
) select t.gid, t.code_cb, string_agg(h.libelle, ',') as code_cbs
from t_habitat t join habref_cb h on h.code_cb = any (regexp_split_to_array(t.code_cb, ','))
group by t.gid, t.code_cb
order by t.gid;
gid code_cb code_cbs
25 36.1 Prés salés continentaux
26 87.24 Prairies et landes des bancs de galets
27 37.7 Fourrés halophiles semi-désertiques
28 36.1,42.21 Prés salés continentaux,DunesPour construire un texte qui est une liste de valeurs séparée par le caractère de votre choix, utilisez string_agg(text, char)
Nicolas
Dernière modification par Nicolas Ribot (Wed 31 October 2018 12:04)
Hors ligne
