Le portail francophone de la géomatique

David.loic · Fri 23 May 2014 08:33

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.

JP LLORENS · Fri 23 May 2014 09:31

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)

SANTANNA · Fri 23 May 2014 09:41

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)

David.loic · Fri 23 May 2014 10:12

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" ?

ChristopheV · Fri 23 May 2014 10:25

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

David.loic · Fri 23 May 2014 10:52

Merci,
je vais essayer cette fonction et vous ferrai parvenir le retour de cette nouvelle expérience.
cordialement,
Loïc

Nicolas Ribot · Fri 23 May 2014 11:46

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

David.loic · Fri 23 May 2014 19:07

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

Nicolas Ribot · Mon 26 May 2014 09:54

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

David.loic · Mon 26 May 2014 11:41

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

SANTANNA · Mon 26 May 2014 12:12

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

David.loic · Tue 27 May 2014 09:21

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.

Theos2000 · Wed 18 November 2015 18:45

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 :-)

Theos2000 · Wed 18 November 2015 20:24

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 ! :-)

Nicolas Ribot · Thu 19 November 2015 09:29

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.

Theos2000 · Sat 21 November 2015 02:05

Merci pour cet élément de réponse, je vais tenter de ce coté la et me lancer.

Communauté	Ressources	L'emploi	Le marché	L'association	Fils RSS
Forum Blogs Wiki Planet Sites amis	Agenda Biblio Fiches AMO-CNIG Paris PCGIS Téléchargements	Offres d'emploi Banque de CV Formations Démarche Métiers	Geo-Entreprises Geo-Communiqué Appels d'offres	L'association Qui sommes-nous ? Nous aider Bienfaiteurs Partenaires Historique Statuts	Tout le site Job Géomatique Services GeoBlogs ...	Faire un don Mentions légales

Le portail francophone de la géomatique

Annonce

#1 Fri 23 May 2014 08:33

Requête permettant de regrouper et de sommer des données

#2 Fri 23 May 2014 09:31

Re: Requête permettant de regrouper et de sommer des données

#3 Fri 23 May 2014 09:41

Re: Requête permettant de regrouper et de sommer des données

Code:

#4 Fri 23 May 2014 10:12

Re: Requête permettant de regrouper et de sommer des données

#5 Fri 23 May 2014 10:25

Re: Requête permettant de regrouper et de sommer des données

#6 Fri 23 May 2014 10:52

Re: Requête permettant de regrouper et de sommer des données

#7 Fri 23 May 2014 11:46

Re: Requête permettant de regrouper et de sommer des données

Code:

#8 Fri 23 May 2014 19:07

Re: Requête permettant de regrouper et de sommer des données

#9 Mon 26 May 2014 09:54

Re: Requête permettant de regrouper et de sommer des données

#10 Mon 26 May 2014 11:41

Re: Requête permettant de regrouper et de sommer des données

#11 Mon 26 May 2014 12:12

Re: Requête permettant de regrouper et de sommer des données

#12 Tue 27 May 2014 09:21

Re: Requête permettant de regrouper et de sommer des données

#13 Wed 18 November 2015 18:45

Re: Requête permettant de regrouper et de sommer des données

#14 Wed 18 November 2015 20:24

Re: Requête permettant de regrouper et de sommer des données

#15 Thu 19 November 2015 09:29

Re: Requête permettant de regrouper et de sommer des données

Code:

#16 Sat 21 November 2015 02:05

Re: Requête permettant de regrouper et de sommer des données

Pied de page des forums