| 1 |
create table t1 (i int, i2 char(10), g geometry not null, primary key (i, i2), spatial index (g))engine=innodb; |
| 2 |
insert into t1 values (1, "111", POINT(1,1)); |
| 3 |
insert into t1 values (2, "222", POINT(1.5,1.5)); |
| 4 |
insert into t1 values (3, "333", POINT(3,3)); |
| 5 |
insert into t1 values (4, "444", POINT(3.1,3.1)); |
| 6 |
insert into t1 values (5, "555", POINT(5,5)); |
| 7 |
analyze table t1; |
| 8 |
Table Op Msg_type Msg_text |
| 9 |
test.t1 analyze status Engine-independent statistics collected |
| 10 |
test.t1 analyze status OK |
| 11 |
set @g1 = ST_GeomFromText('Polygon((0 0,0 3,3 3,3 0,0 0))'); |
| 12 |
explain select ST_astext(t1.g) from t1 where MBRWithin(t1.g, @g1); |
| 13 |
id select_type table type possible_keys key key_len ref rows Extra |
| 14 |
1 SIMPLE t1 range g g 34 NULL 5 Using where |
| 15 |
select ST_astext(t1.g) from t1 where MBRWithin(t1.g, @g1); |
| 16 |
ST_astext(t1.g) |
| 17 |
POINT(1.5 1.5) |
| 18 |
POINT(1 1) |
| 19 |
set @g1 = ST_GeomFromText('Polygon((0 0,0 3,3 3,3 0,0 0))'); |
| 20 |
delete from t1 where MBRWithin(t1.g, @g1); |
| 21 |
check table t1; |
| 22 |
Table Op Msg_type Msg_text |
| 23 |
test.t1 check status OK |
| 24 |
select ST_astext(t1.g) from t1; |
| 25 |
ST_astext(t1.g) |
| 26 |
POINT(3 3) |
| 27 |
POINT(3.1 3.1) |
| 28 |
POINT(5 5) |
| 29 |
set @g1 = ST_GeomFromText('Polygon((5 5,5 5,5 5,5 5,5 5))'); |
| 30 |
update t1 set g = POINT(2,2) where MBRWithin(t1.g, @g1); |
| 31 |
check table t1; |
| 32 |
Table Op Msg_type Msg_text |
| 33 |
test.t1 check status OK |
| 34 |
select ST_astext(t1.g) from t1; |
| 35 |
ST_astext(t1.g) |
| 36 |
POINT(3 3) |
| 37 |
POINT(3.1 3.1) |
| 38 |
POINT(2 2) |
| 39 |
show indexes from t1; |
| 40 |
Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment Ignored |
| 41 |
t1 0 PRIMARY 1 i A # NULL NULL BTREE NO |
| 42 |
t1 0 PRIMARY 2 i2 A # NULL NULL BTREE NO |
| 43 |
t1 1 g 1 g A # 32 NULL SPATIAL NO |
| 44 |
drop table t1; |
| 45 |
create table t1 (name VARCHAR(100), square GEOMETRY not null, spatial index (square))engine=innodb; |
| 46 |
INSERT INTO t1 VALUES("small", ST_GeomFromText('POLYGON (( 0 0, 0 1, 1 1, 1 0, 0 0))')); |
| 47 |
INSERT INTO t1 VALUES("big", ST_GeomFromText('POLYGON (( 0 0, 0 3, 3 3, 3 0, 0 0))')); |
| 48 |
INSERT INTO t1 VALUES("up", ST_GeomFromText('POLYGON (( 0 1, 0 3, 2 3, 2 1, 0 1))')); |
| 49 |
INSERT INTO t1 VALUES("up2", ST_GeomFromText('POLYGON (( 0 2, 0 4, 2 4, 2 2, 0 2))')); |
| 50 |
INSERT INTO t1 VALUES("up3", ST_GeomFromText('POLYGON (( 0 3, 0 5, 2 5, 2 3, 0 3))')); |
| 51 |
INSERT INTO t1 VALUES("down", ST_GeomFromText('POLYGON (( 0 -1, 0 1, 2 1, 2 -1, 0 -1))')); |
| 52 |
INSERT INTO t1 VALUES("down2", ST_GeomFromText('POLYGON (( 0 -2, 0 0, 2 0, 2 -2, 0 -2))')); |
| 53 |
INSERT INTO t1 VALUES("down3", ST_GeomFromText('POLYGON (( 0 -3, 0 -1, 2 -1, 2 -3, 0 -3))')); |
| 54 |
INSERT INTO t1 VALUES("right", ST_GeomFromText('POLYGON (( 1 0, 1 2, 3 2, 3 0, 1 0))')); |
| 55 |
INSERT INTO t1 VALUES("right2", ST_GeomFromText('POLYGON (( 2 0, 2 2, 4 2, 4 0, 2 0))')); |
| 56 |
INSERT INTO t1 VALUES("right3", ST_GeomFromText('POLYGON (( 3 0, 3 2, 5 2, 5 0, 3 0))')); |
| 57 |
INSERT INTO t1 VALUES("left", ST_GeomFromText('POLYGON (( -1 0, -1 2, 1 2, 1 0, -1 0))')); |
| 58 |
INSERT INTO t1 VALUES("left2", ST_GeomFromText('POLYGON (( -2 0, -2 2, 0 2, 0 0, -2 0))')); |
| 59 |
INSERT INTO t1 VALUES("left3", ST_GeomFromText('POLYGON (( -3 0, -3 2, -1 2, -1 0, -3 0))')); |
| 60 |
SET @p = ST_GeomFromText('POLYGON (( 0 0, 0 2, 2 2, 2 0, 0 0))'); |
| 61 |
SELECT name, ST_AsText(square) from t1 where MBRContains(@p, square); |
| 62 |
name ST_AsText(square) |
| 63 |
small POLYGON((0 0,0 1,1 1,1 0,0 0)) |
| 64 |
SELECT name, ST_AsText(square) from t1 where MBRDisjoint(@p, square); |
| 65 |
name ST_AsText(square) |
| 66 |
down3 POLYGON((0 -3,0 -1,2 -1,2 -3,0 -3)) |
| 67 |
left3 POLYGON((-3 0,-3 2,-1 2,-1 0,-3 0)) |
| 68 |
right3 POLYGON((3 0,3 2,5 2,5 0,3 0)) |
| 69 |
up3 POLYGON((0 3,0 5,2 5,2 3,0 3)) |
| 70 |
SELECT name, ST_AsText(square) from t1 where MBREquals(@p, square); |
| 71 |
name ST_AsText(square) |
| 72 |
SELECT name, ST_AsText(square) from t1 where MBRIntersects(@p, square); |
| 73 |
name ST_AsText(square) |
| 74 |
right2 POLYGON((2 0,2 2,4 2,4 0,2 0)) |
| 75 |
right POLYGON((1 0,1 2,3 2,3 0,1 0)) |
| 76 |
up2 POLYGON((0 2,0 4,2 4,2 2,0 2)) |
| 77 |
up POLYGON((0 1,0 3,2 3,2 1,0 1)) |
| 78 |
big POLYGON((0 0,0 3,3 3,3 0,0 0)) |
| 79 |
small POLYGON((0 0,0 1,1 1,1 0,0 0)) |
| 80 |
down POLYGON((0 -1,0 1,2 1,2 -1,0 -1)) |
| 81 |
down2 POLYGON((0 -2,0 0,2 0,2 -2,0 -2)) |
| 82 |
left POLYGON((-1 0,-1 2,1 2,1 0,-1 0)) |
| 83 |
left2 POLYGON((-2 0,-2 2,0 2,0 0,-2 0)) |
| 84 |
SELECT name, ST_AsText(square) from t1 where MBROverlaps(@p, square); |
| 85 |
name ST_AsText(square) |
| 86 |
right POLYGON((1 0,1 2,3 2,3 0,1 0)) |
| 87 |
up POLYGON((0 1,0 3,2 3,2 1,0 1)) |
| 88 |
down POLYGON((0 -1,0 1,2 1,2 -1,0 -1)) |
| 89 |
left POLYGON((-1 0,-1 2,1 2,1 0,-1 0)) |
| 90 |
SELECT name, ST_AsText(square) from t1 where MBRTouches(@p, square); |
| 91 |
name ST_AsText(square) |
| 92 |
right2 POLYGON((2 0,2 2,4 2,4 0,2 0)) |
| 93 |
up2 POLYGON((0 2,0 4,2 4,2 2,0 2)) |
| 94 |
down2 POLYGON((0 -2,0 0,2 0,2 -2,0 -2)) |
| 95 |
left2 POLYGON((-2 0,-2 2,0 2,0 0,-2 0)) |
| 96 |
SELECT name, ST_AsText(square) from t1 where MBRWithin(@p, square); |
| 97 |
name ST_AsText(square) |
| 98 |
big POLYGON((0 0,0 3,3 3,3 0,0 0)) |
| 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 |
MBRoverlaps |
| 101 |
SELECT GROUP_CONCAT(a1.name ORDER BY a1.name) AS MBRoverlaps FROM t1 a1 WHERE MBROverlaps(a1.square, @horiz1) GROUP BY a1.name; |
| 102 |
MBRoverlaps |
| 103 |
SELECT MBROverlaps(@horiz1, @vert1) FROM DUAL; |
| 104 |
MBROverlaps(@horiz1, @vert1) |
| 105 |
NULL |
| 106 |
SELECT MBROverlaps(@horiz1, @horiz2) FROM DUAL; |
| 107 |
MBROverlaps(@horiz1, @horiz2) |
| 108 |
NULL |
| 109 |
SELECT MBROverlaps(@horiz1, @horiz3) FROM DUAL; |
| 110 |
MBROverlaps(@horiz1, @horiz3) |
| 111 |
NULL |
| 112 |
SELECT MBROverlaps(@horiz1, @point1) FROM DUAL; |
| 113 |
MBROverlaps(@horiz1, @point1) |
| 114 |
NULL |
| 115 |
SELECT MBROverlaps(@horiz1, @point2) FROM DUAL; |
| 116 |
MBROverlaps(@horiz1, @point2) |
| 117 |
NULL |
| 118 |
DROP TABLE t1; |
| 119 |
create table t1 (i int not null, g geometry not null)engine=innodb; |
| 120 |
insert into t1 values (1, POINT(1,1)); |
| 121 |
insert into t1 values (2, POINT(1.5,1.5)); |
| 122 |
insert into t1 values (3, POINT(3,3)); |
| 123 |
insert into t1 values (4, POINT(3.1,3.1)); |
| 124 |
insert into t1 values (5, POINT(5,5)); |
| 125 |
alter table t1 add primary key(i), algorithm=inplace; |
| 126 |
alter table t1 drop primary key; |
| 127 |
create spatial index idx on t1(g) algorithm=inplace; |
| 128 |
create spatial index idx2 on t1(g); |
| 129 |
Warnings: |
| 130 |
Note 1831 Duplicate index `idx2`. This is deprecated and will be disallowed in a future release |
| 131 |
alter table t1 add primary key(i), algorithm=inplace; |
| 132 |
show create table t1; |
| 133 |
Table Create Table |
| 134 |
t1 CREATE TABLE `t1` ( |
| 135 |
`i` int(11) NOT NULL, |
| 136 |
`g` geometry NOT NULL, |
| 137 |
PRIMARY KEY (`i`), |
| 138 |
SPATIAL KEY `idx` (`g`), |
| 139 |
SPATIAL KEY `idx2` (`g`) |
| 140 |
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci |
| 141 |
drop index idx on t1; |
| 142 |
drop table t1; |