Bonjour à tous,

J'ai besoin d'aide pour trouver la manière la plus performante de rechercher le point le plus proche d'un autre à partir de 2 tables contenant des positions.

Voici le détail des tables :

Code : Sélectionner tout - Visualiser dans une fenêtre à part
1
2
3
4
5
6
7
8
9
10
11
12
13
 
CREATE TABLE T_POSI 
(
   UniqueId BIGINT PRIMARY KEY IDENTITY(1,1),
   GeoCoord GEOGRAPHY
)
 
CREATE TABLE T_KPOINT
(
   UniqueId BIGINT PRIMARY KEY IDENTITY(1,1),
   GeoCoord GEOGRAPHY,
   MaxRadius INT -- Valeur exprimée en kilomètres
)
Ainsi qu'un jeu de données :
Code : Sélectionner tout - Visualiser dans une fenêtre à part
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
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
 
INSERT INTO T_POSI VALUES (geography::Point(45.1882,5.76157,4326))
INSERT INTO T_POSI VALUES (geography::Point(47.0346,4.86296,4326))
INSERT INTO T_POSI VALUES (geography::Point(45.1883,5.76184,4326))
INSERT INTO T_POSI VALUES (geography::Point(45.1883,5.76197,4326))
INSERT INTO T_POSI VALUES (geography::Point(42.726,2.89992,4326))
INSERT INTO T_POSI VALUES (geography::Point(42.726,2.89992,4326))
INSERT INTO T_POSI VALUES (geography::Point(45.1883,5.76121,4326))
INSERT INTO T_POSI VALUES (geography::Point(47.0298,4.87424,4326))
INSERT INTO T_POSI VALUES (geography::Point(42.7262,2.89992,4326))
INSERT INTO T_POSI VALUES (geography::Point(48.5781,1.78159,4326))
INSERT INTO T_POSI VALUES (geography::Point(44.4526,1.44868,4326))
INSERT INTO T_POSI VALUES (geography::Point(45.1871,5.76134,4326))
INSERT INTO T_POSI VALUES (geography::Point(47.0221,4.87245,4326))
INSERT INTO T_POSI VALUES (geography::Point(44.4435,1.44744,4326))
INSERT INTO T_POSI VALUES (geography::Point(45.1883,5.76121,4326))
INSERT INTO T_POSI VALUES (geography::Point(42.7266,2.89913,4326))
INSERT INTO T_POSI VALUES (geography::Point(48.5782,1.7828,4326))
INSERT INTO T_POSI VALUES (geography::Point(48.815,2.04551,4326))
INSERT INTO T_POSI VALUES (geography::Point(48.815,2.04551,4326))
INSERT INTO T_POSI VALUES (geography::Point(45.1869,5.76122,4326))
INSERT INTO T_POSI VALUES (geography::Point(48.8149,2.04543,4326))
INSERT INTO T_POSI VALUES (geography::Point(48.8148,2.04548,4326))
INSERT INTO T_POSI VALUES (geography::Point(45.1866,5.76184,4326))
INSERT INTO T_POSI VALUES (geography::Point(42.7269,2.89963,4326))
INSERT INTO T_POSI VALUES (geography::Point(42.8205,2.82797,4326))
INSERT INTO T_POSI VALUES (geography::Point(47.0138,4.86708,4326))
INSERT INTO T_POSI VALUES (geography::Point(42.7269,2.90067,4326))
INSERT INTO T_POSI VALUES (geography::Point(48.7603,2.2566,4326))
INSERT INTO T_POSI VALUES (geography::Point(48.8165,2.04675,4326))
INSERT INTO T_POSI VALUES (geography::Point(45.1858,5.76147,4326))
INSERT INTO T_POSI VALUES (geography::Point(45.1857,5.76138,4326))
INSERT INTO T_POSI VALUES (geography::Point(44.4401,1.44133,4326))
INSERT INTO T_POSI VALUES (geography::Point(45.1883,5.76121,4326))
INSERT INTO T_POSI VALUES (geography::Point(48.5782,1.7828,4326))
INSERT INTO T_POSI VALUES (geography::Point(42.7261,2.89997,4326))
INSERT INTO T_POSI VALUES (geography::Point(47.0052,4.86243,4326))
INSERT INTO T_POSI VALUES (geography::Point(48.8157,2.05139,4326))
INSERT INTO T_POSI VALUES (geography::Point(42.8205,2.81823,4326))
INSERT INTO T_POSI VALUES (geography::Point(48.7599,2.27032,4326))
INSERT INTO T_POSI VALUES (geography::Point(44.4368,1.44273,4326))
INSERT INTO T_POSI VALUES (geography::Point(42.7684,2.87099,4326))
INSERT INTO T_POSI VALUES (geography::Point(46.9967,4.85783,4326))
INSERT INTO T_POSI VALUES (geography::Point(45.1858,5.76086,4326))
INSERT INTO T_POSI VALUES (geography::Point(45.1883,5.76121,4326))
INSERT INTO T_POSI VALUES (geography::Point(48.5781,1.78252,4326))
INSERT INTO T_POSI VALUES (geography::Point(42.7261,2.89999,4326))
INSERT INTO T_POSI VALUES (geography::Point(48.5779,1.78233,4326))
INSERT INTO T_POSI VALUES (geography::Point(48.5777,1.78237,4326))
 
