gistfile1.sql

-- Création et ajouts des nombres à tester
drop table nombres;
create table nombres(id serial primary key, nombre double precision);
insert into nombres(nombre) values(3.14159265359),(3.14159265359*10),(3.14159265359*100),(3.14159265359*1000)
					,(2.71828182846),(2.71828182846*10),(2.71828182846*100),(2.71828182846*1000)
					,(1.61803398875),(1.61803398875*10),(1.61803398875*100),(1.61803398875*1000)
					,(1.61803398875*3.14159265359),(1.61803398875*3.14159265359*10),(1.61803398875*3.14159265359*100),(1.61803398875*3.14159265359*1000);

-- Création de la table résultat
create table resultats( id serial primary key, type_resultat character, e double precision, pi double precision, phi double precision, pi_phi double precision, total double precision);

-- Création table lieux

CREATE TABLE lieux(nom text primary key, point geometry(point,4326));

insert into lieux(nom,point) values ('Gizeh',ST_setsrid('POINT(31.133940 29.979437)'::geometry,4326));
insert into lieux(nom,point) values ('Nasca',ST_setsrid('POINT(-75.133333 -14.716667 )'::geometry,4326));
insert into lieux(nom,point) values ('Paques',ST_setsrid('POINT(-109.424205 -27.190547)'::geometry,4326));
insert into lieux(nom,point) values ('Mohen-Jo-Daro',ST_setsrid('POINT(68.137211 27.326851)'::geometry,4326));

insert into lieux(nom,point) values ('Petra',ST_setsrid('POINT(35.44361 30.32917)'::geometry,4326));
insert into lieux(nom,point) values ('UR',ST_setsrid('POINT(46.10551 30.961243)'::geometry,4326));
insert into lieux(nom,point) values ('Persepolis',ST_setsrid('POINT(52.891389 29.934444)'::geometry,4326));
insert into lieux(nom,point) values ('KhaJuraho',ST_setsrid('POINT(79.93 24.85)'::geometry,4326));
insert into lieux(nom,point) values ('Pyay',ST_setsrid('POINT(95.217 18.817)'::geometry,4326));
insert into lieux(nom,point) values ('Sukhotail',ST_setsrid('POINT(99.78972 17.00722)'::geometry,4326));
insert into lieux(nom,point) values ('Angkor_Vat',ST_setsrid('POINT(103.8668 13.41249)'::geometry,4326));
insert into lieux(nom,point) values ('Preah_Vihear',ST_setsrid('POINT(104.68389 14.38833)'::geometry,4326));
insert into lieux(nom,point) values ('Paracas',ST_setsrid('POINT(-76.305028 -13.795056)'::geometry,4326));

insert into lieux(nom,point) values ('Ollantaytambo',ST_setsrid('POINT(-72.26333 -13.25806)'::geometry,4326));
insert into lieux(nom,point) values ('Machu Piccu',ST_setsrid('POINT(-72.54583 -13.1639)'::geometry,4326));
insert into lieux(nom,point) values ('Cuzco',ST_setsrid('POINT(-71.972222 -13.525)'::geometry,4326));
insert into lieux(nom,point) values ('Sacsayhuamán',ST_setsrid('POINT(-71.982222 -13.507778)'::geometry,4326));
insert into lieux(nom,point) values ('Paratoari',ST_setsrid('POINT(-71.4612 -12.673164)'::geometry,4326));
insert into lieux(nom,point) values ('Tassili n Ajjer',ST_setsrid('POINT(8.166667 25.166667)'::geometry,4326));
insert into lieux(nom,point) values ('Siwa',ST_setsrid('POINT(25.55 29.183333)'::geometry,4326));
-- création de 1800 lieux au hasard sur terre collée dans la table lieux

insert into lieux (
with brut as (
 select generate_series(1,19800) as s, ST_SetSRID(
      ST_MakePoint(
        random()*360-180,
        random()*180-90
      ), 4326
    )  pt , 'hasard-'|| round(random()*1800) as lieu
)
select distinct on (lieu) lieu as nom, pt as point from brut order by lieu limit 1800
)




