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