INSERT INTO T_KPOINT (GeoCoord, MaxRadius) VALUES (geography::Point(45.1885,5.7607,4326), 20)
INSERT INTO T_KPOINT (GeoCoord, MaxRadius) VALUES (geography::Point(45.1486,5.73292,4326), 50)
INSERT INTO T_KPOINT (GeoCoord, MaxRadius) VALUES (geography::Point(45.1822,5.7351,4326), 100)
INSERT INTO T_KPOINT (GeoCoord, MaxRadius) VALUES (geography::Point(45.1883,5.76154,4326), 50)
INSERT INTO T_KPOINT (GeoCoord, MaxRadius) VALUES (geography::Point(45.2142,5.68504,4326), 50)
INSERT INTO T_KPOINT (GeoCoord, MaxRadius) VALUES (geography::Point(45.2579,5.16907,4326), 240)
INSERT INTO T_KPOINT (GeoCoord, MaxRadius) VALUES (geography::Point(45.187,5.76177,4326), 40)
INSERT INTO T_KPOINT (GeoCoord, MaxRadius) VALUES (geography::Point(45.1892,5.78066,4326), 100)
INSERT INTO T_KPOINT (GeoCoord, MaxRadius) VALUES (geography::Point(45.2032,5.76368,4326), 100)
INSERT INTO T_KPOINT (GeoCoord, MaxRadius) VALUES (geography::Point(45.1899,5.725,4326), 100)
INSERT INTO T_KPOINT (GeoCoord, MaxRadius) VALUES (geography::Point(45.0839,5.74319,4326), 100)
INSERT INTO T_KPOINT (GeoCoord, MaxRadius) VALUES (geography::Point(45.3223,5.59115,4326), 50)
INSERT INTO T_KPOINT (GeoCoord, MaxRadius) VALUES (geography::Point(45.184,5.77221,4326), 50)
INSERT INTO T_KPOINT (GeoCoord, MaxRadius) VALUES (geography::Point(47.911,1.8803,4326), 50)
INSERT INTO T_KPOINT (GeoCoord, MaxRadius) VALUES (geography::Point(47.911,1.8803,4326), 50)
INSERT INTO T_KPOINT (GeoCoord, MaxRadius) VALUES (geography::Point(46.2036,6.27643,4326), 50)
INSERT INTO T_KPOINT (GeoCoord, MaxRadius) VALUES (geography::Point(47.8109,3.5824,4326), 50)
INSERT INTO T_KPOINT (GeoCoord, MaxRadius) VALUES (geography::Point(45.165,5.76236,4326), 50)
INSERT INTO T_KPOINT (GeoCoord, MaxRadius) VALUES (geography::Point(45.7753,4.93677,4326), 50)
INSERT INTO T_KPOINT (GeoCoord, MaxRadius) VALUES (geography::Point(48.7125,2.30108,4326), 50)
INSERT INTO T_KPOINT (GeoCoord, MaxRadius) VALUES (geography::Point(48.9227,2.32054,4326), 50)
INSERT INTO T_KPOINT (GeoCoord, MaxRadius) VALUES (geography::Point(47.7886,7.29648,4326), 50)
INSERT INTO T_KPOINT (GeoCoord, MaxRadius) VALUES (geography::Point(48.5812,7.78544,4326), 50)
INSERT INTO T_KPOINT (GeoCoord, MaxRadius) VALUES (geography::Point(50.1516,3.21813,4326), 50)
INSERT INTO T_KPOINT (GeoCoord, MaxRadius) VALUES (geography::Point(50.5306,2.80436,4326), 50)
INSERT INTO T_KPOINT (GeoCoord, MaxRadius) VALUES (geography::Point(47.4589,-0.560697,4326), 50)
INSERT INTO T_KPOINT (GeoCoord, MaxRadius) VALUES (geography::Point(47.1739,-1.61444,4326), 50)
INSERT INTO T_KPOINT (GeoCoord, MaxRadius) VALUES (geography::Point(46.3185,-0.493681,4326), 50)
INSERT INTO T_KPOINT (GeoCoord, MaxRadius) VALUES (geography::Point(48.4363,1.52878,4326), 50)
INSERT INTO T_KPOINT (GeoCoord, MaxRadius) VALUES (geography::Point(42.6903,2.92427,4326), 50)
INSERT INTO T_KPOINT (GeoCoord, MaxRadius) VALUES (geography::Point(43.1286,5.92181,4326), 50)
INSERT INTO T_KPOINT (GeoCoord, MaxRadius) VALUES (geography::Point(43.6145,1.48911,4326), 50)
INSERT INTO T_KPOINT (GeoCoord, MaxRadius) VALUES (geography::Point(45.34,5.49083,4326), 50)
INSERT INTO T_KPOINT (GeoCoord, MaxRadius) VALUES (geography::Point(45.2944,3.38096,4326), 100)
INSERT INTO T_KPOINT (GeoCoord, MaxRadius) VALUES (geography::Point(48.8461,2.28173,4326), 50)
INSERT INTO T_KPOINT (GeoCoord, MaxRadius) VALUES (geography::Point(45.5097,-73.5544,4326), 50)
INSERT INTO T_KPOINT (GeoCoord, MaxRadius) VALUES (geography::Point(47.911,1.8803,4326), 50)
INSERT INTO T_KPOINT (GeoCoord, MaxRadius) VALUES (geography::Point(47.3787,0.67037,4326), 50)
INSERT INTO T_KPOINT (GeoCoord, MaxRadius) VALUES (geography::Point(46.2036,6.27643,4326), 50)
INSERT INTO T_KPOINT (GeoCoord, MaxRadius) VALUES (geography::Point(47.8109,3.5824,4326), 50)
INSERT INTO T_KPOINT (GeoCoord, MaxRadius) VALUES (geography::Point(45.7859,3.14038,4326), 50)
INSERT INTO T_KPOINT (GeoCoord, MaxRadius) VALUES (geography::Point(45.165,5.76236,4326), 50)
INSERT INTO T_KPOINT (GeoCoord, MaxRadius) VALUES (geography::Point(45.1767,5.70997,4326), 50)
INSERT INTO T_KPOINT (GeoCoord, MaxRadius) VALUES (geography::Point(48.3639,-4.77757,4326), 90)
INSERT INTO T_KPOINT (GeoCoord, MaxRadius) VALUES (geography::Point(48.3639,-4.77757,4326), 90)
INSERT INTO T_KPOINT (GeoCoord, MaxRadius) VALUES (geography::Point(45.2868,4.53074,4326), 50)
INSERT INTO T_KPOINT (GeoCoord, MaxRadius) VALUES (geography::Point(45.2982,6.05928,4326), 50)
INSERT INTO T_KPOINT (GeoCoord, MaxRadius) VALUES (geography::Point(46.6013,2.62126,4326), 100)
INSERT INTO T_KPOINT (GeoCoord, MaxRadius) VALUES (geography::Point(48.7924,2.15333,4326), 190)
INSERT INTO T_KPOINT (GeoCoord, MaxRadius) VALUES (geography::Point(49.1786,-2.11079,4326), 50)
Le but de la recherche étant de trouver, pour chaque élément contenu dans T_POSI, le point contenu dans T_KPOINT pour lequel la distance est inférieure au radius indiqué.

Du fait de mon niveau assez faible en TSQL, j'ai pensé écrire la requête suivante :

Code : Sélectionner tout - Visualiser dans une fenêtre à part
1
2
3
4
5
6
 
SELECT T_POSI.UniqueId, (SELECT TOP 1 T_KPOINT.UniqueId
					FROM T_KPOINT
					WHERE T_KPOINT.GeoCoord.STDistance(T_POSI.GeoCoord) < (T_KPOINT.MaxRadius * 1000)
					ORDER BY T_KPOINT.GeoCoord.STDistance(T_POSI.GeoCoord) ASC)
FROM T_POSI
Ok, ça fonctionne, cependant, je me doute que ce n'est probablement pas la manière la plus optimisée d'arriver à mes fins.

Sauriez vous me conseiller pour trouver une manière plus élégante et surtout plus efficace ? En effet, il s'agit là d'un jeu de test, mais les tables pour ce genre d'opération pourraient contenir jusqu'à 10K lignes chacune...

Merci d'avance pour votre aide.