Debian Codegraph @

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

raw
1 #
2 # Test when POINT is used in spatial index
3 #
4 SET @ls1 = ST_GeomFromText('LINESTRING(0 20, 10 0)');
5 SET @ls2 = ST_GeomFromText('LINESTRING(0 0, 10 20)');
6 SET @ls3 = ST_GeomFromText('LINESTRING(20 -40, 21 -42)');
7 SET @ls4 = ST_GeomFromText('LINESTRING(20 -42, 21 -40)');
8 SET @poly1 = ST_GeomFromText('POLYGON((2 2, 2 10, 10 10, 10 2, 2 2))');
9 SET @poly2 = ST_GeomFromText('POLYGON((0 0, -5 0, -4 -1, -6 -15, -3 -15, 0 0))');
10 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))');
11 SET @poly4 = ST_GeomFromText('POLYGON((0 10, -10 10, -10 -10, 0 -10, 0 10))');
12 SET @p1 = ST_PointFromText('POINT(0 0)');
13 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)))');
14 CREATE TABLE gis_point (p1 POINT NOT NULL SRID 0, p2 POINT NOT NULL SRID 0, SPATIAL KEY k1 (p1), SPATIAL KEY k2 (p2)) ENGINE=InnoDB;
15 INSERT INTO gis_point VALUES
16 (ST_PointFromText('POINT(1 2)'), ST_PointFromText('POINT(-1 -3)')),
17 (ST_PointFromText('POINT(2 4)'), ST_PointFromText('POINT(-2 -6)')),
18 (ST_PointFromText('POINT(3 6)'), ST_PointFromText('POINT(-3 -9)')),
19 (ST_PointFromText('POINT(4 8)'), ST_PointFromText('POINT(-4 -12)')),
20 (ST_PointFromText('POINT(5 10)'), ST_PointFromText('POINT(-5 -15)')),
21 (ST_PointFromText('POINT(6 12)'), ST_PointFromText('POINT(-6 -18)')),
22 (ST_PointFromText('POINT(7 14)'), ST_PointFromText('POINT(-7 -21)')),
23 (ST_PointFromText('POINT(8 16)'), ST_PointFromText('POINT(0 0)')),
24 (ST_PointFromText('POINT(9 18)'), ST_PointFromText('POINT(-4 2)')),
25 (ST_PointFromText('POINT(10 21)'), ST_PointFromText('POINT(-6 3)')),
26 (ST_PointFromText('POINT(20.5 41)'), ST_PointFromText('POINT(-8 4)')),
27 (ST_PointFromText('POINT(26.25 57)'), ST_PointFromText('POINT(1 2)')),
28 (ST_PointFromText('POINT(32.1234 64.2468)'), ST_PointFromText('POINT(-1 -1)'));
29 'The ORDER BY for spatial index will use filesort'
30 EXPLAIN SELECT ST_AsText(p1), ST_AsText(p2) FROM gis_point ORDER BY p1, p2;
31 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
32 1 SIMPLE gis_point NULL ALL NULL NULL NULL NULL # 100.00 Using filesort
33 Warnings:
34 Note 1003 /* select#1 */ select st_astext(`test`.`gis_point`.`p1`) AS `ST_AsText(p1)`,st_astext(`test`.`gis_point`.`p2`) AS `ST_AsText(p2)` from `test`.`gis_point` order by `test`.`gis_point`.`p1`,`test`.`gis_point`.`p2`
35 SELECT ST_AsText(p1), ST_AsText(p2) FROM gis_point ORDER BY p1, p2;
36 ST_AsText(p1) ST_AsText(p2)
37 POINT(2 4) POINT(-2 -6)
38 POINT(3 6) POINT(-3 -9)
39 POINT(4 8) POINT(-4 -12)
40 POINT(5 10) POINT(-5 -15)
41 POINT(6 12) POINT(-6 -18)
42 POINT(7 14) POINT(-7 -21)
43 POINT(8 16) POINT(0 0)
44 POINT(9 18) POINT(-4 2)
45 POINT(10 21) POINT(-6 3)
46 POINT(1 2) POINT(-1 -3)
47 POINT(26.25 57) POINT(1 2)
48 POINT(20.5 41) POINT(-8 4)
49 POINT(32.1234 64.2468) POINT(-1 -1)
50 'Try to do IDU on the table and verify the result'
51 DELETE FROM gis_point WHERE ST_Equals(p2, ST_PointFromText('POINT(-8 4)'));
52 INSERT INTO gis_point VALUES(ST_PointFromText('POINT(20.5 -41)'), ST_PointFromText('POINT(8 -4)'));
53 SELECT ST_AsText(p1) AS 'Expect (32.1234 64.2468)' FROM gis_point WHERE ST_TOUCHES(@poly3, p1) AND MBRWithin(p2, @poly4);
54 Expect (32.1234 64.2468)
55 POINT(32.1234 64.2468)
56 UPDATE gis_point SET p1 = ST_PointFromText('POINT(20.5 41)'), p2 = ST_PointFromText('POINT(-8 4)') WHERE ST_Intersection(@ls3, @ls4) = p1;
57 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);
58 Expect (32.1234 64.2468) AND (20.5 41)
59 POINT(32.1234 64.2468)
60 POINT(20.5 41)
61 CHECK TABLE gis_point;
62 Table Op Msg_type Msg_text
63 test.gis_point check status OK
64 ============================================================
65 Use a trx to test the IDU on the table and verify the result
66 ============================================================
67 START TRANSACTION;
68 DELETE FROM gis_point WHERE ST_Equals(p2, ST_PointFromText('POINT(-8 4)'));
69 INSERT INTO gis_point VALUES(ST_PointFromText('POINT(20.5 -41)'), ST_PointFromText('POINT(8 -4)'));
70 SELECT ST_AsText(p1) AS 'Expect (32.1234 64.2468)' FROM gis_point WHERE ST_TOUCHES(@poly3, p1) AND MBRWithin(p2, @poly4);
71 Expect (32.1234 64.2468)
72 POINT(32.1234 64.2468)
73 UPDATE gis_point SET p1 = ST_PointFromText('POINT(20.5 49)'), p2 = ST_PointFromText('POINT(-8 4)') WHERE ST_Intersection(@ls3, @ls4) = p1;
74 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);
75 Expect (32.1234 64.2468) AND (20.5 49)
76 POINT(32.1234 64.2468)
77 POINT(20.5 49)
78 ROLLBACK;
79 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);
80 Expect (32.1234 64.2468) AND (20.5 41)
81 POINT(32.1234 64.2468)
82 POINT(20.5 41)
83 CHECK TABLE gis_point;
84 Table Op Msg_type Msg_text
85 test.gis_point check status OK
86 =======================================================
87 Enlarge the table by inserting the same data and verify
88 =======================================================
89 INSERT INTO gis_point SELECT * FROM gis_point;
90 INSERT INTO gis_point SELECT * FROM gis_point;
91 INSERT INTO gis_point SELECT * FROM gis_point;
92 INSERT INTO gis_point SELECT * FROM gis_point;
93 INSERT INTO gis_point SELECT * FROM gis_point;
94 INSERT INTO gis_point SELECT * FROM gis_point;
95 INSERT INTO gis_point SELECT * FROM gis_point;
96 INSERT INTO gis_point SELECT * FROM gis_point;
97 INSERT INTO gis_point SELECT * FROM gis_point;
98 INSERT INTO gis_point SELECT * FROM gis_point;
99 CHECK TABLE gis_point;
100 Table Op Msg_type Msg_text
101 test.gis_point check status OK
102 SELECT COUNT(*), ST_AsText(p1), ST_AsText(p2) FROM gis_point GROUP BY p1, p2;
103 COUNT(*) ST_AsText(p1) ST_AsText(p2)
104 1024 POINT(1 2) POINT(-1 -3)
105 1024 POINT(10 21) POINT(-6 3)
106 1024 POINT(2 4) POINT(-2 -6)
107 1024 POINT(20.5 41) POINT(-8 4)
108 1024 POINT(26.25 57) POINT(1 2)
109 1024 POINT(3 6) POINT(-3 -9)
110 1024 POINT(32.1234 64.2468) POINT(-1 -1)
111 1024 POINT(4 8) POINT(-4 -12)
112 1024 POINT(5 10) POINT(-5 -15)
113 1024 POINT(6 12) POINT(-6 -18)
114 1024 POINT(7 14) POINT(-7 -21)
115 1024 POINT(8 16) POINT(0 0)
116 1024 POINT(9 18) POINT(-4 2)
117 SELECT COUNT(*), ST_AsText(p1) FROM gis_point WHERE ST_Intersection(@ls1, @ls2) = p1 GROUP BY p1;
118 COUNT(*) ST_AsText(p1)
119 1024 POINT(5 10)
120 SELECT COUNT(*), ST_AsText(p1) FROM gis_point WHERE MBRWithin(p1, @poly1) GROUP BY p1 ORDER BY p1;
121 COUNT(*) ST_AsText(p1)
122 1024 POINT(3 6)
123 1024 POINT(4 8)
124 SELECT COUNT(*), ST_AsText(p2) FROM gis_point WHERE ST_Contains(@poly2, p2) GROUP BY p2;
125 COUNT(*) ST_AsText(p2)
126 1024 POINT(-1 -1)
127 1024 POINT(-1 -3)
128 1024 POINT(-2 -6)
129 1024 POINT(-3 -9)
130 1024 POINT(-4 -12)
131 SELECT COUNT(*), ST_AsText(p2) FROM gis_point WHERE ST_Equals(p2, @p1) GROUP BY p2;
132 COUNT(*) ST_AsText(p2)
133 1024 POINT(0 0)
134 SELECT COUNT(*), ST_AsText(p1) FROM gis_point WHERE ST_TOUCHES(@poly3, p1) AND MBRWithin(p2, @poly4) GROUP BY p1;
135 COUNT(*) ST_AsText(p1)
136 1024 POINT(20.5 41)
137 1024 POINT(32.1234 64.2468)
138 SELECT COUNT(*), ST_AsText(p1), ST_AsText(p2) FROM gis_point WHERE ST_Contains(@mpoly, p1) GROUP BY p1, p2;
139 COUNT(*) ST_AsText(p1) ST_AsText(p2)
140 1024 POINT(20.5 41) POINT(-8 4)
141 1024 POINT(4 8) POINT(-4 -12)
142 1024 POINT(5 10) POINT(-5 -15)
143 1024 POINT(6 12) POINT(-6 -18)
144 1024 POINT(7 14) POINT(-7 -21)
145 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;
146 COUNT(*) ST_AsText(p1) ST_AsText(p2)
147 ======================================================================
148 Build another new table with the same schema, will insert data from this table to the orignal one
149 ======================================================================
150 CREATE TABLE p(p1 POINT NOT NULL, p2 POINT NOT NULL SRID 0, SPATIAL INDEX k1(p2)) ENGINE=InnoDB;
151 INSERT INTO p VALUES(ST_PointFromText('POINT(1000 -1000)'), ST_PointFromText('POINT(-201 203.56)'));
152 INSERT INTO p VALUES(ST_PointFromText('POINT(20.5 43.9832)'), ST_PointFromText('POINT(-0 0)'));
153 INSERT INTO p VALUES(ST_PointFromText('POINT(-4.2 -6.98)'), ST_PointFromText('POINT(-120.5 343.9832)'));
154 INSERT INTO p SELECT * FROM p WHERE MBRWithin(p1, ST_GeomFromText('POLYGON((0 0, 0 2000, 2000 2000, 2000 -2000, 0 -2000, 0 0))'));
155 INSERT INTO p SELECT * FROM p WHERE MBRWithin(p1, ST_GeomFromText('POLYGON((0 0, -10 0, -100 -100, 0 -50, 0 0))'));
156 SELECT COUNT(*), ST_AsText(p1), ST_AsText(p2) FROM p GROUP BY p1, p2;
157 COUNT(*) ST_AsText(p1) ST_AsText(p2)
158 2 POINT(1000 -1000) POINT(-201 203.56)
159 2 POINT(20.5 43.9832) POINT(-0 0)
160 2 POINT(-4.2 -6.98) POINT(-120.5 343.9832)
161 INSERT INTO gis_point SELECT * FROM p;
162 INSERT INTO gis_point SELECT * FROM p;
163 INSERT INTO gis_point SELECT * FROM p;
164 INSERT INTO gis_point SELECT * FROM p;
165 INSERT INTO gis_point SELECT * FROM p;
166 INSERT INTO gis_point SELECT * FROM p;
167 INSERT INTO gis_point SELECT * FROM p;
168 CHECK TABLE gis_point;
169 Table Op Msg_type Msg_text
170 test.gis_point check status OK
171 SELECT COUNT(*), ST_AsText(p1), ST_AsText(p2) FROM gis_point GROUP BY p1, p2;
172 COUNT(*) ST_AsText(p1) ST_AsText(p2)
173 1024 POINT(1 2) POINT(-1 -3)
174 1024 POINT(10 21) POINT(-6 3)
175 1024 POINT(2 4) POINT(-2 -6)
176 1024 POINT(20.5 41) POINT(-8 4)
177 1024 POINT(26.25 57) POINT(1 2)
178 1024 POINT(3 6) POINT(-3 -9)
179 1024 POINT(32.1234 64.2468) POINT(-1 -1)
180 1024 POINT(4 8) POINT(-4 -12)
181 1024 POINT(5 10) POINT(-5 -15)
182 1024 POINT(6 12) POINT(-6 -18)
183 1024 POINT(7 14) POINT(-7 -21)
184 1024 POINT(8 16) POINT(0 0)
185 1024 POINT(9 18) POINT(-4 2)
186 14 POINT(-4.2 -6.98) POINT(-120.5 343.9832)
187 14 POINT(1000 -1000) POINT(-201 203.56)
188 14 POINT(20.5 43.9832) POINT(-0 0)
189 SELECT COUNT(*), ST_AsText(p1) FROM gis_point WHERE ST_TOUCHES(@poly3, p1) AND MBRWithin(p2, @poly4) GROUP BY p1;
190 COUNT(*) ST_AsText(p1)
191 1024 POINT(20.5 41)
192 1024 POINT(32.1234 64.2468)
193 SELECT COUNT(*), ST_AsText(p2) FROM gis_point WHERE MBRWithin(p2, @poly2) GROUP BY p2;
194 COUNT(*) ST_AsText(p2)
195 1024 POINT(-1 -1)
196 1024 POINT(-1 -3)
197 1024 POINT(-2 -6)
198 1024 POINT(-3 -9)
199 1024 POINT(-4 -12)
200 DROP TABLE p;
201 ================================
202 Use a trx to test IUD and verify
203 ================================
204 =============================================
205 Delete those rows selected from another table
206 =============================================
207 SELECT COUNT(*), ST_AsText(p1), ST_AsText(p2) FROM gis_point GROUP BY p1, p2;
208 COUNT(*) ST_AsText(p1) ST_AsText(p2)
209 1024 POINT(1 2) POINT(-1 -3)
210 1024 POINT(10 21) POINT(-6 3)
211 1024 POINT(2 4) POINT(-2 -6)
212 1024 POINT(20.5 41) POINT(-8 4)
213 1024 POINT(26.25 57) POINT(1 2)
214 1024 POINT(3 6) POINT(-3 -9)
215 1024 POINT(32.1234 64.2468) POINT(-1 -1)
216 1024 POINT(4 8) POINT(-4 -12)
217 1024 POINT(5 10) POINT(-5 -15)
218 1024 POINT(6 12) POINT(-6 -18)
219 1024 POINT(7 14) POINT(-7 -21)
220 1024 POINT(8 16) POINT(0 0)
221 1024 POINT(9 18) POINT(-4 2)
222 14 POINT(-4.2 -6.98) POINT(-120.5 343.9832)
223 14 POINT(1000 -1000) POINT(-201 203.56)
224 14 POINT(20.5 43.9832) POINT(-0 0)
225 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);
226 CHECK TABLE gis_point;
227 Table Op Msg_type Msg_text
228 test.gis_point check status OK
229 'To remove all the just updated rows'
230 DELETE FROM gis_point WHERE ST_Intersection(ST_GeomFromText('LINESTRING(1800 1900, 2200 2100)'), ST_GeomFromText('LINESTRING(0 0, 2001 2001)')) = p2;
231 INSERT INTO gis_point VALUES
232 (ST_PointFromText('POINT(8 16)'), ST_PointFromText('POINT(0 0)')),
233 (ST_PointFromText('POINT(8 16)'), ST_PointFromText('POINT(0 0)'));
234 ======================================================================
235 Following results should be almost the same with those at the beginning
236 ======================================================================
237 SELECT COUNT(*), ST_AsText(p1), ST_AsText(p2) FROM gis_point GROUP BY p1, p2;
238 COUNT(*) ST_AsText(p1) ST_AsText(p2)
239 1024 POINT(1 2) POINT(-1 -3)
240 1024 POINT(10 21) POINT(-6 3)
241 1024 POINT(2 4) POINT(-2 -6)
242 1024 POINT(20.5 41) POINT(-8 4)
243 1024 POINT(26.25 57) POINT(1 2)
244 1024 POINT(3 6) POINT(-3 -9)
245 1024 POINT(32.1234 64.2468) POINT(-1 -1)
246 1024 POINT(4 8) POINT(-4 -12)
247 1024 POINT(5 10) POINT(-5 -15)
248 1024 POINT(6 12) POINT(-6 -18)
249 1024 POINT(7 14) POINT(-7 -21)
250 1024 POINT(9 18) POINT(-4 2)
251 2 POINT(8 16) POINT(0 0)
252 SELECT COUNT(*), ST_AsText(p1), ST_AsText(p2)
253 FROM gis_point
254 WHERE ST_Contains(ST_GeomFromText('POLYGON((-1000 -1000, -1000 1000, 1000 1000, 1001 -1001, -1000 -1000))'), p1)
255 GROUP BY p1, p2;
256 COUNT(*) ST_AsText(p1) ST_AsText(p2)
257 1024 POINT(1 2) POINT(-1 -3)
258 1024 POINT(10 21) POINT(-6 3)
259 1024 POINT(2 4) POINT(-2 -6)
260 1024 POINT(20.5 41) POINT(-8 4)
261 1024 POINT(26.25 57) POINT(1 2)
262 1024 POINT(3 6) POINT(-3 -9)
263 1024 POINT(32.1234 64.2468) POINT(-1 -1)
264 1024 POINT(4 8) POINT(-4 -12)
265 1024 POINT(5 10) POINT(-5 -15)
266 1024 POINT(6 12) POINT(-6 -18)
267 1024 POINT(7 14) POINT(-7 -21)
268 1024 POINT(9 18) POINT(-4 2)
269 2 POINT(8 16) POINT(0 0)
270 SELECT ST_AsText(p1), ST_AsText(p2) FROM gis_point GROUP BY p1, p2;
271 ST_AsText(p1) ST_AsText(p2)
272 POINT(1 2) POINT(-1 -3)
273 POINT(10 21) POINT(-6 3)
274 POINT(2 4) POINT(-2 -6)
275 POINT(20.5 41) POINT(-8 4)
276 POINT(26.25 57) POINT(1 2)
277 POINT(3 6) POINT(-3 -9)
278 POINT(32.1234 64.2468) POINT(-1 -1)
279 POINT(4 8) POINT(-4 -12)
280 POINT(5 10) POINT(-5 -15)
281 POINT(6 12) POINT(-6 -18)
282 POINT(7 14) POINT(-7 -21)
283 POINT(8 16) POINT(0 0)
284 POINT(9 18) POINT(-4 2)
285 SELECT COUNT(*), ST_AsText(p1) FROM gis_point WHERE ST_Intersection(@ls1, @ls2) = p1 GROUP BY p1;
286 COUNT(*) ST_AsText(p1)
287 1024 POINT(5 10)
288 SELECT COUNT(*), ST_AsText(p1) FROM gis_point WHERE MBRWithin(p1, @poly1) GROUP BY p1 ORDER BY p1;
289 COUNT(*) ST_AsText(p1)
290 1024 POINT(3 6)
291 1024 POINT(4 8)
292 SELECT COUNT(*), ST_AsText(p2) FROM gis_point WHERE ST_Contains(@poly2, p2) GROUP BY p2 ORDER BY p2;
293 COUNT(*) ST_AsText(p2)
294 1024 POINT(-2 -6)
295 1024 POINT(-3 -9)
296 1024 POINT(-4 -12)
297 1024 POINT(-1 -3)
298 1024 POINT(-1 -1)
299 SELECT COUNT(*), ST_AsText(p2) FROM gis_point WHERE ST_Equals(p2, @p1) GROUP BY p2;
300 COUNT(*) ST_AsText(p2)
301 2 POINT(0 0)
302 SELECT COUNT(*), ST_AsText(p1) FROM gis_point WHERE ST_TOUCHES(@poly3, p1) AND MBRWithin(p2, @poly4) GROUP BY p1;
303 COUNT(*) ST_AsText(p1)
304 1024 POINT(20.5 41)
305 1024 POINT(32.1234 64.2468)
306 SELECT COUNT(*), ST_AsText(p1), ST_AsText(p2) FROM gis_point WHERE ST_Contains(@mpoly, p1) GROUP BY p1, p2 ORDER BY p1, p2;
307 COUNT(*) ST_AsText(p1) ST_AsText(p2)
308 1024 POINT(4 8) POINT(-4 -12)
309 1024 POINT(5 10) POINT(-5 -15)
310 1024 POINT(6 12) POINT(-6 -18)
311 1024 POINT(7 14) POINT(-7 -21)
312 1024 POINT(20.5 41) POINT(-8 4)
313 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;
314 COUNT(*) ST_AsText(p1) ST_AsText(p2)
315 CHECK TABLE gis_point;
316 Table Op Msg_type Msg_text
317 test.gis_point check status OK
318 DROP TABLE gis_point;