#1 Wed 07 August 2013 15:38
PostgreSQL 8.4 : requête sur liste de valeurs
Bonjour,
je souhaiterai un peu d'aide, je bloque sur la rédaction d'une requête. Non initiée, il me manque certainement des éléments SQL...
Je dispose de deux tables : les espaces naturels et les réglementations des activités associées.
Table "espaces naturels" (ENP) Table "reglementation_activites" (RA)
-------------------------------------- -------------------------------------------
ENP.code (identifiant) RA.code_enp
ENP.nom RA.code_activités
ENP.désignation RA. fréquence_réglementation (permanente/temporaire)
ENP.date_création RA.caractère_réglementation (charte/autorisé/interdite)
etc.... etc...
Je m’intéresse à une liste d'activités en particulier, soient les activités 5,6,7,9,11,23,25,26,30.
Je souhaiterai avoir une liste des espaces naturels où :
- une des activités listées est présente (à minima une seule)
- toutes les activités listées sont présentes
Pour la première je suis partie sur cette requête :
select distinct ENP.code, ENP.nom, ENP.désignation
from espaces_naturels as ENP
left join regulations_activities as RA ON ENP.code = RA.code_enp
where activities_list_activities_id IN (5,6,7,9,11,23,25,26,30)
Je n'arrive pas à lister les espaces protégés qui listent touts les activités ciblées.
J'attends vos conseils, idées avec impatience.
Merci,
Mélanie
Hors ligne
#2 Wed 07 August 2013 17:10
- Nicolas Ribot
- Membre
- Lieu: Toulouse
- Date d'inscription: 9 Sep 2005
- Messages: 1566
Re: PostgreSQL 8.4 : requête sur liste de valeurs
Bonjour,
Une piste possible pour résoudre cela:
Créer une liste ordonnée de toutes les activités pour un espace naturel et comparer cette liste avec la liste attendue.
Au final, ne choisir que les espaces naturels qui ont une liste complete d'activité.
Avec Postgresql, les ARRAYS permettent de faire ça de façon plutôt efficace:
Code:
-- premiere requete donnant la liste ordonnée des activités pour tous les Espaces naturels
with ordered_act as (
select * from regulations_activities order by code_enp, activities_list_activities_id
),
-- deuxieme requete qui agrège dans un tableau toutes les activités pour chaque espace naturel.
-- le tableau est ordonné grace a la requete précédente.
agg_act as (
select code_enp, array_agg(o.activities_list_activities_id) as arr_act
from ordered_act o
group by code_enp
)
-- enfin, selection de tous les espaces naturels qui ont une liste complete d'activités
select * from espaces_naturels e left join agg_act a on (e.code = a.code_enp)
where a.arr_act = '{5,6,7,9,11,23,25,26,30}';La syntaxe '{5,6,7,9,11,23,25,26,30}' permet de créer un tableau d'entiers.
Nicolas
Hors ligne
#3 Thu 08 August 2013 09:59
Re: PostgreSQL 8.4 : requête sur liste de valeurs
Merci Nicolas pour cette proposition,
La requête tourne mais ne renvoie aucun résultat alors que je sais qu'au moins un ENP liste toutes ces activités....
Hors ligne
#4 Thu 08 August 2013 10:19
- Nicolas Ribot
- Membre
- Lieu: Toulouse
- Date d'inscription: 9 Sep 2005
- Messages: 1566
Re: PostgreSQL 8.4 : requête sur liste de valeurs
Se peut-il qu'il y ait plusieurs activités identiques pour un meme ENP ? Si oui, il faut modifier la requête pour filtre les doublons avant de construire la liste des activités pour un ENP. (sans ce filtre, la liste peut ressembler à {2,2,5,7,7,9,11} et ne pas être egale au critère de filtre).
Pouvez-vous partager vos données ou une partie de celles-ci ?
Nicolas
Hors ligne
#5 Thu 08 August 2013 16:12
Re: PostgreSQL 8.4 : requête sur liste de valeurs
Une même activité ne peut être listée qu'une seule fois dans chaque espace naturel.
Je joins ici un extrait de la donnée espace naturel (format Shape) et de la table "regulations_activities".
Le lien entre les deux tables se fait via le champ gid pour le Shape et le champ wdpa_gid pour la table.
Si je cherche les espaces naturels ayant les activités (33 et 34 et 35 et 36 et 37 et 38), je devrais trouver à minima le gid = 43.
Merci pour votre aide !
Dernière modification par melimelo (Thu 08 August 2013 16:25)
Hors ligne
#6 Thu 08 August 2013 17:07
- Nicolas Ribot
- Membre
- Lieu: Toulouse
- Date d'inscription: 9 Sep 2005
- Messages: 1566
Re: PostgreSQL 8.4 : requête sur liste de valeurs
Ok: ma requete faisait une comparaison stricte entre les activités d'un ENP et la valeur à tester. Il faut utiliser l'opérateur Contains "@>" (http://www.postgresql.org/docs/8.4/inte … array.html) entre les tableaux pour chercher tous les ENP qui contiennent toutes les activités voulues:
La table des activités est ra, celle des espaces naturels: enp
(S'il ne s'agit que de récupérer l'identifiant des ENP, le left join dans la requete ci-apres est inutile)
Code:
with o as (
select * from ra order by wdpa_gid, activities
), agg as (
select wdpa_gid, array_agg(o.activities) as val
from o
group by wdpa_gid
) select e.__gid, val from enp e left join agg a on (e.__gid = a.wdpa_gid)
where a.val @> '{33,34,35,36,37,38}'Cela donne, avec le jeu de résultats transmis:
Code:
43 {4,8,11,14,15,33,34,35,36,37,38,50}
204 {33,34,35,36,37,38,44,45,50}
208 {12,15,16,32,33,34,35,36,37,38,44,47,48,50}
210 {15,16,17,18,32,33,34,35,36,37,38,40,42,43,44,45,47,50}
212 {12,16,33,34,35,36,37,38,44,45,50}
214 {6,12,16,33,34,35,36,37,38,44,45,50}
216 {12,33,34,35,36,37,38,45,50}
226 {3,4,5,10,12,16,17,32,33,34,35,36,37,38,44,47,50}Nicolas
Hors ligne
#7 Thu 08 August 2013 17:25
Re: PostgreSQL 8.4 : requête sur liste de valeurs
Cela fonctionne !!!!!
J'ai une question subsidiaire :
si je voulais sélectionner uniquement les espaces naturels avec seulement les activités "33,34,35,36,37,38", je devrais écrire :
where a.val = '{33,34,35,36,37,38}' ?
La requête pour lister les espaces naturels qui ont au moins une des activités listées vous parait-elle correcte ?
Code:
select distinct ENP.code, ENP.nom, ENP.désignation from espaces_naturels as ENP left join regulations_activities as RA ON ENP.code = RA.code_enp where activities_list_activities_id IN (5,6,7,9,11,23,25,26,30)
Ca avance, merci !!
Dernière modification par melimelo (Thu 08 August 2013 17:34)
Hors ligne
#8 Thu 08 August 2013 18:06
- Nicolas Ribot
- Membre
- Lieu: Toulouse
- Date d'inscription: 9 Sep 2005
- Messages: 1566
Re: PostgreSQL 8.4 : requête sur liste de valeurs
Oui tout a fait "=" entre les tableaux fait une comparaison stricte.
Oui la requete pour au moins une activité est bien: left join, opérateur "IN".
Nicolas
Hors ligne
