159 lines
7.1 KiB
Plaintext
159 lines
7.1 KiB
Plaintext
create table t1 (i int, i2 char(10), g geometry not null SRID 0, primary key (i, i2), spatial index (g))engine=innodb;
|
|
insert into t1 values (1, "111", POINT(1,1));
|
|
insert into t1 values (2, "222", POINT(1.5,1.5));
|
|
insert into t1 values (3, "333", POINT(3,3));
|
|
insert into t1 values (4, "444", POINT(3.1,3.1));
|
|
insert into t1 values (5, "555", POINT(5,5));
|
|
analyze table t1;
|
|
Table Op Msg_type Msg_text
|
|
test.t1 analyze status OK
|
|
set @g1 = ST_GeomFromText('Polygon((0 0,0 3,3 3,3 0,0 0))');
|
|
explain select ST_astext(t1.g) from t1 where MBRWithin(t1.g, @g1);
|
|
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
|
|
1 SIMPLE t1 NULL range g g 34 NULL 5 100.00 Using where
|
|
Warnings:
|
|
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`)))
|
|
select ST_astext(t1.g) from t1 where MBRWithin(t1.g, @g1);
|
|
ST_astext(t1.g)
|
|
POINT(1.5 1.5)
|
|
POINT(1 1)
|
|
set @g1 = ST_GeomFromText('Polygon((0 0,0 3,3 3,3 0,0 0))');
|
|
delete from t1 where MBRWithin(t1.g, @g1);
|
|
check table t1;
|
|
Table Op Msg_type Msg_text
|
|
test.t1 check status OK
|
|
select ST_astext(t1.g) from t1;
|
|
ST_astext(t1.g)
|
|
POINT(3 3)
|
|
POINT(3.1 3.1)
|
|
POINT(5 5)
|
|
set @g1 = ST_GeomFromText('Polygon((5 5,5 5,5 5,5 5,5 5))');
|
|
update t1 set g = POINT(2,2) where MBRWithin(t1.g, @g1);
|
|
check table t1;
|
|
Table Op Msg_type Msg_text
|
|
test.t1 check status OK
|
|
select ST_astext(t1.g) from t1;
|
|
ST_astext(t1.g)
|
|
POINT(3 3)
|
|
POINT(3.1 3.1)
|
|
POINT(2 2)
|
|
analyze table t1;
|
|
Table Op Msg_type Msg_text
|
|
test.t1 analyze status OK
|
|
show indexes from t1;
|
|
Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment Visible Expression
|
|
t1 0 PRIMARY 1 i A # NULL NULL BTREE YES NULL
|
|
t1 0 PRIMARY 2 i2 A # NULL NULL BTREE YES NULL
|
|
t1 1 g 1 g A # 32 NULL SPATIAL YES NULL
|
|
drop table t1;
|
|
create table t1 (name VARCHAR(100), square GEOMETRY not null SRID 0, spatial index (square))engine=innodb;
|
|
INSERT INTO t1 VALUES("small", ST_GeomFromText('POLYGON (( 0 0, 0 1, 1 1, 1 0, 0 0))'));
|
|
INSERT INTO t1 VALUES("big", ST_GeomFromText('POLYGON (( 0 0, 0 3, 3 3, 3 0, 0 0))'));
|
|
INSERT INTO t1 VALUES("up", ST_GeomFromText('POLYGON (( 0 1, 0 3, 2 3, 2 1, 0 1))'));
|
|
INSERT INTO t1 VALUES("up2", ST_GeomFromText('POLYGON (( 0 2, 0 4, 2 4, 2 2, 0 2))'));
|
|
INSERT INTO t1 VALUES("up3", ST_GeomFromText('POLYGON (( 0 3, 0 5, 2 5, 2 3, 0 3))'));
|
|
INSERT INTO t1 VALUES("down", ST_GeomFromText('POLYGON (( 0 -1, 0 1, 2 1, 2 -1, 0 -1))'));
|
|
INSERT INTO t1 VALUES("down2", ST_GeomFromText('POLYGON (( 0 -2, 0 0, 2 0, 2 -2, 0 -2))'));
|
|
INSERT INTO t1 VALUES("down3", ST_GeomFromText('POLYGON (( 0 -3, 0 -1, 2 -1, 2 -3, 0 -3))'));
|
|
INSERT INTO t1 VALUES("right", ST_GeomFromText('POLYGON (( 1 0, 1 2, 3 2, 3 0, 1 0))'));
|
|
INSERT INTO t1 VALUES("right2", ST_GeomFromText('POLYGON (( 2 0, 2 2, 4 2, 4 0, 2 0))'));
|
|
INSERT INTO t1 VALUES("right3", ST_GeomFromText('POLYGON (( 3 0, 3 2, 5 2, 5 0, 3 0))'));
|
|
INSERT INTO t1 VALUES("left", ST_GeomFromText('POLYGON (( -1 0, -1 2, 1 2, 1 0, -1 0))'));
|
|
INSERT INTO t1 VALUES("left2", ST_GeomFromText('POLYGON (( -2 0, -2 2, 0 2, 0 0, -2 0))'));
|
|
INSERT INTO t1 VALUES("left3", ST_GeomFromText('POLYGON (( -3 0, -3 2, -1 2, -1 0, -3 0))'));
|
|
SET @p = ST_GeomFromText('POLYGON (( 0 0, 0 2, 2 2, 2 0, 0 0))');
|
|
SELECT name, ST_AsText(square) from t1 where MBRContains(@p, square);
|
|
name ST_AsText(square)
|
|
small POLYGON((0 0,0 1,1 1,1 0,0 0))
|
|
SELECT name, ST_AsText(square) from t1 where MBRDisjoint(@p, square);
|
|
name ST_AsText(square)
|
|
up3 POLYGON((0 3,0 5,2 5,2 3,0 3))
|
|
down3 POLYGON((0 -3,0 -1,2 -1,2 -3,0 -3))
|
|
right3 POLYGON((3 0,3 2,5 2,5 0,3 0))
|
|
left3 POLYGON((-3 0,-3 2,-1 2,-1 0,-3 0))
|
|
SELECT name, ST_AsText(square) from t1 where MBREquals(@p, square);
|
|
name ST_AsText(square)
|
|
SELECT name, ST_AsText(square) from t1 where MBRIntersects(@p, square);
|
|
name ST_AsText(square)
|
|
right2 POLYGON((2 0,2 2,4 2,4 0,2 0))
|
|
right POLYGON((1 0,1 2,3 2,3 0,1 0))
|
|
up2 POLYGON((0 2,0 4,2 4,2 2,0 2))
|
|
up POLYGON((0 1,0 3,2 3,2 1,0 1))
|
|
big POLYGON((0 0,0 3,3 3,3 0,0 0))
|
|
small POLYGON((0 0,0 1,1 1,1 0,0 0))
|
|
down POLYGON((0 -1,0 1,2 1,2 -1,0 -1))
|
|
down2 POLYGON((0 -2,0 0,2 0,2 -2,0 -2))
|
|
left POLYGON((-1 0,-1 2,1 2,1 0,-1 0))
|
|
left2 POLYGON((-2 0,-2 2,0 2,0 0,-2 0))
|
|
SELECT name, ST_AsText(square) from t1 where MBROverlaps(@p, square);
|
|
name ST_AsText(square)
|
|
right POLYGON((1 0,1 2,3 2,3 0,1 0))
|
|
up POLYGON((0 1,0 3,2 3,2 1,0 1))
|
|
down POLYGON((0 -1,0 1,2 1,2 -1,0 -1))
|
|
left POLYGON((-1 0,-1 2,1 2,1 0,-1 0))
|
|
SELECT name, ST_AsText(square) from t1 where MBRTouches(@p, square);
|
|
name ST_AsText(square)
|
|
right2 POLYGON((2 0,2 2,4 2,4 0,2 0))
|
|
up2 POLYGON((0 2,0 4,2 4,2 2,0 2))
|
|
down2 POLYGON((0 -2,0 0,2 0,2 -2,0 -2))
|
|
left2 POLYGON((-2 0,-2 2,0 2,0 0,-2 0))
|
|
SELECT name, ST_AsText(square) from t1 where MBRWithin(@p, square);
|
|
name ST_AsText(square)
|
|
big POLYGON((0 0,0 3,3 3,3 0,0 0))
|
|
SET @vert1 = ST_GeomFromText('POLYGON ((0 -2, 0 2, 0 -2))');
|
|
ERROR 22023: Invalid GIS data provided to function st_geomfromtext.
|
|
SET @horiz1 = ST_GeomFromText('POLYGON ((-2 0, 2 0, -2 0))');
|
|
ERROR 22023: Invalid GIS data provided to function st_geomfromtext.
|
|
SET @horiz2 = ST_GeomFromText('POLYGON ((-1 0, 3 0, -1 0))');
|
|
ERROR 22023: Invalid GIS data provided to function st_geomfromtext.
|
|
SET @horiz3 = ST_GeomFromText('POLYGON ((2 0, 3 0, 2 0))');
|
|
ERROR 22023: Invalid GIS data provided to function st_geomfromtext.
|
|
SET @point1 = ST_GeomFromText('POLYGON ((0 0))');
|
|
ERROR 22023: Invalid GIS data provided to function st_geomfromtext.
|
|
SET @point2 = ST_GeomFromText('POLYGON ((-2 0))');
|
|
ERROR 22023: Invalid GIS data provided to function st_geomfromtext.
|
|
SELECT GROUP_CONCAT(a1.name ORDER BY a1.name) AS MBRoverlaps FROM t1 a1 WHERE MBROverlaps(a1.square, @vert1) GROUP BY a1.name;
|
|
MBRoverlaps
|
|
SELECT GROUP_CONCAT(a1.name ORDER BY a1.name) AS MBRoverlaps FROM t1 a1 WHERE MBROverlaps(a1.square, @horiz1) GROUP BY a1.name;
|
|
MBRoverlaps
|
|
SELECT MBROverlaps(@horiz1, @vert1) FROM DUAL;
|
|
MBROverlaps(@horiz1, @vert1)
|
|
NULL
|
|
SELECT MBROverlaps(@horiz1, @horiz2) FROM DUAL;
|
|
MBROverlaps(@horiz1, @horiz2)
|
|
NULL
|
|
SELECT MBROverlaps(@horiz1, @horiz3) FROM DUAL;
|
|
MBROverlaps(@horiz1, @horiz3)
|
|
NULL
|
|
SELECT MBROverlaps(@horiz1, @point1) FROM DUAL;
|
|
MBROverlaps(@horiz1, @point1)
|
|
NULL
|
|
SELECT MBROverlaps(@horiz1, @point2) FROM DUAL;
|
|
MBROverlaps(@horiz1, @point2)
|
|
NULL
|
|
DROP TABLE t1;
|
|
create table t1 (i int not null, g geometry not null SRID 0)engine=innodb;
|
|
insert into t1 values (1, POINT(1,1));
|
|
insert into t1 values (2, POINT(1.5,1.5));
|
|
insert into t1 values (3, POINT(3,3));
|
|
insert into t1 values (4, POINT(3.1,3.1));
|
|
insert into t1 values (5, POINT(5,5));
|
|
alter table t1 add primary key(i), algorithm=inplace;
|
|
alter table t1 drop primary key;
|
|
create spatial index idx on t1(g) algorithm=inplace;
|
|
create spatial index idx2 on t1(g);
|
|
Warnings:
|
|
Warning 1831 Duplicate index 'idx2' defined on the table 'test.t1'. This is deprecated and will be disallowed in a future release.
|
|
alter table t1 add primary key(i), algorithm=inplace;
|
|
show create table t1;
|
|
Table Create Table
|
|
t1 CREATE TABLE `t1` (
|
|
`i` int(11) NOT NULL,
|
|
`g` geometry NOT NULL /*!80003 SRID 0 */,
|
|
PRIMARY KEY (`i`),
|
|
SPATIAL KEY `idx` (`g`),
|
|
SPATIAL KEY `idx2` (`g`)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
|
|
drop index idx on t1;
|
|
drop table t1;
|