Debian Codegraph @

mysql-test/suite/innodb_gis/t/point_big.test

raw
1 #********************************************************
2 # wl6942: Store certain GIS data type in the row rather
3 # than as BLOB.
4 #
5 #********************************************************
6
7
8 --source include/big_test.inc
9 --source include/not_valgrind.inc
10
11 --echo #
12 --echo # Test when POINT is used in spatial index
13 --echo #
14 SET @ls1 = ST_GeomFromText('LINESTRING(0 20, 10 0)');
15 SET @ls2 = ST_GeomFromText('LINESTRING(0 0, 10 20)');
16 SET @ls3 = ST_GeomFromText('LINESTRING(20 -40, 21 -42)');
17 SET @ls4 = ST_GeomFromText('LINESTRING(20 -42, 21 -40)');
18 SET @poly1 = ST_GeomFromText('POLYGON((2 2, 2 10, 10 10, 10 2, 2 2))');
19 SET @poly2 = ST_GeomFromText('POLYGON((0 0, -5 0, -4 -1, -6 -15, -3 -15, 0 0))');
20 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))');
21 SET @poly4 = ST_GeomFromText('POLYGON((0 10, -10 10, -10 -10, 0 -10, 0 10))');
22 SET @p1 = ST_PointFromText('POINT(0 0)');
23 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)))');
24
25 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;
26
27 INSERT INTO gis_point VALUES
28 (ST_PointFromText('POINT(1 2)'), ST_PointFromText('POINT(-1 -3)')),
29 (ST_PointFromText('POINT(2 4)'), ST_PointFromText('POINT(-2 -6)')),
30 (ST_PointFromText('POINT(3 6)'), ST_PointFromText('POINT(-3 -9)')),
31 (ST_PointFromText('POINT(4 8)'), ST_PointFromText('POINT(-4 -12)')),
32 (ST_PointFromText('POINT(5 10)'), ST_PointFromText('POINT(-5 -15)')),
33 (ST_PointFromText('POINT(6 12)'), ST_PointFromText('POINT(-6 -18)')),
34 (ST_PointFromText('POINT(7 14)'), ST_PointFromText('POINT(-7 -21)')),
35 (ST_PointFromText('POINT(8 16)'), ST_PointFromText('POINT(0 0)')),
36 (ST_PointFromText('POINT(9 18)'), ST_PointFromText('POINT(-4 2)')),
37 (ST_PointFromText('POINT(10 21)'), ST_PointFromText('POINT(-6 3)')),
38 (ST_PointFromText('POINT(20.5 41)'), ST_PointFromText('POINT(-8 4)')),
39 (ST_PointFromText('POINT(26.25 57)'), ST_PointFromText('POINT(1 2)')),
40 (ST_PointFromText('POINT(32.1234 64.2468)'), ST_PointFromText('POINT(-1 -1)'));
41
42 -- echo 'The ORDER BY for spatial index will use filesort'
43 --replace_column 10 #
44 EXPLAIN SELECT ST_AsText(p1), ST_AsText(p2) FROM gis_point ORDER BY p1, p2;
45 SELECT ST_AsText(p1), ST_AsText(p2) FROM gis_point ORDER BY p1, p2;
46
47 -- echo 'Try to do IDU on the table and verify the result'
48 DELETE FROM gis_point WHERE ST_Equals(p2, ST_PointFromText('POINT(-8 4)'));
49
50 INSERT INTO gis_point VALUES(ST_PointFromText('POINT(20.5 -41)'), ST_PointFromText('POINT(8 -4)'));
51
52 SELECT ST_AsText(p1) AS 'Expect (32.1234 64.2468)' FROM gis_point WHERE ST_TOUCHES(@poly3, p1) AND MBRWithin(p2, @poly4);
53
54 UPDATE gis_point SET p1 = ST_PointFromText('POINT(20.5 41)'), p2 = ST_PointFromText('POINT(-8 4)') WHERE ST_Intersection(@ls3, @ls4) = p1;
55
56 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);
57
58 CHECK TABLE gis_point;
59
60 -- echo ============================================================
61 -- echo Use a trx to test the IDU on the table and verify the result
62 -- echo ============================================================
63
64 START TRANSACTION;
65
66 DELETE FROM gis_point WHERE ST_Equals(p2, ST_PointFromText('POINT(-8 4)'));
67
68 INSERT INTO gis_point VALUES(ST_PointFromText('POINT(20.5 -41)'), ST_PointFromText('POINT(8 -4)'));
69
70 SELECT ST_AsText(p1) AS 'Expect (32.1234 64.2468)' FROM gis_point WHERE ST_TOUCHES(@poly3, p1) AND MBRWithin(p2, @poly4);
71
72 UPDATE gis_point SET p1 = ST_PointFromText('POINT(20.5 49)'), p2 = ST_PointFromText('POINT(-8 4)') WHERE ST_Intersection(@ls3, @ls4) = p1;
73
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
76 ROLLBACK;
77
78 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);
79
80 CHECK TABLE gis_point;
81
82 -- echo =======================================================
83 -- echo Enlarge the table by inserting the same data and verify
84 -- echo =======================================================
85
86 INSERT INTO gis_point SELECT * FROM gis_point;
87 INSERT INTO gis_point SELECT * FROM gis_point;
88 INSERT INTO gis_point SELECT * FROM gis_point;
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
97 CHECK TABLE gis_point;
98
99 --sorted_result
100 SELECT COUNT(*), ST_AsText(p1), ST_AsText(p2) FROM gis_point GROUP BY p1, p2;
101
102 --sorted_result
103 SELECT COUNT(*), ST_AsText(p1) FROM gis_point WHERE ST_Intersection(@ls1, @ls2) = p1 GROUP BY p1;
104 SELECT COUNT(*), ST_AsText(p1) FROM gis_point WHERE MBRWithin(p1, @poly1) GROUP BY p1 ORDER BY p1;
105 --sorted_result
106 SELECT COUNT(*), ST_AsText(p2) FROM gis_point WHERE ST_Contains(@poly2, p2) GROUP BY p2;
107 SELECT COUNT(*), ST_AsText(p2) FROM gis_point WHERE ST_Equals(p2, @p1) GROUP BY p2;
108 --sorted_result
109 SELECT COUNT(*), ST_AsText(p1) FROM gis_point WHERE ST_TOUCHES(@poly3, p1) AND MBRWithin(p2, @poly4) GROUP BY p1;
110 --sorted_result
111 SELECT COUNT(*), ST_AsText(p1), ST_AsText(p2) FROM gis_point WHERE ST_Contains(@mpoly, p1) GROUP BY p1, p2;
112 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;
113
114 -- echo ======================================================================
115 -- echo Build another new table with the same schema, will insert data from this table to the orignal one
116 -- echo ======================================================================
117
118 CREATE TABLE p(p1 POINT NOT NULL, p2 POINT NOT NULL SRID 0, SPATIAL INDEX k1(p2)) ENGINE=InnoDB;
119
120 INSERT INTO p VALUES(ST_PointFromText('POINT(1000 -1000)'), ST_PointFromText('POINT(-201 203.56)'));
121 INSERT INTO p VALUES(ST_PointFromText('POINT(20.5 43.9832)'), ST_PointFromText('POINT(-0 0)'));
122 INSERT INTO p VALUES(ST_PointFromText('POINT(-4.2 -6.98)'), ST_PointFromText('POINT(-120.5 343.9832)'));
123
124 INSERT INTO p SELECT * FROM p WHERE MBRWithin(p1, ST_GeomFromText('POLYGON((0 0, 0 2000, 2000 2000, 2000 -2000, 0 -2000, 0 0))'));
125 INSERT INTO p SELECT * FROM p WHERE MBRWithin(p1, ST_GeomFromText('POLYGON((0 0, -10 0, -100 -100, 0 -50, 0 0))'));
126
127 SELECT COUNT(*), ST_AsText(p1), ST_AsText(p2) FROM p GROUP BY p1, p2;
128
129 INSERT INTO gis_point SELECT * FROM p;
130 INSERT INTO gis_point SELECT * FROM p;
131 INSERT INTO gis_point SELECT * FROM p;
132 INSERT INTO gis_point SELECT * FROM p;
133 INSERT INTO gis_point SELECT * FROM p;
134 INSERT INTO gis_point SELECT * FROM p;
135 INSERT INTO gis_point SELECT * FROM p;
136
137 CHECK TABLE gis_point;
138
139 --sorted_result
140 SELECT COUNT(*), ST_AsText(p1), ST_AsText(p2) FROM gis_point GROUP BY p1, p2;
141
142 --sorted_result
143 SELECT COUNT(*), ST_AsText(p1) FROM gis_point WHERE ST_TOUCHES(@poly3, p1) AND MBRWithin(p2, @poly4) GROUP BY p1;
144
145 --sorted_result
146 SELECT COUNT(*), ST_AsText(p2) FROM gis_point WHERE MBRWithin(p2, @poly2) GROUP BY p2;
147
148 DROP TABLE p;
149
150 -- echo ================================
151 -- echo Use a trx to test IUD and verify
152 -- echo ================================
153
154 # Temporarily disabled the following test case, since the ROLLBACK and
155 # some other DML would cost too much of time. We could test them until
156 # we have optimized the UNDO, etc.
157
158 #START TRANSACTION;
159
160 #-- echo 'To remove (-2 -6), (-1 -3)'
161 #DELETE FROM gis_point WHERE ST_Contains(ST_GeomFromText('POLYGON((-8 -8, 4 -7, 1 1, 0 -1, -2 -2, -8 -8))') , p2);
162
163 #SELECT COUNT(*), ST_AsText(p2) FROM gis_point WHERE ST_Contains(@poly2, p2) GROUP BY p2;
164
165 #-- echo 'To set (-1 -1) to (-5.12 -10.84)'
166 #UPDATE gis_point SET p2 = ST_PointFromText('POINT(-5.12 -10.84)') WHERE ST_Intersection(ST_GeomFromText('LINESTRING(-3 4, -2 0, 0.5 -2.5, 5 0)'), ST_GeomFromText('LINESTRING(0.6 0.6, -10 -10)')) = p2;
167
168 #SELECT COUNT(*), ST_AsText(p2) FROM gis_point WHERE MBRWithin(p2, @poly2) GROUP BY p2;
169
170 #-- echo 'To insert several (-3.6 -4.05) (-1 -1)
171 #INSERT INTO gis_point VALUES
172 #(ST_PointFromText('POINT(10.1 2.6)'), ST_PointFromText('POINT(-1 -1)')),
173 #(ST_PointFromText('POINT(100.99 -2.53)'), ST_PointFromText('POINT(-3.6 -4.05)')),
174 #(ST_PointFromText('POINT(13.62 3.87)'), ST_PointFromText('POINT(-1 -1)')),
175 #(ST_PointFromText('POINT(-36.3 0.34)'), ST_PointFromText('POINT(-1 -1)')),
176 #(ST_PointFromText('POINT(1.04 25.6)'), ST_PointFromText('POINT(-3.6 -4.05)'));
177
178 #SELECT COUNT(*), ST_AsText(p2) FROM gis_point WHERE MBRWithin(p2, @poly2) GROUP BY p2;
179
180 #SELECT COUNT(*), ST_AsText(p2) FROM gis_point WHERE ST_Contains(ST_GeomFromText('POLYGON((-0.999 -0.999, -6 -1, -6 -7, -0.99999 -0.99999, -0.999 -0.999))'), p2) GROUP BY p2;
181
182 #ROLLBACK;
183
184 #CHECK TABLE gis_point;
185
186 -- echo =============================================
187 -- echo Delete those rows selected from another table
188 -- echo =============================================
189
190 --sorted_result
191 SELECT COUNT(*), ST_AsText(p1), ST_AsText(p2) FROM gis_point GROUP BY p1, p2;
192
193 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);
194
195 CHECK TABLE gis_point;
196
197 -- echo 'To remove all the just updated rows'
198 DELETE FROM gis_point WHERE ST_Intersection(ST_GeomFromText('LINESTRING(1800 1900, 2200 2100)'), ST_GeomFromText('LINESTRING(0 0, 2001 2001)')) = p2;
199
200 INSERT INTO gis_point VALUES
201 (ST_PointFromText('POINT(8 16)'), ST_PointFromText('POINT(0 0)')),
202 (ST_PointFromText('POINT(8 16)'), ST_PointFromText('POINT(0 0)'));
203
204 -- echo ======================================================================
205 -- echo Following results should be almost the same with those at the beginning
206 -- echo ======================================================================
207
208 --sorted_result
209 SELECT COUNT(*), ST_AsText(p1), ST_AsText(p2) FROM gis_point GROUP BY p1, p2;
210 --sorted_result
211 SELECT COUNT(*), ST_AsText(p1), ST_AsText(p2)
212 FROM gis_point
213 WHERE ST_Contains(ST_GeomFromText('POLYGON((-1000 -1000, -1000 1000, 1000 1000, 1001 -1001, -1000 -1000))'), p1)
214 GROUP BY p1, p2;
215
216 --sorted_result
217 SELECT ST_AsText(p1), ST_AsText(p2) FROM gis_point GROUP BY p1, p2;
218 --sorted_result
219 SELECT COUNT(*), ST_AsText(p1) FROM gis_point WHERE ST_Intersection(@ls1, @ls2) = p1 GROUP BY p1;
220 SELECT COUNT(*), ST_AsText(p1) FROM gis_point WHERE MBRWithin(p1, @poly1) GROUP BY p1 ORDER BY p1;
221 SELECT COUNT(*), ST_AsText(p2) FROM gis_point WHERE ST_Contains(@poly2, p2) GROUP BY p2 ORDER BY p2;
222 SELECT COUNT(*), ST_AsText(p2) FROM gis_point WHERE ST_Equals(p2, @p1) GROUP BY p2;
223 --sorted_result
224 SELECT COUNT(*), ST_AsText(p1) FROM gis_point WHERE ST_TOUCHES(@poly3, p1) AND MBRWithin(p2, @poly4) GROUP BY p1;
225 SELECT COUNT(*), ST_AsText(p1), ST_AsText(p2) FROM gis_point WHERE ST_Contains(@mpoly, p1) GROUP BY p1, p2 ORDER BY p1, p2;
226 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;
227
228 CHECK TABLE gis_point;
229
230 DROP TABLE gis_point;
231