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 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56
| delimiter $$
drop function if exists distance$$
create function distance(la1 double, lo1 double, alt1 double, la2 double, lo2 double, alt2 double)
returns double
deterministic
begin
declare rla1,rla2,d,h double;
set rla1=radians(la1),rla2=radians(la2);
set d=6378137*acos(sin(rla1)*sin(rla2)+cos(rla1)*cos(rla2)*cos(radians(lo2-lo1))),h=alt2-alt1;
if h=0 then
return d;
else
return sqrt(d*d+h*h);
end if;
end$$
drop procedure if exists maj_chemin$$
create procedure maj_chemin(in i smallint unsigned)
begin
declare dt,pp,pn double;
drop table if exists maj;
create temporary table maj(
o1 int unsigned,
o2 int unsigned,
di double,
d double,
dh_p double,
di_p double,
dh_n double,
di_n double
)engine=memory;
insert into maj(o1,o2,di,d,dh_p,di_p,dh_n,di_n)
select pa1.ordre,pa2.ordre,
@d:=distance(po1.lat,po1.lon,po1.alt,po2.lat,po2.lon,po2.alt),@d,
if(@h:=po2.alt-po1.alt>0,@h,0),if(@h>0,@d,0),
if(@h<0,@h,0),if(@h<0,@d,0)
from parcours pa1
left join
(select id_point,ordre
from parcours
where id_chemin=i) pa2 on pa2.ordre>pa1.ordre
left join points po1 on po1.id=pa1.id_point
left join points po2 on po2.id=pa2.id_point
where pa1.id_chemin=i
group by pa1.ordre having pa2.ordre=min(pa2.ordre)
order by pa1.ordre;
select sum(d),coalesce(sum(dh_p)/sum(di_p),0),coalesce(sum(dh_n)/sum(di_n),0)
into dt,pp,pn
from maj;
update chemins set
longueur=dt,
pente_p=pp,
pente_n=pn
where id=i;
end$$
delimiter ; |
Partager