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