Discussion :

[solom7]

bonjour,
j'ai deux tables : espece

--------------------------------------
ID| esp | fct | facteur1 | facteur 2
--------------------------------------
1-----A ----- F1----- 5----- 3
2----- A----- F2----- 6 ----- 2
3----- A----- F3----- 5----- 0
4----- B----- F1----- 1----- 3
5----- B ----- F2----- 1----- 6
6----- B----- F3----- 1----- 0

et la table 2 : maille
--------------------------------------------
codemaille | the_geom | facteur1 | facteur 2
--------------------------------------------
1----- xx----- 5----- 3
2----- yy ----- 1----- 3
3----- tt ----- 5----- 0
4----- gg----- 1----- 3
5----- hh----- 1----- 6
6----- nn----- 1----- 0
7----- uu----- 5----- 3
8----- rr ----- 1----- 3
9----- as ----- 5----- 0
10----- dr ----- 1----- 3
11----- vc----- 1----- 6
12----- wx ----- 1----- 0
...

et je veux faire des statistiques (genre combien d'espèce dans chaque maille ?, ou fonction /maille ? ....) que je vais visualiser après sous SIG.

donc l'idee est de créer une grande table statistique, dans la quelle je vais insérer ( code de la maille ,the_geom, esp, Fct) chaque fois les deux conditions : espece.facteur1=maille.facteur1 AND espece.facteur2=maille.facteur2 sont satisfaites.
ce test sera réalisé pour chaque ligne de la table espèce. cette table dite statistique sera interrogée et en faisant un group by code maille on peut déterminer le nbre d'espèce par maille ..../

pour faire j'ai créer une fonction stat qui permet de récupérer la ligne de la table espèce ensuite chercher dans la table maille les codes mailles qui répondent aux conditions et enfin alimenter la table statistique.

voila le code

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
 
 
CREATE OR REPLACE FUNCTION stat() RETURNS SETOF espece AS
$BODY$
DECLARE
    r espece%rowtype;
    t maillec%rowtype;
BEGIN
 
    FOR r IN SELECT * FROM espece
    WHERE id > 0
    LOOP
		FOR t IN SELECT * FROM maille
		WHERE t.facteur1=r.facteur2 AND t.facteur2=r.facteur2		
		LOOP
			     INSERT INTO statistique (codemaille, the_geom, esp, fct) VALUES (t.codemaille,t.the_geom, r.esp,r.fct);
			RETURN NEXT t ;
		END LOOP;
        RETURN NEXT r;
    END LOOP;
    RETURN;
END
$BODY$
LANGUAGE 'plpgsql' ;
 
select * from stat()

la fonction s'exécute mais, elle n' insère dans la table statistique qu'une seule ligne .

Merci

[estofilo]

La ligne

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

RETURN NEXT t

a pour effet de quitter la fonction, et comme elle est placée immédiatement après l'INSERT, il est normal que ça quitte après le 1er INSERT.

[solom7]

Merci estofilo pour la réponse;

Effectivement il faut supprimer la ligne RETURN NEXT t;

Mais toujours pas d'insertion, et quand je supprime la close WHERE j'ai 72 lignes,

Je ne sais pas ou est le problème avec la condition t.facteur1=r.facteur2 AND t.facteur2=r.facteur2 (tous les champs de type integer),

vous avez une idée ???

[solom7]

en fait il faut mettre

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
		FOR t IN (SELECT * FROM maille
		WHERE facteur1=r.facteur2 AND facteur2=r.facteur2)

au lieu de

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
		FOR t IN SELECT * FROM maille
		WHERE t.facteur1=r.facteur2 AND t.facteur2=r.facteur2

ca marche sur les tables d'essai , je vais tester sur les tables reelles

[estofilo]

En fait il n'y a pas besoin de boucle ni même de fonction, c'est au SGBD de faire ce travail de parcours des tables et d'appariement par jointure. Il me semble que ce tu veux est:

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
 
INSERT INTO statistique (codemaille, the_geom, esp, fct)
  SELECT t.codemaille,t.the_geom, r.esp,r.fct
  FROM maille AS t, espece AS r
  WHERE t.facteur1=r.facteur1 AND t.facteur2=r.facteur2
  AND r.id>0

[solom7]

encore Merci estofilo,

j'ai testé la deuxième solution c'est plus rapide.

par contre j'ai une autre question, après la réalisation de la table statistique, je fais des requêtes genre nombre d'espèce ( COUNT esp ) par maille (GROUP BY codemaille), j'aimerais bien ajouter une autre colonne pour stocker le nom des espèces rencontrées .

exemple

--------------------------------------------
codemaille | the_geom | nbre espece | noms des espèces
--------------------------------------------
1----- xx----- 1----- esp1
2----- yy ----- 0-----
3----- tt ----- 5----- esp6 /esp1/....
4----- gg----- 2----- esp4/esp6
5----- hh----- 10----- esp1/esp5/....
6----- nn----- 11----- esp8/esp4/esp12...

vous avez une idée ??

[estofilo]

Pour concaténer le contenu de plusieurs lignes dans une seule colonne, une solution simple est de passer par un tableau intermédiaire.
C'est du style:

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
SELECT autres colonnes, array_to_string(array_agg(nom_espece), "/") AS noms
FROM tables GROUP BY autres colonnes

array_agg() existe en 8.4, pour les versions inférieures il faut créer la fonction:

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
CREATE AGGREGATE array_agg (anyelement)
(
sfunc = array_append,
stype = anyarray,
initcond = '{}'
);

[CinePhil]

... en ayant bien a l'esprit que ce dernier besoin est de la cosmétique et n'est pas le boulot de SQL mais de l'application qui présente le résultat à l'utilisateur.

[solom7]

Bonjour estofilo,CinePhil,

Merci pour les réponses, la solution de estofilo me conviens bien, en effet j'ai besoin juste d'un champs pour stoker les traces , dans mon cas pour deux mailles de même nombre d'espèce, en cliquant sur la maille sur les SIG je consulte les nom d'espèces.

Et dans un deuxième temps j'aurais besoin de la cosmétique, et je compte développer une application avec python.


Encore merci et Bonne journée.

[solom7]

Bonjour,
y t-il moyen de concaténer le contenu de plusieurs lignes avec la fonction array_agg() en triant le contenu,

exemple :

-esp1 , esp2, esp8

-esp3, esp5

...

Normalement la table d'origine (statistique) est bien triée, mais les résultats de l'agrégation array_agg ne le sont pas ?
Cette agrégation utilise la fonction array_append, donc concaténer les données dans l'ordre.
y a t il moyen pour régler ce problème ??

Merci

[estofilo]

Une solution possible est d'utiliser une fonction array_sort, à appliquer au résultat de array_agg et définie ainsi:

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
 
CREATE OR REPLACE FUNCTION array_sort (ANYARRAY)
RETURNS ANYARRAY LANGUAGE SQL
AS $$
SELECT ARRAY(
    SELECT $1[s.i] AS "foo"
    FROM
        generate_series(array_lower($1,1), array_upper($1,1)) AS s(i)
    ORDER BY foo
);
$$;

(Ce code vient de http://www.postgres.cz/index.php/Pos...ral_array_sort)

Normalement la table d'origine (statistique) est bien triée

Attention, aucune table n'est intrinsèquement triée dans un SGBD, et en l'absence de tri explicite demandé par l'utilisateur, le SGBD sort les lignes dans un ordre indéterminé.

[solom7]

Merci pour l'aide, c'est la fonction qu'il me faut.

La syntaxe sera donc :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

array_to_string(array_sort (array_agg (nom_espece)), ',') AS N_especes

c'est parfait !!!

Et merci pour l'éclaircissement concernant le tri dans un SGBD, et pour le lien c'est vraiment intéressent

Bonne journée