1# WL#6745 InnoDB R-tree support 2# This test case will test basic R-tree support features. 3 4--source include/have_innodb.inc 5 6# Create table with R-tree index. 7create table t1 (i int, i2 char(10), g geometry not null, primary key (i, i2), spatial index (g))engine=innodb; 8 9# Insert values. 10insert into t1 values (1, "111", POINT(1,1)); 11insert into t1 values (2, "222", POINT(1.5,1.5)); 12insert into t1 values (3, "333", POINT(3,3)); 13insert into t1 values (4, "444", POINT(3.1,3.1)); 14insert into t1 values (5, "555", POINT(5,5)); 15 16analyze table t1; 17 18# Select by R-tree index. 19set @g1 = ST_GeomFromText('Polygon((0 0,0 3,3 3,3 0,0 0))'); 20explain select ST_astext(t1.g) from t1 where MBRWithin(t1.g, @g1); 21select ST_astext(t1.g) from t1 where MBRWithin(t1.g, @g1); 22 23# Delete values. 24set @g1 = ST_GeomFromText('Polygon((0 0,0 3,3 3,3 0,0 0))'); 25delete from t1 where MBRWithin(t1.g, @g1); 26check table t1; 27 28select ST_astext(t1.g) from t1; 29 30# Update values. 31set @g1 = ST_GeomFromText('Polygon((5 5,5 5,5 5,5 5,5 5))'); 32update t1 set g = POINT(2,2) where MBRWithin(t1.g, @g1); 33check table t1; 34 35select ST_astext(t1.g) from t1; 36 37# Show index. 38--replace_column 7 # 39show indexes from t1; 40 41# Cleanup. 42drop table t1; 43 44# Test functions. 45create table t1 (name VARCHAR(100), square GEOMETRY not null, spatial index (square))engine=innodb; 46 47 48INSERT INTO t1 VALUES("small", ST_GeomFromText('POLYGON (( 0 0, 0 1, 1 1, 1 0, 0 0))')); 49INSERT INTO t1 VALUES("big", ST_GeomFromText('POLYGON (( 0 0, 0 3, 3 3, 3 0, 0 0))')); 50 51INSERT INTO t1 VALUES("up", ST_GeomFromText('POLYGON (( 0 1, 0 3, 2 3, 2 1, 0 1))')); 52INSERT INTO t1 VALUES("up2", ST_GeomFromText('POLYGON (( 0 2, 0 4, 2 4, 2 2, 0 2))')); 53INSERT INTO t1 VALUES("up3", ST_GeomFromText('POLYGON (( 0 3, 0 5, 2 5, 2 3, 0 3))')); 54 55INSERT INTO t1 VALUES("down", ST_GeomFromText('POLYGON (( 0 -1, 0 1, 2 1, 2 -1, 0 -1))')); 56INSERT INTO t1 VALUES("down2", ST_GeomFromText('POLYGON (( 0 -2, 0 0, 2 0, 2 -2, 0 -2))')); 57INSERT INTO t1 VALUES("down3", ST_GeomFromText('POLYGON (( 0 -3, 0 -1, 2 -1, 2 -3, 0 -3))')); 58 59INSERT INTO t1 VALUES("right", ST_GeomFromText('POLYGON (( 1 0, 1 2, 3 2, 3 0, 1 0))')); 60INSERT INTO t1 VALUES("right2", ST_GeomFromText('POLYGON (( 2 0, 2 2, 4 2, 4 0, 2 0))')); 61INSERT INTO t1 VALUES("right3", ST_GeomFromText('POLYGON (( 3 0, 3 2, 5 2, 5 0, 3 0))')); 62 63INSERT INTO t1 VALUES("left", ST_GeomFromText('POLYGON (( -1 0, -1 2, 1 2, 1 0, -1 0))')); 64INSERT INTO t1 VALUES("left2", ST_GeomFromText('POLYGON (( -2 0, -2 2, 0 2, 0 0, -2 0))')); 65INSERT INTO t1 VALUES("left3", ST_GeomFromText('POLYGON (( -3 0, -3 2, -1 2, -1 0, -3 0))')); 66 67SET @p = ST_GeomFromText('POLYGON (( 0 0, 0 2, 2 2, 2 0, 0 0))'); 68SELECT name, ST_AsText(square) from t1 where MBRContains(@p, square); 69SELECT name, ST_AsText(square) from t1 where MBRDisjoint(@p, square); 70SELECT name, ST_AsText(square) from t1 where MBREquals(@p, square); 71SELECT name, ST_AsText(square) from t1 where MBRIntersects(@p, square); 72SELECT name, ST_AsText(square) from t1 where MBROverlaps(@p, square); 73SELECT name, ST_AsText(square) from t1 where MBRTouches(@p, square); 74SELECT name, ST_AsText(square) from t1 where MBRWithin(@p, square); 75 76# MBROverlaps needs a few more tests, with point and line dimensions 77 78SELECT GROUP_CONCAT(a1.name ORDER BY a1.name) AS MBRoverlaps FROM t1 a1 WHERE MBROverlaps(a1.square, @vert1) GROUP BY a1.name; 79SELECT GROUP_CONCAT(a1.name ORDER BY a1.name) AS MBRoverlaps FROM t1 a1 WHERE MBROverlaps(a1.square, @horiz1) GROUP BY a1.name; 80SELECT MBROverlaps(@horiz1, @vert1) FROM DUAL; 81SELECT MBROverlaps(@horiz1, @horiz2) FROM DUAL; 82SELECT MBROverlaps(@horiz1, @horiz3) FROM DUAL; 83SELECT MBROverlaps(@horiz1, @point1) FROM DUAL; 84SELECT MBROverlaps(@horiz1, @point2) FROM DUAL; 85 86DROP TABLE t1; 87 88# Inplace create spatial index is not supported 89create table t1 (i int not null, g geometry not null)engine=innodb; 90 91# Insert values. 92insert into t1 values (1, POINT(1,1)); 93insert into t1 values (2, POINT(1.5,1.5)); 94insert into t1 values (3, POINT(3,3)); 95insert into t1 values (4, POINT(3.1,3.1)); 96insert into t1 values (5, POINT(5,5)); 97 98alter table t1 add primary key(i), algorithm=inplace; 99alter table t1 drop primary key; 100 101create spatial index idx on t1(g) algorithm=inplace; 102 103create spatial index idx2 on t1(g); 104 105alter table t1 add primary key(i), algorithm=inplace; 106 107show create table t1; 108 109drop index idx on t1; 110 111drop table t1; 112 113