Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
-- 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
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment