Debian Codegraph @

mysql-test/suite/gis/t/spatial_utility_function_distance_sphere.test

raw
1 ###############################################################################
2 # #
3 # ST_Distance_Sphere returns the minimum distance on the earth in meters #
4 # between two lon/lat geometries. Uses a spherical earth and a configurable #
5 # radius, which is 6370986 meters by default. #
6 # #
7 # Creation Date: 2014-12-02 #
8 # Author : Pavan Naik #
9 # #
10 ###############################################################################
11
12 ################################################################################
13 # Code coverage test #
14 ################################################################################
15
16 # Null
17 SELECT ST_DISTANCE_SPHERE(NULL, NULL);
18 SELECT ST_DISTANCE_SPHERE(ST_GEOMFROMTEXT('POINT(0 0)'), ST_GEOMFROMTEXT('POINT(0 0)'), NULL);
19
20 # Different SRS
21 --error ER_GIS_DIFFERENT_SRIDS
22 SELECT ST_DISTANCE_SPHERE(ST_GEOMFROMTEXT('POINT(0 0)', 4326), ST_GEOMFROMTEXT('POINT(0 0)'));
23
24 # Default radius
25 SELECT ST_DISTANCE_SPHERE(ST_GEOMFROMTEXT('POINT(0 0)'), ST_GEOMFROMTEXT('POINT(1 1)'));
26 SELECT ST_DISTANCE_SPHERE(ST_GEOMFROMTEXT('MULTIPOINT(0 0)'), ST_GEOMFROMTEXT('MULTIPOINT(1 1)'));
27
28 # Explicitly provided radius
29 SELECT ST_DISTANCE_SPHERE(ST_GEOMFROMTEXT('POINT(0 0)'), ST_GEOMFROMTEXT('POINT(1 1)'), 1);
30 SELECT ST_DISTANCE_SPHERE(ST_GEOMFROMTEXT('MULTIPOINT(0 0)'), ST_GEOMFROMTEXT('MULTIPOINT(1 1)'), 1);
31
32 # Spherical SRS
33 SELECT ST_DISTANCE_SPHERE(ST_GEOMFROMTEXT('POINT(0 0)', 4047), ST_GEOMFROMTEXT('POINT(1 1)', 4047));
34 SELECT ST_DISTANCE_SPHERE(ST_GEOMFROMTEXT('MULTIPOINT(0 0)', 4047), ST_GEOMFROMTEXT('MULTIPOINT(1 1)', 4047));
35
36 # Non-spherical SRS
37 SELECT ST_DISTANCE_SPHERE(ST_GEOMFROMTEXT('POINT(0 0)', 4326), ST_GEOMFROMTEXT('POINT(1 1)', 4326));
38 SELECT ST_DISTANCE_SPHERE(ST_GEOMFROMTEXT('MULTIPOINT(0 0)', 4326), ST_GEOMFROMTEXT('MULTIPOINT(1 1)', 4326));
39
40 # Projected SRS
41 --error ER_NOT_IMPLEMENTED_FOR_PROJECTED_SRS
42 SELECT ST_DISTANCE_SPHERE(ST_GEOMFROMTEXT('POINT(0 0)', 3857), ST_GEOMFROMTEXT('POINT(1 1)', 3857), 1);
43
44 # Nonpositive radius
45 --error ER_NONPOSITIVE_RADIUS
46 SELECT ST_DISTANCE_SPHERE(ST_GEOMFROMTEXT('POINT(0 0)'), ST_GEOMFROMTEXT('POINT(1 1)'), 0);
47 --error ER_NONPOSITIVE_RADIUS
48 SELECT ST_DISTANCE_SPHERE(ST_GEOMFROMTEXT('POINT(0 0)'), ST_GEOMFROMTEXT('POINT(1 1)'), -1);
49
50 # Numeric overflow
51 --error ER_STD_OVERFLOW_ERROR
52 SELECT ST_Distance_Sphere(POINT(-50, -50), POINT(50, 50), 1.0e308);
53
54 ############################################################################################
55 # Creating the spatial objects #
56 ############################################################################################
57
58 --echo # Creating the spatial Geometry object
59 USE test;
60
61 CREATE TABLE gis_geometrycollection (fid INTEGER NOT NULL PRIMARY KEY, g GEOMETRY);
62
63 ############################################################################################
64 # Inserting the values specific to the spatial objects #
65 ############################################################################################
66
67 # Geometric elements constructing a star and some of its elememts as base
68 SET @star_elem_vertical= 'POLYGON((5 0,15 25,25 0,15 5,5 0))';
69 SET @star_elem_horizontal= 'POLYGON((25 0,0 15,30 15,22 10,25 0))';
70 SET @star_center= 'POINT(15 10)';
71 SET @star_top= 'POINT(15 25)';
72 SET @star_bottom_left= 'POINT(5 0)';
73 SET @star_bottom_right= 'POINT(25 0)';
74 SET @star_bottom_points= 'MULTIPOINT(5 0,25 0)';
75 SET @star_all_points= 'MULTIPOINT(5 0,25 0,15 10,15 25)';
76 SET @star_line_horizontal= 'LINESTRING(10 15,20 15)';
77 SET @star_line_vertical= 'LINESTRING(15 5,15 25)';
78 SET @star_top_to_center= 'LINESTRING(15 25,15 10)';
79 SET @star_lines_near_horizontal= 'MULTILINESTRING((25 0,0 15,15 30,0 5))';
80 SET @star_lines_near_vertical= 'MULTILINESTRING((0 5,15 25,0 25))';
81 SET @star= 'POLYGON((5 0,7 10,0 15,10 15,15 25,20 15,30 15,22 10,25 0,15 5,5 0))';
82 SET @star_of_elems='MULTIPOLYGON(((5 0,15 25,25 0,15 5,5 0)),((25 0,0 15,30 15,22 10,25 0)))';
83 SET @star_collection_elems='GEOMETRYCOLLECTION(MULTIPOLYGON(((5 0,15 25,25 0,15 5,5 0)),((25 0,0 15,30 15,22 10,25 0))),POLYGON((5 0,7 10,0 15,10 15,15 25,20 15,30 15,22 10,25 0,15 5,5 0)),LINESTRING(15 25,15 10),MULTIPOINT(5 0,25 0),POINT(15 25))';
84 SET @star_collection_multilinestr='GEOMETRYCOLLECTION(MULTIPOLYGON(((5 0,15 25,25 0,15 5,5 0)),((25 0,0 15,30 15,22 10,25 0))),POLYGON((5 0,7 10,0 15,10 15,15 25,20 15,30 15,22 10,25 0,15 5,5 0)),MULTILINESTRING((25 0,0 15,15 30,0 5)),LINESTRING(15 25,15 10),MULTIPOINT(5 0,25 0),POINT(15 25))';
85
86 --echo #INSERT base star
87 INSERT INTO gis_geometrycollection VALUES
88 (100,ST_GEOMFROMTEXT(@star)),
89 (101,ST_GEOMFROMTEXT(@star_elem_vertical)),
90 (102,ST_GEOMFROMTEXT(@star_elem_horizontal)),
91 (103,ST_GEOMFROMTEXT(@star_of_elems)),
92 (104,ST_GEOMFROMTEXT(@star_top)),
93 (105,ST_GEOMFROMTEXT(@star_center)),
94 (106,ST_GEOMFROMTEXT(@star_bottom_left)),
95 (107,ST_GEOMFROMTEXT(@star_bottom_right)),
96 (108,ST_GEOMFROMTEXT(@star_bottom_points)),
97 (109,ST_GEOMFROMTEXT(@star_all_points)),
98 (110,ST_GEOMFROMTEXT(@star_line_horizontal)),
99 (111,ST_GEOMFROMTEXT(@star_line_vertical)),
100 (112,ST_GEOMFROMTEXT(@star_top_to_center)),
101 (113,ST_GEOMFROMTEXT(@star_lines_near_horizontal)),
102 (114,ST_GEOMFROMTEXT(@star_lines_near_vertical)),
103 (115,ST_GEOMFROMTEXT(@star_collection_elems)),
104 (116,ST_GEOMFROMTEXT(@star_collection_multilinestr));
105
106 --echo # Checking the integrity of the above create/insert statements
107
108 --echo # 17 rows.
109 SELECT count(ST_ASTEXT(g) != 'NULL') FROM gis_geometrycollection;
110
111 --echo #####################################################################################
112 --echo # ST_DISTANCE_SPHERE(point, point)
113 --echo #####################################################################################
114
115 SELECT ST_DISTANCE_SPHERE(ST_GEOMFROMTEXT('POINT(0 0)'), ST_GEOMFROMTEXT('POINT(0 0)'));
116
117 SELECT ST_DISTANCE_SPHERE(ST_GEOMFROMTEXT('POINT(0 0)'), ST_GEOMFROMTEXT('POINT(180 0)'));
118
119 --replace_numeric_round 5
120 SELECT ST_DISTANCE_SPHERE(ST_GEOMFROMTEXT('POINT(0 0)'), ST_GEOMFROMTEXT('POINT(0 90)'));
121
122 --replace_numeric_round 5
123 SELECT ST_DISTANCE_SPHERE(ST_GEOMFROMTEXT('POINT(0 0)'), ST_GEOMFROMTEXT('POINT(-179 0)'));
124
125 --replace_numeric_round 5
126 SELECT ST_DISTANCE_SPHERE(ST_GEOMFROMTEXT('POINT(0 0)'), ST_GEOMFROMTEXT('POINT(0 -90)'));
127
128 --replace_numeric_round 5
129 SELECT ST_DISTANCE_SPHERE(ST_GEOMFROMTEXT('POINT(0 0)'), ST_GEOMFROMTEXT('POINT(180 90)'));
130
131 --replace_numeric_round 5
132 SELECT ST_DISTANCE_SPHERE(ST_GEOMFROMTEXT('POINT(0 0)'), ST_GEOMFROMTEXT('POINT(180 -90)'));
133
134 --replace_numeric_round 5
135 SELECT ST_DISTANCE_SPHERE(ST_GEOMFROMTEXT('POINT(0 0)'), ST_GEOMFROMTEXT('POINT(-179 90)'));
136
137 --replace_numeric_round 5
138 SELECT ST_DISTANCE_SPHERE(ST_GEOMFROMTEXT('POINT(0 0)'), ST_GEOMFROMTEXT('POINT(-179 -90)'));
139
140 SELECT ST_DISTANCE_SPHERE(ST_GEOMFROMTEXT('POINT(0 0)'), ST_GEOMFROMTEXT('POINT(1 1)'));
141
142 SELECT ST_DISTANCE_SPHERE(ST_GEOMFROMTEXT('POINT(0 0)'), ST_GEOMFROMTEXT('POINT(0.0000 -0.000)'));
143
144 SELECT ST_DISTANCE_SPHERE(ST_GEOMFROMTEXT('POINT(0 0)'), ST_GEOMFROMTEXT('POINT(1 1)'));
145
146 SELECT ST_DISTANCE_SPHERE(ST_GEOMFROMTEXT('POINT(0 0)'), ST_GEOMFROMTEXT('POINT(1 -1)'));
147
148 SELECT ST_DISTANCE_SPHERE(ST_GEOMFROMTEXT('POINT(0 0)'), ST_GEOMFROMTEXT('POINT(-1 1)'));
149
150 SELECT ST_DISTANCE_SPHERE(ST_GEOMFROMTEXT('POINT(0 0)'), ST_GEOMFROMTEXT('POINT(-1 -1)'));
151
152 --replace_numeric_round 8
153 SELECT ST_DISTANCE_SPHERE(ST_GEOMFROMTEXT('POINT(0 0)'), ST_GEOMFROMTEXT('POINT(90 45)'));
154
155 --replace_numeric_round 8
156 SELECT ST_DISTANCE_SPHERE(ST_GEOMFROMTEXT('POINT(0 0)'), ST_GEOMFROMTEXT('POINT(90 -45)'));
157
158 --replace_numeric_round 8
159 SELECT ST_DISTANCE_SPHERE(ST_GEOMFROMTEXT('POINT(0 0)'), ST_GEOMFROMTEXT('POINT(-90 45)'));
160
161 --replace_numeric_round 8
162 SELECT ST_DISTANCE_SPHERE(ST_GEOMFROMTEXT('POINT(0 0)'), ST_GEOMFROMTEXT('POINT(-90 -45)'));
163
164 --replace_numeric_round 8
165 SELECT ST_DISTANCE_SPHERE(ST_GEOMFROMTEXT('POINT(180 90)'), ST_GEOMFROMTEXT('POINT(180 90)'));
166
167 --replace_numeric_round 8
168 SELECT ST_DISTANCE_SPHERE(ST_GEOMFROMTEXT('POINT(180 -90)'), ST_GEOMFROMTEXT('POINT(180 -90)'));
169
170 SELECT ST_DISTANCE_SPHERE(ST_GEOMFROMTEXT('POINT(-179.999 -90)'), ST_GEOMFROMTEXT('POINT(-179.999 -90)'));
171
172 SELECT ST_DISTANCE_SPHERE(ST_GEOMFROMTEXT('POINT(-179.999 -89.999)'), ST_GEOMFROMTEXT('POINT(-179.999 -89.999)'));
173
174 --replace_numeric_round 15
175 SELECT ST_DISTANCE_SPHERE(ST_GEOMFROMTEXT('POINT(-179.9 90)'), ST_GEOMFROMTEXT('POINT(-179.99 90)'));
176
177 --replace_numeric_round 15
178 SELECT ST_DISTANCE_SPHERE(ST_GEOMFROMTEXT('POINT(-179.9 -90)'), ST_GEOMFROMTEXT('POINT(-179.9999 -90)'));
179
180 --echo #####################################################################################
181 --echo # ST_DISTANCE_SPHERE(point, multipoint)
182 --echo #####################################################################################
183
184 SELECT ST_DISTANCE_SPHERE(ST_GEOMFROMTEXT('POINT(0 0)'), ST_GEOMFROMTEXT('MULTIPOINT(0 0)'));
185
186 SELECT ST_DISTANCE_SPHERE(ST_GEOMFROMTEXT('POINT(0 0)'), ST_GEOMFROMTEXT('MULTIPOINT(180 0)'));
187
188 --replace_numeric_round 5
189 SELECT ST_DISTANCE_SPHERE(ST_GEOMFROMTEXT('POINT(0 0)'), ST_GEOMFROMTEXT('MULTIPOINT(0 90)'));
190
191 --replace_numeric_round 5
192 SELECT ST_DISTANCE_SPHERE(ST_GEOMFROMTEXT('POINT(0 0)'), ST_GEOMFROMTEXT('MULTIPOINT(-179 0)'));
193
194 SELECT ST_DISTANCE_SPHERE(ST_GEOMFROMTEXT('POINT(0 0)'), ST_GEOMFROMTEXT('MULTIPOINT(0 0,1 1,2 2)'));
195
196 SELECT ST_DISTANCE_SPHERE(ST_GEOMFROMTEXT('POINT(0 0)'), ST_GEOMFROMTEXT('MULTIPOINT(0 0,180 90)'));
197
198 SELECT ST_DISTANCE_SPHERE(ST_GEOMFROMTEXT('POINT(0 0)'), ST_GEOMFROMTEXT('MULTIPOINT(180 90,0 0,-179 -90)'));
199
200 SELECT ST_DISTANCE_SPHERE(ST_GEOMFROMTEXT('POINT(0 0)'), ST_GEOMFROMTEXT('MULTIPOINT(0 1)'));
201
202 SELECT ST_DISTANCE_SPHERE(ST_GEOMFROMTEXT('POINT(0 0)'), ST_GEOMFROMTEXT('MULTIPOINT(0 1,1 0,1 1)'));
203
204 --replace_numeric_round 14
205 SELECT ST_DISTANCE_SPHERE(ST_GEOMFROMTEXT('POINT(180 90)'), ST_GEOMFROMTEXT('MULTIPOINT(180 -90,-179 90,-179 -90)'));
206
207 SELECT ST_DISTANCE_SPHERE(ST_GEOMFROMTEXT('POINT(37 -42)'), ST_GEOMFROMTEXT('MULTIPOINT(57 32)'));
208
209 --replace_numeric_round 7
210 SELECT ST_DISTANCE_SPHERE(ST_GEOMFROMTEXT('POINT(-85 48)'), ST_GEOMFROMTEXT('MULTIPOINT(159 -49)'));
211
212 --replace_numeric_round 7
213 SELECT ST_DISTANCE_SPHERE(ST_GEOMFROMTEXT('POINT(168 -21)'), ST_GEOMFROMTEXT('MULTIPOINT(-22 -32)'));
214
215 --replace_numeric_round 5
216 SELECT ST_DISTANCE_SPHERE(ST_GEOMFROMTEXT('POINT(-64 67)'), ST_GEOMFROMTEXT('MULTIPOINT(-103 45,155 57,-166 85)'));
217
218 SELECT ST_DISTANCE_SPHERE(ST_GEOMFROMTEXT('POINT(-36 -41)'), ST_GEOMFROMTEXT('MULTIPOINT(-37 45,-49 23,97 -19)'));
219
220 --replace_numeric_round 5
221 SELECT ST_DISTANCE_SPHERE(ST_GEOMFROMTEXT('POINT(138 86)'), ST_GEOMFROMTEXT('MULTIPOINT(15 -79,-110 -76,49 31)'));
222
223 --replace_numeric_round 5
224 SELECT ST_DISTANCE_SPHERE(ST_GEOMFROMTEXT('POINT(164 -41)'), ST_GEOMFROMTEXT('MULTIPOINT(-11 2,-81 3,161 62,-119 31)'));
225
226 --replace_numeric_round 5
227 SELECT ST_DISTANCE_SPHERE(ST_GEOMFROMTEXT('POINT(-106 -23)'), ST_GEOMFROMTEXT('MULTIPOINT(-152 72,89 -3,-23 -42,126 59)'));
228
229 --replace_numeric_round 5
230 SELECT ST_DISTANCE_SPHERE(ST_GEOMFROMTEXT('POINT(168 -15)'), ST_GEOMFROMTEXT('MULTIPOINT(68 73,157 -55,-178 -29,46 78,-133 59,180 59)'));
231
232 --replace_numeric_round 5
233 SELECT ST_DISTANCE_SPHERE(ST_GEOMFROMTEXT('POINT(106 76)'), ST_GEOMFROMTEXT('MULTIPOINT(53 20,148 -40,-61 -90,170 -37,-64 -82,-167 85)'));
234
235 --replace_numeric_round 5
236 SELECT ST_DISTANCE_SPHERE(ST_GEOMFROMTEXT('POINT(-50 36)'), ST_GEOMFROMTEXT('MULTIPOINT(-32 -64,-116 -35,148 89,-75 16,-99 87,-51 -2,-113 24,93 46,168 53)'));
237
238 --replace_numeric_round 5
239 SELECT ST_DISTANCE_SPHERE(ST_GEOMFROMTEXT('POINT(-139 54)'), ST_GEOMFROMTEXT('MULTIPOINT(151 -57,-74 61,10 28,170 -47,97 -45,-5 -82,121 -21,-96 -44,81 -68)'));
240
241 --echo #####################################################################################
242 --echo # ST_DISTANCE_SPHERE(multipoint, point)
243 --echo #####################################################################################
244
245 SELECT ST_DISTANCE_SPHERE(ST_GEOMFROMTEXT('MULTIPOINT(0 0)'), ST_GEOMFROMTEXT('POINT(0 0)'));
246
247 SELECT ST_DISTANCE_SPHERE(ST_GEOMFROMTEXT('MULTIPOINT(0 0)'), ST_GEOMFROMTEXT('POINT(180 0)'));
248
249 --replace_numeric_round 5
250 SELECT ST_DISTANCE_SPHERE(ST_GEOMFROMTEXT('MULTIPOINT(0 0)'), ST_GEOMFROMTEXT('POINT(0 90)'));
251
252 --replace_numeric_round 5
253 SELECT ST_DISTANCE_SPHERE(ST_GEOMFROMTEXT('MULTIPOINT(0 0)'), ST_GEOMFROMTEXT('POINT(-179 0)'));
254
255 SELECT ST_DISTANCE_SPHERE(ST_GEOMFROMTEXT('MULTIPOINT(0 0,1 1,2 2)'), ST_GEOMFROMTEXT('POINT(0 0)'));
256
257 SELECT ST_DISTANCE_SPHERE(ST_GEOMFROMTEXT('MULTIPOINT(180 90,0 0,180 -90)'), ST_GEOMFROMTEXT('POINT(-0 0)'));
258
259 --replace_numeric_round 5
260 SELECT ST_DISTANCE_SPHERE(ST_GEOMFROMTEXT('MULTIPOINT(-179 90,0 0,-179 -90)'), ST_GEOMFROMTEXT('POINT(-179 0)'));
261
262 SELECT ST_DISTANCE_SPHERE(ST_GEOMFROMTEXT('MULTIPOINT(-179 90,0 0,-179 -90)'), ST_GEOMFROMTEXT('POINT(-179 -90)'));
263
264 SELECT ST_DISTANCE_SPHERE(ST_GEOMFROMTEXT('MULTIPOINT(-172 -70)'), ST_GEOMFROMTEXT('POINT(45 90)'));
265
266 --replace_numeric_round 5
267 SELECT ST_DISTANCE_SPHERE(ST_GEOMFROMTEXT('MULTIPOINT(-157 -67)'), ST_GEOMFROMTEXT('POINT(-92 -88)'));
268
269 SELECT ST_DISTANCE_SPHERE(ST_GEOMFROMTEXT('MULTIPOINT(156 -64,-96 57,-15 -18)'), ST_GEOMFROMTEXT('POINT(-73 -1)'));
270
271 --replace_numeric_round 5
272 SELECT ST_DISTANCE_SPHERE(ST_GEOMFROMTEXT('MULTIPOINT(-110 43,-79 -82,-32 24)'), ST_GEOMFROMTEXT('POINT(61 27)'));
273
274 --replace_numeric_round 5
275 SELECT ST_DISTANCE_SPHERE(ST_GEOMFROMTEXT('MULTIPOINT(-85 87,-155 48,-163 -84)'), ST_GEOMFROMTEXT('POINT(-95 84)'));
276
277 --replace_numeric_round 5
278 SELECT ST_DISTANCE_SPHERE(ST_GEOMFROMTEXT('MULTIPOINT(33 -49,174 -25,-4 -56)'), ST_GEOMFROMTEXT('POINT(48 46)'));
279
280 --replace_numeric_round 5
281 SELECT ST_DISTANCE_SPHERE(ST_GEOMFROMTEXT('MULTIPOINT(-36 -87,-75 20,-157 78)'), ST_GEOMFROMTEXT('POINT(-165 18)'));
282
283 --replace_numeric_round 5
284 SELECT ST_DISTANCE_SPHERE(ST_GEOMFROMTEXT('MULTIPOINT(151 17,-150 -43,-165 76,87 53)'), ST_GEOMFROMTEXT('POINT(152 27)'));
285
286 SELECT ST_DISTANCE_SPHERE(ST_GEOMFROMTEXT('MULTIPOINT(-159 -88,-177 -82,17 76,79 1)'), ST_GEOMFROMTEXT('POINT(35 -74)'));
287
288 --replace_numeric_round 5
289 SELECT ST_DISTANCE_SPHERE(ST_GEOMFROMTEXT('MULTIPOINT(-130 -20,-118 -63,-57 0)'), ST_GEOMFROMTEXT('POINT(-156 89)'));
290
291 --replace_numeric_round 5
292 SELECT ST_DISTANCE_SPHERE(ST_GEOMFROMTEXT('MULTIPOINT(-84 25,-52 53,125 -50,-171 51,55 -13,-4 -69)'), ST_GEOMFROMTEXT('POINT(-127 52)'));
293
294 --replace_numeric_round 5
295 SELECT ST_DISTANCE_SPHERE(ST_GEOMFROMTEXT('MULTIPOINT(56 -45,158 84,112 2,85 21,88 89,-151 33,-158 40)'), ST_GEOMFROMTEXT('POINT(-172 87)'));
296
297 --replace_numeric_round 5
298 SELECT ST_DISTANCE_SPHERE(ST_GEOMFROMTEXT('MULTIPOINT(-6 -56,-73 54,20 -74,-160 72,-4 42,-120 -62,-54 -8,-85 48,159 -49,100 -75)'),
299 ST_GEOMFROMTEXT('POINT(167 -17)'));
300
301 --echo #####################################################################################
302 --echo # ST_DISTANCE_SPHERE(multipoint, multipoint)
303 --echo #####################################################################################
304
305 SELECT ST_DISTANCE_SPHERE(ST_GEOMFROMTEXT('MULTIPOINT(0 0)'), ST_GEOMFROMTEXT('MULTIPOINT(0 0)'));
306
307 SELECT ST_DISTANCE_SPHERE(ST_GEOMFROMTEXT('MULTIPOINT(0 0)'), ST_GEOMFROMTEXT('MULTIPOINT(180 0)'));
308
309 --replace_numeric_round 5
310 SELECT ST_DISTANCE_SPHERE(ST_GEOMFROMTEXT('MULTIPOINT(0 0)'), ST_GEOMFROMTEXT('MULTIPOINT(0 90)'));
311
312 --replace_numeric_round 5
313 SELECT ST_DISTANCE_SPHERE(ST_GEOMFROMTEXT('MULTIPOINT(0 0)'), ST_GEOMFROMTEXT('MULTIPOINT(-179 0)'));
314
315 SELECT ST_DISTANCE_SPHERE(ST_GEOMFROMTEXT('MULTIPOINT(0 0,1 1,2 2)'), ST_GEOMFROMTEXT('MULTIPOINT(1 1,0 0,2 2)'));
316
317 SELECT ST_DISTANCE_SPHERE(ST_GEOMFROMTEXT('MULTIPOINT(180 90,0 0)'), ST_GEOMFROMTEXT('MULTIPOINT(180 90)'));
318
319 SELECT ST_DISTANCE_SPHERE(ST_GEOMFROMTEXT('MULTIPOINT(180 0,0 0)'), ST_GEOMFROMTEXT('MULTIPOINT(180 0,0 90)'));
320
321 --replace_numeric_round 5
322 SELECT ST_DISTANCE_SPHERE(ST_GEOMFROMTEXT('MULTIPOINT(-179 90,0 0)'), ST_GEOMFROMTEXT('MULTIPOINT(-179 0,0 -90)'));
323
324 SELECT ST_DISTANCE_SPHERE(ST_GEOMFROMTEXT('MULTIPOINT(111 11,11 1)'), ST_GEOMFROMTEXT('MULTIPOINT(1 11,11 11)'));
325
326 SELECT ST_DISTANCE_SPHERE(ST_GEOMFROMTEXT('MULTIPOINT(10 10,20 20,30 30)'), ST_GEOMFROMTEXT('MULTIPOINT(15 15,20 20,25 25)'));
327
328 SELECT ST_DISTANCE_SPHERE(ST_GEOMFROMTEXT('MULTIPOINT(-10 -10,-20 -20,-30 -30)'), ST_GEOMFROMTEXT('MULTIPOINT(180 0,-30 -30,180 -90)'));
329
330 SELECT ST_DISTANCE_SPHERE(ST_GEOMFROMTEXT('MULTIPOINT(-150 -79)'), ST_GEOMFROMTEXT('MULTIPOINT(145 -2)'));
331
332 --replace_numeric_round 5
333 SELECT ST_DISTANCE_SPHERE(ST_GEOMFROMTEXT('MULTIPOINT(164 -37)'), ST_GEOMFROMTEXT('MULTIPOINT(67 59)'));
334
335 SELECT ST_DISTANCE_SPHERE(ST_GEOMFROMTEXT('MULTIPOINT(135 62)'), ST_GEOMFROMTEXT('MULTIPOINT(-116 -22)'));
336
337 --replace_numeric_round 5
338 SELECT ST_DISTANCE_SPHERE(ST_GEOMFROMTEXT('MULTIPOINT(-123 85)'), ST_GEOMFROMTEXT('MULTIPOINT(-54 -40)'));
339
340 --replace_numeric_round 5
341 SELECT ST_DISTANCE_SPHERE(ST_GEOMFROMTEXT('MULTIPOINT(152 43)'), ST_GEOMFROMTEXT('MULTIPOINT(106 76,53 20,148 -40)'));
342
343 --replace_numeric_round 5
344 SELECT ST_DISTANCE_SPHERE(ST_GEOMFROMTEXT('MULTIPOINT(-177 -88,-165 9,153 40,2 17)'), ST_GEOMFROMTEXT('MULTIPOINT(-150 -4)'));
345
346 --replace_numeric_round 5
347 SELECT ST_DISTANCE_SPHERE(ST_GEOMFROMTEXT('MULTIPOINT(154 84,-124 37,17 50)'), ST_GEOMFROMTEXT('MULTIPOINT(42 34)'));
348
349 --replace_numeric_round 5
350 SELECT ST_DISTANCE_SPHERE(ST_GEOMFROMTEXT('MULTIPOINT(88 -65,49 85,68 77)'), ST_GEOMFROMTEXT('MULTIPOINT(125 82,-83 -5,3 -40)'));
351
352 --replace_numeric_round 5
353 SELECT ST_DISTANCE_SPHERE(ST_GEOMFROMTEXT('MULTIPOINT(174 59,91 24,26 28)'), ST_GEOMFROMTEXT('MULTIPOINT(-35 -33,-171 -37,-30 -43)'));
354
355 --replace_numeric_round 5
356 SELECT ST_DISTANCE_SPHERE(ST_GEOMFROMTEXT('MULTIPOINT(-140 -63,103 -42,160 -53)'), ST_GEOMFROMTEXT('MULTIPOINT(-78 -80,-133 -24,73 -15)'));
357
358 SELECT ST_DISTANCE_SPHERE(ST_GEOMFROMTEXT('MULTIPOINT(137 -65,-63 13,38 9)'), ST_GEOMFROMTEXT('MULTIPOINT(-166 -35,107 -18,-83 -18,91 -24)'));
359
360 --replace_numeric_round 5
361 SELECT ST_DISTANCE_SPHERE(ST_GEOMFROMTEXT('MULTIPOINT(-128 90,90 -17,-176 -37,40 -78,156 -82,37 -42)'), ST_GEOMFROMTEXT('MULTIPOINT(57 32,-3 22,24 -88)'));
362
363 SELECT ST_DISTANCE_SPHERE(ST_GEOMFROMTEXT('MULTIPOINT(87 -12,-25 31,-47 23,134 83,120 69,39 -41)'), ST_GEOMFROMTEXT('MULTIPOINT(-9 -4,-171 -82,140 -26)'));
364
365 --replace_numeric_round 5
366 SELECT ST_DISTANCE_SPHERE(ST_GEOMFROMTEXT('MULTIPOINT(-119 50,28 22,-122 -74)'), ST_GEOMFROMTEXT('MULTIPOINT(134 14,-32 -84,96 74,44 59,-88 73,-136 12)'));
367
368 SELECT ST_DISTANCE_SPHERE(ST_GEOMFROMTEXT('MULTIPOINT(-144 73,81 61,-78 4,50 -28)'),
369 ST_GEOMFROMTEXT('MULTIPOINT(-176 48,-154 -39,-140 -60,135 72,-97 28,-35 69)'));
370
371 --replace_numeric_round 5
372 SELECT ST_DISTANCE_SPHERE(ST_GEOMFROMTEXT('MULTIPOINT(-14 49)'),
373 ST_GEOMFROMTEXT('MULTIPOINT(-160 -51,-141 72,50 -30,96 -64,-41 -59,-128 -28,0 -77,179 -78,-84 -67)'));
374
375 SELECT ST_DISTANCE_SPHERE(ST_GEOMFROMTEXT('MULTIPOINT(-165 61,-43 -47,-89 40,-137 -64,11 26,-8 -31,28 -77,37 -44,124 51)'),
376 ST_GEOMFROMTEXT('MULTIPOINT(63 39,98 14,-123 79,-30 -27)'));
377
378 SELECT ST_DISTANCE_SPHERE(ST_GEOMFROMTEXT('MULTIPOINT(11 -38,-17 54,-104 73)'),
379 ST_GEOMFROMTEXT('MULTIPOINT(167 -50,103 -21,-122 24,-122 80,-41 37,-175 -25,-155 48,-23 -34,-105 48)'));
380
381 --echo #####################################################################################
382 --echo # ST_DISTANCE_SPHERE with different earth radius value
383 --echo #####################################################################################
384
385 SELECT ST_DISTANCE_SPHERE(ST_GEOMFROMTEXT('POINT(1 1)'), ST_GEOMFROMTEXT('POINT(0 0)'), 1);
386
387 SELECT ST_DISTANCE_SPHERE(ST_GEOMFROMTEXT('POINT(1 1)'), ST_GEOMFROMTEXT('POINT(0 0)'), 10);
388
389 SELECT ST_DISTANCE_SPHERE(ST_GEOMFROMTEXT('POINT(1 1)'), ST_GEOMFROMTEXT('POINT(0 0)'), 100);
390
391 SELECT ST_DISTANCE_SPHERE(ST_GEOMFROMTEXT('POINT(1 1)'), ST_GEOMFROMTEXT('POINT(0 0)'), 2000);
392
393 SELECT ST_DISTANCE_SPHERE(ST_GEOMFROMTEXT('POINT(1 1)'), ST_GEOMFROMTEXT('POINT(0 0)'), 1234567);
394
395 SELECT ST_DISTANCE_SPHERE(ST_GEOMFROMTEXT('POINT(1 1)'), ST_GEOMFROMTEXT('POINT(0 0)'), 6370986);
396
397 SELECT ST_DISTANCE_SPHERE(ST_GEOMFROMTEXT('POINT(1 1)'), ST_GEOMFROMTEXT('POINT(0 0)'), 6370987);
398
399 SELECT ST_DISTANCE_SPHERE(ST_GEOMFROMTEXT('POINT(1 1)'), ST_GEOMFROMTEXT('POINT(0 0)'), 1234567890);
400
401 --echo #####################################################################################
402 --echo # Invalid function calls
403 --echo #####################################################################################
404
405 --error ER_WRONG_PARAMCOUNT_TO_NATIVE_FCT
406 SELECT ST_DISTANCE_SPHERE();
407
408 --error ER_WRONG_PARAMCOUNT_TO_NATIVE_FCT
409 SELECT ST_DISTANCE_SPHERE(ST_GEOMFROMTEXT('POINT(10 10)'));
410
411 SELECT ST_DISTANCE_SPHERE(ST_GEOMFROMTEXT('POINT(10 10)'),NULL);
412
413 SELECT ST_DISTANCE_SPHERE(NULL,ST_GEOMFROMTEXT('POINT(10 10)'));
414
415 SELECT ST_DISTANCE_SPHERE(NULL,ST_GEOMFROMTEXT(@star_top));
416
417 SELECT ST_DISTANCE_SPHERE(ST_GEOMFROMTEXT(@star_of_elems),NULL);
418
419 SELECT ST_DISTANCE_SPHERE(NULL,NULL);
420
421 --error ER_GIS_INVALID_DATA
422 SELECT ST_DISTANCE_SPHERE(ST_GEOMFROMTEXT('POINT()'),ST_GEOMFROMTEXT('POINT(0 0)'));
423
424 --error ER_GIS_INVALID_DATA
425 SELECT ST_DISTANCE_SPHERE(ST_GEOMFROMTEXT('POINT(a 0)'),ST_GEOMFROMTEXT('POINT(0 0)'));
426
427 --error ER_GIS_INVALID_DATA
428 SELECT ST_DISTANCE_SPHERE(ST_GEOMFROMTEXT('POINT(! 0)'),ST_GEOMFROMTEXT('POINT(0 0)'));
429
430 --error ER_PARSE_ERROR
431 SELECT ST_DISTANCE_SPHERE(ST_GEOMFROMTEXT('POINT('!' 0)'),ST_GEOMFROMTEXT('POINT(0 0)'));
432
433 --error ER_GIS_INVALID_DATA
434 SELECT ST_DISTANCE_SPHERE(ST_GEOMFROMTEXT('POINT(12,34 0)'),ST_GEOMFROMTEXT('POINT(0 0)'));
435
436 --error ER_GIS_INVALID_DATA
437 SELECT ST_DISTANCE_SPHERE(ST_GEOMFROMTEXT('MULTIPOINT()'),ST_GEOMFROMTEXT('POINT(0 0)'));
438
439 --error ER_GIS_INVALID_DATA
440 SELECT ST_DISTANCE_SPHERE(ST_GEOMFROMTEXT('MULTIPOINT(1 12,34 a)'),ST_GEOMFROMTEXT('POINT(0 0)'));
441
442 --error ER_GIS_INVALID_DATA
443 SELECT ST_DISTANCE_SPHERE(ST_GEOMFROMTEXT('MULTIPOINT(1 12,34 0,1)'),ST_GEOMFROMTEXT('POINT(0 0)'));
444
445 --error ER_PARSE_ERROR
446 SELECT ST_DISTANCE_SPHERE(ST_GEOMFROMTEXT('MULTIPOINT(1 1,2 2,'!' 0)'),ST_GEOMFROMTEXT('POINT(0 0)'));
447
448 --error ER_PARSE_ERROR
449 SELECT ST_DISTANCE_SPHERE(,);
450
451 --error ER_PARSE_ERROR
452 SELECT ST_DISTANCE_SPHERE(ST_GEOMFROMTEXT(@star_of_elems),);
453
454 --error ER_PARSE_ERROR
455 SELECT ST_DISTANCE_SPHERE(,ST_GEOMFROMTEXT(@star_top));
456
457 --error ER_NOT_IMPLEMENTED_FOR_CARTESIAN_SRS
458 SELECT ST_DISTANCE_SPHERE(ST_GEOMFROMTEXT('POINT(0 1)'), ST_GEOMFROMTEXT('LINESTRING(0 0,10 10)'));
459
460 --error ER_NOT_IMPLEMENTED_FOR_CARTESIAN_SRS
461 SELECT ST_DISTANCE_SPHERE(ST_GEOMFROMTEXT('POINT(0 1)'), ST_GEOMFROMTEXT('POLYGON((0 0,0 5,5 5,5 0,0 0))'));
462
463 --error ER_NOT_IMPLEMENTED_FOR_CARTESIAN_SRS
464 SELECT ST_DISTANCE_SPHERE(ST_GEOMFROMTEXT('POINT(0 1)'), ST_GEOMFROMTEXT('GEOMETRYCOLLECTION(POINT(0 0))'));
465
466 --error ER_NOT_IMPLEMENTED_FOR_CARTESIAN_SRS
467 SELECT ST_DISTANCE_SPHERE(ST_GEOMFROMTEXT('GEOMETRYCOLLECTION(POINT(1 1))'), ST_GEOMFROMTEXT('POINT(0 0)'));
468
469 --error ER_NOT_IMPLEMENTED_FOR_CARTESIAN_SRS
470 SELECT ST_DISTANCE_SPHERE(ST_GEOMFROMTEXT('POINT(0 1)'), ST_GEOMFROMTEXT('GEOMETRYCOLLECTION()'));
471
472 --error ER_NOT_IMPLEMENTED_FOR_CARTESIAN_SRS
473 SELECT ST_DISTANCE_SPHERE(ST_GEOMFROMTEXT('POINT(0 1)'), ST_GEOMFROMTEXT('GEOMETRYCOLLECTION(GEOMETRYCOLLECTION(POINT(0 0)))'));
474
475 --error ER_NOT_IMPLEMENTED_FOR_CARTESIAN_SRS
476 SELECT ST_DISTANCE_SPHERE(ST_GEOMFROMTEXT('POINT(0 1)'), ST_GEOMFROMTEXT('GEOMETRYCOLLECTION(POINT(0 0),GEOMETRYCOLLECTION())'));
477
478 --error ER_NOT_IMPLEMENTED_FOR_CARTESIAN_SRS
479 SELECT ST_DISTANCE_SPHERE(ST_GEOMFROMTEXT('MULTIPOINT(0 1)'), ST_GEOMFROMTEXT('LINESTRING(0 0,10 10)'));
480
481 --error ER_NOT_IMPLEMENTED_FOR_CARTESIAN_SRS
482 SELECT ST_DISTANCE_SPHERE(ST_GEOMFROMTEXT('MULTIPOINT(0 1)'), ST_GEOMFROMTEXT('POLYGON((0 0,0 5,5 5,5 0,0 0))'));
483
484 --error ER_NOT_IMPLEMENTED_FOR_CARTESIAN_SRS
485 SELECT ST_DISTANCE_SPHERE(ST_GEOMFROMTEXT('MULTIPOINT(0 1)'), ST_GEOMFROMTEXT('GEOMETRYCOLLECTION(POINT(0 0))'));
486
487 --error ER_NOT_IMPLEMENTED_FOR_CARTESIAN_SRS
488 SELECT ST_DISTANCE_SPHERE(ST_GEOMFROMTEXT('GEOMETRYCOLLECTION(MULTIPOINT(1 1))'), ST_GEOMFROMTEXT('POINT(0 0)'));
489
490 --error ER_NOT_IMPLEMENTED_FOR_CARTESIAN_SRS
491 SELECT ST_DISTANCE_SPHERE(ST_GEOMFROMTEXT('MULTIPOINT(0 1)'), ST_GEOMFROMTEXT('GEOMETRYCOLLECTION()'));
492
493 --error ER_NOT_IMPLEMENTED_FOR_CARTESIAN_SRS
494 SELECT ST_DISTANCE_SPHERE(ST_GEOMFROMTEXT('MULTIPOINT(0 1)'), ST_GEOMFROMTEXT('GEOMETRYCOLLECTION(GEOMETRYCOLLECTION(POINT(0 0)))'));
495
496 --error ER_NOT_IMPLEMENTED_FOR_CARTESIAN_SRS
497 SELECT ST_DISTANCE_SPHERE(ST_GEOMFROMTEXT('MULTIPOINT(0 1)'), ST_GEOMFROMTEXT('GEOMETRYCOLLECTION(POINT(0 0),GEOMETRYCOLLECTION())'));
498
499 --error ER_NOT_IMPLEMENTED_FOR_CARTESIAN_SRS
500 SELECT ST_DISTANCE_SPHERE(ST_GEOMFROMTEXT('GEOMETRYCOLLECTION()'), ST_GEOMFROMTEXT('GEOMETRYCOLLECTION()'));
501
502 --error ER_NONPOSITIVE_RADIUS
503 SELECT ST_DISTANCE_SPHERE(ST_GEOMFROMTEXT('POINT(1 1)'), ST_GEOMFROMTEXT('POINT(0 0)'), -200);
504
505 --error ER_NONPOSITIVE_RADIUS
506 SELECT ST_DISTANCE_SPHERE(ST_GEOMFROMTEXT('POINT(1 1)'), ST_GEOMFROMTEXT('POINT(0 0)'), -1);
507
508 --error ER_NONPOSITIVE_RADIUS
509 SELECT ST_DISTANCE_SPHERE(ST_GEOMFROMTEXT('POINT(1 1)'), ST_GEOMFROMTEXT('POINT(0 0)'), 0);
510
511 --error ER_LONGITUDE_OUT_OF_RANGE
512 SELECT ST_DISTANCE_SPHERE(ST_GEOMFROMTEXT('POINT(200 1)'), ST_GEOMFROMTEXT('POINT(0 0)'), 200);
513
514 --error ER_LATITUDE_OUT_OF_RANGE
515 SELECT ST_DISTANCE_SPHERE(ST_GEOMFROMTEXT('POINT(20 1)'), ST_GEOMFROMTEXT('POINT(0 100)'), 200);
516
517 --error ER_WRONG_PARAMCOUNT_TO_NATIVE_FCT
518 SELECT ST_DISTANCE_SPHERE(ST_GEOMFROMTEXT('POINT(1 1)'), ST_GEOMFROMTEXT('POINT(0 0)'), 200, 300);
519
520 --echo # Clean up
521 DROP TABLE gis_geometrycollection;
522
523 --echo #
524 --echo # WL#8579 Spatial Reference Systems
525 --echo #
526
527 --echo # SRID 0 (should pass)
528 --replace_regex /([0-9]+\.[0-9]{5})[0-9]*/\1/
529 SELECT ST_DISTANCE_SPHERE(ST_GEOMFROMTEXT('POINT(0 0)', 0), ST_GEOMFROMTEXT('POINT(1 1)', 0));
530
531 --echo # Projected SRS (should error)
532 --replace_regex /([0-9]+\.[0-9]{5})[0-9]*/\1/
533 --error ER_NOT_IMPLEMENTED_FOR_PROJECTED_SRS
534 SELECT ST_DISTANCE_SPHERE(ST_GEOMFROMTEXT('POINT(0 0)', 2000), ST_GEOMFROMTEXT('POINT(1 1)', 2000));
535
536 --echo # Geographic SRS (should pass)
537 SELECT ST_DISTANCE_SPHERE(ST_GEOMFROMTEXT('POINT(0 0)', 4326), ST_GEOMFROMTEXT('POINT(1 1)', 4326));
538
539 --echo #
540 --echo # WL#11096 Don't do Cartesian computations on geographic geometries
541 --echo #
542
543 --echo # Assume SRID 10 is not defined.
544 --error ER_SRS_NOT_FOUND
545 DO ST_DISTANCE_SPHERE(
546 x'0A000000010100000000000000000000000000000000000000',
547 x'0A000000010100000000000000000000000000000000000000'
548 );