--Cette fonction s'applique à une table contenant déjà des polygones de voronoi  et au moins un attribut numérique 
--dont on souhaite analyser sa distribution spatiale
--Le résultat sont trois colonnes permettant de représenter 
--  voronoi moyenne -> colonne "moyenne"
--  voronoi cluster -> colonne "cluster"
--  voronoi entropie -> colonne "entrop"
--tablex est le nom de la table en entrée
--gid est le nom de la colonne identifiant de la table
--attr est le nom de la colonne (obligatoirement numérique) à traiter
--'||geom||' est le nom du cham géométrie de la table
--* 
 CREATE OR REPLACE FUNCTION public.af_voronoi(tablex character varying, gid character varying,attr character varying,geom character varying)
--*
    RETURNS text 
    LANGUAGE 'plpgsql'
--
AS $BODY$
--
DECLARE
        rec   record;
		rec2  record;
		nb	integer;
        L1    numeric;
		L2    numeric;
		L3    numeric;
		L4    numeric;
		L5    numeric;
		LL    integer;
		Pi1   integer :=0;
		Pi2   integer:=0;
		Pi3   integer:=0;
		Pi4   integer:=0;
		Pi5   integer:=0;
		
BEGIN
--Création des colonnes de résultat et temporaires
EXECUTE 'alter table '||tablex||' DROP  COLUMN  IF EXISTS  smartcl';
EXECUTE 'alter table '||tablex||' add column smartcl integer';
EXECUTE 'alter table '||tablex||' DROP  COLUMN  IF EXISTS  entrop';
EXECUTE 'alter table '||tablex||' add column entrop float';
EXECUTE 'alter table '||tablex||' DROP  COLUMN  IF EXISTS  Nv';
EXECUTE 'alter table '||tablex||' add column Nv integer';
EXECUTE 'alter table '||tablex||' DROP  COLUMN  IF EXISTS  P1';
EXECUTE 'alter table '||tablex||' add column P1 integer' ;
EXECUTE 'alter table '||tablex||' DROP  COLUMN  IF EXISTS  P2';
EXECUTE 'alter table '||tablex||' add column P2 integer' ;
EXECUTE 'alter table '||tablex||' DROP  COLUMN  IF EXISTS  P3';
EXECUTE 'alter table '||tablex||' add column P3 integer ';
EXECUTE 'alter table '||tablex||' DROP  COLUMN  IF EXISTS  P4';
EXECUTE 'alter table '||tablex||' add column P4 integer' ;
EXECUTE 'alter table '||tablex||' DROP  COLUMN  IF EXISTS  P5';
EXECUTE 'alter table '||tablex||' add column P5 integer' ;
EXECUTE 'alter table '||tablex||' DROP  COLUMN  IF EXISTS  moyenne';
EXECUTE 'alter table '||tablex||' add column moyenne float ';
EXECUTE 'alter table '||tablex||' DROP  COLUMN  IF EXISTS  cluster';
EXECUTE 'alter table '||tablex||' add column cluster integer' ;
EXECUTE 'select count(*) as nb from '||tablex||'' INTO rec;
--
--calcul du voronoi "moyenne"
EXECUTE 'update '||tablex||' vor
set moyenne= (SELECT (sum(a.'||attr||')::real+b.'||attr||'::real)/(count(a.'||attr||')::real+1)
FROM '||tablex||' as a
JOIN '||tablex||' as b
ON st_touches(a.'||geom||',b.'||geom||')
where b.'||gid||' =vor.'||gid||'
group by b.'||gid||')';
--
--classement de l'attribut en cinq classes de même nombre d'échantillons
--      calcul des limites des classes
EXECUTE 'select max('||attr||') as L1 from 
(select '||attr||' from '||tablex||'
order by '||attr||' asc
limit ((2597/5)*1)::int)as a' INTO rec;
L1:=rec.L1;
--
EXECUTE 'select max('||attr||') as L2 from 
(select '||attr||' from '||tablex||'
order by '||attr||' asc
limit ((2597/5)*2)::int)as a' INTO rec;
L2:=rec.L2;
--
EXECUTE 'select max('||attr||')as L3 from 
(select '||attr||' from '||tablex||'
order by '||attr||' asc
limit ((2597/5)*3)::int)as a' INTO rec;
L3:=rec.L3;
--
EXECUTE 'select max('||attr||')as L4 from 
(select '||attr||' from '||tablex||'
order by '||attr||' asc
limit ((2597/5)*4)::int)as a' INTO rec;
L4:=rec.L4;
--
EXECUTE 'select max('||attr||')as L5 from 
(select '||attr||' from '||tablex||'
order by '||attr||' asc
limit ((2597/5)*5)::int)as a' INTO rec;
L5:=rec.L5;
--
--affectation d'une classe à chaque enregistrement de la table
FOR rec IN EXECUTE 'select '||gid||' as gid,'||attr||' as attr from '||tablex||'' 
LOOP
--
   CASE
      WHEN rec.attr <=L1 THEN
         EXECUTE 'update '||tablex||' set smartcl=1 where '||gid||'='||rec.gid;   
      WHEN rec.attr <=L2 and rec.attr > L1 THEN
         EXECUTE 'update '||tablex||' set smartcl=2 where '||gid||'='||rec.gid;
	 WHEN rec.attr <=L3 and rec.attr > L2 THEN
         EXECUTE 'update '||tablex||' set smartcl=3 where '||gid||'='||rec.gid;
	 WHEN rec.attr <=L4 and rec.attr > L3 THEN
         EXECUTE 'update '||tablex||' set smartcl=4 where '||gid||'='||rec.gid;
	 ELSE
	     EXECUTE 'update '||tablex||' set smartcl=5 where '||gid||'='||rec.gid;
   END CASE ;
