viewtopic.php?f=40&t=218#p1365
9 messages · 4 vues

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));
with lignes as (
select l1.nom as n1, l2.nom as n2, ST_MakeLine(l1.point,l2.point) as l from lieux l1, lieux l2
),
lieuxTest AS (
select n1,n2,l,nom,point from lignes lg, lieux l where
--lg.n1 like 'Paques' and lg.n2 like 'KhaJuraho' AND
l.nom not like lg.n1 AND l.nom not like lg.n2
)
select n1,n2,ST_ASText(l), nom, ST_ASText(point) as point_lieu,
ST_ASText(ST_Line_Interpolate_Point(l,ST_LineLocatePoint(l, point))) as point_le_plus_proche_sur_la_ligne,
ST_Distance(ST_Line_Interpolate_Point(l,ST_LineLocatePoint(l, point)), point,TRUE)/1000 as distance_du_lieu_a_la_ligne_en_km FROM lieuxTest
where n1 not like n2
order by n1,n2,point_lieu;
Ligne Lieu Distance du point à la ligne
"Paques";"Gizeh";"LINESTRING(-109.424205 -27.190547,31.13394 29.979437)";"Paratoari";"POINT(-71.4612 -12.673164)";"POINT(-71.7835070344074 -11.8807397992735)";94,413967408307
"Paques";"Gizeh";"LINESTRING(-109.424205 -27.190547,31.13394 29.979437)";"Cuzco";"POINT(-71.972222 -13.525)";"POINT(-72.5192793790796 -12.1800045140472)";160,206987948056
"Paques";"Gizeh";"LINESTRING(-109.424205 -27.190547,31.13394 29.979437)";"Sacsayhuamán";"POINT(-71.982222 -13.507778)";"POINT(-72.5218494107443 -12.1810498370995)";158,031729914896
"Paques";"Gizeh";"LINESTRING(-109.424205 -27.190547,31.13394 29.979437)";"Ollantaytambo";"POINT(-72.26333 -13.25806)";"POINT(-72.6759026384218 -12.2437087499748)";120,828847566723
"Paques";"Gizeh";"LINESTRING(-109.424205 -27.190547,31.13394 29.979437)";"Machu Piccu";"POINT(-72.54583 -13.1639)";"POINT(-72.8854398928045 -12.3289349848576)";99,460749121187
"Paques";"Gizeh";"LINESTRING(-109.424205 -27.190547,31.13394 29.979437)";"Nasca";"POINT(-75.133333 -14.716667)";"POINT(-75.6475603754163 -13.4523873738813)";150,500038481002
"Paques";"Gizeh";"LINESTRING(-109.424205 -27.190547,31.13394 29.979437)";"Angkor_Vat";"POINT(103.8668 13.41249)";"POINT(31.13394 29.979437)";7 629,70052744389
"Paques";"Gizeh";"LINESTRING(-109.424205 -27.190547,31.13394 29.979437)";"Preah_Vihear";"POINT(104.68389 14.38833)";"POINT(31.13394 29.979437)";7 659,20819708596
"Paques";"Gizeh";"LINESTRING(-109.424205 -27.190547,31.13394 29.979437)";"Siwa";"POINT(25.55 29.183333)";"POINT(26.0648026486336 27.9176390069245)";149,045989104357
"Paques";"Gizeh";"LINESTRING(-109.424205 -27.190547,31.13394 29.979437)";"Petra";"POINT(35.44361 30.32917)";"POINT(31.13394 29.979437)";416,959567708869
"Paques";"Gizeh";"LINESTRING(-109.424205 -27.190547,31.13394 29.979437)";"UR";"POINT(46.10551 30.961243)";"POINT(31.13394 29.979437)";1 440,71504992314
"Paques";"Gizeh";"LINESTRING(-109.424205 -27.190547,31.13394 29.979437)";"Persepolis";"POINT(52.891389 29.934444)";"POINT(31.13394 29.979437)";2 097,02722459711
"Paques";"Gizeh";"LINESTRING(-109.424205 -27.190547,31.13394 29.979437)";"Mohen-Jo-Daro";"POINT(68.137211 27.326851)";"POINT(31.13394 29.979437)";3 613,9698939491
"Paques";"Gizeh";"LINESTRING(-109.424205 -27.190547,31.13394 29.979437)";"KhaJuraho";"POINT(79.93 24.85)";"POINT(31.13394 29.979437)";4 823,26916967897
"Paques";"Gizeh";"LINESTRING(-109.424205 -27.190547,31.13394 29.979437)";"Tassili n Ajjer";"POINT(8.166667 25.166667)";"POINT(9.74722681039076 21.280702032613)";459,745127694975
"Paques";"Gizeh";"LINESTRING(-109.424205 -27.190547,31.13394 29.979437)";"Pyay";"POINT(95.217 18.817)";"POINT(31.13394 29.979437)";6 536,78922002678
"Paques";"Gizeh";"LINESTRING(-109.424205 -27.190547,31.13394 29.979437)";"Sukhotail";"POINT(99.78972 17.00722)";"POINT(31.13394 29.979437)";7 060,58187120903theophil a écrit :Pour info ontologiae,
Jacques dit dans une de ces interview que l'équateur penché n'est pas
une droite (ou une orthodromie comme on est sur une sphère),
mais une sinusoïde symbolisant le balancement de la lune autour de la
terre en 18.6 ans.
--> Tu as eu la bonne intuition
Noublies pas daller te presenter.
select ST_Distance( ST_setsrid('POINT(-109.424205 -27.190547)'::geometry,4326), ST_setsrid('POINT(-109.424205 -26.9667299740519084)'::geometry,4326), TRUE);
With points AS (
select nom, point as point_orig, degrees(radians(27.95) * sin(radians(34.667 + st_x(point)))) as comp
from lieux
),
Results as (
select nom,
point_orig,
ST_setsrid(
ST_makePoint(ST_x(point_orig), degrees(radians(27.95) * sin(radians(34.667 + st_x(point_orig)))) ),
4326 ) as calc_point
from points
)
select nom, st_astext(point_orig) as point_lieu, st_astext(calc_point) as point_calculee, st_distance(point_orig,calc_point,true)/1000 as distance_au_point_dorigine from Results
Monument Coordonnées_lieu coordonnées_calculée Distance entre le calcul et la coordonnée d'origine
"Gizeh" "POINT(31.13394 29.979437)" "POINT(31.13394 25.4939452139365)" 497,062211461495
"Paques" "POINT(-109.424205 -27.190547)" "POINT(-109.424205 -26.9667299740513)" 24,799991735847
"Mohen-Jo-Daro" "POINT(68.137211 27.326851)" "POINT(68.137211 27.2549692725275)" 7,965078651286
"Petra" "POINT(35.44361 30.32917)" "POINT(35.44361 26.2828144239444)" 448,435982354791
"UR" "POINT(46.10551 30.961243)" "POINT(46.10551 27.5883114011077)" 373,857363534667
"Persepolis" "POINT(52.891389 29.934444)" "POINT(52.891389 27.9246257172781)" 222,757534858094
"KhaJuraho" "POINT(79.93 24.85)" "POINT(79.93 25.4137584227257)" 62,450259989017
"Pyay" "POINT(95.217 18.817)" "POINT(95.217 21.4472717435994)" 291,187104602886
"Sukhotail" "POINT(99.78972 17.00722)" "POINT(99.78972 19.9501425424295)" 325,740644475462
"Angkor_Vat" "POINT(103.8668 13.41249)" "POINT(103.8668 18.5078781191667)" 563,850059687565
"Preah_Vihear" "POINT(104.68389 14.38833)" "POINT(104.68389 18.2073224125122)" 422,617812483991
"Ollantaytambo" "POINT(-72.26333 -13.25806)" "POINT(-72.26333 -17.0521388617625)" 419,816947794292
"Machu Piccu" "POINT(-72.54583 -13.1639)" "POINT(-72.54583 -17.161121189662)" 442,295204305561
"Cuzco" "POINT(-71.972222 -13.525)" "POINT(-71.972222 -16.9394021011028)" 377,807855889958
"Sacsayhuamán" "POINT(-71.982222 -13.507778)" "POINT(-71.982222 -16.9432820486034)" 380,142599572291
"Paratoari" "POINT(-71.4612 -12.673164)" "POINT(-71.4612 -16.7404439408234)" 450,029414734221
"Tassili n Ajjer" "POINT(8.166667 25.166667)" "POINT(8.166667 19.0024315286916)" 682,579290867446
"Siwa" "POINT(25.55 29.183333)" "POINT(25.55 24.2581647252407)" 545,706453104762
"Nasca" "POINT(-75.133333 -14.716667)" "POINT(-75.133333 -18.1395813513021)" 378,791414967778