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
| delimiter $$
DROP FUNCTION IF EXISTS dist_ville$$
CREATE FUNCTION dist_ville(la double,lo double,ca double,sa double,ro double)
returns double
begin
declare rla double DEFAULT RADIANS(la);
RETURN ACOS(sa * SIN(rla) + ca * COS(rla) * COS(RADIANS(lo) - ro)) * 6371;
end$$
DROP procedure IF EXISTS recherche_villes$$
CREATE procedure recherche_villes(IN nomVille varchar(255),IN rayon double,IN notemin double)
begin
declare latville,lonville,eclo,ecla,lo,la,minla,maxla,minlo,maxlo,cla,sla double;
SELECT lat,lon INTO latville,lonville FROM villes WHERE nom=nomVille;
SET lo=radians(lonville),la=radians(latville);
SET cla=cos(la),sla=sin(la);
SET eclo=rayon/(11.120*cla),ecla=rayon/11.120;
SET minla=latville-ecla,maxla=latville+ecla,minlo=lonville-eclo,maxlo=lonville+eclo;
SELECT t.nomeleve,t.note,t.nomville,t.lat,t.lon,dist_ville(t.lat,t.lon,cla,sla,lo) as dist
FROM
(SELECT e.nom AS `nomeleve`,e.note AS `note`,v.nom AS `nomville`,v.lat AS `lat`,v.lon AS `lon`
FROM villes v
INNER JOIN eleves e ON e.idville=v.id AND e.note>=notemin
WHERE lat BETWEEN minla AND maxla
AND lon BETWEEN minlo AND maxlo) t
WHERE dist_ville(t.lat,t.lon,cla,sla,lo)<=rayon
order by dist desc;
end$$
delimiter ;
-- pour être sur que ca te prenne bien le dernier delimiter |
