Debian Codegraph @

mysql-test/suite/innodb_gis/r/point_big.result

raw
1 #
2 # Test when the POINT is on B-TREE
3 #
4 CREATE TABLE t1(fid INTEGER NOT NULL PRIMARY KEY AUTO_INCREMENT, p POINT, KEY(p)) ENGINE=InnoDB;
5 INSERT INTO t1 VALUES
6 (101, ST_PointFromText('POINT(10 10)')),
7 (102, ST_PointFromText('POINT(20 10)')),
8 (103, ST_PointFromText('POINT(20 20)')),
9 (104, ST_PointFromWKB(ST_AsWKB(ST_PointFromText('POINT(10 20)'))));
10 SELECT ST_AsText(p) FROM t1;
11 ST_AsText(p)
12 POINT(10 10)
13 POINT(20 10)
14 POINT(20 20)
15 POINT(10 20)
16 SELECT ST_AsText(p) FROM t1 WHERE p = ST_PointFromText('POINT(20 20)');
17 ST_AsText(p)
18 POINT(20 20)
19 INSERT INTO t1 VALUES
20 (201, ST_PointFromText('POINT(100.32374832 101.23741821)')),
21 (202, ST_PointFromText('POINT(102.43287328 100.23489233)')),
22 (203, ST_PointFromText('POINT(101.43284962 100.45892392)')),
23 (204, ST_PointFromWKB(ST_AsWKB(ST_PointFromText('POINT(103.43718640 105.248206478)')))),
24 (205, ST_PointFromText('POINT(101.43284962 100.45892392)')),
25 (206, ST_PointFromWKB(ST_AsWKB(ST_PointFromText('POINT(103.43718640 105.248206478)'))));
26 SELECT ST_AsText(p), COUNT(*) FROM t1 GROUP BY p;
27 ST_AsText(p) COUNT(*)
28 POINT(10 10) 1
29 POINT(10 20) 1
30 POINT(20 10) 1
31 POINT(20 20) 1
32 POINT(101.43284962 100.45892392) 2
33 POINT(100.32374832 101.23741821) 1
34 POINT(102.43287328 100.23489233) 1
35 POINT(103.4371864 105.248206478) 2
36 TRUNCATE t1;
37 SELECT * FROM t1;
38 fid p
39 INSERT INTO t1(p) VALUES(ST_PointFromText('POINT(10 10)')),
40 (ST_PointFromText('POINT(10 20)')),
41 (ST_PointFromText('POINT(10 40)')),
42 (ST_PointFromText('POINT(10 60)')),
43 (ST_PointFromText('POINT(20 15)')),
44 (ST_PointFromText('POINT(30 10)')),
45 (ST_PointFromText('POINT(40 10)')),
46 (ST_PointFromText('POINT(50 10)')),
47 (ST_PointFromText('POINT(60 5)')),
48 (ST_PointFromText('POINT(60 10)')),
49 (ST_PointFromText('POINT(60 20)')),
50 (ST_PointFromText('POINT(60 100)')),
51 (ST_PointFromText('POINT(100 100)')),
52 (ST_PointFromText('POINT(80 60)')),
53 (ST_PointFromText('POINT(81 50)')),
54 (ST_PointFromText('POINT(82 70)')),
55 (ST_PointFromText('POINT(83 30)')),
56 (ST_PointFromText('POINT(20 100)')),
57 (ST_PointFromText('POINT(150 2000)')),
58 (ST_PointFromText('POINT(109 230)')),
59 (ST_PointFromText('POINT(280 250)')),
60 (ST_PointFromText('POINT(176 175)')),
61 (ST_PointFromText('POINT(200 10)')),
62 (NULL),
63 (NULL);
64 SELECT ST_AsText(p) FROM t1;
65 ST_AsText(p)
66 POINT(10 10)
67 POINT(10 20)
68 POINT(10 40)
69 POINT(10 60)
70 POINT(20 15)
71 POINT(30 10)
72 POINT(40 10)
73 POINT(50 10)
74 POINT(60 5)
75 POINT(60 10)
76 POINT(60 20)
77 POINT(60 100)
78 POINT(100 100)
79 POINT(80 60)
80 POINT(81 50)
81 POINT(82 70)
82 POINT(83 30)
83 POINT(20 100)
84 POINT(150 2000)
85 POINT(109 230)
86 POINT(280 250)
87 POINT(176 175)
88 POINT(200 10)
89 NULL
90 NULL
91 SELECT COUNT(*) FROM t1;
92 COUNT(*)
93 25
94 SELECT ST_AsText(p) FROM t1;
95 ST_AsText(p)
96 POINT(10 10)
97 POINT(10 20)
98 POINT(10 40)
99 POINT(10 60)
100 POINT(20 15)
101 POINT(30 10)
102 POINT(40 10)
103 POINT(50 10)
104 POINT(60 5)
105 POINT(60 10)
106 POINT(60 20)
107 POINT(60 100)
108 POINT(100 100)
109 POINT(80 60)
110 POINT(81 50)
111 POINT(82 70)
112 POINT(83 30)
113 POINT(20 100)
114 POINT(150 2000)
115 POINT(109 230)
116 POINT(280 250)
117 POINT(176 175)
118 POINT(200 10)
119 NULL
120 NULL
121 SELECT COUNT(*) FROM t1;
122 COUNT(*)
123 25
124 INSERT INTO t1(p) SELECT p FROM t1;
125 INSERT INTO t1(p) SELECT p FROM t1;
126 INSERT INTO t1(p) SELECT p FROM t1;
127 INSERT INTO t1(p) SELECT p FROM t1;
128 INSERT INTO t1(p) SELECT p FROM t1;
129 INSERT INTO t1(p) SELECT p FROM t1;
130 INSERT INTO t1(p) SELECT p FROM t1;
131 INSERT INTO t1(p) SELECT p FROM t1;
132 INSERT INTO t1(p) SELECT p FROM t1;
133 INSERT INTO t1(p) SELECT p FROM t1;
134 INSERT INTO t1(p) SELECT p FROM t1;
135 INSERT INTO t1(p) SELECT p FROM t1;
136 CHECK TABLE t1;
137 Table Op Msg_type Msg_text
138 test.t1 check status OK
139 SELECT COUNT(*) FROM t1;
140 COUNT(*)
141 102400
142 SELECT ST_AsText(p), COUNT(*) FROM t1 GROUP BY p;
143 ST_AsText(p) COUNT(*)
144 NULL 8192
145 POINT(10 10) 4096
146 POINT(10 20) 4096
147 POINT(10 40) 4096
148 POINT(10 60) 4096
149 POINT(20 15) 4096
150 POINT(20 100) 4096
151 POINT(30 10) 4096
152 POINT(40 10) 4096
153 POINT(50 10) 4096
154 POINT(60 5) 4096
155 POINT(60 10) 4096
156 POINT(60 20) 4096
157 POINT(60 100) 4096
158 POINT(80 60) 4096
159 POINT(100 100) 4096
160 POINT(176 175) 4096
161 POINT(200 10) 4096
162 POINT(81 50) 4096
163 POINT(109 230) 4096
164 POINT(82 70) 4096
165 POINT(280 250) 4096
166 POINT(83 30) 4096
167 POINT(150 2000) 4096
168 SELECT COUNT(*) FROM t1 WHERE p = ST_PointFromText('POINT(280 250)');
169 COUNT(*)
170 4096
171 DELETE FROM t1 WHERE p = ST_PointFromText('POINT(280 250)');
172 SELECT COUNT(*) FROM t1 WHERE p = ST_PointFromText('POINT(280 250)');
173 COUNT(*)
174 0
175 SELECT COUNT(*) FROM t1 WHERE p = ST_PointFromText('POINT(60 5)');
176 COUNT(*)
177 4096
178 DELETE FROM t1 WHERE P = ST_PointFromText('POINT(60 5)');
179 SELECT COUNT(*) FROM t1 WHERE p = ST_PointFromText('POINT(60 5)');
180 COUNT(*)
181 0
182 SELECT ST_AsText(p), COUNT(*) FROM t1 WHERE p = ST_PointFromText('POINT(60 10)') OR p = ST_PointFromText('POINT(60 20)') OR p = ST_PointFromText('POINT(60 30)') GROUP BY p;
183 ST_AsText(p) COUNT(*)
184 POINT(60 10) 4096
185 POINT(60 20) 4096
186 UPDATE t1 SET p = ST_PointFromText('POINT(101 102)') WHERE p = ST_PointFromText('POINT(60 10)') OR p = ST_PointFromText('POINT(60 20)') OR p = ST_PointFromText('POINT(60 30)');
187 SELECT ST_AsText(p), COUNT(*) FROM t1 WHERE p = ST_PointFromText('POINT(60 10)') OR p = ST_PointFromText('POINT(60 20)') OR p = ST_PointFromText('POINT(60 30)') GROUP BY p;
188 ST_AsText(p) COUNT(*)
189 SELECT COUNT(*) FROM t1 WHERE p = ST_PointFromText('POINT(101 102)');
190 COUNT(*)
191 8192
192 SELECT COUNT(*) FROM t1 WHERE p IS NULL;
193 COUNT(*)
194 8192
195 UPDATE t1 SET p = ST_PointFromText('POINT(160 160)') WHERE p IS NULL;
196 SELECT COUNT(*) FROM t1 WHERE p IS NULL;
197 COUNT(*)
198 0
199 SELECT COUNT(*) FROM t1 WHERE p = ST_PointFromText('POINT(160 160)');
200 COUNT(*)
201 8192
202 SELECT ST_AsText(p), COUNT(*) FROM t1 GROUP BY p;
203 ST_AsText(p) COUNT(*)
204 POINT(10 10) 4096
205 POINT(10 20) 4096
206 POINT(10 40) 4096
207 POINT(10 60) 4096
208 POINT(20 15) 4096
209 POINT(20 100) 4096
210 POINT(30 10) 4096
211 POINT(40 10) 4096
212 POINT(50 10) 4096
213 POINT(60 100) 4096
214 POINT(80 60) 4096
215 POINT(100 100) 4096
216 POINT(160 160) 8192
217 POINT(176 175) 4096
218 POINT(200 10) 4096
219 POINT(81 50) 4096
220 POINT(101 102) 8192
221 POINT(109 230) 4096
222 POINT(82 70) 4096
223 POINT(83 30) 4096
224 POINT(150 2000) 4096
225 SELECT COUNT(*) FROM t1;
226 COUNT(*)
227 94208
228 CHECK TABLE t1;
229 Table Op Msg_type Msg_text
230 test.t1 check status OK
231 DROP TABLE t1;
232 #
233 # Test when POINT is used in spatial index
234 #
235 SET @ls1 = ST_GeomFromText('LINESTRING(0 20, 10 0)');
236 SET @ls2 = ST_GeomFromText('LINESTRING(0 0, 10 20)');
237 SET @ls3 = ST_GeomFromText('LINESTRING(20 -40, 21 -42)');
238 SET @ls4 = ST_GeomFromText('LINESTRING(20 -42, 21 -40)');
239 SET @poly1 = ST_GeomFromText('POLYGON((2 2, 2 10, 10 10, 10 2, 2 2))');
240 SET @poly2 = ST_GeomFromText('POLYGON((0 0, -5 0, -4 -1, -6 -15, -3 -15, 0 0))');
241 SET @poly3 = ST_GeomFromText('POLYGON((10.0 10.0, 20.5 20, 20.5 50, 32.0 64.0, 32.3 64.6, 5 60, 10 10))');
242 SET @poly4 = ST_GeomFromText('POLYGON((0 10, -10 10, -10 -10, 0 -10, 0 10))');
243 SET @p1 = ST_PointFromText('POINT(0 0)');
244 SET @mpoly = ST_GeomFromText('MULTIPOLYGON(((3 3, 3 16, 16 16, 16 3, 3 3)), ((10 10, 10 50, 50 50, 50 10, 10 10)))');
245 CREATE TABLE gis_point (p1 POINT NOT NULL, p2 POINT NOT NULL, SPATIAL KEY k1 (p1), SPATIAL KEY k2 (p2)) ENGINE=InnoDB;
246 INSERT INTO gis_point VALUES
247 (ST_PointFromText('POINT(1 2)'), ST_PointFromText('POINT(-1 -3)')),
248 (ST_PointFromText('POINT(2 4)'), ST_PointFromText('POINT(-2 -6)')),
249 (ST_PointFromText('POINT(3 6)'), ST_PointFromText('POINT(-3 -9)')),
250 (ST_PointFromText('POINT(4 8)'), ST_PointFromText('POINT(-4 -12)')),
251 (ST_PointFromText('POINT(5 10)'), ST_PointFromText('POINT(-5 -15)')),
252 (ST_PointFromText('POINT(6 12)'), ST_PointFromText('POINT(-6 -18)')),
253 (ST_PointFromText('POINT(7 14)'), ST_PointFromText('POINT(-7 -21)')),
254 (ST_PointFromText('POINT(8 16)'), ST_PointFromText('POINT(0 0)')),
255 (ST_PointFromText('POINT(9 18)'), ST_PointFromText('POINT(-4 2)')),
256 (ST_PointFromText('POINT(10 21)'), ST_PointFromText('POINT(-6 3)')),
257 (ST_PointFromText('POINT(20.5 41)'), ST_PointFromText('POINT(-8 4)')),
258 (ST_PointFromText('POINT(26.25 57)'), ST_PointFromText('POINT(1 2)')),
259 (ST_PointFromText('POINT(32.1234 64.2468)'), ST_PointFromText('POINT(-1 -1)'));
260 'The ORDER BY for spatial index will use filesort'
261 EXPLAIN SELECT ST_AsText(p1), ST_AsText(p2) FROM gis_point ORDER BY p1, p2;
262 id select_type table type possible_keys key key_len ref rows Extra
263 1 SIMPLE gis_point ALL NULL NULL NULL NULL # Using filesort
264 SELECT ST_AsText(p1), ST_AsText(p2) FROM gis_point ORDER BY p1, p2;
265 ST_AsText(p1) ST_AsText(p2)
266 POINT(2 4) POINT(-2 -6)
267 POINT(3 6) POINT(-3 -9)
268 POINT(4 8) POINT(-4 -12)
269 POINT(5 10) POINT(-5 -15)
270 POINT(6 12) POINT(-6 -18)
271 POINT(7 14) POINT(-7 -21)
272 POINT(8 16) POINT(0 0)
273 POINT(9 18) POINT(-4 2)
274 POINT(10 21) POINT(-6 3)
275 POINT(1 2) POINT(-1 -3)
276 POINT(26.25 57) POINT(1 2)
277 POINT(20.5 41) POINT(-8 4)
278 POINT(32.1234 64.2468) POINT(-1 -1)
279 'Try to do IDU on the table and verify the result'
280 DELETE FROM gis_point WHERE ST_Equals(p2, ST_PointFromText('POINT(-8 4)'));
281 INSERT INTO gis_point VALUES(ST_PointFromText('POINT(20.5 -41)'), ST_PointFromText('POINT(8 -4)'));
282 SELECT ST_AsText(p1) AS 'Expect (32.1234 64.2468)' FROM gis_point WHERE ST_TOUCHES(@poly3, p1) AND MBRWithin(p2, @poly4);
283 Expect (32.1234 64.2468)
284 POINT(32.1234 64.2468)
285 UPDATE gis_point SET p1 = ST_PointFromText('POINT(20.5 41)'), p2 = ST_PointFromText('POINT(-8 4)') WHERE ST_Intersection(@ls3, @ls4) = p1;
286 SELECT ST_AsText(p1) AS 'Expect (32.1234 64.2468) AND (20.5 41)' FROM gis_point WHERE ST_TOUCHES(@poly3, p1) AND MBRWithin(p2, @poly4);
287 Expect (32.1234 64.2468) AND (20.5 41)
288 POINT(32.1234 64.2468)
289 POINT(20.5 41)
290 CHECK TABLE gis_point;
291 Table Op Msg_type Msg_text
292 test.gis_point check status OK
293 ============================================================
294 Use a trx to test the IDU on the table and verify the result
295 ============================================================
296 START TRANSACTION;
297 DELETE FROM gis_point WHERE ST_Equals(p2, ST_PointFromText('POINT(-8 4)'));
298 INSERT INTO gis_point VALUES(ST_PointFromText('POINT(20.5 -41)'), ST_PointFromText('POINT(8 -4)'));
299 SELECT ST_AsText(p1) AS 'Expect (32.1234 64.2468)' FROM gis_point WHERE ST_TOUCHES(@poly3, p1) AND MBRWithin(p2, @poly4);
300 Expect (32.1234 64.2468)
301 POINT(32.1234 64.2468)
302 UPDATE gis_point SET p1 = ST_PointFromText('POINT(20.5 49)'), p2 = ST_PointFromText('POINT(-8 4)') WHERE ST_Intersection(@ls3, @ls4) = p1;
303 SELECT ST_AsText(p1) AS 'Expect (32.1234 64.2468) AND (20.5 49)' FROM gis_point WHERE ST_TOUCHES(@poly3, p1) AND MBRWithin(p2, @poly4);
304 Expect (32.1234 64.2468) AND (20.5 49)
305 POINT(32.1234 64.2468)
306 POINT(20.5 49)
307 ROLLBACK;
308 SELECT ST_AsText(p1) AS 'Expect (32.1234 64.2468) AND (20.5 41)' FROM gis_point WHERE ST_TOUCHES(@poly3, p1) AND MBRWithin(p2, @poly4);
309 Expect (32.1234 64.2468) AND (20.5 41)
310 POINT(32.1234 64.2468)
311 POINT(20.5 41)
312 CHECK TABLE gis_point;
313 Table Op Msg_type Msg_text
314 test.gis_point check status OK
315 =======================================================
316 Enlarge the table by inserting the same data and verify
317 =======================================================
318 INSERT INTO gis_point SELECT * FROM gis_point;
319 INSERT INTO gis_point SELECT * FROM gis_point;
320 INSERT INTO gis_point SELECT * FROM gis_point;
321 INSERT INTO gis_point SELECT * FROM gis_point;
322 INSERT INTO gis_point SELECT * FROM gis_point;
323 INSERT INTO gis_point SELECT * FROM gis_point;
324 INSERT INTO gis_point SELECT * FROM gis_point;
325 INSERT INTO gis_point SELECT * FROM gis_point;
326 INSERT INTO gis_point SELECT * FROM gis_point;
327 INSERT INTO gis_point SELECT * FROM gis_point;
328 CHECK TABLE gis_point;
329 Table Op Msg_type Msg_text
330 test.gis_point check status OK
331 SELECT COUNT(*), ST_AsText(p1), ST_AsText(p2) FROM gis_point GROUP BY p1, p2;
332 COUNT(*) ST_AsText(p1) ST_AsText(p2)
333 1024 POINT(2 4) POINT(-2 -6)
334 1024 POINT(3 6) POINT(-3 -9)
335 1024 POINT(4 8) POINT(-4 -12)
336 1024 POINT(5 10) POINT(-5 -15)
337 1024 POINT(6 12) POINT(-6 -18)
338 1024 POINT(7 14) POINT(-7 -21)
339 1024 POINT(8 16) POINT(0 0)
340 1024 POINT(9 18) POINT(-4 2)
341 1024 POINT(10 21) POINT(-6 3)
342 1024 POINT(1 2) POINT(-1 -3)
343 1024 POINT(26.25 57) POINT(1 2)
344 1024 POINT(20.5 41) POINT(-8 4)
345 1024 POINT(32.1234 64.2468) POINT(-1 -1)
346 SELECT COUNT(*), ST_AsText(p1) FROM gis_point WHERE ST_Intersection(@ls1, @ls2) = p1 GROUP BY p1;
347 COUNT(*) ST_AsText(p1)
348 1024 POINT(5 10)
349 SELECT COUNT(*), ST_AsText(p1) FROM gis_point WHERE MBRWithin(p1, @poly1) GROUP BY p1;
350 COUNT(*) ST_AsText(p1)
351 1024 POINT(3 6)
352 1024 POINT(4 8)
353 SELECT COUNT(*), ST_AsText(p2) FROM gis_point WHERE ST_Contains(@poly2, p2) GROUP BY p2;
354 COUNT(*) ST_AsText(p2)
355 1024 POINT(0 0)
356 1024 POINT(-2 -6)
357 1024 POINT(-3 -9)
358 1024 POINT(-4 -12)
359 1024 POINT(-5 -15)
360 1024 POINT(-1 -3)
361 1024 POINT(-1 -1)
362 SELECT COUNT(*), ST_AsText(p2) FROM gis_point WHERE ST_Equals(p2, @p1) GROUP BY p2;
363 COUNT(*) ST_AsText(p2)
364 1024 POINT(0 0)
365 SELECT COUNT(*), ST_AsText(p1) FROM gis_point WHERE ST_TOUCHES(@poly3, p1) AND MBRWithin(p2, @poly4) GROUP BY p1;
366 COUNT(*) ST_AsText(p1)
367 1024 POINT(20.5 41)
368 1024 POINT(32.1234 64.2468)
369 SELECT COUNT(*), ST_AsText(p1), ST_AsText(p2) FROM gis_point WHERE ST_Contains(@mpoly, p1) GROUP BY p1, p2;
370 COUNT(*) ST_AsText(p1) ST_AsText(p2)
371 1024 POINT(3 6) POINT(-3 -9)
372 1024 POINT(4 8) POINT(-4 -12)
373 1024 POINT(5 10) POINT(-5 -15)
374 1024 POINT(6 12) POINT(-6 -18)
375 1024 POINT(7 14) POINT(-7 -21)
376 1024 POINT(8 16) POINT(0 0)
377 1024 POINT(10 21) POINT(-6 3)
378 1024 POINT(20.5 41) POINT(-8 4)
379 SELECT COUNT(*), ST_AsText(p1), ST_AsText(p2) FROM gis_point WHERE ST_Contains(@mpoly, p1) AND NOT MBRWithin(p1, @mpoly) GROUP BY p1, p2;
380 COUNT(*) ST_AsText(p1) ST_AsText(p2)
381 1024 POINT(3 6) POINT(-3 -9)
382 ======================================================================
383 Build another new table with the same schema, will insert data from this table to the orignal one
384 ======================================================================
385 CREATE TABLE p(p1 POINT NOT NULL, p2 POINT NOT NULL, SPATIAL INDEX k1(p2)) ENGINE=InnoDB;
386 INSERT INTO p VALUES(ST_PointFromText('POINT(1000 -1000)'), ST_PointFromText('POINT(-201 203.56)'));
387 INSERT INTO p VALUES(ST_PointFromText('POINT(20.5 43.9832)'), ST_PointFromText('POINT(-0 0)'));
388 INSERT INTO p VALUES(ST_PointFromText('POINT(-4.2 -6.98)'), ST_PointFromText('POINT(-120.5 343.9832)'));
389 INSERT INTO p SELECT * FROM p WHERE MBRWithin(p1, ST_GeomFromText('POLYGON((0 0, 0 2000, 2000 2000, 2000 -2000, 0 -2000, 0 0))'));
390 INSERT INTO p SELECT * FROM p WHERE MBRWithin(p1, ST_GeomFromText('POLYGON((0 0, -10 0, -100 -100, 0 -50, 0 0))'));
391 SELECT COUNT(*), ST_AsText(p1), ST_AsText(p2) FROM p GROUP BY p1, p2;
392 COUNT(*) ST_AsText(p1) ST_AsText(p2)
393 2 POINT(1000 -1000) POINT(-201 203.56)
394 2 POINT(20.5 43.9832) POINT(0 0)
395 2 POINT(-4.2 -6.98) POINT(-120.5 343.9832)
396 INSERT INTO gis_point SELECT * FROM p;
397 INSERT INTO gis_point SELECT * FROM p;
398 INSERT INTO gis_point SELECT * FROM p;
399 INSERT INTO gis_point SELECT * FROM p;
400 INSERT INTO gis_point SELECT * FROM p;
401 INSERT INTO gis_point SELECT * FROM p;
402 INSERT INTO gis_point SELECT * FROM p;
403 CHECK TABLE gis_point;
404 Table Op Msg_type Msg_text
405 test.gis_point check status OK
406 SELECT COUNT(*), ST_AsText(p1), ST_AsText(p2) FROM gis_point GROUP BY p1, p2;
407 COUNT(*) ST_AsText(p1) ST_AsText(p2)
408 1024 POINT(2 4) POINT(-2 -6)
409 1024 POINT(3 6) POINT(-3 -9)
410 1024 POINT(4 8) POINT(-4 -12)
411 1024 POINT(5 10) POINT(-5 -15)
412 1024 POINT(6 12) POINT(-6 -18)
413 1024 POINT(7 14) POINT(-7 -21)
414 1024 POINT(8 16) POINT(0 0)
415 1024 POINT(9 18) POINT(-4 2)
416 1024 POINT(10 21) POINT(-6 3)
417 1024 POINT(1 2) POINT(-1 -3)
418 1024 POINT(26.25 57) POINT(1 2)
419 14 POINT(1000 -1000) POINT(-201 203.56)
420 1024 POINT(20.5 41) POINT(-8 4)
421 14 POINT(20.5 43.9832) POINT(0 0)
422 1024 POINT(32.1234 64.2468) POINT(-1 -1)
423 14 POINT(-4.2 -6.98) POINT(-120.5 343.9832)
424 SELECT COUNT(*), ST_AsText(p1) FROM gis_point WHERE ST_TOUCHES(@poly3, p1) AND MBRWithin(p2, @poly4) GROUP BY p1;
425 COUNT(*) ST_AsText(p1)
426 1024 POINT(20.5 41)
427 1024 POINT(32.1234 64.2468)
428 SELECT COUNT(*), ST_AsText(p2) FROM gis_point WHERE MBRWithin(p2, @poly2) GROUP BY p2;
429 COUNT(*) ST_AsText(p2)
430 1024 POINT(-2 -6)
431 1024 POINT(-3 -9)
432 1024 POINT(-4 -12)
433 1024 POINT(-1 -3)
434 1024 POINT(-1 -1)
435 DROP TABLE p;
436 ================================
437 Use a trx to test IUD and verify
438 ================================
439 =============================================
440 Delete those rows selected from another table
441 =============================================
442 SELECT COUNT(*), ST_AsText(p1), ST_AsText(p2) FROM gis_point GROUP BY p1, p2;
443 COUNT(*) ST_AsText(p1) ST_AsText(p2)
444 1024 POINT(2 4) POINT(-2 -6)
445 1024 POINT(3 6) POINT(-3 -9)
446 1024 POINT(4 8) POINT(-4 -12)
447 1024 POINT(5 10) POINT(-5 -15)
448 1024 POINT(6 12) POINT(-6 -18)
449 1024 POINT(7 14) POINT(-7 -21)
450 1024 POINT(8 16) POINT(0 0)
451 1024 POINT(9 18) POINT(-4 2)
452 1024 POINT(10 21) POINT(-6 3)
453 1024 POINT(1 2) POINT(-1 -3)
454 1024 POINT(26.25 57) POINT(1 2)
455 14 POINT(1000 -1000) POINT(-201 203.56)
456 1024 POINT(20.5 41) POINT(-8 4)
457 14 POINT(20.5 43.9832) POINT(0 0)
458 1024 POINT(32.1234 64.2468) POINT(-1 -1)
459 14 POINT(-4.2 -6.98) POINT(-120.5 343.9832)
460 UPDATE gis_point SET p2 = ST_PointFromText('POINT(2000 2000)') WHERE ST_Contains(ST_GeomFromText('POLYGON((-100 100, -400 100, -400 400, -100 400, -100 100))'), p2) OR ST_Contains(ST_GeomFromText('POLYGON((-0.0001 -0.0002, -0.0001 0.00002, 0.00000005 0.000001, 0.0000025 -0.001, -0.0001 -0.0002))'), p2);
461 CHECK TABLE gis_point;
462 Table Op Msg_type Msg_text
463 test.gis_point check status OK
464 'To remove all the just updated rows'
465 DELETE FROM gis_point WHERE ST_Intersection(ST_GeomFromText('LINESTRING(1800 1900, 2200 2100)'), ST_GeomFromText('LINESTRING(0 0, 2001 2001)')) = p2;
466 INSERT INTO gis_point VALUES
467 (ST_PointFromText('POINT(8 16)'), ST_PointFromText('POINT(0 0)')),
468 (ST_PointFromText('POINT(8 16)'), ST_PointFromText('POINT(0 0)'));
469 ======================================================================
470 Following results should be almost the same with those at the beginning
471 ======================================================================
472 SELECT COUNT(*), ST_AsText(p1), ST_AsText(p2) FROM gis_point GROUP BY p1, p2;
473 COUNT(*) ST_AsText(p1) ST_AsText(p2)
474 1024 POINT(2 4) POINT(-2 -6)
475 1024 POINT(3 6) POINT(-3 -9)
476 1024 POINT(4 8) POINT(-4 -12)
477 1024 POINT(5 10) POINT(-5 -15)
478 1024 POINT(6 12) POINT(-6 -18)
479 1024 POINT(7 14) POINT(-7 -21)
480 2 POINT(8 16) POINT(0 0)
481 1024 POINT(9 18) POINT(-4 2)
482 1024 POINT(10 21) POINT(-6 3)
483 1024 POINT(1 2) POINT(-1 -3)
484 1024 POINT(26.25 57) POINT(1 2)
485 1024 POINT(20.5 41) POINT(-8 4)
486 1024 POINT(32.1234 64.2468) POINT(-1 -1)
487 SELECT COUNT(*), ST_AsText(p1), ST_AsText(p2)
488 FROM gis_point
489 WHERE ST_Contains(ST_GeomFromText('POLYGON((-1000 -1000, -1000 1000, 1000 1000, 1001 -1001, -1000 -1000))'), p1)
490 GROUP BY p1, p2;
491 COUNT(*) ST_AsText(p1) ST_AsText(p2)
492 1024 POINT(2 4) POINT(-2 -6)
493 1024 POINT(3 6) POINT(-3 -9)
494 1024 POINT(4 8) POINT(-4 -12)
495 1024 POINT(5 10) POINT(-5 -15)
496 1024 POINT(6 12) POINT(-6 -18)
497 1024 POINT(7 14) POINT(-7 -21)
498 2 POINT(8 16) POINT(0 0)
499 1024 POINT(9 18) POINT(-4 2)
500 1024 POINT(10 21) POINT(-6 3)
501 1024 POINT(1 2) POINT(-1 -3)
502 1024 POINT(26.25 57) POINT(1 2)
503 1024 POINT(20.5 41) POINT(-8 4)
504 1024 POINT(32.1234 64.2468) POINT(-1 -1)
505 SELECT ST_AsText(p1), ST_AsText(p2) FROM gis_point GROUP BY p1, p2;
506 ST_AsText(p1) ST_AsText(p2)
507 POINT(2 4) POINT(-2 -6)
508 POINT(3 6) POINT(-3 -9)
509 POINT(4 8) POINT(-4 -12)
510 POINT(5 10) POINT(-5 -15)
511 POINT(6 12) POINT(-6 -18)
512 POINT(7 14) POINT(-7 -21)
513 POINT(8 16) POINT(0 0)
514 POINT(9 18) POINT(-4 2)
515 POINT(10 21) POINT(-6 3)
516 POINT(1 2) POINT(-1 -3)
517 POINT(26.25 57) POINT(1 2)
518 POINT(20.5 41) POINT(-8 4)
519 POINT(32.1234 64.2468) POINT(-1 -1)
520 SELECT COUNT(*), ST_AsText(p1) FROM gis_point WHERE ST_Intersection(@ls1, @ls2) = p1 GROUP BY p1;
521 COUNT(*) ST_AsText(p1)
522 1024 POINT(5 10)
523 SELECT COUNT(*), ST_AsText(p1) FROM gis_point WHERE MBRWithin(p1, @poly1) GROUP BY p1;
524 COUNT(*) ST_AsText(p1)
525 1024 POINT(3 6)
526 1024 POINT(4 8)
527 SELECT COUNT(*), ST_AsText(p2) FROM gis_point WHERE ST_Contains(@poly2, p2) GROUP BY p2;
528 COUNT(*) ST_AsText(p2)
529 2 POINT(0 0)
530 1024 POINT(-2 -6)
531 1024 POINT(-3 -9)
532 1024 POINT(-4 -12)
533 1024 POINT(-5 -15)
534 1024 POINT(-1 -3)
535 1024 POINT(-1 -1)
536 SELECT COUNT(*), ST_AsText(p2) FROM gis_point WHERE ST_Equals(p2, @p1) GROUP BY p2;
537 COUNT(*) ST_AsText(p2)
538 2 POINT(0 0)
539 SELECT COUNT(*), ST_AsText(p1) FROM gis_point WHERE ST_TOUCHES(@poly3, p1) AND MBRWithin(p2, @poly4) GROUP BY p1;
540 COUNT(*) ST_AsText(p1)
541 1024 POINT(20.5 41)
542 1024 POINT(32.1234 64.2468)
543 SELECT COUNT(*), ST_AsText(p1), ST_AsText(p2) FROM gis_point WHERE ST_Contains(@mpoly, p1) GROUP BY p1, p2;
544 COUNT(*) ST_AsText(p1) ST_AsText(p2)
545 1024 POINT(3 6) POINT(-3 -9)
546 1024 POINT(4 8) POINT(-4 -12)
547 1024 POINT(5 10) POINT(-5 -15)
548 1024 POINT(6 12) POINT(-6 -18)
549 1024 POINT(7 14) POINT(-7 -21)
550 2 POINT(8 16) POINT(0 0)
551 1024 POINT(10 21) POINT(-6 3)
552 1024 POINT(20.5 41) POINT(-8 4)
553 SELECT COUNT(*), ST_AsText(p1), ST_AsText(p2) FROM gis_point WHERE ST_Contains(@mpoly, p1) AND NOT MBRWithin(p1, @mpoly) GROUP BY p1, p2;
554 COUNT(*) ST_AsText(p1) ST_AsText(p2)
555 1024 POINT(3 6) POINT(-3 -9)
556 CHECK TABLE gis_point;
557 Table Op Msg_type Msg_text
558 test.gis_point check status OK
559 DROP TABLE gis_point;