| 1 |
create table t1 (c1 int, c2 geometry not null SRID 0, spatial index (c2))engine=innodb; |
| 2 |
create procedure insert_t1(IN total int) |
| 3 |
begin |
| 4 |
declare i int default 1; |
| 5 |
while (i <= total) DO |
| 6 |
insert into t1 values (i, Point(i, i)); |
| 7 |
set i = i + 1; |
| 8 |
end while; |
| 9 |
end| |
| 10 |
CALL insert_t1(1000); |
| 11 |
select count(*) from t1; |
| 12 |
count(*) |
| 13 |
1000 |
| 14 |
set @g1 = ST_GeomFromText('Polygon((0 0,0 1000,1000 1000,1000 0,0 0))'); |
| 15 |
select count(*) from t1 where MBRWithin(t1.c2, @g1); |
| 16 |
count(*) |
| 17 |
999 |
| 18 |
set @g1 = ST_GeomFromText('Polygon((0 0,0 100,100 100,100 0,0 0))'); |
| 19 |
select count(*) from t1 where MBRWithin(t1.c2, @g1); |
| 20 |
count(*) |
| 21 |
99 |
| 22 |
set @g1 = ST_GeomFromText('Polygon((10 10,10 800,800 800,800 10,10 10))'); |
| 23 |
select count(*) from t1 where MBRWithin(t1.c2, @g1); |
| 24 |
count(*) |
| 25 |
789 |
| 26 |
set @g1 = ST_GeomFromText('Polygon((100 100,100 800,800 800,800 100,100 100))'); |
| 27 |
select count(*) from t1 where MBRWithin(t1.c2, @g1); |
| 28 |
count(*) |
| 29 |
699 |
| 30 |
set @g1 = ST_GeomFromText('Point(1 1)'); |
| 31 |
select count(*) from t1 where MBRequals(t1.c2, @g1); |
| 32 |
count(*) |
| 33 |
1 |
| 34 |
set @g1 = ST_GeomFromText('Polygon((0 0,0 100,100 100,100 0,0 0))'); |
| 35 |
select count(*) from t1 where MBRdisjoint(t1.c2, @g1); |
| 36 |
count(*) |
| 37 |
900 |
| 38 |
DROP PROCEDURE insert_t1; |
| 39 |
truncate t1; |
| 40 |
INSERT INTO t1 VALUES (150, ST_GeomFromText('LineString(150 150, 150 150)')); |
| 41 |
INSERT INTO t1 VALUES (149, ST_GeomFromText('LineString(149 149, 151 151)')); |
| 42 |
INSERT INTO t1 VALUES (148, ST_GeomFromText('LineString(148 148, 152 152)')); |
| 43 |
INSERT INTO t1 VALUES (147, ST_GeomFromText('LineString(147 147, 153 153)')); |
| 44 |
INSERT INTO t1 VALUES (146, ST_GeomFromText('LineString(146 146, 154 154)')); |
| 45 |
INSERT INTO t1 VALUES (145, ST_GeomFromText('LineString(145 145, 155 155)')); |
| 46 |
INSERT INTO t1 VALUES (144, ST_GeomFromText('LineString(144 144, 156 156)')); |
| 47 |
INSERT INTO t1 VALUES (143, ST_GeomFromText('LineString(143 143, 157 157)')); |
| 48 |
INSERT INTO t1 VALUES (142, ST_GeomFromText('LineString(142 142, 158 158)')); |
| 49 |
INSERT INTO t1 VALUES (141, ST_GeomFromText('LineString(141 141, 159 159)')); |
| 50 |
INSERT INTO t1 VALUES (140, ST_GeomFromText('LineString(140 140, 160 160)')); |
| 51 |
INSERT INTO t1 VALUES (139, ST_GeomFromText('LineString(139 139, 161 161)')); |
| 52 |
INSERT INTO t1 VALUES (138, ST_GeomFromText('LineString(138 138, 162 162)')); |
| 53 |
INSERT INTO t1 VALUES (137, ST_GeomFromText('LineString(137 137, 163 163)')); |
| 54 |
INSERT INTO t1 VALUES (136, ST_GeomFromText('LineString(136 136, 164 164)')); |
| 55 |
INSERT INTO t1 VALUES (135, ST_GeomFromText('LineString(135 135, 165 165)')); |
| 56 |
INSERT INTO t1 VALUES (134, ST_GeomFromText('LineString(134 134, 166 166)')); |
| 57 |
INSERT INTO t1 VALUES (133, ST_GeomFromText('LineString(133 133, 167 167)')); |
| 58 |
INSERT INTO t1 VALUES (132, ST_GeomFromText('LineString(132 132, 168 168)')); |
| 59 |
INSERT INTO t1 VALUES (131, ST_GeomFromText('LineString(131 131, 169 169)')); |
| 60 |
INSERT INTO t1 VALUES (130, ST_GeomFromText('LineString(130 130, 170 170)')); |
| 61 |
INSERT INTO t1 VALUES (129, ST_GeomFromText('LineString(129 129, 171 171)')); |
| 62 |
INSERT INTO t1 VALUES (128, ST_GeomFromText('LineString(128 128, 172 172)')); |
| 63 |
INSERT INTO t1 VALUES (127, ST_GeomFromText('LineString(127 127, 173 173)')); |
| 64 |
INSERT INTO t1 VALUES (126, ST_GeomFromText('LineString(126 126, 174 174)')); |
| 65 |
INSERT INTO t1 VALUES (125, ST_GeomFromText('LineString(125 125, 175 175)')); |
| 66 |
INSERT INTO t1 VALUES (124, ST_GeomFromText('LineString(124 124, 176 176)')); |
| 67 |
INSERT INTO t1 VALUES (123, ST_GeomFromText('LineString(123 123, 177 177)')); |
| 68 |
INSERT INTO t1 VALUES (122, ST_GeomFromText('LineString(122 122, 178 178)')); |
| 69 |
INSERT INTO t1 VALUES (121, ST_GeomFromText('LineString(121 121, 179 179)')); |
| 70 |
INSERT INTO t1 VALUES (120, ST_GeomFromText('LineString(120 120, 180 180)')); |
| 71 |
INSERT INTO t1 VALUES (119, ST_GeomFromText('LineString(119 119, 181 181)')); |
| 72 |
INSERT INTO t1 VALUES (118, ST_GeomFromText('LineString(118 118, 182 182)')); |
| 73 |
INSERT INTO t1 VALUES (117, ST_GeomFromText('LineString(117 117, 183 183)')); |
| 74 |
INSERT INTO t1 VALUES (116, ST_GeomFromText('LineString(116 116, 184 184)')); |
| 75 |
INSERT INTO t1 VALUES (115, ST_GeomFromText('LineString(115 115, 185 185)')); |
| 76 |
INSERT INTO t1 VALUES (114, ST_GeomFromText('LineString(114 114, 186 186)')); |
| 77 |
INSERT INTO t1 VALUES (113, ST_GeomFromText('LineString(113 113, 187 187)')); |
| 78 |
INSERT INTO t1 VALUES (112, ST_GeomFromText('LineString(112 112, 188 188)')); |
| 79 |
INSERT INTO t1 VALUES (111, ST_GeomFromText('LineString(111 111, 189 189)')); |
| 80 |
INSERT INTO t1 VALUES (110, ST_GeomFromText('LineString(110 110, 190 190)')); |
| 81 |
INSERT INTO t1 VALUES (109, ST_GeomFromText('LineString(109 109, 191 191)')); |
| 82 |
INSERT INTO t1 VALUES (108, ST_GeomFromText('LineString(108 108, 192 192)')); |
| 83 |
INSERT INTO t1 VALUES (107, ST_GeomFromText('LineString(107 107, 193 193)')); |
| 84 |
INSERT INTO t1 VALUES (106, ST_GeomFromText('LineString(106 106, 194 194)')); |
| 85 |
INSERT INTO t1 VALUES (105, ST_GeomFromText('LineString(105 105, 195 195)')); |
| 86 |
INSERT INTO t1 VALUES (104, ST_GeomFromText('LineString(104 104, 196 196)')); |
| 87 |
INSERT INTO t1 VALUES (103, ST_GeomFromText('LineString(103 103, 197 197)')); |
| 88 |
INSERT INTO t1 VALUES (102, ST_GeomFromText('LineString(102 102, 198 198)')); |
| 89 |
INSERT INTO t1 VALUES (101, ST_GeomFromText('LineString(101 101, 199 199)')); |
| 90 |
INSERT INTO t1 VALUES (100, ST_GeomFromText('LineString(100 100, 200 200)')); |
| 91 |
INSERT INTO t1 VALUES (99, ST_GeomFromText('LineString(99 99, 201 201)')); |
| 92 |
INSERT INTO t1 VALUES (98, ST_GeomFromText('LineString(98 98, 202 202)')); |
| 93 |
INSERT INTO t1 VALUES (97, ST_GeomFromText('LineString(97 97, 203 203)')); |
| 94 |
INSERT INTO t1 VALUES (96, ST_GeomFromText('LineString(96 96, 204 204)')); |
| 95 |
INSERT INTO t1 VALUES (95, ST_GeomFromText('LineString(95 95, 205 205)')); |
| 96 |
INSERT INTO t1 VALUES (94, ST_GeomFromText('LineString(94 94, 206 206)')); |
| 97 |
INSERT INTO t1 VALUES (93, ST_GeomFromText('LineString(93 93, 207 207)')); |
| 98 |
INSERT INTO t1 VALUES (92, ST_GeomFromText('LineString(92 92, 208 208)')); |
| 99 |
INSERT INTO t1 VALUES (91, ST_GeomFromText('LineString(91 91, 209 209)')); |
| 100 |
INSERT INTO t1 VALUES (90, ST_GeomFromText('LineString(90 90, 210 210)')); |
| 101 |
INSERT INTO t1 VALUES (89, ST_GeomFromText('LineString(89 89, 211 211)')); |
| 102 |
INSERT INTO t1 VALUES (88, ST_GeomFromText('LineString(88 88, 212 212)')); |
| 103 |
INSERT INTO t1 VALUES (87, ST_GeomFromText('LineString(87 87, 213 213)')); |
| 104 |
INSERT INTO t1 VALUES (86, ST_GeomFromText('LineString(86 86, 214 214)')); |
| 105 |
INSERT INTO t1 VALUES (85, ST_GeomFromText('LineString(85 85, 215 215)')); |
| 106 |
INSERT INTO t1 VALUES (84, ST_GeomFromText('LineString(84 84, 216 216)')); |
| 107 |
INSERT INTO t1 VALUES (83, ST_GeomFromText('LineString(83 83, 217 217)')); |
| 108 |
INSERT INTO t1 VALUES (82, ST_GeomFromText('LineString(82 82, 218 218)')); |
| 109 |
INSERT INTO t1 VALUES (81, ST_GeomFromText('LineString(81 81, 219 219)')); |
| 110 |
INSERT INTO t1 VALUES (80, ST_GeomFromText('LineString(80 80, 220 220)')); |
| 111 |
INSERT INTO t1 VALUES (79, ST_GeomFromText('LineString(79 79, 221 221)')); |
| 112 |
INSERT INTO t1 VALUES (78, ST_GeomFromText('LineString(78 78, 222 222)')); |
| 113 |
INSERT INTO t1 VALUES (77, ST_GeomFromText('LineString(77 77, 223 223)')); |
| 114 |
INSERT INTO t1 VALUES (76, ST_GeomFromText('LineString(76 76, 224 224)')); |
| 115 |
INSERT INTO t1 VALUES (75, ST_GeomFromText('LineString(75 75, 225 225)')); |
| 116 |
INSERT INTO t1 VALUES (74, ST_GeomFromText('LineString(74 74, 226 226)')); |
| 117 |
INSERT INTO t1 VALUES (73, ST_GeomFromText('LineString(73 73, 227 227)')); |
| 118 |
INSERT INTO t1 VALUES (72, ST_GeomFromText('LineString(72 72, 228 228)')); |
| 119 |
INSERT INTO t1 VALUES (71, ST_GeomFromText('LineString(71 71, 229 229)')); |
| 120 |
INSERT INTO t1 VALUES (70, ST_GeomFromText('LineString(70 70, 230 230)')); |
| 121 |
INSERT INTO t1 VALUES (69, ST_GeomFromText('LineString(69 69, 231 231)')); |
| 122 |
INSERT INTO t1 VALUES (68, ST_GeomFromText('LineString(68 68, 232 232)')); |
| 123 |
INSERT INTO t1 VALUES (67, ST_GeomFromText('LineString(67 67, 233 233)')); |
| 124 |
INSERT INTO t1 VALUES (66, ST_GeomFromText('LineString(66 66, 234 234)')); |
| 125 |
INSERT INTO t1 VALUES (65, ST_GeomFromText('LineString(65 65, 235 235)')); |
| 126 |
INSERT INTO t1 VALUES (64, ST_GeomFromText('LineString(64 64, 236 236)')); |
| 127 |
INSERT INTO t1 VALUES (63, ST_GeomFromText('LineString(63 63, 237 237)')); |
| 128 |
INSERT INTO t1 VALUES (62, ST_GeomFromText('LineString(62 62, 238 238)')); |
| 129 |
INSERT INTO t1 VALUES (61, ST_GeomFromText('LineString(61 61, 239 239)')); |
| 130 |
INSERT INTO t1 VALUES (60, ST_GeomFromText('LineString(60 60, 240 240)')); |
| 131 |
INSERT INTO t1 VALUES (59, ST_GeomFromText('LineString(59 59, 241 241)')); |
| 132 |
INSERT INTO t1 VALUES (58, ST_GeomFromText('LineString(58 58, 242 242)')); |
| 133 |
INSERT INTO t1 VALUES (57, ST_GeomFromText('LineString(57 57, 243 243)')); |
| 134 |
INSERT INTO t1 VALUES (56, ST_GeomFromText('LineString(56 56, 244 244)')); |
| 135 |
INSERT INTO t1 VALUES (55, ST_GeomFromText('LineString(55 55, 245 245)')); |
| 136 |
INSERT INTO t1 VALUES (54, ST_GeomFromText('LineString(54 54, 246 246)')); |
| 137 |
INSERT INTO t1 VALUES (53, ST_GeomFromText('LineString(53 53, 247 247)')); |
| 138 |
INSERT INTO t1 VALUES (52, ST_GeomFromText('LineString(52 52, 248 248)')); |
| 139 |
INSERT INTO t1 VALUES (51, ST_GeomFromText('LineString(51 51, 249 249)')); |
| 140 |
INSERT INTO t1 VALUES (50, ST_GeomFromText('LineString(50 50, 250 250)')); |
| 141 |
INSERT INTO t1 VALUES (49, ST_GeomFromText('LineString(49 49, 251 251)')); |
| 142 |
INSERT INTO t1 VALUES (48, ST_GeomFromText('LineString(48 48, 252 252)')); |
| 143 |
INSERT INTO t1 VALUES (47, ST_GeomFromText('LineString(47 47, 253 253)')); |
| 144 |
INSERT INTO t1 VALUES (46, ST_GeomFromText('LineString(46 46, 254 254)')); |
| 145 |
INSERT INTO t1 VALUES (45, ST_GeomFromText('LineString(45 45, 255 255)')); |
| 146 |
INSERT INTO t1 VALUES (44, ST_GeomFromText('LineString(44 44, 256 256)')); |
| 147 |
INSERT INTO t1 VALUES (43, ST_GeomFromText('LineString(43 43, 257 257)')); |
| 148 |
INSERT INTO t1 VALUES (42, ST_GeomFromText('LineString(42 42, 258 258)')); |
| 149 |
INSERT INTO t1 VALUES (41, ST_GeomFromText('LineString(41 41, 259 259)')); |
| 150 |
INSERT INTO t1 VALUES (40, ST_GeomFromText('LineString(40 40, 260 260)')); |
| 151 |
INSERT INTO t1 VALUES (39, ST_GeomFromText('LineString(39 39, 261 261)')); |
| 152 |
INSERT INTO t1 VALUES (38, ST_GeomFromText('LineString(38 38, 262 262)')); |
| 153 |
INSERT INTO t1 VALUES (37, ST_GeomFromText('LineString(37 37, 263 263)')); |
| 154 |
INSERT INTO t1 VALUES (36, ST_GeomFromText('LineString(36 36, 264 264)')); |
| 155 |
INSERT INTO t1 VALUES (35, ST_GeomFromText('LineString(35 35, 265 265)')); |
| 156 |
INSERT INTO t1 VALUES (34, ST_GeomFromText('LineString(34 34, 266 266)')); |
| 157 |
INSERT INTO t1 VALUES (33, ST_GeomFromText('LineString(33 33, 267 267)')); |
| 158 |
INSERT INTO t1 VALUES (32, ST_GeomFromText('LineString(32 32, 268 268)')); |
| 159 |
INSERT INTO t1 VALUES (31, ST_GeomFromText('LineString(31 31, 269 269)')); |
| 160 |
INSERT INTO t1 VALUES (30, ST_GeomFromText('LineString(30 30, 270 270)')); |
| 161 |
INSERT INTO t1 VALUES (29, ST_GeomFromText('LineString(29 29, 271 271)')); |
| 162 |
INSERT INTO t1 VALUES (28, ST_GeomFromText('LineString(28 28, 272 272)')); |
| 163 |
INSERT INTO t1 VALUES (27, ST_GeomFromText('LineString(27 27, 273 273)')); |
| 164 |
INSERT INTO t1 VALUES (26, ST_GeomFromText('LineString(26 26, 274 274)')); |
| 165 |
INSERT INTO t1 VALUES (25, ST_GeomFromText('LineString(25 25, 275 275)')); |
| 166 |
INSERT INTO t1 VALUES (24, ST_GeomFromText('LineString(24 24, 276 276)')); |
| 167 |
INSERT INTO t1 VALUES (23, ST_GeomFromText('LineString(23 23, 277 277)')); |
| 168 |
INSERT INTO t1 VALUES (22, ST_GeomFromText('LineString(22 22, 278 278)')); |
| 169 |
INSERT INTO t1 VALUES (21, ST_GeomFromText('LineString(21 21, 279 279)')); |
| 170 |
INSERT INTO t1 VALUES (20, ST_GeomFromText('LineString(20 20, 280 280)')); |
| 171 |
INSERT INTO t1 VALUES (19, ST_GeomFromText('LineString(19 19, 281 281)')); |
| 172 |
INSERT INTO t1 VALUES (18, ST_GeomFromText('LineString(18 18, 282 282)')); |
| 173 |
INSERT INTO t1 VALUES (17, ST_GeomFromText('LineString(17 17, 283 283)')); |
| 174 |
INSERT INTO t1 VALUES (16, ST_GeomFromText('LineString(16 16, 284 284)')); |
| 175 |
INSERT INTO t1 VALUES (15, ST_GeomFromText('LineString(15 15, 285 285)')); |
| 176 |
INSERT INTO t1 VALUES (14, ST_GeomFromText('LineString(14 14, 286 286)')); |
| 177 |
INSERT INTO t1 VALUES (13, ST_GeomFromText('LineString(13 13, 287 287)')); |
| 178 |
INSERT INTO t1 VALUES (12, ST_GeomFromText('LineString(12 12, 288 288)')); |
| 179 |
INSERT INTO t1 VALUES (11, ST_GeomFromText('LineString(11 11, 289 289)')); |
| 180 |
INSERT INTO t1 VALUES (10, ST_GeomFromText('LineString(10 10, 290 290)')); |
| 181 |
INSERT INTO t1 VALUES (9, ST_GeomFromText('LineString(9 9, 291 291)')); |
| 182 |
INSERT INTO t1 VALUES (8, ST_GeomFromText('LineString(8 8, 292 292)')); |
| 183 |
INSERT INTO t1 VALUES (7, ST_GeomFromText('LineString(7 7, 293 293)')); |
| 184 |
INSERT INTO t1 VALUES (6, ST_GeomFromText('LineString(6 6, 294 294)')); |
| 185 |
INSERT INTO t1 VALUES (5, ST_GeomFromText('LineString(5 5, 295 295)')); |
| 186 |
INSERT INTO t1 VALUES (4, ST_GeomFromText('LineString(4 4, 296 296)')); |
| 187 |
INSERT INTO t1 VALUES (3, ST_GeomFromText('LineString(3 3, 297 297)')); |
| 188 |
INSERT INTO t1 VALUES (2, ST_GeomFromText('LineString(2 2, 298 298)')); |
| 189 |
INSERT INTO t1 VALUES (1, ST_GeomFromText('LineString(1 1, 299 299)')); |
| 190 |
select count(*) from t1; |
| 191 |
count(*) |
| 192 |
150 |
| 193 |
set @g1 = ST_GeomFromText('Polygon((0 0,0 1000,1000 1000,1000 0,0 0))'); |
| 194 |
select count(*) from t1 where MBRwithin(t1.c2, @g1); |
| 195 |
count(*) |
| 196 |
150 |
| 197 |
truncate t1; |
| 198 |
INSERT INTO t1 VALUES (1, ST_GeomFromText('LineString(2 2, 150 150)')); |
| 199 |
INSERT INTO t1 VALUES (1, ST_GeomFromText('LineString(3 3, 160 160)')); |
| 200 |
INSERT INTO t1 VALUES (1, ST_GeomFromText('LineString(4 4, 170 170)')); |
| 201 |
INSERT INTO t1 VALUES (1, ST_GeomFromText('LineString(5 5, 180 180)')); |
| 202 |
INSERT INTO t1 VALUES (1, ST_GeomFromText('LineString(6 6, 190 190)')); |
| 203 |
INSERT INTO t1 VALUES (1, ST_GeomFromText('LineString(7 7, 200 200)')); |
| 204 |
INSERT INTO t1 VALUES (1, ST_GeomFromText('LineString(8 8, 210 210)')); |
| 205 |
insert into t1 select * from t1; |
| 206 |
insert into t1 select * from t1; |
| 207 |
insert into t1 select * from t1; |
| 208 |
insert into t1 select * from t1; |
| 209 |
insert into t1 select * from t1; |
| 210 |
insert into t1 select * from t1; |
| 211 |
insert into t1 select * from t1; |
| 212 |
insert into t1 select * from t1; |
| 213 |
insert into t1 select * from t1; |
| 214 |
insert into t1 select * from t1; |
| 215 |
insert into t1 select * from t1; |
| 216 |
set @g1 = ST_GeomFromText('Polygon((0 0,0 2, 2 2, 2 0, 0 0))'); |
| 217 |
select count(*) from t1 where MBRtouches(t1.c2, @g1); |
| 218 |
count(*) |
| 219 |
2048 |
| 220 |
set @g1 = ST_GeomFromText('Polygon((0 0,0 200,200 200,200 0,0 0))'); |
| 221 |
select count(*) from t1 where MBRWithin(t1.c2, @g1); |
| 222 |
count(*) |
| 223 |
12288 |
| 224 |
set @g1 = ST_GeomFromText('LineString(2 2, 150 150)'); |
| 225 |
select count(*) from t1 where MBRequals(t1.c2, @g1); |
| 226 |
count(*) |
| 227 |
2048 |
| 228 |
set @g1 = ST_GeomFromText('Polygon((0 0,0 200,200 200,200 0,0 0))'); |
| 229 |
create table t3 (a int) engine = innodb; |
| 230 |
CREATE PROCEDURE curdemo() |
| 231 |
BEGIN |
| 232 |
DECLARE done INT DEFAULT FALSE; |
| 233 |
DECLARE a INT; |
| 234 |
DECLARE cur1 CURSOR FOR SELECT c1 from t1 where MBRWithin(t1.c2, @g1); |
| 235 |
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE; |
| 236 |
OPEN cur1; |
| 237 |
read_loop: LOOP |
| 238 |
FETCH cur1 INTO a; |
| 239 |
IF done THEN |
| 240 |
LEAVE read_loop; |
| 241 |
END IF; |
| 242 |
INSERT INTO test.t3 VALUES (a); |
| 243 |
END LOOP; |
| 244 |
CLOSE cur1; |
| 245 |
END| |
| 246 |
call curdemo(); |
| 247 |
select count(*) from t3; |
| 248 |
count(*) |
| 249 |
12288 |
| 250 |
drop procedure curdemo; |
| 251 |
drop table t3; |
| 252 |
drop table t1; |