#1 Fri 23 May 2014 08:33
- David.loic
- Participant occasionnel
- Date d'inscription: 21 Feb 2014
- Messages: 17
Requête permettant de regrouper et de sommer des données
Bonjour,
j'aurais besoin d'un petit coup de main. Je retourne le code dans tous les sens, afin d'obtenir une seule ligne faisant la somme des troncons de voie navigable et en regroupant les lignes ayant les mêmes attributs. J'aimerais obtenir une ligne par commune dans le cas ou le gabarit , la largeur et le toponyme sont égal.
Actuellement le résultat de ma requête est le suivant:
code insee longeur_vooie_navi_km toponyme_voie_navi gabarit_voie_navi largeur_voie_navi
"21005" 1.58386751756505 "canal de bourgogne" "Gabarit Freycinet" "Entre 15 et 50 mètres"
"21005" 1.33461928497509 "canal de bourgogne" "Gabarit Freycinet" "Entre 15 et 50 mètres"
"21005" 0.02758600279521 "canal de bourgogne" "Gabarit Freycinet" "Entre 15 et 50 mètres"
"21005" 0.417230600816796 "canal de bourgogne" "Gabarit Freycinet" "Entre 15 et 50 mètres"
résultat attendu:
code insee longeur_vooie_navi_km toponyme_voie_navi gabarit_voie_navi largeur_voie_navi
"21005" 3.36330340615215 "canal de bourgogne" "Gabarit Freycinet" "Entre 15 et 50 mètres"
voici les lignes de codes:
SELECT tab1.insee_comm,longueur_voie_navi_km,toponyme_voie_navi,gabarit_voie_navi,largeur_voie_navi
FROM voie_navigable_bourgogne_elargie as v_e
INNER JOIN (SELECT vue_bg.insee_comm,bg.gid,sum(st_length(st_intersection(vue_bg.geom,bg.geom))/1000) as longueur_voie_navi_km
FROM voie_navigable_bourgogne_elargie as bg,vue_commune_bourgogne as vue_bg
Where st_intersects(vue_bg.geom,bg.geom)
group by vue_bg.insee_comm,bg.gid) as tab1
on v_e.gid=tab1.gid
INNER JOIN toponyme_voie_navi as t1 on v_e.id_toponyme=t1.id_toponyme
INNER JOIN gabarit_voie_navi as t2 on v_e.id_gabarit=t2.id_gabarit
INNER JOIN largeur_voie_navi as t3 on v_e.id_largeur=t3.id_largeur
Order by tab1.insee_comm;
Merci d'avance pour votre aide.
Hors ligne
#2 Fri 23 May 2014 09:31
- JP LLORENS
- Participant assidu
- Date d'inscription: 12 Nov 2008
- Messages: 231
Re: Requête permettant de regrouper et de sommer des données
Bonjour.
Sans me plonger dans ta requête, et si elle rend les résultats que tu indiques, alors je ferai (au plus simple) :
Select foo.code_insee, sum(foo.longeur_voie_navi_km) as longueur, foo.toponyme_voie_navi, foo.gabarit_voie_navi, foo.largeur_voie_navi
from (ta requête) foo
group by foo.code_insee, foo.toponyme_voie_navi, foo.gabarit_voie_navi, foo.largeur_voie_navi.
Je pense que ça devrait fonctionner, mais on doit pouvoir faire quelque chose de plus propre...
Cordialement
JPL
Dernière modification par JP LLORENS (Fri 23 May 2014 09:32)
Hors ligne
#3 Fri 23 May 2014 09:41
- SANTANNA
- Moderateur
- Lieu: Angers
- Date d'inscription: 18 Jan 2008
- Messages: 3884
Re: Requête permettant de regrouper et de sommer des données
Bonjour,
à première vue, en faisant une somme sur longueur_voie_navi_km et un group by les quatre autres champs, ça devrait le faire, je pense soit
Code:
SELECT tab1.insee_comm, sum(longueur_voie_navi_km), toponyme_voie_navi, gabarit_voie_navi, largeur_voie_navi FROM voie_navigable_bourgogne_elargie as v_e INNER JOIN ( SELECT vue_bg.insee_comm, bg.gid,sum(st_length(st_intersection(vue_bg.geom,bg.geom))/1000) as longueur_voie_navi_km FROM voie_navigable_bourgogne_elargie as bg, vue_commune_bourgogne as vue_bg Where st_intersects(vue_bg.geom,bg.geom) group by vue_bg.insee_comm,bg.gid ) as tab1 on v_e.gid=tab1.gid INNER JOIN toponyme_voie_navi as t1 on v_e.id_toponyme=t1.id_toponyme INNER JOIN gabarit_voie_navi as t2 on v_e.id_gabarit=t2.id_gabarit INNER JOIN largeur_voie_navi as t3 on v_e.id_largeur=t3.id_largeur group by tab1.insee_comm, toponyme_voie_navi, gabarit_voie_navi, largeur_voie_navi Order by tab1.insee_comm;
Il doit y avoir moyen de l'écrire plus légèrement mais sur la base de votre code et de résultats obtenus, ça devrait marcher
Question : vous avez des tables toponyme_voie_navi, gabarit_voie_navi, largeur_voie_navi contenant des champs du même nom, c'est ça?
Dernière modification par SANTANNA (Fri 23 May 2014 09:43)
Hors ligne
#4 Fri 23 May 2014 10:12
- David.loic
- Participant occasionnel
- Date d'inscription: 21 Feb 2014
- Messages: 17
Re: Requête permettant de regrouper et de sommer des données
Bonjour et tout d'abord merci pour vos réponses:
j'ai essayer les GROUP BY comme indiqué mais cela me renvoie le même résultat. Il me demande ensuite de grouper t1,T2,T3. Cela n'en fini pas.
SANTANA: Oui j'ai construit la base de données de manière à éviter les redondances de type caractère. J'ai créé des tables de nomenclature pour optimiser le temps de requête. Donc j'ai une table commune, une table voie_navigable, une gabarit, une largeur, une toponyme. Les trois dernières sont liées par une clé étrangère à la table voie navigable.
J'espère que cela répond à votre question.
Que pensez vous de la fonction: "overpartitionby" ?
Hors ligne
#5 Fri 23 May 2014 10:25
- ChristopheV
- Membre
- Lieu: Ajaccio
- Date d'inscription: 7 Sep 2005
- Messages: 3175
- Site web
Re: Requête permettant de regrouper et de sommer des données
Bonjour,
Pas trop le temps de me plonger dans cette requête spécifique mais voilà une piste, regarder du coté des WINDOW Function
http://www.postgresql.org/docs/9.3/stat … indow.html
Christophe
L'avantage d'être une île c'est d'être une terre topologiquement close
Hors ligne
#6 Fri 23 May 2014 10:52
- David.loic
- Participant occasionnel
- Date d'inscription: 21 Feb 2014
- Messages: 17
Re: Requête permettant de regrouper et de sommer des données
Merci,
je vais essayer cette fonction et vous ferrai parvenir le retour de cette nouvelle expérience.
cordialement,
Loïc
Hors ligne
#7 Fri 23 May 2014 11:46
- Nicolas Ribot
- Membre
- Lieu: Toulouse
- Date d'inscription: 9 Sep 2005
- Messages: 1538
Re: Requête permettant de regrouper et de sommer des données
Bonjour,
Si vous souhaitez avoir une synthèse d'un attribut lorsque certains autres champs sont égaux, alors il faut utiliser la construction group by.
Les Window Functions permettent le contraire: afficher la synthèse d'un champ pour TOUTES les lignes de la table.
Dans votre cas, si je comprends: vous voulez la somme des surfaces par code commune et par toponyme_voie_navi,gabarit_voie_navi,largeur_voie_navi.
(J'utilise dans cet exemple les CTE: common table expression: c'est la meme chose que des sous requetes, mais je trouve ca plus lisible)
D'abord, la requete d'intersection des voies en bourgogne.
Puis ensuite la somme des longueurs des voies groupée par les colonnes nécessaires
Enfin, le lien avec les tables de référence pour avoir les intitulés:
Code:
-- sous requetes mise en premier with sum_inter as ( SELECT vue_bg.insee_comm,bg.gid,sum(st_length(st_intersection(vue_bg.geom,bg.geom))/1000) as longueur_voie_navi_km FROM voie_navigable_bourgogne_elargie as bg,vue_commune_bourgogne as vue_bg Where st_intersects(vue_bg.geom,bg.geom) group by vue_bg.insee_comm,bg.gid -- somme par commune et par topo, gabarit, largeur ) stat as ( select s.insee_com, v_e.id_toponyme, v_e.id_gabarit, v_e.id_largeur, sum(longueur_voie_navi_km) as longueur_voie_navi_km FROM voie_navigable_bourgogne_elargie as v_e join sum_inter s on s.gid = v_e.gid group by insee_com, v_e.id_toponyme, v_e.id_gabarit, v_e.id_largeur -- jointure avec les intitulés ) select insee_com, longueur_voie_navi_km,toponyme_voie_navi,gabarit_voie_navi,largeur_voie_navi from stat s JOIN toponyme_voie_navi as t1 on s.id_toponyme=t1.id_toponyme JOIN gabarit_voie_navi as t2 on s.id_gabarit=t2.id_gabarit JOIN largeur_voie_navi as t3 on s.id_largeur=t3.id_largeur Order by insee_comm;
Nicolas
Hors ligne
#8 Fri 23 May 2014 19:07
- David.loic
- Participant occasionnel
- Date d'inscription: 21 Feb 2014
- Messages: 17
Re: Requête permettant de regrouper et de sommer des données
Merci pour vos aides, finalement j'ai réussi en utilisant des over (partition by), voici la requête finale (elle est pas trop propre mais elle fonctionne.
SELECT DISTINCT tab_ref.insee_comm,tab_ref.toponyme_voie_navi,tab_ref.largeur_voie_navi,tab_ref.gabarit_voie_navi,
sum(longueur_by_troncon_km)over (partition by tab_ref.insee_comm,tab_ref.toponyme_voie_navi,tab_ref.largeur_voie_navi,tab_ref.gabarit_voie_navi order
by tab_ref.insee_comm,tab_ref.toponyme_voie_navi) as voie_navi_km
FROM
(SELECT vue_bg.insee_comm,bg.gid,toponyme_voie_navi,largeur_voie_navi,gabarit_voie_navi,
sum(st_length(st_intersection(vue_bg.geom,bg.geom))/1000) over (partition by vue_bg.insee_comm,bg.gid order by bg.gid) as longueur_by_troncon_km
FROM voie_navigable_bourgogne_elargie as bg,vue_commune_bourgogne as vue_bg,toponyme_voie_navi as t1,largeur_voie_navi as T3,gabarit_voie_navi as t2
Where st_intersects(vue_bg.geom,bg.geom)
AND bg.id_toponyme=t1.id_toponyme
AND bg.id_largeur=t3.id_largeur
AND bg.id_gabarit=t2.id_gabarit) as tab_ref
Hors ligne
#9 Mon 26 May 2014 09:54
- Nicolas Ribot
- Membre
- Lieu: Toulouse
- Date d'inscription: 9 Sep 2005
- Messages: 1538
Re: Requête permettant de regrouper et de sommer des données
Bonjour,
Là, vous compliquez un peu
Vous utilisez une window function, qui permet d'afficher des stats pour toutes les lignes de la table, puis vous faites un distinct à la fin, dans le select, pour virer les lignes identiques retournées par le OVER PARTITION BY
Les champs que vous mettez dans la clause PARTITION BY doivent se mettre dans la partie GROUP BY pour identifier les lignes ayant un ensemble d'attributs communs.
Nicolas
Hors ligne
#10 Mon 26 May 2014 11:41
- David.loic
- Participant occasionnel
- Date d'inscription: 21 Feb 2014
- Messages: 17
Re: Requête permettant de regrouper et de sommer des données
Oui, je me suis un peu compliqué la vie.
J'ai commencé à me mettre à PostGIS depuis trois mois. Petit à petit l'oiseau fais son nid.
Je vais essayer avec les groupe by.
Merci
Hors ligne
#11 Mon 26 May 2014 12:12
- SANTANNA
- Moderateur
- Lieu: Angers
- Date d'inscription: 18 Jan 2008
- Messages: 3884
Re: Requête permettant de regrouper et de sommer des données
Bonjour,
Avez-vous testé le code de Nicolas qui a l'avantage de découper la requête étape par étape, est plus lisible et peut vous aider à mieux comprendre la structuration et l'imbrication des requêtes entre elles?
Je suis par ailleurs étonné qu'en #4 vous fassiez état d'un échec de la proposition de code (qui n'ajoute qu'un group by les quatre champs et un sum sur le cinquième). Ça devrait marcher... Et qu'entendez-vous par "Il me demande ensuite de grouper t1,T2,T3" (qui sont des tables et non des champs)?
Bonne construction du nid
Hors ligne
#12 Tue 27 May 2014 09:21
- David.loic
- Participant occasionnel
- Date d'inscription: 21 Feb 2014
- Messages: 17
Re: Requête permettant de regrouper et de sommer des données
Bonjour,
oui moi aussi je ne l'explique pas. Dès que je commence à faire un groupe by, il faut faire des group by partout et le résultat voulu n'est pas bon.
Je testerai le code plus tard, j'avoue que j'ai actuellement la tête dans le guidon. Je dois finir de construire une base de données sur les transports pour la DREAL Bourgogne et paramétrer des vues pour que les agents n'est plus qu'à ouvrir les couches vectorielles dont ils ont besoin sous QGIS.
Le problème est que bien que se soit un service géomatique personne ne travaillait à partir d'une base de données (alarmant non?). Donc maintenant je dois réfléchir au protocole de mise à jour de la base. Je pensais à Geokettle. Avez vous des conseils concernant cet ETL? Est-il stable et facile d'usage?
Merci encore pour les conseils et pour votre aide.
Hors ligne
#13 Wed 18 November 2015 18:45
- Theos2000
- Participant assidu
- Date d'inscription: 15 Jun 2015
- Messages: 221
Re: Requête permettant de regrouper et de sommer des données
Bonjour tout le monde,
J’espère ne pas me tromper de topic mais je suis dans le même cas de figure...sous Qgis. Cela concerne des logements que je voudrais regrouper par leurs identifiants sous forme de somme afinf d'avoir 'n' logements pour 1 identifiants. Existe il une solution pour parvenir au meme résultat.
Merci pour votre aide :-)
Hors ligne
#14 Wed 18 November 2015 20:24
- Theos2000
- Participant assidu
- Date d'inscription: 15 Jun 2015
- Messages: 221
Re: Requête permettant de regrouper et de sommer des données
Histoire d'étoffer mon dernier post...
Je suis dans le même cas de figure et plus particulièrement en en vue de faire un carroyage sur la densité de logement a l'hectare. Aprés avoir extrait les PEV de Majic, un certains nombre de logements est associé a un numéro "invar" et des logements . Je suis passé par le plugin Qmarxan et GroupStat (Bon plugin en passant). De la j'ai fait un export en CSV, effectué une jointure afin de comparer mes résultats entre Qmarxan et groupestat (le but étant de les intégrer à la grille)...mais je trouve des résultats bizarre...du genre 1000 logements a l'hectare. Soit j'ai trouvé la commune la plus dense du monde soit je me suis planté mais je ne vois pas ou. est ce que quelqu'un connait un tuto bien fait pour effectuer un carroyage de densité de logement (J'ai pas mal cherché sur plusieurs sites, mais rien d'expliquer étape par étape a part pour le PLH d'Avignon : http://www.arcopole.fr/Portals/0/Images … dastre.pdf) Si quelqu'un a une suggestion je suis preneur !!!
Merci d'avance pour les conseils ! :-)
Hors ligne
#15 Thu 19 November 2015 09:29
- Nicolas Ribot
- Membre
- Lieu: Toulouse
- Date d'inscription: 9 Sep 2005
- Messages: 1538
Re: Requête permettant de regrouper et de sommer des données
Bonjour,
D'abord, Postgis est parfait pour ca, pas besoin de qgis, a part pour afficher le résultat.
Définissez la grille que vous voulez (les forums vous permettront de trouver les fonctions pour fabriquer une grille), puis comptez les éléments que vous voulez contenus dans chaque case de la grille.
Code:
select g.id as id_grid, count(t.id) as cnt from table t join grill g on st_intersects(t.geom, g.geom) group by g.id;
Nicolas
PS. Dans ce fil de message, il y a une grande confusion sur ce GROUP BY: je vous invite a lire la doc de cette clause et à faire une requete simple avec pour bien comprendre comment on s'en sert.
Hors ligne
#16 Sat 21 November 2015 02:05
- Theos2000
- Participant assidu
- Date d'inscription: 15 Jun 2015
- Messages: 221
Re: Requête permettant de regrouper et de sommer des données
Merci pour cet élément de réponse, je vais tenter de ce coté la et me lancer.
Hors ligne