Le portail francophone de la géomatique

Géronimo · Fri 23 May 2014 16:24

Bonjour à tous,

Voici ma requête :

Code:

WITH RECURSIVE en_aval(profondeur, code_zone, id_nd_ini, id_nd_fin, geom) AS (
 SELECT 1, code_zone, id_nd_ini, id_nd_fin, geom
  FROM essai_pl
UNION ALL
  SELECT ea.profondeur + 1, e.code_zone, e.id_nd_ini, e.id_nd_fin, e.geom
  FROM en_aval  AS ea, essai_pl AS e 
  WHERE e.id_nd_fin = ea.id_nd_ini
)
SELECT row_number()OVER()::integer AS gid, * FROM en_aval

Dans la colonne "profondeur" de cette requête, il m'est indiquer un numéro pour chaque récursion (1 pour la première, 2 pour la seconde, 3 pour la troisième,...). J'aimerais remplacer ce numéro par l'identifiant de la zone où est pratiquée chaque récursion (code_zone). Il s'agit d'une chaîne de caractère sous la forme : lettre numéro numéro numéro.

Je vous remercie pour vos futures réponses.

Géronimo

Géronimo · Fri 23 May 2014 17:20

Et voilà la réponse :

Code:

WITH RECURSIVE en_aval(profondeur, code_zone, id_nd_ini, id_nd_fin, geom) AS (
SELECT  MAX(code_zone) OVER () AS  profondeur, code_zone, id_nd_ini, id_nd_fin, geom FROM essai_pl
    UNION ALL
        SELECT  MAX(e.code_zone)  OVER () AS  profondeur, e.code_zone, e.id_nd_ini, e.id_nd_fin, e.geom
        FROM    en_aval  AS ea
        INNER JOIN essai_pl AS e 
        ON  e.id_nd_fin = ea.id_nd_ini)
SELECT  ROW_NUMBER() OVER()::integer AS gid, profondeur, code_zone, id_nd_ini, id_nd_fin, geom FROM en_aval;

Bon week-end !

Géronimo · Mon 26 May 2014 19:20

Bonjour !

Je me permets de poster à nouveau sur ce sujet, car en fait la fonction que j'ai écrite ne permet pas de nommer la récursivité d'une requête récursive comme il faut. Elle se contente de prendre la valeur maximum des code_zone et la met comme nom.

Voilà la table sur laquelle je travaille. Elle s'appelle init recurs_ord_2 :

Code:

gid ; rnk_desc ; profondeur ; code_hydro ; c_hyd_cdo, code_zone, lettre_bv, toponyme1, fpkh, tpkh, id_nd_ini, id_nd_fin

1;12;1;"V7300000";"V---0000";"V730";"V";"fleuve le rhône";947663;1000000;619003051;619005470
2;11;1;"V7210000";"V---0000";"V721";"V";"fleuve le rhône";944880;947663;619002712;619003051
3;10;1;"V7200000";"V---0000";"V720";"V";"fleuve le rhône";931497;944880;619001184;619002712
4;9;1;"V7000000";"V---0000";"V700";"V";"fleuve le rhône";916913;931497;614010566;619001184
5;8;1;"X3500000";"X---0000";"X350";"X";"rivière la durance";975886;1000000;619000786;614010566
6;7;1;"X3480400";"X34-0400";"X348";"X";"rivière le coulon";975108;1000000;619000784;619000786
7;6;1;"X3460400";"X34-0400";"X346";"X";"rivière le coulon";963787;975108;619000560;619000784
8;5;1;"X3450400";"X34-0400";"X345";"X";"rivière le calavon";955523;963787;615009286;619000560
9;4;1;"X3430400";"X34-0400";"X343";"X";"rivière le calavon";938396;955523;615009662;615009286
10;3;1;"X3420400";"X34-0400";"X342";"X";"rivière le calavon";935022;938396;615009013;615009662
11;2;1;"X3410400";"X34-0400";"X341";"X";"rivière le calavon";922089;935022;614009387;615009013
12;1;1;"X3400400";"X34-0400";"X340";"X";"rivière le calavon";913111;922089;614008612;614009387