END LOOP;
--*
--calcul du voronoi "entropie"
--*
EXECUTE 'update '||tablex||' vor set Nv= (SELECT (count(a.smartcl)) FROM '||tablex||' as a JOIN '||tablex||' as b
ON st_touches(a.'||geom||',b.'||geom||') where b.'||gid||' =vor.'||gid||'  group by b.'||gid||')';
EXECUTE 'update '||tablex||' vor set P1= (SELECT (count(a.smartcl)) FROM '||tablex||' as a JOIN '||tablex||' as b
ON st_touches(a.'||geom||',b.'||geom||') where b.'||gid||' =vor.'||gid||' and a.smartcl=1 group by b.'||gid||')';
EXECUTE 'update '||tablex||' vor set P2= (SELECT (count(a.smartcl)) FROM '||tablex||' as a JOIN '||tablex||' as b
ON st_touches(a.'||geom||',b.'||geom||') where b.'||gid||' =vor.'||gid||' and a.smartcl=2 group by b.'||gid||')';
EXECUTE 'update '||tablex||' vor set P3= (SELECT (count(a.smartcl)) FROM '||tablex||' as a JOIN '||tablex||' as b
ON st_touches(a.'||geom||',b.'||geom||') where b.'||gid||' =vor.'||gid||' and a.smartcl=3 group by b.'||gid||')';
EXECUTE 'update '||tablex||' vor set P4= (SELECT (count(a.smartcl)) FROM '||tablex||' as a JOIN '||tablex||' as b
ON st_touches(a.'||geom||',b.'||geom||') where b.'||gid||' =vor.'||gid||' and a.smartcl=4 group by b.'||gid||')';
EXECUTE 'update '||tablex||' vor set P5= (SELECT (count(a.smartcl)) FROM '||tablex||' as a JOIN '||tablex||' as b
ON st_touches(a.'||geom||',b.'||geom||') where b.'||gid||' =vor.'||gid||' and a.smartcl=5 group by b.'||gid||')';
EXECUTE 'update '||tablex||' set entrop=0';
EXECUTE 'update '||tablex||' set entrop= entrop +((P1::real/Nv::real)*(ln(P1::real/Nv::real))) where P1 IS NOT NULL';
EXECUTE 'update '||tablex||' set entrop= entrop+((P2::real/Nv::real)*(ln(P2::real/Nv::real))) where P2 IS NOT NULL';
EXECUTE 'update '||tablex||' set entrop= entrop+((P3::real/Nv::real)*(ln(P3::real/Nv::real))) where P3 IS NOT NULL';
EXECUTE 'update '||tablex||' set entrop= entrop+((P4::real/Nv::real)*(ln(P4::real/Nv::real))) where P4 IS NOT NULL';
EXECUTE 'update '||tablex||' set entrop= entrop+((P5::real/Nv::real)*(ln(P5::real/Nv::real))) where P5 IS NOT NULL';
EXECUTE 'update '||tablex||' set entrop=entrop*(-1) where entrop <>0';
--
--calcul du voronoi "cluster"
EXECUTE'update '||tablex||' vor
set cluster= (SELECT a.smartcl
FROM '||tablex||' as a
JOIN '||tablex||' as b
ON st_touches(a.'||geom||',b.'||geom||')
where b.'||gid||' =vor.'||gid||' and a.smartcl=b.smartcl 
group by b.'||gid||',a.smartcl )';
--
EXECUTE 'update '||tablex||' set cluster=99 where cluster IS NULL';
--
--effacement des colonnes temporaires
EXECUTE 'alter table '||tablex||' DROP  COLUMN  IF EXISTS  Nv';
EXECUTE 'alter table '||tablex||' DROP  COLUMN  IF EXISTS  P1';
EXECUTE 'alter table '||tablex||' DROP  COLUMN  IF EXISTS  P2';
EXECUTE 'alter table '||tablex||' DROP  COLUMN  IF EXISTS  P3';
EXECUTE 'alter table '||tablex||' DROP  COLUMN  IF EXISTS  P4';
EXECUTE 'alter table '||tablex||' DROP  COLUMN  IF EXISTS  P5';
RETURN 'OK';
END;
$BODY$;