-- Etape 1 : on prend au hasard des groupes de 21 lieux au hasard, on calcul le pourcentage d'alpha correspondant à 99,5% des chiffres dans ta table nombres




with serie AS (
select distinct on (r) generate_series(1,100) as n, round(random()*1801) as r
),
echantillon_lieu as (
select nom, point from lieux, serie where nom like 'hasard-'|| r limit 21
) ,
alphas as (
select distinct l1.nom || '-' || l2.nom  || '  --  ' ||  l3.nom || '-' || l4.nom as rapport_4_lieux,
 st_distance(l1.point,l2.point,true) as dist1,  st_distance(l3.point,l4.point,true) as dist2,
 CASE 
	WHEN st_distance(l1.point,l2.point,true)/st_distance(l3.point,l4.point,true) < 1 THEN st_distance(l3.point,l4.point,true)/st_distance(l1.point,l2.point,true)
	ELSE st_distance(l1.point,l2.point,true)/st_distance(l3.point,l4.point,true)
  END as alpha
FROM echantillon_lieu l1, echantillon_lieu l2, echantillon_lieu l3, echantillon_lieu l4 where 
	l1.nom not like l2.nom
	AND l3.nom not like l4.nom
	--AND l1.nom not like l3.nom
	AND l2.nom not like l4.nom
	AND l2.nom not like l3.nom
	AND l1.nom  like 'has%' AND l2.nom  like 'has%' 
	AND l3.nom  like 'has%' AND l4.nom  like 'has%'
),
total AS (
select count(alpha) as combi from alphas
),
resultats1 as 
(
select n.nombre, count(n.nombre), (100/ t.combi::float)* count(n.nombre) as pourcentage
from nombres n, alphas a, total t where (n.nombre - 0.005*n.nombre) < a.alpha 
				    and (n.nombre + 0.005*n.nombre  > a.alpha )
group by n.nombre, t.combi
),
groupes AS (
select grp, sum(pourcentage) as pourcent FROM 
	(
	select 
	case 
		when nombre = 1.61803398875 then 'phi'
		when nombre = 16.1803398875 then 'phi'
		when nombre = 161.803398875 then 'phi'
		when nombre = 1618.03398875 then 'phi'
		WHEN nombre = 5.08320369231592 THEN 'pi_phi'
		WHEN nombre = 50.8320369231592 THEN 'pi_phi'
		WHEN nombre = 508.320369231592 THEN 'pi_phi'
		WHEN nombre = 5083.20369231592 THEN 'pi_phi'
		WHEN nombre = 3.14159265359 THEN 'pi'
		WHEN nombre = 31.4159265359 THEN 'pi'
		WHEN nombre = 314.159265359 THEN 'pi'
		WHEN nombre = 3141.59265359 THEN 'pi'
		WHEN nombre = 2.71828182846 THEN 'e'
		WHEN nombre = 27.1828182846 THEN 'e'
		WHEN nombre = 271.828182846 THEN 'e'
		WHEN nombre = 2718.28182846 THEN 'e'
		else nombre::text
	END as grp,
	pourcentage
	 from resultats1) as tbl
 group by grp
 order by grp
),
--select * from groupes
resultat2 as (
select  
	case when grp = 'e' then pourcent end as e,
	case when grp = 'pi' then pourcent end as pi,
	case when grp = 'phi' then pourcent end as phi,
	coalesce(case when grp = 'pi_phi' then pourcent end, 0.0) as pi_phi
from groupes
),
arrays AS (
select 
array( select * from (select unnest(array_agg(e)) as ee) t where ee is not null)  as e,
array( select * from (select unnest(array_agg(pi)) as ee) t where ee is not null)  as pi, 
array( select * from (select unnest(array_agg(phi)) as ee) t where ee is not null)  as phi
--,array( select * from (select unnest(array_agg(phi)) as ee) t where ee is not null)  as pi_phi,
from resultat2
)
insert into resultats( type_resultat, e, pi, phi, pi_phi, total)
select 'H' as type_resultat, e[1] as e, pi[1] as pi, phi[1] as phi, cast(-1.0 as double precision) as pi_phi, e[1] + pi[1] + phi[1]  as total from arrays