Ce que je veux faire, c'est une récursivité par l'égalité id_nd_fin=id_nd_ini.
Cette table représente les tronçons hydrographiques de cours d'eau qui se suivent. Pour chacun des tronçons, de part et d'autres, il y a un identifiant : id_nd_ini pour l'extrémité la plus en amont et id_nd_fin pour l'extrémité la plus en aval. On comprend donc bien que, l'id_nd_fin d'un tronçon est égal à l'id_nd_ini du tronçon situé en aval.
Je veux donc que, pour chaque tronçon, le logiciel m'indique la liste des tronçons qui lui sont en amont.
Cela j'y arrive.

Ce que je veux, en plus, c'est que sur une des colonnes de la table qui m'est renvoyée, pour chaque liste, il soit indiqué le la nomenclature du tronçon dont est déployé toute la listes des tronçons qui lui sont en amont (l'identifiant code_zone).
Et ça je n'arrive pas à le faire...

J'arrive à ce qu'il me donne la récursivité pour chaque tronçon sans rangement par code_zone avec le code suivant :

Code:

WITH recursive rec (gid, profondeur, code_zone_max, code_zone, lettre_bv, id_nd_ini, id_nd_fin, rnk_link) AS (
SELECT gid, profondeur, code_zone, code_zone, lettre_bv, id_nd_ini, id_nd_fin, rnk_desc
FROM init_recurs_ord_2
union ALL
SELECT b.gid, a.profondeur, a.code_zone_max, b.code_zone, b.lettre_bv, b.id_nd_ini, b.id_nd_fin, b.rnk_desc
FROM rec a
INNER JOIN init_recurs_ord_2 b ON b.id_nd_fin = a.id_nd_ini )
SELECT * FROM rec

La table qui m'est retournée est :

Code:

gid, profondeur, code_zone_max, code_zone, lettre_bv, id_nd_ini, id_nd_fin, rnk_link
12;1;"V730";"V730";"V";619003051;619005470;1
11;1;"V721";"V721";"V";619002712;619003051;2
10;1;"V720";"V720";"V";619001184;619002712;3
9;1;"V700";"V700";"V";614010566;619001184;4
8;1;"X350";"X350";"X";619000786;614010566;5
7;1;"X348";"X348";"X";619000784;619000786;6
6;1;"X346";"X346";"X";619000560;619000784;7
5;1;"X345";"X345";"X";615009286;619000560;8
4;1;"X343";"X343";"X";615009662;615009286;9
3;1;"X342";"X342";"X";615009013;615009662;10
2;1;"X341";"X341";"X";614009387;615009013;11
1;1;"X340";"X340";"X";614008612;614009387;12
11;1;"V730";"V721";"V";619002712;619003051;2
10;1;"V721";"V720";"V";619001184;619002712;3
9;1;"V720";"V700";"V";614010566;619001184;4
8;1;"V700";"X350";"X";619000786;614010566;5
7;1;"X350";"X348";"X";619000784;619000786;6
6;1;"X348";"X346";"X";619000560;619000784;7
5;1;"X346";"X345";"X";615009286;619000560;8
4;1;"X345";"X343";"X";615009662;615009286;9
3;1;"X343";"X342";"X";615009013;615009662;10
2;1;"X342";"X341";"X";614009387;615009013;11
1;1;"X341";"X340";"X";614008612;614009387;12
etc...
3;1;"V730";"X342";"X";615009013;615009662;10
2;1;"V721";"X341";"X";614009387;615009013;11
1;1;"V720";"X340";"X";614008612;614009387;12
2;1;"V730";"X341";"X";614009387;615009013;11
1;1;"V721";"X340";"X";614008612;614009387;12
1;1;"V730";"X340";"X";614008612;614009387;12

78 lignes en tout.

J'arrive à avoir un rangement en fonction de code_zone, mais seulement sur une récursivité, avec cette fonction :

Code:

WITH recursive rec (gid, profondeur, code_zone_max, code_zone, lettre_bv, id_nd_ini, id_nd_fin, rnk_link) AS (
SELECT gid, profondeur, code_zone, code_zone, lettre_bv, id_nd_ini, id_nd_fin, rnk_desc
FROM init_recurs_ord_2
WHERE rnk_desc=1
union ALL
SELECT b.gid, a.profondeur, a.code_zone_max, b.code_zone, b.lettre_bv, b.id_nd_ini, b.id_nd_fin, b.rnk_desc
FROM rec a
INNER JOIN init_recurs_ord_2 b ON b.id_nd_fin = a.id_nd_ini AND a.rnk_link + 1 = b.rnk_desc)
SELECT * FROM rec

Qui me donne cette table :

Code:

gid, profondeur, code_zone_max, code_zone, lettre_bv, id_nd_ini, id_nd_fin, rnk_link

12;1;"V730";"V730";"V";619003051;619005470;1
11;1;"V730";"V721";"V";619002712;619003051;2
10;1;"V730";"V720";"V";619001184;619002712;3
9;1;"V730";"V700";"V";614010566;619001184;4
8;1;"V730";"X350";"X";619000786;614010566;5
7;1;"V730";"X348";"X";619000784;619000786;6
6;1;"V730";"X346";"X";619000560;619000784;7
5;1;"V730";"X345";"X";615009286;619000560;8
4;1;"V730";"X343";"X";615009662;615009286;9
3;1;"V730";"X342";"X";615009013;615009662;10
2;1;"V730";"X341";"X";614009387;615009013;11
1;1;"V730";"X340";"X";614008612;614009387;12

12 lignes

J'aimerais savoir si vous pouvez m'indiquer quel code utiliser pour avoir l'ensemble des récursivités (78 lignes comme sur la 1ère table) avec le rangement par code_zone (comme sur la deuxième table).

Je suis désolé pour le long poste, mais ça me semble indispensable pour tout décrire.

Je vous remercie d'avance pour vos futures réponses,

Bonne soirée.

Nicolas Ribot · Tue 27 May 2014 09:36

Bonjour,

Je ne suis pas sur de bien comprendre ce que vous voulez faire avec les codes zones, mais ne pouvez-vous pas refaire une jointure sur la table apres la requete recursive pour récupérer les codes zones que vous voulez ?

Nicolas

Géronimo · Tue 27 May 2014 09:48

Désolé,
Je n'avais pas écrit correctement la 1ère fonction et la table qui en découlait hier soir. Surement la fatigue...

Donc, voilà la fonction :

Code:

WITH recursive rec (gid, profondeur, code_zone_max, code_zone, lettre_bv, id_nd_ini, id_nd_fin, rnk_link) AS (
SELECT gid, 1, code_zone, code_zone, lettre_bv, id_nd_ini, id_nd_fin, rnk_desc
FROM init_recurs_ord_2
union ALL
SELECT b.gid, a.profondeur +1, code_zone_max, b.code_zone, b.lettre_bv, b.id_nd_ini, b.id_nd_fin, b.rnk_desc
FROM rec a
INNER JOIN init_recurs_ord_2 b ON b.id_nd_fin = a.id_nd_ini)
SELECT * FROM rec

Et voilà la table qui en découle :

Code:

12;1;"V730";"V730";"V";619003051;619005470;1
11;1;"V721";"V721";"V";619002712;619003051;2
10;1;"V720";"V720";"V";619001184;619002712;3
9;1;"V700";"V700";"V";614010566;619001184;4
8;1;"X350";"X350";"X";619000786;614010566;5
7;1;"X348";"X348";"X";619000784;619000786;6
6;1;"X346";"X346";"X";619000560;619000784;7
5;1;"X345";"X345";"X";615009286;619000560;8
4;1;"X343";"X343";"X";615009662;615009286;9
3;1;"X342";"X342";"X";615009013;615009662;10
2;1;"X341";"X341";"X";614009387;615009013;11
1;1;"X340";"X340";"X";614008612;614009387;12
11;2;"V730";"V721";"V";619002712;619003051;2
10;2;"V721";"V720";"V";619001184;619002712;3
9;2;"V720";"V700";"V";614010566;619001184;4
8;2;"V700";"X350";"X";619000786;614010566;5
7;2;"X350";"X348";"X";619000784;619000786;6
6;2;"X348";"X346";"X";619000560;619000784;7
5;2;"X346";"X345";"X";615009286;619000560;8
4;2;"X345";"X343";"X";615009662;615009286;9
3;2;"X343";"X342";"X";615009013;615009662;10
2;2;"X342";"X341";"X";614009387;615009013;11
1;2;"X341";"X340";"X";614008612;614009387;12
10;3;"V730";"V720";"V";619001184;619002712;3
9;3;"V721";"V700";"V";614010566;619001184;4
8;3;"V720";"X350";"X";619000786;614010566;5
7;3;"V700";"X348";"X";619000784;619000786;6

etc...

3;10;"V730";"X342";"X";615009013;615009662;10
2;10;"V721";"X341";"X";614009387;615009013;11
1;10;"V720";"X340";"X";614008612;614009387;12
2;11;"V730";"X341";"X";614009387;615009013;11
1;11;"V721";"X340";"X";614008612;614009387;12
1;12;"V730";"X340";"X";614008612;614009387;12

On voit bien que ma deuxième colonne, la colonne profondeur a un numéro différent pour chaque récursivité. Je voudrais que, sur la troisième colonne (code_zone_max), apparaisse le nom du tronçon (code_zone) sur lequel s'effectue chaque récursivité.
Ainsi, à côté du 1 de la colonne profondeur pour la 1ère récursivité, il s'afficherait V730 sur les 12 lignes.
A côté du 2 de la colonne profondeur pour la 2e récursivité, il s'afficherait V720 sur 11 lignes.
...
A côté du 10 de la colonne profondeur pour la 10e récursivité, il s'afficherait X342 sur 3 lignes.
A côté du 11 de la colonne profondeur pour la 11e récursivité, il s'afficherait X341 sur 2 lignes.
A côté du 12 de la colonne profondeur pour la 12e récursivité, il s'afficherait X340 sur 1 lignes.

Comment pensez-vous faire une jointure pour récupérer les codes_zones que je veux ?

Nicolas Ribot · Wed 28 May 2014 10:56

Bonjour,

Merci pour ces précisions.

Dans la premiere requete (partie non recursive: SELECT ... FROM init_recurs_ord_2), il y a bien 12 tronçons différents, chacun associé à son propre code_zone ?
Du coup, pourquoi est-ce le code V730 que vous souhaitez voir apparaitre à la premiere itération ?

N'est-ce pas un ORDER BY code_zone que vous voulez dans la requete finale, histoire de voir tous les tronçons connectés ayant pour code_zone V730 ?

Nicolas

Géronimo · Wed 28 May 2014 11:00

Bijour !!!

Voilà le code qui m'a permis de résoudre mon problème :

Code:

WITH recursive rec (gid, profondeur, code_zone_max, code_zone, id_nd_ini, id_nd_fin, geom, chemin) AS (
SELECT gid, 1, code_zone, code_zone, id_nd_ini, id_nd_fin, geom, code_zone::text
FROM init_recurs_ord_7
union ALL
SELECT b.gid, a.profondeur +1, a.code_zone_max, b.code_zone, b.id_nd_ini, b.id_nd_fin, b.geom, a.chemin || ' > ' || b.code_zone
FROM rec a
INNER JOIN init_recurs_ord_7 b ON a.id_nd_fin = b.id_nd_ini)
SELECT * FROM rec
ORDER BY 1, profondeur DESC

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

#1 Fri 23 May 2014 16:24

[9.3] nommer les récursions d'une requête récursive

Code:

#2 Fri 23 May 2014 17:20

Re: [9.3] nommer les récursions d'une requête récursive

Code:

#3 Mon 26 May 2014 19:20

Re: [9.3] nommer les récursions d'une requête récursive

Code:

Code:

Code:

Code:

Code:

#4 Tue 27 May 2014 09:36

Re: [9.3] nommer les récursions d'une requête récursive

#5 Tue 27 May 2014 09:48

Re: [9.3] nommer les récursions d'une requête récursive

Code:

Code:

#6 Wed 28 May 2014 10:56

Re: [9.3] nommer les récursions d'une requête récursive

#7 Wed 28 May 2014 11:00

Re: [9.3] nommer les récursions d'une requête récursive

Code:

Pied de page des forums