boucle avec mysql?

Bonjour,

j'ai cree deux tables dont voici des extraits
tab1
b_cod_pr Latitude Longitude reperes
14796686 49.00366 2.57089
15458884 50.92011 1.82143
46634848 46.58789 5.44579

et tab2
villes latitude longitude
ST QUENTIN 49.85 3.283333
CHARLEVILLE-MEZ 49.766667 4.716667

Je dois remplir la colonne repère par la ville de tab2 la plus proche du cod_pr.
à faire pour chaque code pr. ( en utilisant les coordonnées geo)
J'ai une crée une fonction get_distance _metre qui calcule la distance entre deux points (avec coord geo)
get_distance_metres (lat1 , lng1 , lat2 , lng2 ) qui renvoit une distance en metre .
Je pensais faire deux boucles for mais je ne sais pas comment ca marche avec mysql ou un repeat .
Si quelqu'un peut m'aider
merci

Une seule requête devrait fonctionner :

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
update tab1
INNER JOIN
(
  SELECT b_cod_pr,idVille
  FROM tab1 as t1_1
  INNER JOIN tab2 as t2_1
  WHERE NOT EXISTS
  (
    SELECT 1
    FROM tab1 as t1_2
    INNER JOIN tab2 as t2_2
    WHERE t1_1.b_cod_pr=t1_2.b_cod_pr
    AND get_distance_metres(t2_1.latitude,t2_1.longitude,t1_1.latitude,t1_1.longitude) > get_distance_metres(t2_2.latitude,t2_2.longitude,t1_2.latitude,t1_2.longitude)
  )
)tmp
USING(b_cod_pr)
SET reperes=tmp.idVille

fred je suis pas sur de comprendre ta requête...

je vais te montrer comment calculer, la distance, la différence de hauteur, le dénivelé positif et négatif pour un chemin (une série de points ordonnés). le but ici est d'optimiser au maximum le traitement

on stocke dans une table temporaire le distance entre les points consécutifs 2 à 2 puis on utilise cette table pour sommer les valeurs et obtenir les valeurs finale de distance et dénivelés totaux...

les tables utilisées:

Code:

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
#liste les chemins
create table chemins(
	id			smallint unsigned auto_increment not null,
	id_ville	tinyint unsigned,
	longueur	double,
	pente_p	double,
	pente_n	double,
	nom		varchar(128),
	constraint pk_chemins primary key(id)
)engine=innodb auto_increment=1;
 
#liste les points et leurs coordonnées
create table points(
	id			int unsigned auto_increment not null,
	lon		double not null,
	lat		double not null,
	alt		double not null,
	constraint pk_points primary key(id)
)engine=innodb auto_increment=1;
 
#associe les points et les chemins
create table parcours(
	id				int unsigned auto_increment not null,
	id_chemin	smallint unsigned not null,
	id_point		int unsigned not null,
	ordre			int unsigned not null,
	constraint pk_parcours primary key(id),
	constraint fk_parcours_chemin	foreign key(id_chemin) references chemins(id),
	constraint fk_parcours_point	foreign key(id_point) references points(id)
)engine=innodb auto_increment=1;

l'api associé:

Code:

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 ;

le plus dur est en fait juste de faire le calcul à chaque fois sur 2 lignes consécutives... voilà un exemple simplifié pour illustrer la technique que j'ai trouvé pour le faire. l'idée c'est d'utiliser une auto-jointure sur le critère d'ordonnancement des valeurs puis de grouper les résultats sur cette colonne et d'éliminer les lignes dont la valeur de classement des données n'est pas minimale

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
drop table if exists altitude;
create table altitude(
	id		smallint not null auto_increment,
	alt	mediumint,
	primary key(id)
)engine=myisam;
insert into altitude(alt)values
(10),(25),(40),(35),(55),(25);
 
select a.alt,b.alt,b.id,b.alt-a.alt
from altitude a
left join(
	select id,alt
	from altitude
) b on a.id<b.id
group by a.alt having b.id=min(b.id);
 
select sum(if((@a:=b.alt-a.alt)>0,@a,0)) as `positif`,sum(if(@a<0,@a,0)) as `négatif`
from altitude a
left join altitude b on b.id=a.id+1;

tu as de quoi étudier et adapter à ton problème, après tout le but c'est que tu apprennes pas qu'on fasse le boulot pour toi ;)

tu peux sinon te faire une fonction ou procédure avec un curseur et une requête dans un bouche "while ... do ... end while;" ou "repeat ... until ... end repeat;" mais ça aurais des performances pourries par rapport à l'approche avec les jointures dans ce cas...