Discussion :

[rhinolophus]

Bonjour à tous,

Je possède deux tables avec géométrie: communes et site:
Je souhaite récupérer automatique la liste des communes que chaque site intersecte sans prendre en compte les contours qui se touchent.

La mise à jour du nom des communes pour chaque site déjà existant dans la base fonctionne très bien:

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
 
UPDATE geo_site
SET nom_communes = 
(SELECT liste_communes --liaison entre le nom_site de la table et celui du tableau de la requête
 FROM
     (SELECT nom_site, array_agg(communes)as liste_communes -- concatène les communes par site
      FROM
           (SELECT geo_site.nom_site, initcap(bdcarto_commune.nom_comm)as communes -- sélection des communes qui intersectent les sites
	    FROM geo_site, bdcarto_commune 
	    WHERE st_intersects (geo_site.the_geom, bdcarto_commune.geom)
	    EXCEPT
	    SELECT geo_site.nom_site, initcap(bdcarto_commune.nom_comm)as communes -- enlève de la sélection les communes qui touchent seulement les contours des sites
	    FROM geo_site, bdcarto_commune 
	    WHERE st_touches (geo_site.the_geom, bdcarto_commune.geom)
	    ) AS site_intersect_communes
      GROUP BY nom_site
      ) as liste_communes_par_site
      WHERE geo_site.nom_site = liste_communes_par_site.nom_site)	
;

Par contre lorsque je souhaite automatiser avec un trigger, pour l'ajout d'un nouveau site, j'ai un petit souci. Il me récupère bien les communes qui intersectent et qui ne font pas que toucher la limite du site, mais il me répète autant de fois la communes qu'il y a de site....

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
27
28
29
30
31
32
 
CREATE OR REPLACE FUNCTION calc_liste_communes()
  RETURNS trigger AS
$BODY$
begin
 
IF NEW.nom_communes is null THEN
NEW.nom_communes = (SELECT liste_communes FROM
			                  (SELECT nom_site, array_agg(communes) as liste_communes FROM
			                            (SELECT geo_site.nom_site, initcap(bdcarto_commune.nom_comm)as communes
				                     FROM geo_site, bdcarto_commune 
				                     WHERE st_intersects (geo_site.the_geom, bdcarto_commune.geom)
				                     EXCEPT
				                     SELECT geo_site.nom_site, initcap(bdcarto_commune.nom_comm)as communes 
				                     FROM geo_site, bdcarto_commune 
				                     WHERE st_touches (geo_site.the_geom, bdcarto_commune.geom)
				            ) AS site_intersect_communes
			                   GROUP BY nom_site
			         ) as liste_communes_par_site
		    WHERE geo_site.nom_site = liste_communes_par_site.nom_site);
end if;
 
IF NEW.nom_communes is not null THEN
NEW.nom_communes = replace(replace(replace(replace(NEW.nom_communes,'"',''), ',',', '),'{',''),'}',''); -- pour une meilleur visibilité cf résultat de array_agg
end if;
 
return NEW;
end;$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;
ALTER FUNCTION calc_liste_communes()
  OWNER TO postgres;

et le trigger sur site:

Code :

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

1
2
3
4
5
6
 
CREATE TRIGGER add_liste_commune
  AFTER INSERT OR UPDATE
  ON geo_site
  FOR EACH ROW
  EXECUTE PROCEDURE calc_liste_communes();

Si quelqu'un a une petit idée de mon erreur et du pourquoi il me répète la commune autant de fois qu'il existe de site dans la base alors que l'update fonctionne bien?

cordialement