------ TEST avec échantillon 'Grimault'


WITH alphas as (
select distinct l1.nom || '-' || l2.nom  || '  --  ' ||  l3.nom || '-' || l4.nom as rapport_4_lieux,
 st_distance(l1.point,l2.point,true) as dist1,  st_distance(l3.point,l4.point,true) as dist2,
 CASE 
	WHEN st_distance(l1.point,l2.point,true)/st_distance(l3.point,l4.point,true) < 1 THEN st_distance(l3.point,l4.point,true)/st_distance(l1.point,l2.point,true)
	ELSE st_distance(l1.point,l2.point,true)/st_distance(l3.point,l4.point,true)
  END as alpha
FROM lieux l1, lieux l2, lieux l3, lieux l4 where 
	l1.nom not like l2.nom
	AND l3.nom not like l4.nom
	--AND l1.nom not like l3.nom
	AND l2.nom not like l4.nom
	AND l2.nom not like l3.nom
	AND l1.nom NOT like 'has%' AND l2.nom NOT like 'has%' 
	AND l3.nom NOT like 'has%' AND l4.nom NOT like 'has%'
),
total AS (
select count(alpha) as combi from alphas
),
resultats1 as 
(
select n.nombre, count(n.nombre), (100/ t.combi::float)* count(n.nombre) as pourcentage
from nombres n, alphas a, total t where (n.nombre - 0.005*n.nombre) < a.alpha 
				    and (n.nombre + 0.005*n.nombre  > a.alpha )
group by n.nombre, t.combi
),
groupes AS (
select grp, sum(pourcentage) as pourcent FROM 
	(
	select 
	case 
		when nombre = 1.61803398875 then 'phi'
		when nombre = 16.1803398875 then 'phi'
		when nombre = 161.803398875 then 'phi'
		when nombre = 1618.03398875 then 'phi'
		WHEN nombre = 5.08320369231592 THEN 'pi_phi'
		WHEN nombre = 50.8320369231592 THEN 'pi_phi'
		WHEN nombre = 508.320369231592 THEN 'pi_phi'
		WHEN nombre = 5083.20369231592 THEN 'pi_phi'
		WHEN nombre = 3.14159265359 THEN 'pi'
		WHEN nombre = 31.4159265359 THEN 'pi'
		WHEN nombre = 314.159265359 THEN 'pi'
		WHEN nombre = 3141.59265359 THEN 'pi'
		WHEN nombre = 2.71828182846 THEN 'e'
		WHEN nombre = 27.1828182846 THEN 'e'
		WHEN nombre = 271.828182846 THEN 'e'
		WHEN nombre = 2718.28182846 THEN 'e'
		else nombre::text
	END as grp,
	pourcentage
	 from resultats1) as tbl
 group by grp
 order by grp
),
--select * from groupes
resultat2 as (
select  
	case when grp = 'e' then pourcent end as e,
	case when grp = 'pi' then pourcent end as pi,
	case when grp = 'phi' then pourcent end as phi,
	coalesce(case when grp = 'pi_phi' then pourcent end, 0.0) as pi_phi
from groupes
),
arrays AS (
select 
array( select * from (select unnest(array_agg(e)) as ee) t where ee is not null)  as e,
array( select * from (select unnest(array_agg(pi)) as ee) t where ee is not null)  as pi, 
array( select * from (select unnest(array_agg(phi)) as ee) t where ee is not null)  as phi
--,array( select * from (select unnest(array_agg(phi)) as ee) t where ee is not null)  as pi_phi,
from resultat2
)
insert into resultats( type_resultat, e, pi, phi, pi_phi, total)
select 'T' as type_resultat, e[1] as e, pi[1] as pi, phi[1] as phi, cast(-1.0 as double precision) as pi_phi, e[1] + pi[1] + phi[1]  as total from arrays