1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
|
# WL#6968 InnoDB R-tree cursor support
# Not supported in embedded
--source include/not_embedded.inc
--source include/not_valgrind.inc
--source include/have_innodb.inc
# Create table with R-tree index.
create table t1 (c1 int, c2 geometry not null, spatial index (c2))engine=innodb;
# Insert enough values to let R-tree split.
delimiter |;
create procedure insert_t1(IN total int)
begin
declare i int default 1;
while (i <= total) DO
insert into t1 values (i, Point(i, i));
set i = i + 1;
end while;
end|
delimiter ;|
# Test level 1 rtree.
CALL insert_t1(1000);
select count(*) from t1;
set @g1 = ST_GeomFromText('Polygon((0 0,0 1000,1000 1000,1000 0,0 0))');
select count(*) from t1 where MBRWithin(t1.c2, @g1);
set @g1 = ST_GeomFromText('Polygon((0 0,0 100,100 100,100 0,0 0))');
select count(*) from t1 where MBRWithin(t1.c2, @g1);
set @g1 = ST_GeomFromText('Polygon((10 10,10 800,800 800,800 10,10 10))');
select count(*) from t1 where MBRWithin(t1.c2, @g1);
set @g1 = ST_GeomFromText('Polygon((100 100,100 800,800 800,800 100,100 100))');
select count(*) from t1 where MBRWithin(t1.c2, @g1);
#SET @save_dbug= @@session.debug_dbug;
#SET debug_dbug = '+d,rtr_pessimistic_position';
#select count(*) from t1 where MBRWithin(t1.c2, @g1);
#SET debug_dbug = @save_dbug;
# Equality search
set @g1 = ST_GeomFromText('Point(1 1)');
select count(*) from t1 where MBRequals(t1.c2, @g1);
# MBRDisjoint search
set @g1 = ST_GeomFromText('Polygon((0 0,0 100,100 100,100 0,0 0))');
select count(*) from t1 where MBRdisjoint(t1.c2, @g1);
# Clean up.
DROP PROCEDURE insert_t1;
truncate t1;
let $1=150;
let $2=150;
while ($1)
{
eval INSERT INTO t1 VALUES ($1, ST_GeomFromText('LineString($1 $1, $2 $2)'));
dec $1;
inc $2;
}
select count(*) from t1;
set @g1 = ST_GeomFromText('Polygon((0 0,0 1000,1000 1000,1000 0,0 0))');
select count(*) from t1 where MBRwithin(t1.c2, @g1);
truncate t1;
INSERT INTO t1 VALUES (1, ST_GeomFromText('LineString(2 2, 150 150)'));
INSERT INTO t1 VALUES (1, ST_GeomFromText('LineString(3 3, 160 160)'));
INSERT INTO t1 VALUES (1, ST_GeomFromText('LineString(4 4, 170 170)'));
INSERT INTO t1 VALUES (1, ST_GeomFromText('LineString(5 5, 180 180)'));
INSERT INTO t1 VALUES (1, ST_GeomFromText('LineString(6 6, 190 190)'));
INSERT INTO t1 VALUES (1, ST_GeomFromText('LineString(7 7, 200 200)'));
INSERT INTO t1 VALUES (1, ST_GeomFromText('LineString(8 8, 210 210)'));
insert into t1 select * from t1;
insert into t1 select * from t1;
insert into t1 select * from t1;
insert into t1 select * from t1;
insert into t1 select * from t1;
insert into t1 select * from t1;
insert into t1 select * from t1;
insert into t1 select * from t1;
insert into t1 select * from t1;
insert into t1 select * from t1;
insert into t1 select * from t1;
# Testing "MBRtouches"
# This is apparently now treated as "intersects"
set @g1 = ST_GeomFromText('Polygon((0 0,0 2, 2 2, 2 0, 0 0))');
select count(*) from t1 where MBRtouches(t1.c2, @g1);
set @g1 = ST_GeomFromText('Polygon((0 0,0 200,200 200,200 0,0 0))');
select count(*) from t1 where MBRWithin(t1.c2, @g1);
# Test MBRequals
set @g1 = ST_GeomFromText('LineString(2 2, 150 150)');
select count(*) from t1 where MBRequals(t1.c2, @g1);
# Test store procedure with open cursor
set @g1 = ST_GeomFromText('Polygon((0 0,0 200,200 200,200 0,0 0))');
create table t3 (a int) engine = innodb;
delimiter |;
CREATE PROCEDURE curdemo()
BEGIN
DECLARE done INT DEFAULT FALSE;
DECLARE a INT;
DECLARE cur1 CURSOR FOR SELECT c1 from t1 where MBRWithin(t1.c2, @g1);
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
OPEN cur1;
read_loop: LOOP
FETCH cur1 INTO a;
IF done THEN
LEAVE read_loop;
END IF;
INSERT INTO test.t3 VALUES (a);
END LOOP;
CLOSE cur1;
END|
delimiter ;|
call curdemo();
select count(*) from t3;
drop procedure curdemo;
drop table t3;
drop table t1;
|
