mirror/mariadb
1
0
Fork 0
mirror of https://github.com/MariaDB/server.git synced 2025-10-31 10:56:12 +01:00
Code Issues Projects Releases Packages Wiki Activity
mariadb/mysql-test/suite/innodb_gis/r/create_spatial_index.result
Sergei Golubchik 44ff2f7831 reject invalid spatial key declarations in the parser
2024-11-05 14:00:47 -08:00

1348 lines
74 KiB
Text
Raw Permalink Blame History

CREATE TABLE tab(c1 int NOT NULL PRIMARY KEY,c2 POINT NOT NULL,
c3 LINESTRING NOT NULL,c4 POLYGON NOT NULL,c5 GEOMETRY NOT NULL)
ENGINE=InnoDB ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=8;
CREATE SPATIAL INDEX idx1 on tab(c2);
CREATE SPATIAL INDEX idx2 on tab(c3) COMMENT 'wl6968';
CREATE SPATIAL INDEX idx3 on tab(c4) KEY_BLOCK_SIZE=8 ;
CREATE SPATIAL INDEX idx4 on tab(c5) KEY_BLOCK_SIZE=4
COMMENT 'Spatial index on Geometry type column';
SHOW INDEXES FROM tab;
Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment Ignored
tab 0 PRIMARY 1 c1 A 0 NULL NULL BTREE NO
tab 1 idx1 1 c2 A NULL 32 NULL SPATIAL NO
tab 1 idx2 1 c3 A NULL 32 NULL SPATIAL wl6968 NO
tab 1 idx3 1 c4 A NULL 32 NULL SPATIAL NO
tab 1 idx4 1 c5 A NULL 32 NULL SPATIAL Spatial index on Geometry type column NO
INSERT INTO tab(c1,c2,c3,c4,c5)
VALUES(1,ST_GeomFromText('POINT(10 10)'),ST_GeomFromText('LINESTRING(5 5,20 20,30 30)'),
ST_GeomFromText('POLYGON((30 30,40 40,50 50,30 50,30 40,30 30))'),
ST_GeomFromText('POLYGON((30 30,40 40,50 50,30 50,30 40,30 30))'));
INSERT INTO tab(c1,c2,c3,c4,c5)
VALUES(2,ST_GeomFromText('POINT(20 20)'),ST_GeomFromText('LINESTRING(20 20,30 30,40 40)'),
ST_GeomFromText('POLYGON((40 50,40 70,50 100,70 100,80 80,70 50,40 50))'),
ST_GeomFromText('POLYGON((40 50,40 70,50 100,70 100,80 80,70 50,40 50))'));
INSERT INTO tab(c1,c2,c3,c4,c5)
VALUES(3,ST_GeomFromText('POINT(4 4)'),ST_GeomFromText('LINESTRING(130 130,140 140,150 150)'),
ST_GeomFromText('POLYGON((7 1,6 2,6 3,10 3,10 1,7 1))'),
ST_GeomFromText('POLYGON((4 -2,5 -4,6 -5,7 -4,7 2,4 -2))'));
INSERT INTO tab(c1,c2,c3,c4,c5)
VALUES(4,ST_GeomFromText('POINT(50 50)'),ST_GeomFromText('LINESTRING(200 200,300 300,400 400)'),
ST_GeomFromText('POLYGON((300 300,400 400,500 500,300 500,300 400,300 300))'),
ST_GeomFromText('POLYGON((300 300,400 400,500 500,300 500,300 400,300 300))'));
INSERT INTO tab(c1,c2,c3,c4,c5)
VALUES(5,ST_GeomFromText('POINT(3 3)'),ST_GeomFromText('LINESTRING(400 400,500 500,600 700)'),
ST_GeomFromText('POLYGON((1010 1010,1020 1020,1030 1030,1040 1030,1020 1010,1010 1010))'),
ST_GeomFromText('POLYGON((1010 1010,1020 1020,1030 1030,1040 1030,1020 1010,1010 1010))'));
INSERT INTO tab(c1,c2,c3,c4,c5)
VALUES(6,ST_GeomFromText('POINT(3 3)'),ST_GeomFromText('LINESTRING(40 40,50 50,60 70)'),
ST_GeomFromText('POLYGON((2010 2010,2020 2020,2030 2030,2040 2030,2020 2010,2010 2010))'),
ST_GeomFromText('POLYGON((2010 2010,2020 2020,2030 2030,2040 2030,2020 2010,2010 2010))'));
INSERT INTO tab(c1,c2,c3,c4,c5)
VALUES(7,ST_GeomFromText('POINT(60 70)'),ST_GeomFromText('LINESTRING(40 40,50 50,60 70)'),
ST_GeomFromText('POLYGON((3010 3010,3020 3020,3030 3030,3040 3030,3020 3010,3010 3010))'),
ST_GeomFromText('POLYGON((3010 3010,3020 3020,3030 3030,3040 3030,3020 3010,3010 3010))'));
INSERT INTO tab(c1,c2,c3,c4,c5)
VALUES(8,ST_GeomFromText('POINT(0 0)'),ST_GeomFromText('LINESTRING(40 40,50 50,60 70)'),
ST_GeomFromText('POLYGON((3010 3010,3020 3020,3030 3030,3040 3030,3020 3010,3010 3010))'),
ST_GeomFromText('POLYGON((3010 3010,3020 3020,3030 3030,3040 3030,3020 3010,3010 3010))'));
INSERT INTO tab(c1,c2,c3,c4,c5)
VALUES(9,ST_GeomFromText('POINT(120 120)'),ST_GeomFromText('LINESTRING(100 100,110 110,120 120)'),
ST_GeomFromText('POLYGON((4010 4010,4020 4020,4030 4030,4040 4030,4020 4010,4010 4010))'),
ST_GeomFromText('POLYGON((4010 4010,4020 4020,4030 4030,4040 4030,4020 4010,4010 4010))'));
INSERT INTO tab(c1,c2,c3,c4,c5)
VALUES(10,ST_GeomFromText('POINT(160 160)'),ST_GeomFromText('LINESTRING(140 140,150 150,160 160)'),
ST_GeomFromText('POLYGON((5010 5010,5020 5020,5030 5030,5040 5030,5020 5010,5010 5010))'),
ST_GeomFromText('POLYGON((5010 5010,5020 5020,5030 5030,5040 5030,5020 5010,5010 5010))'));
ANALYZE TABLE tab;
Table Op Msg_type Msg_text
test.tab analyze status Engine-independent statistics collected
test.tab analyze status OK
# Test the MBRContains
SET @g1 = ST_GeomFromText( 'POLYGON((7 1,6 2,6 3,10 3,10 1,7 1))');
EXPLAIN SELECT c1,ST_Astext(c4) FROM tab WHERE MBRContains(tab.c4, @g1) ORDER BY c1+0;
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE tab range idx3 idx3 34 NULL 1 Using where; Using filesort
EXPLAIN SELECT c1,ST_Astext(c4) FROM tab WHERE MBRContains(tab.c4, @g1) ORDER BY c1;
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE tab index idx3 PRIMARY 4 NULL 10 Using where
SELECT c1,ST_Astext(c4) FROM tab WHERE MBRContains(tab.c4, @g1) ORDER BY c1;
c1 ST_Astext(c4)
3 POLYGON((7 1,6 2,6 3,10 3,10 1,7 1))
EXPLAIN UPDATE tab SET C2 = ST_GeomFromText('POINT(0 0)')
WHERE MBRContains(tab.c4, @g1);
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE tab range idx3 idx3 34 NULL 1 Using where
EXPLAIN DELETE FROM tab WHERE MBRContains(tab.c4, @g1);
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE tab range idx3 idx3 34 NULL 1 Using where
SET @g1 = ST_GeomFromText('LINESTRING( 300 300,400 400)');
EXPLAIN SELECT c1,ST_Astext(c4) FROM tab WHERE MBRContains(tab.c4, @g1) ORDER BY c1;
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE tab index idx3 PRIMARY 4 NULL 10 Using where
SELECT c1,ST_Astext(c4) FROM tab WHERE MBRContains(tab.c4, @g1) ORDER BY c1;
c1 ST_Astext(c4)
4 POLYGON((300 300,400 400,500 500,300 500,300 400,300 300))
EXPLAIN UPDATE tab SET C2 = ST_GeomFromText('POINT(0 0)')
WHERE MBRContains(tab.c4, @g1);
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE tab range idx3 idx3 34 NULL 1 Using where
EXPLAIN DELETE FROM tab WHERE MBRContains(tab.c4, @g1);
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE tab range idx3 idx3 34 NULL 1 Using where
# Test the MBRWithin
SET @g1 = ST_GeomFromText( 'POLYGON((30 30,40 40,50 50,30 50,30 40,30 30)) ');
EXPLAIN SELECT c1,ST_Astext(c4) FROM tab WHERE MBRWithin(tab.c4, @g1) ORDER BY c1;
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE tab index idx3 PRIMARY 4 NULL 10 Using where
SELECT c1,ST_Astext(c4) FROM tab WHERE MBRWithin(tab.c4, @g1) ORDER BY c1;
c1 ST_Astext(c4)
1 POLYGON((30 30,40 40,50 50,30 50,30 40,30 30))
EXPLAIN UPDATE tab SET C2 = ST_GeomFromText('POINT(0 0)')
WHERE MBRWithin(tab.c4, @g1);
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE tab range idx3 idx3 34 NULL 1 Using where
EXPLAIN DELETE FROM tab WHERE MBRWithin(tab.c4, @g1);
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE tab range idx3 idx3 34 NULL 1 Using where
# Test the ST_Crosses
SET @g1 = ST_GeomFromText('POLYGON((100 200,200 300,400 500,500 300,300 200,100 300,100 200))');
EXPLAIN SELECT c1,ST_Astext(c4) FROM tab WHERE ST_Crosses(tab.c4, @g1) ORDER BY c1;
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE tab index idx3 PRIMARY 4 NULL 10 Using where
SELECT c1,ST_Astext(c4) FROM tab WHERE ST_Crosses(tab.c4, @g1) ORDER BY c1;
c1 ST_Astext(c4)
4 POLYGON((300 300,400 400,500 500,300 500,300 400,300 300))
EXPLAIN UPDATE tab SET C2 = ST_GeomFromText('POINT(0 0)')
WHERE ST_Crosses(tab.c4, @g1);
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE tab range idx3 idx3 34 NULL 1 Using where
EXPLAIN DELETE FROM tab WHERE ST_Crosses(tab.c4, @g1);
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE tab range idx3 idx3 34 NULL 1 Using where
SET @g1 = ST_GeomFromText('LINESTRING( 10 10,30 30,40 40)');
EXPLAIN SELECT c1,ST_Astext(c4) FROM tab WHERE ST_CRosses(tab.c4, @g1) ORDER BY c1;
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE tab index idx3 PRIMARY 4 NULL 10 Using where
SELECT c1,ST_Astext(c4) FROM tab WHERE ST_Crosses(tab.c4, @g1) ORDER BY c1;
c1 ST_Astext(c4)
1 POLYGON((30 30,40 40,50 50,30 50,30 40,30 30))
EXPLAIN UPDATE tab SET C2 = ST_GeomFromText('POINT(0 0)')
WHERE ST_Crosses(tab.c4, @g1);
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE tab range idx3 idx3 34 NULL 1 Using where
EXPLAIN DELETE FROM tab WHERE ST_Crosses(tab.c4, @g1);
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE tab range idx3 idx3 34 NULL 1 Using where
# Test the MBRDisjoint
SET @g1 = ST_GeomFromText('POLYGON((4 -2,5 -4,6 -5,7 -4,7 2,4 -2))');
EXPLAIN SELECT c1,ST_Astext(c4) FROM tab WHERE MBRDisjoint(tab.c4, @g1) ORDER BY c1;
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE tab index idx3 PRIMARY 4 NULL 10 Using where
SELECT c1,ST_Astext(c4) FROM tab WHERE MBRDisjoint(tab.c4, @g1) ORDER BY c1;
c1 ST_Astext(c4)
1 POLYGON((30 30,40 40,50 50,30 50,30 40,30 30))
2 POLYGON((40 50,40 70,50 100,70 100,80 80,70 50,40 50))
4 POLYGON((300 300,400 400,500 500,300 500,300 400,300 300))
5 POLYGON((1010 1010,1020 1020,1030 1030,1040 1030,1020 1010,1010 1010))
6 POLYGON((2010 2010,2020 2020,2030 2030,2040 2030,2020 2010,2010 2010))
7 POLYGON((3010 3010,3020 3020,3030 3030,3040 3030,3020 3010,3010 3010))
8 POLYGON((3010 3010,3020 3020,3030 3030,3040 3030,3020 3010,3010 3010))
9 POLYGON((4010 4010,4020 4020,4030 4030,4040 4030,4020 4010,4010 4010))
10 POLYGON((5010 5010,5020 5020,5030 5030,5040 5030,5020 5010,5010 5010))
EXPLAIN UPDATE tab SET C2 = ST_GeomFromText('POINT(0 0)')
WHERE MBRDisjoint(tab.c4, @g1);
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE tab range idx3 idx3 34 NULL 9 Using where
EXPLAIN DELETE FROM tab WHERE MBRDisjoint(tab.c4, @g1);
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE tab range idx3 idx3 34 NULL 9 Using where
# Test the MBREquals
SET @g1 = ST_GeomFromText('POLYGON((5010 5010,5020 5020,5030 5030,5040 5030,5020 5010,5010 5010))');
EXPLAIN SELECT c1,ST_Astext(c4) FROM tab WHERE MBREquals(tab.c4, @g1) ORDER BY c1;
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE tab index idx3 PRIMARY 4 NULL 10 Using where
SELECT c1,ST_Astext(c4) FROM tab WHERE MBREquals(tab.c4, @g1) ORDER BY c1;
c1 ST_Astext(c4)
10 POLYGON((5010 5010,5020 5020,5030 5030,5040 5030,5020 5010,5010 5010))
EXPLAIN UPDATE tab SET C2 = ST_GeomFromText('POINT(0 0)')
WHERE MBREquals(tab.c4, @g1);
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE tab range idx3 idx3 34 NULL 1 Using where
EXPLAIN DELETE FROM tab WHERE MBREquals(tab.c4, @g1);
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE tab range idx3 idx3 34 NULL 1 Using where
SET @g1 = ST_GeomFromText( 'POLYGON((0 0,0 30,30 40,40 50,50 30,0 0))');
EXPLAIN SELECT c1,ST_Astext(c4) FROM tab WHERE MBRIntersects(tab.c4, @g1) ORDER BY c1;
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE tab index idx3 PRIMARY 4 NULL 10 Using where
SELECT c1,ST_Astext(c4) FROM tab WHERE MBRIntersects(tab.c4, @g1) ORDER BY c1;
c1 ST_Astext(c4)
1 POLYGON((30 30,40 40,50 50,30 50,30 40,30 30))
2 POLYGON((40 50,40 70,50 100,70 100,80 80,70 50,40 50))
3 POLYGON((7 1,6 2,6 3,10 3,10 1,7 1))
EXPLAIN UPDATE tab SET C2 = ST_GeomFromText('POINT(0 0)')
WHERE MBRintersects(tab.c4, @g1);
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE tab range idx3 idx3 34 NULL 2 Using where
EXPLAIN DELETE FROM tab WHERE MBRintersects(tab.c4, @g1);
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE tab range idx3 idx3 34 NULL 2 Using where
SET @g1 = ST_GeomFromText('LINESTRING( 30 30,40 40,50 50)');
EXPLAIN SELECT c1,ST_Astext(c4) FROM tab WHERE MBRIntersects(tab.c4, @g1) ORDER BY c1;
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE tab index idx3 PRIMARY 4 NULL 10 Using where
SELECT c1,ST_Astext(c4) FROM tab WHERE MBRIntersects(tab.c4, @g1) ORDER BY c1;
c1 ST_Astext(c4)
1 POLYGON((30 30,40 40,50 50,30 50,30 40,30 30))
2 POLYGON((40 50,40 70,50 100,70 100,80 80,70 50,40 50))
EXPLAIN UPDATE tab SET C2 = ST_GeomFromText('POINT(0 0)')
WHERE MBRintersects(tab.c4, @g1);
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE tab range idx3 idx3 34 NULL 1 Using where
EXPLAIN DELETE FROM tab WHERE MBRintersects(tab.c4, @g1);
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE tab range idx3 idx3 34 NULL 1 Using where
# Test the Overelaps
SET @g1 = ST_GeomFromText( 'POLYGON((0 0,0 2,4 5,5 5,7 1,0 0 ))');
EXPLAIN SELECT c1,ST_Astext(c4) FROM tab WHERE MBROverlaps(tab.c4, @g1) ORDER BY c1;
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE tab index idx3 PRIMARY 4 NULL 10 Using where
SELECT c1,ST_Astext(c4) FROM tab WHERE MBROverlaps(tab.c4, @g1) ORDER BY c1;
c1 ST_Astext(c4)
3 POLYGON((7 1,6 2,6 3,10 3,10 1,7 1))
EXPLAIN UPDATE tab SET C2 = ST_GeomFromText('POINT(0 0)')
WHERE MBROverlaps(tab.c4, @g1);
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE tab range idx3 idx3 34 NULL 1 Using where
EXPLAIN DELETE FROM tab WHERE MBROverlaps(tab.c4, @g1);
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE tab range idx3 idx3 34 NULL 1 Using where
SET @g1 = ST_GeomFromText('LINESTRING(7 1,30 30,1010 3010,1010 2010,3010 3010,4010 4010,5010 5010 )');
EXPLAIN SELECT c1,ST_Astext(c4) FROM tab WHERE MBROverlaps(tab.c4, @g1) ORDER BY c1;
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE tab index idx3 PRIMARY 4 NULL 10 Using where
SELECT c1,ST_Astext(c4) FROM tab WHERE MBROverlaps(tab.c4, @g1) ORDER BY c1;
c1 ST_Astext(c4)
3 POLYGON((7 1,6 2,6 3,10 3,10 1,7 1))
EXPLAIN UPDATE tab SET C2 = ST_GeomFromText('POINT(0 0)')
WHERE MBROverlaps(tab.c4, @g1);
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE tab range idx3 idx3 34 NULL 8 Using where
EXPLAIN DELETE FROM tab WHERE MBROverlaps(tab.c4, @g1);
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE tab range idx3 idx3 34 NULL 8 Using where
# Test the ST_Touches
SET @g1 = ST_GeomFromText( 'POLYGON((0 0,0 30,30 40,40 50,50 30,0 0))');
EXPLAIN SELECT c1,ST_Astext(c4) FROM tab WHERE ST_Touches(tab.c4, @g1) ORDER BY c1;
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE tab index idx3 PRIMARY 4 NULL 10 Using where
SELECT c1,ST_Astext(c4) FROM tab WHERE ST_Touches(tab.c4, @g1) ORDER BY c1;
c1 ST_Astext(c4)
2 POLYGON((40 50,40 70,50 100,70 100,80 80,70 50,40 50))
EXPLAIN UPDATE tab SET C2 = ST_GeomFromText('POINT(0 0)')
WHERE ST_Touches(tab.c4, @g1);
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE tab range idx3 idx3 34 NULL 2 Using where
EXPLAIN DELETE FROM tab WHERE ST_Touches(tab.c4, @g1);
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE tab range idx3 idx3 34 NULL 2 Using where
SET @g1 = ST_GeomFromText('LINESTRING( 100 100,200 200,300 300)');
EXPLAIN SELECT c1,ST_Astext(c4) FROM tab WHERE ST_Touches(tab.c4, @g1) ORDER BY c1;
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE tab index idx3 PRIMARY 4 NULL 10 Using where
SELECT c1,ST_Astext(c4) FROM tab WHERE ST_Touches(tab.c4, @g1) ORDER BY c1;
c1 ST_Astext(c4)
4 POLYGON((300 300,400 400,500 500,300 500,300 400,300 300))
EXPLAIN UPDATE tab SET C2 = ST_GeomFromText('POINT(0 0)')
WHERE ST_Touches(tab.c4, @g1);
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE tab range idx3 idx3 34 NULL 1 Using where
EXPLAIN DELETE FROM tab WHERE ST_Touches(tab.c4, @g1);
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE tab range idx3 idx3 34 NULL 1 Using where
# Test the MBRContains
SET @g1 = ST_GeomFromText( 'POLYGON((7 1,6 2,6 3,10 3,10 1,7 1))');
EXPLAIN SELECT c1,ST_Astext(c4) FROM tab WHERE MBRContains(tab.c4, @g1) ORDER BY c1;
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE tab index idx3 PRIMARY 4 NULL 10 Using where
SELECT c1,ST_Astext(c4) FROM tab WHERE MBRContains(tab.c4, @g1) ORDER BY c1;
c1 ST_Astext(c4)
3 POLYGON((7 1,6 2,6 3,10 3,10 1,7 1))
EXPLAIN UPDATE tab SET C2 = ST_GeomFromText('POINT(0 0)')
WHERE MBRContains(tab.c4, @g1);
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE tab range idx3 idx3 34 NULL 1 Using where
EXPLAIN DELETE FROM tab WHERE MBRContains(tab.c4, @g1);
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE tab range idx3 idx3 34 NULL 1 Using where
SET @g1 = ST_GeomFromText( 'POLYGON((30 30,40 40,50 50,30 50,30 40,30 30)) ');
EXPLAIN SELECT c1,ST_Astext(c4) FROM tab WHERE MBRWithin(tab.c4, @g1) ORDER BY c1;
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE tab index idx3 PRIMARY 4 NULL 10 Using where
SELECT c1,ST_Astext(c4) FROM tab WHERE MBRWithin(tab.c4, @g1) ORDER BY c1;
c1 ST_Astext(c4)
1 POLYGON((30 30,40 40,50 50,30 50,30 40,30 30))
EXPLAIN UPDATE tab SET C2 = ST_GeomFromText('POINT(0 0)')
WHERE MBRWithin(tab.c4, @g1);
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE tab range idx3 idx3 34 NULL 1 Using where
EXPLAIN DELETE FROM tab WHERE MBRWithin(tab.c4, @g1);
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE tab range idx3 idx3 34 NULL 1 Using where
# Test the MBRDisjoint
SET @g1 = ST_GeomFromText('POLYGON((4 -2,5 -4,6 -5,7 -4,7 2,4 -2))');
EXPLAIN SELECT c1,ST_Astext(c4) FROM tab WHERE MBRDisjoint(tab.c4, @g1) ORDER BY c1;
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE tab index idx3 PRIMARY 4 NULL 10 Using where
SELECT c1,ST_Astext(c4) FROM tab WHERE MBRDisjoint(tab.c4, @g1) ORDER BY c1;
c1 ST_Astext(c4)
1 POLYGON((30 30,40 40,50 50,30 50,30 40,30 30))
2 POLYGON((40 50,40 70,50 100,70 100,80 80,70 50,40 50))
4 POLYGON((300 300,400 400,500 500,300 500,300 400,300 300))
5 POLYGON((1010 1010,1020 1020,1030 1030,1040 1030,1020 1010,1010 1010))
6 POLYGON((2010 2010,2020 2020,2030 2030,2040 2030,2020 2010,2010 2010))
7 POLYGON((3010 3010,3020 3020,3030 3030,3040 3030,3020 3010,3010 3010))
8 POLYGON((3010 3010,3020 3020,3030 3030,3040 3030,3020 3010,3010 3010))
9 POLYGON((4010 4010,4020 4020,4030 4030,4040 4030,4020 4010,4010 4010))
10 POLYGON((5010 5010,5020 5020,5030 5030,5040 5030,5020 5010,5010 5010))
EXPLAIN UPDATE tab SET C2 = ST_GeomFromText('POINT(0 0)')
WHERE MBRDisjoint(tab.c4, @g1);
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE tab range idx3 idx3 34 NULL 9 Using where
EXPLAIN DELETE FROM tab WHERE MBRDisjoint(tab.c4, @g1);
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE tab range idx3 idx3 34 NULL 9 Using where
# Test the MBREquals
SET @g1 = ST_GeomFromText('POLYGON((5010 5010,5020 5020,5030 5030,5040 5030,5020 5010,5010 5010))');
EXPLAIN SELECT c1,ST_Astext(c4) FROM tab WHERE MBREquals(tab.c4, @g1) ORDER BY c1;
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE tab index idx3 PRIMARY 4 NULL 10 Using where
SELECT c1,ST_Astext(c4) FROM tab WHERE MBREquals(tab.c4, @g1) ORDER BY c1;
c1 ST_Astext(c4)
10 POLYGON((5010 5010,5020 5020,5030 5030,5040 5030,5020 5010,5010 5010))
EXPLAIN UPDATE tab SET C2 = ST_GeomFromText('POINT(0 0)')
WHERE MBREquals(tab.c4, @g1);
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE tab range idx3 idx3 34 NULL 1 Using where
EXPLAIN DELETE FROM tab WHERE MBREquals(tab.c4, @g1);
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE tab range idx3 idx3 34 NULL 1 Using where
# Test the MBRintersects
SET @g1 = ST_GeomFromText( 'POLYGON((0 0,0 30,30 40,40 50,50 30,0 0))');
EXPLAIN SELECT c1,ST_Astext(c4) FROM tab WHERE MBRIntersects(tab.c4, @g1) ORDER BY c1;
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE tab index idx3 PRIMARY 4 NULL 10 Using where
SELECT c1,ST_Astext(c4) FROM tab WHERE MBRIntersects(tab.c4, @g1) ORDER BY c1;
c1 ST_Astext(c4)
1 POLYGON((30 30,40 40,50 50,30 50,30 40,30 30))
2 POLYGON((40 50,40 70,50 100,70 100,80 80,70 50,40 50))
3 POLYGON((7 1,6 2,6 3,10 3,10 1,7 1))
EXPLAIN UPDATE tab SET C2 = ST_GeomFromText('POINT(0 0)')
WHERE MBRintersects(tab.c4, @g1);
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE tab range idx3 idx3 34 NULL 2 Using where
EXPLAIN DELETE FROM tab WHERE MBRintersects(tab.c4, @g1);
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE tab range idx3 idx3 34 NULL 2 Using where
SET @g1 = ST_GeomFromText('LINESTRING( 30 30,40 40,50 50)');
EXPLAIN SELECT c1,ST_Astext(c4) FROM tab WHERE MBRIntersects(tab.c4, @g1) ORDER BY c1;
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE tab index idx3 PRIMARY 4 NULL 10 Using where
SELECT c1,ST_Astext(c4) FROM tab WHERE MBRIntersects(tab.c4, @g1) ORDER BY c1;
c1 ST_Astext(c4)
1 POLYGON((30 30,40 40,50 50,30 50,30 40,30 30))
2 POLYGON((40 50,40 70,50 100,70 100,80 80,70 50,40 50))
EXPLAIN UPDATE tab SET C2 = ST_GeomFromText('POINT(0 0)')
WHERE MBRintersects(tab.c4, @g1);
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE tab range idx3 idx3 34 NULL 1 Using where
EXPLAIN DELETE FROM tab WHERE MBRintersects(tab.c4, @g1);
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE tab range idx3 idx3 34 NULL 1 Using where
# Test the MBROverelaps
SET @g1 = ST_GeomFromText( 'POLYGON((0 0,0 2,4 5,5 5,7 1,0 0 ))');
EXPLAIN SELECT c1,ST_Astext(c4) FROM tab WHERE MBROverlaps(tab.c4, @g1) ORDER BY c1;
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE tab index idx3 PRIMARY 4 NULL 10 Using where
SELECT c1,ST_Astext(c4) FROM tab WHERE MBROverlaps(tab.c4, @g1) ORDER BY c1;
c1 ST_Astext(c4)
3 POLYGON((7 1,6 2,6 3,10 3,10 1,7 1))
EXPLAIN UPDATE tab SET C2 = ST_GeomFromText('POINT(0 0)')
WHERE MBROverlaps(tab.c4, @g1);
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE tab range idx3 idx3 34 NULL 1 Using where
EXPLAIN DELETE FROM tab WHERE MBROverlaps(tab.c4, @g1);
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE tab range idx3 idx3 34 NULL 1 Using where
# Test the MBRTouches
SET @g1 = ST_GeomFromText( 'POLYGON((0 0,0 30,30 40,40 50,50 30,0 0))');
EXPLAIN SELECT c1,ST_Astext(c4) FROM tab WHERE MBRTouches(tab.c4, @g1) ORDER BY c1;
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE tab index idx3 PRIMARY 4 NULL 10 Using where
SELECT c1,ST_Astext(c4) FROM tab WHERE MBRTouches(tab.c4, @g1) ORDER BY c1;
c1 ST_Astext(c4)
2 POLYGON((40 50,40 70,50 100,70 100,80 80,70 50,40 50))
EXPLAIN UPDATE tab SET C2 = ST_GeomFromText('POINT(0 0)')
WHERE MBRTouches(tab.c4, @g1);
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE tab range idx3 idx3 34 NULL 2 Using where
EXPLAIN DELETE FROM tab WHERE MBRTouches(tab.c4, @g1);
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE tab range idx3 idx3 34 NULL 2 Using where
# Test with Procedure
CREATE PROCEDURE proc_wl6968()
BEGIN
SET @g1 = ST_GeomFromText( 'POLYGON((7 1,6 2,6 3,10 3,10 1,7 1))');
EXPLAIN SELECT c1,ST_Astext(c4) FROM tab WHERE MBRContains(tab.c4, @g1) ORDER BY c1;
EXPLAIN UPDATE tab SET C2 = ST_GeomFromText('POINT(0 0)')
WHERE MBRContains(tab.c4, @g1);
EXPLAIN DELETE FROM tab WHERE MBRContains(tab.c4, @g1);
END |
CALL proc_wl6968();
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE tab index idx3 PRIMARY 4 NULL 10 Using where
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE tab range idx3 idx3 34 NULL 1 Using where
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE tab range idx3 idx3 34 NULL 1 Using where
# Test the Delete & Update
SET @g1 = ST_GeomFromText( 'POLYGON((7 1,6 2,6 3,10 3,10 1,7 1))');
SELECT c1,ST_Astext(c4) FROM tab WHERE MBRContains(tab.c4, @g1) ORDER BY c1;
c1 ST_Astext(c4)
3 POLYGON((7 1,6 2,6 3,10 3,10 1,7 1))
DELETE FROM tab WHERE MBRContains(tab.c4, @g1);
SELECT c1,ST_Astext(c4) FROM tab WHERE MBRContains(tab.c4, @g1) ORDER BY c1;
c1 ST_Astext(c4)
CHECK TABLE tab;
Table Op Msg_type Msg_text
test.tab check status OK
SET @g1 = ST_GeomFromText('LINESTRING( 300 300,400 400)');
SELECT c1,ST_Astext(c4) FROM tab WHERE MBRContains(tab.c4, @g1) ORDER BY c1;
c1 ST_Astext(c4)
4 POLYGON((300 300,400 400,500 500,300 500,300 400,300 300))
DELETE FROM tab WHERE MBRContains(tab.c4, @g1);
SELECT c1,ST_Astext(c4) FROM tab WHERE MBRContains(tab.c4, @g1) ORDER BY c1;
c1 ST_Astext(c4)
CHECK TABLE tab;
Table Op Msg_type Msg_text
test.tab check status OK
SET @g1 = ST_GeomFromText('POLYGON((100 200,1010 1010,1020 1020,500 300,300 200,100 300,100 200))');
SELECT c1,ST_Astext(c4) FROM tab WHERE ST_Crosses(tab.c4, @g1) ORDER BY c1;
c1 ST_Astext(c4)
5 POLYGON((1010 1010,1020 1020,1030 1030,1040 1030,1020 1010,1010 1010))
DELETE FROM tab WHERE ST_Crosses(tab.c4, @g1);
SELECT c1,ST_Astext(c4) FROM tab WHERE ST_Crosses(tab.c4, @g1) ORDER BY c1;
c1 ST_Astext(c4)
CHECK TABLE tab;
Table Op Msg_type Msg_text
test.tab check status OK
SET @g1 = ST_GeomFromText('POLYGON((30 30,40 40,50 50,30 50,30 40,30 30))');
SET @g2 = ST_GeomFromText( 'POLYGON((7 1,6 2,6 3,10 3,10 1,7 1))');
UPDATE tab SET C4 = @g2 WHERE ST_Crosses(tab.c4, @g1);
SELECT c1,ST_Astext(c4) FROM tab WHERE ST_Crosses(tab.c4, @g2) ORDER BY c1;
c1 ST_Astext(c4)
CHECK TABLE tab;
Table Op Msg_type Msg_text
test.tab check status OK
DROP TABLE tab;
DROP PROCEDURE proc_wl6968;
CREATE TABLE tab(c1 int ,c2 POINT NOT NULL,
c3 LINESTRING NOT NULL,c4 POLYGON NOT NULL,c5 GEOMETRY NOT NULL)
ENGINE=InnoDB ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=16;
CREATE SPATIAL INDEX idx1 on tab(c2);
CREATE SPATIAL INDEX idx2 on tab(c3) COMMENT 'wl6968';
CREATE SPATIAL INDEX idx3 on tab(c4) KEY_BLOCK_SIZE=2 ;
CREATE SPATIAL INDEX idx4 on tab(c5) KEY_BLOCK_SIZE=8
COMMENT 'Spatial index on Geometry type column';
SHOW INDEXES FROM tab;
Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment Ignored
tab 1 idx1 1 c2 A NULL 32 NULL SPATIAL NO
tab 1 idx2 1 c3 A NULL 32 NULL SPATIAL wl6968 NO
tab 1 idx3 1 c4 A NULL 32 NULL SPATIAL NO
tab 1 idx4 1 c5 A NULL 32 NULL SPATIAL Spatial index on Geometry type column NO
INSERT INTO tab(c1,c2,c3,c4,c5)
VALUES(1,ST_GeomFromText('POINT(10 10)'),ST_GeomFromText('LINESTRING(5 5,20 20,30 30)'),
ST_GeomFromText('POLYGON((30 30,40 40,50 50,30 50,30 40,30 30))'),
ST_GeomFromText('POLYGON((30 30,40 40,50 50,30 50,30 40,30 30))'));
INSERT INTO tab(c1,c2,c3,c4,c5)
VALUES(2,ST_GeomFromText('POINT(20 20)'),ST_GeomFromText('LINESTRING(20 20,30 30,40 40)'),
ST_GeomFromText('POLYGON((40 50,40 70,50 100,70 100,80 80,70 50,40 50))'),
ST_GeomFromText('POLYGON((40 50,40 70,50 100,70 100,80 80,70 50,40 50))'));
INSERT INTO tab(c1,c2,c3,c4,c5)
VALUES(3,ST_GeomFromText('POINT(4 4)'),ST_GeomFromText('LINESTRING(130 130,140 140,150 150)'),
ST_GeomFromText('POLYGON((7 1,6 2,6 3,10 3,10 1,7 1))'),
ST_GeomFromText('POLYGON((4 -2,5 -4,6 -5,7 -4,7 2,4 -2))'));
INSERT INTO tab(c1,c2,c3,c4,c5)
VALUES(4,ST_GeomFromText('POINT(50 50)'),ST_GeomFromText('LINESTRING(200 200,300 300,400 400)'),
ST_GeomFromText('POLYGON((300 300,400 400,500 500,300 500,300 400,300 300))'),
ST_GeomFromText('POLYGON((300 300,400 400,500 500,300 500,300 400,300 300))'));
INSERT INTO tab(c1,c2,c3,c4,c5)
VALUES(5,ST_GeomFromText('POINT(3 3)'),ST_GeomFromText('LINESTRING(400 400,500 500,600 700)'),
ST_GeomFromText('POLYGON((1010 1010,1020 1020,1030 1030,1040 1030,1020 1010,1010 1010))'),
ST_GeomFromText('POLYGON((1010 1010,1020 1020,1030 1030,1040 1030,1020 1010,1010 1010))'));
INSERT INTO tab(c1,c2,c3,c4,c5)
VALUES(6,ST_GeomFromText('POINT(3 3)'),ST_GeomFromText('LINESTRING(40 40,50 50,60 70)'),
ST_GeomFromText('POLYGON((2010 2010,2020 2020,2030 2030,2040 2030,2020 2010,2010 2010))'),
ST_GeomFromText('POLYGON((2010 2010,2020 2020,2030 2030,2040 2030,2020 2010,2010 2010))'));
INSERT INTO tab(c1,c2,c3,c4,c5)
VALUES(7,ST_GeomFromText('POINT(60 70)'),ST_GeomFromText('LINESTRING(40 40,50 50,60 70)'),
ST_GeomFromText('POLYGON((3010 3010,3020 3020,3030 3030,3040 3030,3020 3010,3010 3010))'),
ST_GeomFromText('POLYGON((3010 3010,3020 3020,3030 3030,3040 3030,3020 3010,3010 3010))'));
INSERT INTO tab(c1,c2,c3,c4,c5)
VALUES(8,ST_GeomFromText('POINT(0 0)'),ST_GeomFromText('LINESTRING(40 40,50 50,60 70)'),
ST_GeomFromText('POLYGON((3010 3010,3020 3020,3030 3030,3040 3030,3020 3010,3010 3010))'),
ST_GeomFromText('POLYGON((3010 3010,3020 3020,3030 3030,3040 3030,3020 3010,3010 3010))'));
INSERT INTO tab(c1,c2,c3,c4,c5)
VALUES(9,ST_GeomFromText('POINT(120 120)'),ST_GeomFromText('LINESTRING(100 100,110 110,120 120)'),
ST_GeomFromText('POLYGON((4010 4010,4020 4020,4030 4030,4040 4030,4020 4010,4010 4010))'),
ST_GeomFromText('POLYGON((4010 4010,4020 4020,4030 4030,4040 4030,4020 4010,4010 4010))'));
INSERT INTO tab(c1,c2,c3,c4,c5)
VALUES(10,ST_GeomFromText('POINT(160 160)'),ST_GeomFromText('LINESTRING(140 140,150 150,160 160)'),
ST_GeomFromText('POLYGON((5010 5010,5020 5020,5030 5030,5040 5030,5020 5010,5010 5010))'),
ST_GeomFromText('POLYGON((5010 5010,5020 5020,5030 5030,5040 5030,5020 5010,5010 5010))'));
ANALYZE TABLE tab;
Table Op Msg_type Msg_text
test.tab analyze status Engine-independent statistics collected
test.tab analyze status OK
# Test the MBRContains
SET @g1 = ST_GeomFromText( 'POLYGON((7 1,6 2,6 3,10 3,10 1,7 1))');
EXPLAIN SELECT c1,ST_Astext(c4) FROM tab WHERE MBRContains(tab.c4, @g1) ORDER BY c1;
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE tab range idx3 idx3 34 NULL 1 Using where; Using filesort
SELECT c1,ST_Astext(c4) FROM tab WHERE MBRContains(tab.c4, @g1) ORDER BY c1;
c1 ST_Astext(c4)
3 POLYGON((7 1,6 2,6 3,10 3,10 1,7 1))
EXPLAIN UPDATE tab SET C2 = ST_GeomFromText('POINT(0 0)')
WHERE MBRContains(tab.c4, @g1);
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE tab range idx3 idx3 34 NULL 1 Using where
EXPLAIN DELETE FROM tab WHERE MBRContains(tab.c4, @g1);
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE tab range idx3 idx3 34 NULL 1 Using where
SET @g1 = ST_GeomFromText('LINESTRING( 300 300,400 400)');
EXPLAIN SELECT c1,ST_Astext(c4) FROM tab WHERE MBRContains(tab.c4, @g1) ORDER BY c1;
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE tab range idx3 idx3 34 NULL 1 Using where; Using filesort
SELECT c1,ST_Astext(c4) FROM tab WHERE MBRContains(tab.c4, @g1) ORDER BY c1;
c1 ST_Astext(c4)
4 POLYGON((300 300,400 400,500 500,300 500,300 400,300 300))
EXPLAIN UPDATE tab SET C2 = ST_GeomFromText('POINT(0 0)')
WHERE MBRContains(tab.c4, @g1);
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE tab range idx3 idx3 34 NULL 1 Using where
EXPLAIN DELETE FROM tab WHERE MBRContains(tab.c4, @g1);
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE tab range idx3 idx3 34 NULL 1 Using where
# Test the MBRWithin
SET @g1 = ST_GeomFromText( 'POLYGON((30 30,40 40,50 50,30 50,30 40,30 30)) ');
EXPLAIN SELECT c1,ST_Astext(c4) FROM tab WHERE MBRWithin(tab.c4, @g1) ORDER BY c1;
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE tab range idx3 idx3 34 NULL 1 Using where; Using filesort
SELECT c1,ST_Astext(c4) FROM tab WHERE MBRWithin(tab.c4, @g1) ORDER BY c1;
c1 ST_Astext(c4)
1 POLYGON((30 30,40 40,50 50,30 50,30 40,30 30))
EXPLAIN UPDATE tab SET C2 = ST_GeomFromText('POINT(0 0)')
WHERE MBRWithin(tab.c4, @g1);
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE tab range idx3 idx3 34 NULL 1 Using where
EXPLAIN DELETE FROM tab WHERE MBRWithin(tab.c4, @g1);
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE tab range idx3 idx3 34 NULL 1 Using where
# Test the ST_Crosses
SET @g1 = ST_GeomFromText('POLYGON((100 200,200 300,400 500,500 300,300 200,100 300,100 200))');
EXPLAIN SELECT c1,ST_Astext(c4) FROM tab WHERE ST_Crosses(tab.c4, @g1) ORDER BY c1;
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE tab range idx3 idx3 34 NULL 1 Using where; Using filesort
SELECT c1,ST_Astext(c4) FROM tab WHERE ST_Crosses(tab.c4, @g1) ORDER BY c1;
c1 ST_Astext(c4)
4 POLYGON((300 300,400 400,500 500,300 500,300 400,300 300))
EXPLAIN UPDATE tab SET C2 = ST_GeomFromText('POINT(0 0)')
WHERE ST_Crosses(tab.c4, @g1);
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE tab range idx3 idx3 34 NULL 1 Using where
EXPLAIN DELETE FROM tab WHERE ST_Crosses(tab.c4, @g1);
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE tab range idx3 idx3 34 NULL 1 Using where
SET @g1 = ST_GeomFromText('LINESTRING( 10 10,30 30,40 40)');
EXPLAIN SELECT c1,ST_Astext(c4) FROM tab WHERE ST_CRosses(tab.c4, @g1) ORDER BY c1;
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE tab range idx3 idx3 34 NULL 1 Using where; Using filesort
SELECT c1,ST_Astext(c4) FROM tab WHERE ST_Crosses(tab.c4, @g1) ORDER BY c1;
c1 ST_Astext(c4)
1 POLYGON((30 30,40 40,50 50,30 50,30 40,30 30))
EXPLAIN UPDATE tab SET C2 = ST_GeomFromText('POINT(0 0)')
WHERE ST_Crosses(tab.c4, @g1);
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE tab range idx3 idx3 34 NULL 1 Using where
EXPLAIN DELETE FROM tab WHERE ST_Crosses(tab.c4, @g1);
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE tab range idx3 idx3 34 NULL 1 Using where
# Test the MBRDisjoint
SET @g1 = ST_GeomFromText('POLYGON((4 -2,5 -4,6 -5,7 -4,7 2,4 -2))');
EXPLAIN SELECT c1,ST_Astext(c4) FROM tab WHERE MBRDisjoint(tab.c4, @g1) ORDER BY c1;
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE tab range idx3 idx3 34 NULL 9 Using where; Using filesort
SELECT c1,ST_Astext(c4) FROM tab WHERE MBRDisjoint(tab.c4, @g1) ORDER BY c1;
c1 ST_Astext(c4)
1 POLYGON((30 30,40 40,50 50,30 50,30 40,30 30))
2 POLYGON((40 50,40 70,50 100,70 100,80 80,70 50,40 50))
4 POLYGON((300 300,400 400,500 500,300 500,300 400,300 300))
5 POLYGON((1010 1010,1020 1020,1030 1030,1040 1030,1020 1010,1010 1010))
6 POLYGON((2010 2010,2020 2020,2030 2030,2040 2030,2020 2010,2010 2010))
7 POLYGON((3010 3010,3020 3020,3030 3030,3040 3030,3020 3010,3010 3010))
8 POLYGON((3010 3010,3020 3020,3030 3030,3040 3030,3020 3010,3010 3010))
9 POLYGON((4010 4010,4020 4020,4030 4030,4040 4030,4020 4010,4010 4010))
10 POLYGON((5010 5010,5020 5020,5030 5030,5040 5030,5020 5010,5010 5010))
EXPLAIN UPDATE tab SET C2 = ST_GeomFromText('POINT(0 0)')
WHERE MBRDisjoint(tab.c4, @g1);
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE tab range idx3 idx3 34 NULL 9 Using where
EXPLAIN DELETE FROM tab WHERE MBRDisjoint(tab.c4, @g1);
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE tab range idx3 idx3 34 NULL 9 Using where
# Test the MBREquals
SET @g1 = ST_GeomFromText('POLYGON((5010 5010,5020 5020,5030 5030,5040 5030,5020 5010,5010 5010))');
EXPLAIN SELECT c1,ST_Astext(c4) FROM tab WHERE MBREquals(tab.c4, @g1) ORDER BY c1;
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE tab range idx3 idx3 34 NULL 1 Using where; Using filesort
SELECT c1,ST_Astext(c4) FROM tab WHERE MBREquals(tab.c4, @g1) ORDER BY c1;
c1 ST_Astext(c4)
10 POLYGON((5010 5010,5020 5020,5030 5030,5040 5030,5020 5010,5010 5010))
EXPLAIN UPDATE tab SET C2 = ST_GeomFromText('POINT(0 0)')
WHERE MBREquals(tab.c4, @g1);
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE tab range idx3 idx3 34 NULL 1 Using where
EXPLAIN DELETE FROM tab WHERE MBREquals(tab.c4, @g1);
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE tab range idx3 idx3 34 NULL 1 Using where
# Test the MBRintersects
SET @g1 = ST_GeomFromText( 'POLYGON((0 0,0 30,30 40,40 50,50 30,0 0))');
EXPLAIN SELECT c1,ST_Astext(c4) FROM tab WHERE MBRIntersects(tab.c4, @g1) ORDER BY c1;
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE tab range idx3 idx3 34 NULL 2 Using where; Using filesort
SELECT c1,ST_Astext(c4) FROM tab WHERE MBRIntersects(tab.c4, @g1) ORDER BY c1;
c1 ST_Astext(c4)
1 POLYGON((30 30,40 40,50 50,30 50,30 40,30 30))
2 POLYGON((40 50,40 70,50 100,70 100,80 80,70 50,40 50))
3 POLYGON((7 1,6 2,6 3,10 3,10 1,7 1))
EXPLAIN UPDATE tab SET C2 = ST_GeomFromText('POINT(0 0)')
WHERE MBRintersects(tab.c4, @g1);
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE tab range idx3 idx3 34 NULL 2 Using where
EXPLAIN DELETE FROM tab WHERE MBRintersects(tab.c4, @g1);
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE tab range idx3 idx3 34 NULL 2 Using where
SET @g1 = ST_GeomFromText('LINESTRING( 30 30,40 40,50 50)');
EXPLAIN SELECT c1,ST_Astext(c4) FROM tab WHERE MBRIntersects(tab.c4, @g1) ORDER BY c1;
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE tab range idx3 idx3 34 NULL 1 Using where; Using filesort
SELECT c1,ST_Astext(c4) FROM tab WHERE MBRIntersects(tab.c4, @g1) ORDER BY c1;
c1 ST_Astext(c4)
1 POLYGON((30 30,40 40,50 50,30 50,30 40,30 30))
2 POLYGON((40 50,40 70,50 100,70 100,80 80,70 50,40 50))
EXPLAIN UPDATE tab SET C2 = ST_GeomFromText('POINT(0 0)')
WHERE MBRintersects(tab.c4, @g1);
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE tab range idx3 idx3 34 NULL 1 Using where
EXPLAIN DELETE FROM tab WHERE MBRintersects(tab.c4, @g1);
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE tab range idx3 idx3 34 NULL 1 Using where
# Test the Overelaps
SET @g1 = ST_GeomFromText( 'POLYGON((0 0,0 2,4 5,5 5,7 1,0 0 ))');
EXPLAIN SELECT c1,ST_Astext(c4) FROM tab WHERE MBROverlaps(tab.c4, @g1) ORDER BY c1;
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE tab range idx3 idx3 34 NULL 1 Using where; Using filesort
SELECT c1,ST_Astext(c4) FROM tab WHERE MBROverlaps(tab.c4, @g1) ORDER BY c1;
c1 ST_Astext(c4)
3 POLYGON((7 1,6 2,6 3,10 3,10 1,7 1))
EXPLAIN UPDATE tab SET C2 = ST_GeomFromText('POINT(0 0)')
WHERE MBROverlaps(tab.c4, @g1);
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE tab range idx3 idx3 34 NULL 1 Using where
EXPLAIN DELETE FROM tab WHERE MBROverlaps(tab.c4, @g1);
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE tab range idx3 idx3 34 NULL 1 Using where
SET @g1 = ST_GeomFromText('LINESTRING(7 1,30 30,1010 3010,1010 2010,3010 3010,4010 4010,5010 5010 )');
EXPLAIN SELECT c1,ST_Astext(c4) FROM tab WHERE MBROverlaps(tab.c4, @g1) ORDER BY c1;
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE tab range idx3 idx3 34 NULL 8 Using where; Using filesort
SELECT c1,ST_Astext(c4) FROM tab WHERE MBROverlaps(tab.c4, @g1) ORDER BY c1;
c1 ST_Astext(c4)
3 POLYGON((7 1,6 2,6 3,10 3,10 1,7 1))
EXPLAIN UPDATE tab SET C2 = ST_GeomFromText('POINT(0 0)')
WHERE MBROverlaps(tab.c4, @g1);
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE tab range idx3 idx3 34 NULL 8 Using where
EXPLAIN DELETE FROM tab WHERE MBROverlaps(tab.c4, @g1);
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE tab range idx3 idx3 34 NULL 8 Using where
# Test the ST_Touches
SET @g1 = ST_GeomFromText( 'POLYGON((0 0,0 30,30 40,40 50,50 30,0 0))');
EXPLAIN SELECT c1,ST_Astext(c4) FROM tab WHERE ST_Touches(tab.c4, @g1) ORDER BY c1;
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE tab range idx3 idx3 34 NULL 2 Using where; Using filesort
SELECT c1,ST_Astext(c4) FROM tab WHERE ST_Touches(tab.c4, @g1) ORDER BY c1;
c1 ST_Astext(c4)
2 POLYGON((40 50,40 70,50 100,70 100,80 80,70 50,40 50))
EXPLAIN UPDATE tab SET C2 = ST_GeomFromText('POINT(0 0)')
WHERE ST_Touches(tab.c4, @g1);
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE tab range idx3 idx3 34 NULL 2 Using where
EXPLAIN DELETE FROM tab WHERE ST_Touches(tab.c4, @g1);
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE tab range idx3 idx3 34 NULL 2 Using where
SET @g1 = ST_GeomFromText('LINESTRING( 100 100,200 200,300 300)');
EXPLAIN SELECT c1,ST_Astext(c4) FROM tab WHERE ST_Touches(tab.c4, @g1) ORDER BY c1;
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE tab range idx3 idx3 34 NULL 1 Using where; Using filesort
SELECT c1,ST_Astext(c4) FROM tab WHERE ST_Touches(tab.c4, @g1) ORDER BY c1;
c1 ST_Astext(c4)
4 POLYGON((300 300,400 400,500 500,300 500,300 400,300 300))
EXPLAIN UPDATE tab SET C2 = ST_GeomFromText('POINT(0 0)')
WHERE ST_Touches(tab.c4, @g1);
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE tab range idx3 idx3 34 NULL 1 Using where
EXPLAIN DELETE FROM tab WHERE ST_Touches(tab.c4, @g1);
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE tab range idx3 idx3 34 NULL 1 Using where
# Test the MBRContains
SET @g1 = ST_GeomFromText( 'POLYGON((7 1,6 2,6 3,10 3,10 1,7 1))');
EXPLAIN SELECT c1,ST_Astext(c4) FROM tab WHERE MBRContains(tab.c4, @g1) ORDER BY c1;
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE tab range idx3 idx3 34 NULL 1 Using where; Using filesort
SELECT c1,ST_Astext(c4) FROM tab WHERE MBRContains(tab.c4, @g1) ORDER BY c1;
c1 ST_Astext(c4)
3 POLYGON((7 1,6 2,6 3,10 3,10 1,7 1))
EXPLAIN UPDATE tab SET C2 = ST_GeomFromText('POINT(0 0)')
WHERE MBRContains(tab.c4, @g1);
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE tab range idx3 idx3 34 NULL 1 Using where
EXPLAIN DELETE FROM tab WHERE MBRContains(tab.c4, @g1);
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE tab range idx3 idx3 34 NULL 1 Using where
SET @g1 = ST_GeomFromText( 'POLYGON((30 30,40 40,50 50,30 50,30 40,30 30)) ');
EXPLAIN SELECT c1,ST_Astext(c4) FROM tab WHERE MBRWithin(tab.c4, @g1) ORDER BY c1;
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE tab range idx3 idx3 34 NULL 1 Using where; Using filesort
SELECT c1,ST_Astext(c4) FROM tab WHERE MBRWithin(tab.c4, @g1) ORDER BY c1;
c1 ST_Astext(c4)
1 POLYGON((30 30,40 40,50 50,30 50,30 40,30 30))
EXPLAIN UPDATE tab SET C2 = ST_GeomFromText('POINT(0 0)')
WHERE MBRWithin(tab.c4, @g1);
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE tab range idx3 idx3 34 NULL 1 Using where
EXPLAIN DELETE FROM tab WHERE MBRWithin(tab.c4, @g1);
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE tab range idx3 idx3 34 NULL 1 Using where
# Test the MBRDisjoint
SET @g1 = ST_GeomFromText('POLYGON((4 -2,5 -4,6 -5,7 -4,7 2,4 -2))');
EXPLAIN SELECT c1,ST_Astext(c4) FROM tab WHERE MBRDisjoint(tab.c4, @g1) ORDER BY c1;
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE tab range idx3 idx3 34 NULL 9 Using where; Using filesort
SELECT c1,ST_Astext(c4) FROM tab WHERE MBRDisjoint(tab.c4, @g1) ORDER BY c1;
c1 ST_Astext(c4)
1 POLYGON((30 30,40 40,50 50,30 50,30 40,30 30))
2 POLYGON((40 50,40 70,50 100,70 100,80 80,70 50,40 50))
4 POLYGON((300 300,400 400,500 500,300 500,300 400,300 300))
5 POLYGON((1010 1010,1020 1020,1030 1030,1040 1030,1020 1010,1010 1010))
6 POLYGON((2010 2010,2020 2020,2030 2030,2040 2030,2020 2010,2010 2010))
7 POLYGON((3010 3010,3020 3020,3030 3030,3040 3030,3020 3010,3010 3010))
8 POLYGON((3010 3010,3020 3020,3030 3030,3040 3030,3020 3010,3010 3010))
9 POLYGON((4010 4010,4020 4020,4030 4030,4040 4030,4020 4010,4010 4010))
10 POLYGON((5010 5010,5020 5020,5030 5030,5040 5030,5020 5010,5010 5010))
EXPLAIN UPDATE tab SET C2 = ST_GeomFromText('POINT(0 0)')
WHERE MBRDisjoint(tab.c4, @g1);
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE tab range idx3 idx3 34 NULL 9 Using where
EXPLAIN DELETE FROM tab WHERE MBRDisjoint(tab.c4, @g1);
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE tab range idx3 idx3 34 NULL 9 Using where
# Test the MBREquals
SET @g1 = ST_GeomFromText('POLYGON((5010 5010,5020 5020,5030 5030,5040 5030,5020 5010,5010 5010))');
EXPLAIN SELECT c1,ST_Astext(c4) FROM tab WHERE MBREquals(tab.c4, @g1) ORDER BY c1;
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE tab range idx3 idx3 34 NULL 1 Using where; Using filesort
SELECT c1,ST_Astext(c4) FROM tab WHERE MBREquals(tab.c4, @g1) ORDER BY c1;
c1 ST_Astext(c4)
10 POLYGON((5010 5010,5020 5020,5030 5030,5040 5030,5020 5010,5010 5010))
EXPLAIN UPDATE tab SET C2 = ST_GeomFromText('POINT(0 0)')
WHERE MBREquals(tab.c4, @g1);
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE tab range idx3 idx3 34 NULL 1 Using where
EXPLAIN DELETE FROM tab WHERE MBREquals(tab.c4, @g1);
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE tab range idx3 idx3 34 NULL 1 Using where
# Test the MBRintersects
SET @g1 = ST_GeomFromText( 'POLYGON((0 0,0 30,30 40,40 50,50 30,0 0))');
EXPLAIN SELECT c1,ST_Astext(c4) FROM tab WHERE MBRIntersects(tab.c4, @g1) ORDER BY c1;
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE tab range idx3 idx3 34 NULL 2 Using where; Using filesort
SELECT c1,ST_Astext(c4) FROM tab WHERE MBRIntersects(tab.c4, @g1) ORDER BY c1;
c1 ST_Astext(c4)
1 POLYGON((30 30,40 40,50 50,30 50,30 40,30 30))
2 POLYGON((40 50,40 70,50 100,70 100,80 80,70 50,40 50))
3 POLYGON((7 1,6 2,6 3,10 3,10 1,7 1))
EXPLAIN UPDATE tab SET C2 = ST_GeomFromText('POINT(0 0)')
WHERE MBRintersects(tab.c4, @g1);
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE tab range idx3 idx3 34 NULL 2 Using where
EXPLAIN DELETE FROM tab WHERE MBRintersects(tab.c4, @g1);
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE tab range idx3 idx3 34 NULL 2 Using where
SET @g1 = ST_GeomFromText('LINESTRING( 30 30,40 40,50 50)');
EXPLAIN SELECT c1,ST_Astext(c4) FROM tab WHERE MBRIntersects(tab.c4, @g1) ORDER BY c1;
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE tab range idx3 idx3 34 NULL 1 Using where; Using filesort
SELECT c1,ST_Astext(c4) FROM tab WHERE MBRIntersects(tab.c4, @g1) ORDER BY c1;
c1 ST_Astext(c4)
1 POLYGON((30 30,40 40,50 50,30 50,30 40,30 30))
2 POLYGON((40 50,40 70,50 100,70 100,80 80,70 50,40 50))
EXPLAIN UPDATE tab SET C2 = ST_GeomFromText('POINT(0 0)')
WHERE MBRintersects(tab.c4, @g1);
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE tab range idx3 idx3 34 NULL 1 Using where
EXPLAIN DELETE FROM tab WHERE MBRintersects(tab.c4, @g1);
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE tab range idx3 idx3 34 NULL 1 Using where
# Test the MBROverelaps
SET @g1 = ST_GeomFromText( 'POLYGON((0 0,0 2,4 5,5 5,7 1,0 0 ))');
EXPLAIN SELECT c1,ST_Astext(c4) FROM tab WHERE MBROverlaps(tab.c4, @g1) ORDER BY c1;
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE tab range idx3 idx3 34 NULL 1 Using where; Using filesort
SELECT c1,ST_Astext(c4) FROM tab WHERE MBROverlaps(tab.c4, @g1) ORDER BY c1;
c1 ST_Astext(c4)
3 POLYGON((7 1,6 2,6 3,10 3,10 1,7 1))
EXPLAIN UPDATE tab SET C2 = ST_GeomFromText('POINT(0 0)')
WHERE MBROverlaps(tab.c4, @g1);
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE tab range idx3 idx3 34 NULL 1 Using where
EXPLAIN DELETE FROM tab WHERE MBROverlaps(tab.c4, @g1);
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE tab range idx3 idx3 34 NULL 1 Using where
# Test the MBRTouches
SET @g1 = ST_GeomFromText( 'POLYGON((0 0,0 30,30 40,40 50,50 30,0 0))');
EXPLAIN SELECT c1,ST_Astext(c4) FROM tab WHERE MBRTouches(tab.c4, @g1) ORDER BY c1;
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE tab range idx3 idx3 34 NULL 2 Using where; Using filesort
SELECT c1,ST_Astext(c4) FROM tab WHERE MBRTouches(tab.c4, @g1) ORDER BY c1;
c1 ST_Astext(c4)
2 POLYGON((40 50,40 70,50 100,70 100,80 80,70 50,40 50))
EXPLAIN UPDATE tab SET C2 = ST_GeomFromText('POINT(0 0)')
WHERE MBRTouches(tab.c4, @g1);
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE tab range idx3 idx3 34 NULL 2 Using where
EXPLAIN DELETE FROM tab WHERE MBRTouches(tab.c4, @g1);
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE tab range idx3 idx3 34 NULL 2 Using where
# Test with Procedure
CREATE PROCEDURE proc_wl6968()
BEGIN
SET @g1 = ST_GeomFromText('POLYGON((5010 5010,5020 5020,5030 5030,5040 5030,5020 5010,5010 5010))');
EXPLAIN SELECT c1,ST_Astext(c4) FROM tab WHERE MBREquals(tab.c4, @g1) ORDER BY c1;
EXPLAIN UPDATE tab SET C2 = ST_GeomFromText('POINT(0 0)')
WHERE MBREquals(tab.c4, @g1);
EXPLAIN DELETE FROM tab WHERE MBREquals(tab.c4, @g1);
END |
CALL proc_wl6968();
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE tab range idx3 idx3 34 NULL 1 Using where; Using filesort
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE tab range idx3 idx3 34 NULL 1 Using where
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE tab range idx3 idx3 34 NULL 1 Using where
# Test the Delete & Update
SET @g1 = ST_GeomFromText( 'POLYGON((30 30,40 40,50 50,30 50,30 40,30 30)) ');
SELECT c1,ST_Astext(c4) FROM tab WHERE MBRWithin(tab.c4, @g1) ORDER BY c1;
c1 ST_Astext(c4)
1 POLYGON((30 30,40 40,50 50,30 50,30 40,30 30))
DELETE FROM tab WHERE MBRWithin(tab.c4, @g1);
SELECT c1,ST_Astext(c4) FROM tab WHERE MBRWithin(tab.c4, @g1) ORDER BY c1;
c1 ST_Astext(c4)
CHECK TABLE tab;
Table Op Msg_type Msg_text
test.tab check status OK
SET @g1 = ST_GeomFromText('POLYGON((100 200,200 300,400 500,500 300,300 200,100 300,100 200))');
SELECT c1,ST_Astext(c4) FROM tab WHERE ST_Crosses(tab.c4, @g1) ORDER BY c1;
c1 ST_Astext(c4)
4 POLYGON((300 300,400 400,500 500,300 500,300 400,300 300))
DELETE FROM tab WHERE ST_Crosses(tab.c4, @g1);
SELECT c1,ST_Astext(c4) FROM tab WHERE ST_Crosses(tab.c4, @g1) ORDER BY c1;
c1 ST_Astext(c4)
SET @g1 = ST_GeomFromText( 'POLYGON((0 0,0 2,4 5,5 5,7 1,0 0 ))');
SET @g2 = ST_GeomFromText( 'POLYGON((1 1,2 2,3 3,10 3,5 1,1 1))');
UPDATE tab SET C4 = @g2 WHERE MBROverlaps(tab.c4, @g1);
SELECT c1,ST_Astext(c4) FROM tab WHERE MBROverlaps(tab.c4, @g1) ORDER BY c1;
c1 ST_Astext(c4)
3 POLYGON((1 1,2 2,3 3,10 3,5 1,1 1))
CHECK TABLE tab;
Table Op Msg_type Msg_text
test.tab check status OK
DROP TABLE tab;
DROP PROCEDURE proc_wl6968;
CREATE TABLE tab(c1 int AUTO_INCREMENT PRIMARY KEY,c2 POINT NOT NULL,
c3 LINESTRING NOT NULL,c4 POLYGON NOT NULL,c5 GEOMETRY NOT NULL)
ENGINE=InnoDB ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=16;
CREATE SPATIAL INDEX idx1 on tab(c2);
CREATE SPATIAL INDEX idx2 on tab(c3) COMMENT 'wl6968';
CREATE SPATIAL INDEX idx3 on tab(c4) KEY_BLOCK_SIZE=16 ;
CREATE SPATIAL INDEX idx4 on tab(c5) KEY_BLOCK_SIZE=16
COMMENT 'Spatial index on Geometry type column';
SHOW INDEXES FROM tab;
Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment Ignored
tab 0 PRIMARY 1 c1 A 0 NULL NULL BTREE NO
tab 1 idx1 1 c2 A NULL 32 NULL SPATIAL NO
tab 1 idx2 1 c3 A NULL 32 NULL SPATIAL wl6968 NO
tab 1 idx3 1 c4 A NULL 32 NULL SPATIAL NO
tab 1 idx4 1 c5 A NULL 32 NULL SPATIAL Spatial index on Geometry type column NO
INSERT INTO tab(c2,c3,c4,c5)
VALUES(ST_GeomFromText('POINT(10 10)'),ST_GeomFromText('LINESTRING(5 5,20 20,30 30)'),
ST_GeomFromText('POLYGON((30 30,40 40,50 50,30 50,30 40,30 30))'),
ST_GeomFromText('POLYGON((30 30,40 40,50 50,30 50,30 40,30 30))'));
INSERT INTO tab(c2,c3,c4,c5)
VALUES(ST_GeomFromText('POINT(20 20)'),ST_GeomFromText('LINESTRING(20 20,30 30,40 40)'),
ST_GeomFromText('POLYGON((40 50,40 70,50 100,70 100,80 80,70 50,40 50))'),
ST_GeomFromText('POLYGON((40 50,40 70,50 100,70 100,80 80,70 50,40 50))'));
INSERT INTO tab(c2,c3,c4,c5)
VALUES(ST_GeomFromText('POINT(4 4)'),ST_GeomFromText('LINESTRING(130 130,140 140,150 150)'),
ST_GeomFromText('POLYGON((7 1,6 2,6 3,10 3,10 1,7 1))'),
ST_GeomFromText('POLYGON((4 -2,5 -4,6 -5,7 -4,7 2,4 -2))'));
INSERT INTO tab(c2,c3,c4,c5)
VALUES(ST_GeomFromText('POINT(50 50)'),ST_GeomFromText('LINESTRING(200 200,300 300,400 400)'),
ST_GeomFromText('POLYGON((300 300,400 400,500 500,300 500,300 400,300 300))'),
ST_GeomFromText('POLYGON((300 300,400 400,500 500,300 500,300 400,300 300))'));
INSERT INTO tab(c2,c3,c4,c5)
VALUES(ST_GeomFromText('POINT(3 3)'),ST_GeomFromText('LINESTRING(400 400,500 500,600 700)'),
ST_GeomFromText('POLYGON((1010 1010,1020 1020,1030 1030,1040 1030,1020 1010,1010 1010))'),
ST_GeomFromText('POLYGON((1010 1010,1020 1020,1030 1030,1040 1030,1020 1010,1010 1010))'));
INSERT INTO tab(c2,c3,c4,c5)
VALUES(ST_GeomFromText('POINT(3 3)'),ST_GeomFromText('LINESTRING(40 40,50 50,60 70)'),
ST_GeomFromText('POLYGON((2010 2010,2020 2020,2030 2030,2040 2030,2020 2010,2010 2010))'),
ST_GeomFromText('POLYGON((2010 2010,2020 2020,2030 2030,2040 2030,2020 2010,2010 2010))'));
INSERT INTO tab(c2,c3,c4,c5)
VALUES(ST_GeomFromText('POINT(60 70)'),ST_GeomFromText('LINESTRING(40 40,50 50,60 70)'),
ST_GeomFromText('POLYGON((3010 3010,3020 3020,3030 3030,3040 3030,3020 3010,3010 3010))'),
ST_GeomFromText('POLYGON((3010 3010,3020 3020,3030 3030,3040 3030,3020 3010,3010 3010))'));
INSERT INTO tab(c2,c3,c4,c5)
VALUES(ST_GeomFromText('POINT(0 0)'),ST_GeomFromText('LINESTRING(40 40,50 50,60 70)'),
ST_GeomFromText('POLYGON((3010 3010,3020 3020,3030 3030,3040 3030,3020 3010,3010 3010))'),
ST_GeomFromText('POLYGON((3010 3010,3020 3020,3030 3030,3040 3030,3020 3010,3010 3010))'));
INSERT INTO tab(c2,c3,c4,c5)
VALUES(ST_GeomFromText('POINT(120 120)'),ST_GeomFromText('LINESTRING(100 100,110 110,120 120)'),
ST_GeomFromText('POLYGON((4010 4010,4020 4020,4030 4030,4040 4030,4020 4010,4010 4010))'),
ST_GeomFromText('POLYGON((4010 4010,4020 4020,4030 4030,4040 4030,4020 4010,4010 4010))'));
INSERT INTO tab(c2,c3,c4,c5)
VALUES(ST_GeomFromText('POINT(160 160)'),ST_GeomFromText('LINESTRING(140 140,150 150,160 160)'),
ST_GeomFromText('POLYGON((5010 5010,5020 5020,5030 5030,5040 5030,5020 5010,5010 5010))'),
ST_GeomFromText('POLYGON((5010 5010,5020 5020,5030 5030,5040 5030,5020 5010,5010 5010))'));
ANALYZE TABLE tab;
Table Op Msg_type Msg_text
test.tab analyze status Engine-independent statistics collected
test.tab analyze status OK
# Test the MBRContains
SET @g1 = ST_GeomFromText( 'POLYGON((7 1,6 2,6 3,10 3,10 1,7 1))');
EXPLAIN SELECT c1,ST_Astext(c4) FROM tab WHERE MBRContains(tab.c4, @g1) ORDER BY c1;
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE tab index idx3 PRIMARY 4 NULL 10 Using where
SELECT c1,ST_Astext(c4) FROM tab WHERE MBRContains(tab.c4, @g1) ORDER BY c1;
c1 ST_Astext(c4)
3 POLYGON((7 1,6 2,6 3,10 3,10 1,7 1))
EXPLAIN UPDATE tab SET C2 = ST_GeomFromText('POINT(0 0)')
WHERE MBRContains(tab.c4, @g1);
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE tab range idx3 idx3 34 NULL 1 Using where
EXPLAIN DELETE FROM tab WHERE MBRContains(tab.c4, @g1);
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE tab range idx3 idx3 34 NULL 1 Using where
SET @g1 = ST_GeomFromText('LINESTRING( 300 300,400 400)');
EXPLAIN SELECT c1,ST_Astext(c4) FROM tab WHERE MBRContains(tab.c4, @g1) ORDER BY c1;
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE tab index idx3 PRIMARY 4 NULL 10 Using where
SELECT c1,ST_Astext(c4) FROM tab WHERE MBRContains(tab.c4, @g1) ORDER BY c1;
c1 ST_Astext(c4)
4 POLYGON((300 300,400 400,500 500,300 500,300 400,300 300))
EXPLAIN UPDATE tab SET C2 = ST_GeomFromText('POINT(0 0)')
WHERE MBRContains(tab.c4, @g1);
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE tab range idx3 idx3 34 NULL 1 Using where
EXPLAIN DELETE FROM tab WHERE MBRContains(tab.c4, @g1);
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE tab range idx3 idx3 34 NULL 1 Using where
# Test the MBRWithin
SET @g1 = ST_GeomFromText( 'POLYGON((30 30,40 40,50 50,30 50,30 40,30 30)) ');
EXPLAIN SELECT c1,ST_Astext(c4) FROM tab WHERE MBRWithin(tab.c4, @g1) ORDER BY c1;
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE tab index idx3 PRIMARY 4 NULL 10 Using where
SELECT c1,ST_Astext(c4) FROM tab WHERE MBRWithin(tab.c4, @g1) ORDER BY c1;
c1 ST_Astext(c4)
1 POLYGON((30 30,40 40,50 50,30 50,30 40,30 30))
EXPLAIN UPDATE tab SET C2 = ST_GeomFromText('POINT(0 0)')
WHERE MBRWithin(tab.c4, @g1);
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE tab range idx3 idx3 34 NULL 1 Using where
EXPLAIN DELETE FROM tab WHERE MBRWithin(tab.c4, @g1);
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE tab range idx3 idx3 34 NULL 1 Using where
# Test the ST_Crosses
SET @g1 = ST_GeomFromText('POLYGON((100 200,200 300,400 500,500 300,300 200,100 300,100 200))');
EXPLAIN SELECT c1,ST_Astext(c4) FROM tab WHERE ST_Crosses(tab.c4, @g1) ORDER BY c1;
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE tab index idx3 PRIMARY 4 NULL 10 Using where
SELECT c1,ST_Astext(c4) FROM tab WHERE ST_Crosses(tab.c4, @g1) ORDER BY c1;
c1 ST_Astext(c4)
4 POLYGON((300 300,400 400,500 500,300 500,300 400,300 300))
EXPLAIN UPDATE tab SET C2 = ST_GeomFromText('POINT(0 0)')
WHERE ST_Crosses(tab.c4, @g1);
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE tab range idx3 idx3 34 NULL 1 Using where
EXPLAIN DELETE FROM tab WHERE ST_Crosses(tab.c4, @g1);
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE tab range idx3 idx3 34 NULL 1 Using where
SET @g1 = ST_GeomFromText('LINESTRING( 10 10,30 30,40 40)');
EXPLAIN SELECT c1,ST_Astext(c4) FROM tab WHERE ST_CRosses(tab.c4, @g1) ORDER BY c1;
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE tab index idx3 PRIMARY 4 NULL 10 Using where
SELECT c1,ST_Astext(c4) FROM tab WHERE ST_Crosses(tab.c4, @g1) ORDER BY c1;
c1 ST_Astext(c4)
1 POLYGON((30 30,40 40,50 50,30 50,30 40,30 30))
EXPLAIN UPDATE tab SET C2 = ST_GeomFromText('POINT(0 0)')
WHERE ST_Crosses(tab.c4, @g1);
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE tab range idx3 idx3 34 NULL 1 Using where
EXPLAIN DELETE FROM tab WHERE ST_Crosses(tab.c4, @g1);
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE tab range idx3 idx3 34 NULL 1 Using where
# Test the MBRDisjoint
SET @g1 = ST_GeomFromText('POLYGON((4 -2,5 -4,6 -5,7 -4,7 2,4 -2))');
EXPLAIN SELECT c1,ST_Astext(c4) FROM tab WHERE MBRDisjoint(tab.c4, @g1) ORDER BY c1;
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE tab index idx3 PRIMARY 4 NULL 10 Using where
SELECT c1,ST_Astext(c4) FROM tab WHERE MBRDisjoint(tab.c4, @g1) ORDER BY c1;
c1 ST_Astext(c4)
1 POLYGON((30 30,40 40,50 50,30 50,30 40,30 30))
2 POLYGON((40 50,40 70,50 100,70 100,80 80,70 50,40 50))
4 POLYGON((300 300,400 400,500 500,300 500,300 400,300 300))
5 POLYGON((1010 1010,1020 1020,1030 1030,1040 1030,1020 1010,1010 1010))
6 POLYGON((2010 2010,2020 2020,2030 2030,2040 2030,2020 2010,2010 2010))
7 POLYGON((3010 3010,3020 3020,3030 3030,3040 3030,3020 3010,3010 3010))
8 POLYGON((3010 3010,3020 3020,3030 3030,3040 3030,3020 3010,3010 3010))
9 POLYGON((4010 4010,4020 4020,4030 4030,4040 4030,4020 4010,4010 4010))
10 POLYGON((5010 5010,5020 5020,5030 5030,5040 5030,5020 5010,5010 5010))
EXPLAIN UPDATE tab SET C2 = ST_GeomFromText('POINT(0 0)')
WHERE MBRDisjoint(tab.c4, @g1);
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE tab range idx3 idx3 34 NULL 9 Using where
EXPLAIN DELETE FROM tab WHERE MBRDisjoint(tab.c4, @g1);
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE tab range idx3 idx3 34 NULL 9 Using where
# Test the MBREquals
SET @g1 = ST_GeomFromText('POLYGON((5010 5010,5020 5020,5030 5030,5040 5030,5020 5010,5010 5010))');
EXPLAIN SELECT c1,ST_Astext(c4) FROM tab WHERE MBREquals(tab.c4, @g1) ORDER BY c1;
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE tab index idx3 PRIMARY 4 NULL 10 Using where
SELECT c1,ST_Astext(c4) FROM tab WHERE MBREquals(tab.c4, @g1) ORDER BY c1;
c1 ST_Astext(c4)
10 POLYGON((5010 5010,5020 5020,5030 5030,5040 5030,5020 5010,5010 5010))
EXPLAIN UPDATE tab SET C2 = ST_GeomFromText('POINT(0 0)')
WHERE MBREquals(tab.c4, @g1);
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE tab range idx3 idx3 34 NULL 1 Using where
EXPLAIN DELETE FROM tab WHERE MBREquals(tab.c4, @g1);
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE tab range idx3 idx3 34 NULL 1 Using where
# Test the MBRintersects
SET @g1 = ST_GeomFromText( 'POLYGON((0 0,0 30,30 40,40 50,50 30,0 0))');
EXPLAIN SELECT c1,ST_Astext(c4) FROM tab WHERE MBRIntersects(tab.c4, @g1) ORDER BY c1;
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE tab index idx3 PRIMARY 4 NULL 10 Using where
SELECT c1,ST_Astext(c4) FROM tab WHERE MBRIntersects(tab.c4, @g1) ORDER BY c1;
c1 ST_Astext(c4)
1 POLYGON((30 30,40 40,50 50,30 50,30 40,30 30))
2 POLYGON((40 50,40 70,50 100,70 100,80 80,70 50,40 50))
3 POLYGON((7 1,6 2,6 3,10 3,10 1,7 1))
EXPLAIN UPDATE tab SET C2 = ST_GeomFromText('POINT(0 0)')
WHERE MBRintersects(tab.c4, @g1);
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE tab range idx3 idx3 34 NULL 2 Using where
EXPLAIN DELETE FROM tab WHERE MBRintersects(tab.c4, @g1);
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE tab range idx3 idx3 34 NULL 2 Using where
SET @g1 = ST_GeomFromText('LINESTRING( 30 30,40 40,50 50)');
EXPLAIN SELECT c1,ST_Astext(c4) FROM tab WHERE MBRIntersects(tab.c4, @g1) ORDER BY c1;
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE tab index idx3 PRIMARY 4 NULL 10 Using where
SELECT c1,ST_Astext(c4) FROM tab WHERE MBRIntersects(tab.c4, @g1) ORDER BY c1;
c1 ST_Astext(c4)
1 POLYGON((30 30,40 40,50 50,30 50,30 40,30 30))
2 POLYGON((40 50,40 70,50 100,70 100,80 80,70 50,40 50))
EXPLAIN UPDATE tab SET C2 = ST_GeomFromText('POINT(0 0)')
WHERE MBRintersects(tab.c4, @g1);
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE tab range idx3 idx3 34 NULL 1 Using where
EXPLAIN DELETE FROM tab WHERE MBRintersects(tab.c4, @g1);
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE tab range idx3 idx3 34 NULL 1 Using where
# Test the Overelaps
SET @g1 = ST_GeomFromText( 'POLYGON((0 0,0 2,4 5,5 5,7 1,0 0 ))');
EXPLAIN SELECT c1,ST_Astext(c4) FROM tab WHERE MBROverlaps(tab.c4, @g1) ORDER BY c1;
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE tab index idx3 PRIMARY 4 NULL 10 Using where
SELECT c1,ST_Astext(c4) FROM tab WHERE MBROverlaps(tab.c4, @g1) ORDER BY c1;
c1 ST_Astext(c4)
3 POLYGON((7 1,6 2,6 3,10 3,10 1,7 1))
EXPLAIN UPDATE tab SET C2 = ST_GeomFromText('POINT(0 0)')
WHERE MBROverlaps(tab.c4, @g1);
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE tab range idx3 idx3 34 NULL 1 Using where
EXPLAIN DELETE FROM tab WHERE MBROverlaps(tab.c4, @g1);
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE tab range idx3 idx3 34 NULL 1 Using where
SET @g1 = ST_GeomFromText('LINESTRING(7 1,30 30,1010 3010,1010 2010,3010 3010,4010 4010,5010 5010 )');
EXPLAIN SELECT c1,ST_Astext(c4) FROM tab WHERE MBROverlaps(tab.c4, @g1) ORDER BY c1;
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE tab index idx3 PRIMARY 4 NULL 10 Using where
SELECT c1,ST_Astext(c4) FROM tab WHERE MBROverlaps(tab.c4, @g1) ORDER BY c1;
c1 ST_Astext(c4)
3 POLYGON((7 1,6 2,6 3,10 3,10 1,7 1))
EXPLAIN UPDATE tab SET C2 = ST_GeomFromText('POINT(0 0)')
WHERE MBROverlaps(tab.c4, @g1);
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE tab range idx3 idx3 34 NULL 8 Using where
EXPLAIN DELETE FROM tab WHERE MBROverlaps(tab.c4, @g1);
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE tab range idx3 idx3 34 NULL 8 Using where
# Test the ST_Touches
SET @g1 = ST_GeomFromText( 'POLYGON((0 0,0 30,30 40,40 50,50 30,0 0))');
EXPLAIN SELECT c1,ST_Astext(c4) FROM tab WHERE ST_Touches(tab.c4, @g1) ORDER BY c1;
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE tab index idx3 PRIMARY 4 NULL 10 Using where
SELECT c1,ST_Astext(c4) FROM tab WHERE ST_Touches(tab.c4, @g1) ORDER BY c1;
c1 ST_Astext(c4)
2 POLYGON((40 50,40 70,50 100,70 100,80 80,70 50,40 50))
EXPLAIN UPDATE tab SET C2 = ST_GeomFromText('POINT(0 0)')
WHERE ST_Touches(tab.c4, @g1);
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE tab range idx3 idx3 34 NULL 2 Using where
EXPLAIN DELETE FROM tab WHERE ST_Touches(tab.c4, @g1);
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE tab range idx3 idx3 34 NULL 2 Using where
SET @g1 = ST_GeomFromText('LINESTRING( 100 100,200 200,300 300)');
EXPLAIN SELECT c1,ST_Astext(c4) FROM tab WHERE ST_Touches(tab.c4, @g1) ORDER BY c1;
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE tab index idx3 PRIMARY 4 NULL 10 Using where
SELECT c1,ST_Astext(c4) FROM tab WHERE ST_Touches(tab.c4, @g1) ORDER BY c1;
c1 ST_Astext(c4)
4 POLYGON((300 300,400 400,500 500,300 500,300 400,300 300))
EXPLAIN UPDATE tab SET C2 = ST_GeomFromText('POINT(0 0)')
WHERE ST_Touches(tab.c4, @g1);
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE tab range idx3 idx3 34 NULL 1 Using where
EXPLAIN DELETE FROM tab WHERE ST_Touches(tab.c4, @g1);
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE tab range idx3 idx3 34 NULL 1 Using where
# Test the MBRContains
SET @g1 = ST_GeomFromText( 'POLYGON((7 1,6 2,6 3,10 3,10 1,7 1))');
EXPLAIN SELECT c1,ST_Astext(c4) FROM tab WHERE MBRContains(tab.c4, @g1) ORDER BY c1;
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE tab index idx3 PRIMARY 4 NULL 10 Using where
SELECT c1,ST_Astext(c4) FROM tab WHERE MBRContains(tab.c4, @g1) ORDER BY c1;
c1 ST_Astext(c4)
3 POLYGON((7 1,6 2,6 3,10 3,10 1,7 1))
EXPLAIN UPDATE tab SET C2 = ST_GeomFromText('POINT(0 0)')
WHERE MBRContains(tab.c4, @g1);
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE tab range idx3 idx3 34 NULL 1 Using where
EXPLAIN DELETE FROM tab WHERE MBRContains(tab.c4, @g1);
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE tab range idx3 idx3 34 NULL 1 Using where
SET @g1 = ST_GeomFromText( 'POLYGON((30 30,40 40,50 50,30 50,30 40,30 30)) ');
EXPLAIN SELECT c1,ST_Astext(c4) FROM tab WHERE MBRWithin(tab.c4, @g1) ORDER BY c1;
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE tab index idx3 PRIMARY 4 NULL 10 Using where
SELECT c1,ST_Astext(c4) FROM tab WHERE MBRWithin(tab.c4, @g1) ORDER BY c1;
c1 ST_Astext(c4)
1 POLYGON((30 30,40 40,50 50,30 50,30 40,30 30))
EXPLAIN UPDATE tab SET C2 = ST_GeomFromText('POINT(0 0)')
WHERE MBRWithin(tab.c4, @g1);
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE tab range idx3 idx3 34 NULL 1 Using where
EXPLAIN DELETE FROM tab WHERE MBRWithin(tab.c4, @g1);
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE tab range idx3 idx3 34 NULL 1 Using where
# Test the MBRDisjoint
SET @g1 = ST_GeomFromText('POLYGON((4 -2,5 -4,6 -5,7 -4,7 2,4 -2))');
EXPLAIN SELECT c1,ST_Astext(c4) FROM tab WHERE MBRDisjoint(tab.c4, @g1) ORDER BY c1;
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE tab index idx3 PRIMARY 4 NULL 10 Using where
SELECT c1,ST_Astext(c4) FROM tab WHERE MBRDisjoint(tab.c4, @g1) ORDER BY c1;
c1 ST_Astext(c4)
1 POLYGON((30 30,40 40,50 50,30 50,30 40,30 30))
2 POLYGON((40 50,40 70,50 100,70 100,80 80,70 50,40 50))
4 POLYGON((300 300,400 400,500 500,300 500,300 400,300 300))
5 POLYGON((1010 1010,1020 1020,1030 1030,1040 1030,1020 1010,1010 1010))
6 POLYGON((2010 2010,2020 2020,2030 2030,2040 2030,2020 2010,2010 2010))
7 POLYGON((3010 3010,3020 3020,3030 3030,3040 3030,3020 3010,3010 3010))
8 POLYGON((3010 3010,3020 3020,3030 3030,3040 3030,3020 3010,3010 3010))
9 POLYGON((4010 4010,4020 4020,4030 4030,4040 4030,4020 4010,4010 4010))
10 POLYGON((5010 5010,5020 5020,5030 5030,5040 5030,5020 5010,5010 5010))
EXPLAIN UPDATE tab SET C2 = ST_GeomFromText('POINT(0 0)')
WHERE MBRDisjoint(tab.c4, @g1);
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE tab range idx3 idx3 34 NULL 9 Using where
EXPLAIN DELETE FROM tab WHERE MBRDisjoint(tab.c4, @g1);
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE tab range idx3 idx3 34 NULL 9 Using where
# Test the MBREquals
SET @g1 = ST_GeomFromText('POLYGON((5010 5010,5020 5020,5030 5030,5040 5030,5020 5010,5010 5010))');
EXPLAIN SELECT c1,ST_Astext(c4) FROM tab WHERE MBREquals(tab.c4, @g1) ORDER BY c1;
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE tab index idx3 PRIMARY 4 NULL 10 Using where
SELECT c1,ST_Astext(c4) FROM tab WHERE MBREquals(tab.c4, @g1) ORDER BY c1;
c1 ST_Astext(c4)
10 POLYGON((5010 5010,5020 5020,5030 5030,5040 5030,5020 5010,5010 5010))
EXPLAIN UPDATE tab SET C2 = ST_GeomFromText('POINT(0 0)')
WHERE MBREquals(tab.c4, @g1);
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE tab range idx3 idx3 34 NULL 1 Using where
EXPLAIN DELETE FROM tab WHERE MBREquals(tab.c4, @g1);
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE tab range idx3 idx3 34 NULL 1 Using where
# Test the MBRintersects
SET @g1 = ST_GeomFromText( 'POLYGON((0 0,0 30,30 40,40 50,50 30,0 0))');
EXPLAIN SELECT c1,ST_Astext(c4) FROM tab WHERE MBRIntersects(tab.c4, @g1) ORDER BY c1;
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE tab index idx3 PRIMARY 4 NULL 10 Using where
SELECT c1,ST_Astext(c4) FROM tab WHERE MBRIntersects(tab.c4, @g1) ORDER BY c1;
c1 ST_Astext(c4)
1 POLYGON((30 30,40 40,50 50,30 50,30 40,30 30))
2 POLYGON((40 50,40 70,50 100,70 100,80 80,70 50,40 50))
3 POLYGON((7 1,6 2,6 3,10 3,10 1,7 1))
EXPLAIN UPDATE tab SET C2 = ST_GeomFromText('POINT(0 0)')
WHERE MBRintersects(tab.c4, @g1);
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE tab range idx3 idx3 34 NULL 2 Using where
EXPLAIN DELETE FROM tab WHERE MBRintersects(tab.c4, @g1);
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE tab range idx3 idx3 34 NULL 2 Using where
SET @g1 = ST_GeomFromText('LINESTRING( 30 30,40 40,50 50)');
EXPLAIN SELECT c1,ST_Astext(c4) FROM tab WHERE MBRIntersects(tab.c4, @g1) ORDER BY c1;
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE tab index idx3 PRIMARY 4 NULL 10 Using where
SELECT c1,ST_Astext(c4) FROM tab WHERE MBRIntersects(tab.c4, @g1) ORDER BY c1;
c1 ST_Astext(c4)
1 POLYGON((30 30,40 40,50 50,30 50,30 40,30 30))
2 POLYGON((40 50,40 70,50 100,70 100,80 80,70 50,40 50))
EXPLAIN UPDATE tab SET C2 = ST_GeomFromText('POINT(0 0)')
WHERE MBRintersects(tab.c4, @g1);
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE tab range idx3 idx3 34 NULL 1 Using where
EXPLAIN DELETE FROM tab WHERE MBRintersects(tab.c4, @g1);
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE tab range idx3 idx3 34 NULL 1 Using where
# Test the MBROverelaps
SET @g1 = ST_GeomFromText( 'POLYGON((0 0,0 2,4 5,5 5,7 1,0 0 ))');
EXPLAIN SELECT c1,ST_Astext(c4) FROM tab WHERE MBROverlaps(tab.c4, @g1) ORDER BY c1;
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE tab index idx3 PRIMARY 4 NULL 10 Using where
SELECT c1,ST_Astext(c4) FROM tab WHERE MBROverlaps(tab.c4, @g1) ORDER BY c1;
c1 ST_Astext(c4)
3 POLYGON((7 1,6 2,6 3,10 3,10 1,7 1))
EXPLAIN UPDATE tab SET C2 = ST_GeomFromText('POINT(0 0)')
WHERE MBROverlaps(tab.c4, @g1);
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE tab range idx3 idx3 34 NULL 1 Using where
EXPLAIN DELETE FROM tab WHERE MBROverlaps(tab.c4, @g1);
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE tab range idx3 idx3 34 NULL 1 Using where
# Test the MBRTouches
SET @g1 = ST_GeomFromText( 'POLYGON((0 0,0 30,30 40,40 50,50 30,0 0))');
EXPLAIN SELECT c1,ST_Astext(c4) FROM tab WHERE MBRTouches(tab.c4, @g1) ORDER BY c1;
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE tab index idx3 PRIMARY 4 NULL 10 Using where
SELECT c1,ST_Astext(c4) FROM tab WHERE MBRTouches(tab.c4, @g1) ORDER BY c1;
c1 ST_Astext(c4)
2 POLYGON((40 50,40 70,50 100,70 100,80 80,70 50,40 50))
EXPLAIN UPDATE tab SET C2 = ST_GeomFromText('POINT(0 0)')
WHERE MBRTouches(tab.c4, @g1);
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE tab range idx3 idx3 34 NULL 2 Using where
EXPLAIN DELETE FROM tab WHERE MBRTouches(tab.c4, @g1);
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE tab range idx3 idx3 34 NULL 2 Using where
# Test the Delete & Update
SET @g1 = ST_GeomFromText('POLYGON((5010 5010,5020 5020,5030 5030,5040 5030,5020 5010,5010 5010))');
SELECT c1,ST_Astext(c4) FROM tab WHERE MBREquals(tab.c4, @g1) ORDER BY c1;
c1 ST_Astext(c4)
10 POLYGON((5010 5010,5020 5020,5030 5030,5040 5030,5020 5010,5010 5010))
DELETE FROM tab WHERE MBREquals(tab.c4, @g1);
SELECT c1,ST_Astext(c4) FROM tab WHERE MBREquals(tab.c4, @g1) ORDER BY c1;
c1 ST_Astext(c4)
CHECK TABLE tab;
Table Op Msg_type Msg_text
test.tab check status OK
SET @g1 = ST_GeomFromText( 'POLYGON((0 0,0 30,30 40,40 50,50 30,0 0))');
SELECT c1,ST_Astext(c4) FROM tab WHERE MBRIntersects(tab.c4, @g1) ORDER BY c1;
c1 ST_Astext(c4)
1 POLYGON((30 30,40 40,50 50,30 50,30 40,30 30))
2 POLYGON((40 50,40 70,50 100,70 100,80 80,70 50,40 50))
3 POLYGON((7 1,6 2,6 3,10 3,10 1,7 1))
DELETE FROM tab WHERE MBRIntersects(tab.c4, @g1);
SELECT c1,ST_Astext(c4) FROM tab WHERE MBRIntersects(tab.c4, @g1) ORDER BY c1;
c1 ST_Astext(c4)
CHECK TABLE tab;
Table Op Msg_type Msg_text
test.tab check status OK
DROP TABLE tab;
# Test check constraint on spatial column
CREATE TABLE tab(c1 POINT NOT NULL,CONSTRAINT tab_const check(c1 > 0) ) ENGINE=InnoDB;
ERROR HY000: Illegal parameter data types point and int for operation '>'
CREATE TABLE tab(c1 POINT NOT NULL,CONSTRAINT tab_const check(CAST(c1 AS BINARY) > 0) ) ENGINE=InnoDB;
CREATE SPATIAL INDEX idx1 ON tab(c1) ;
SHOW CREATE TABLE tab;
Table Create Table
tab CREATE TABLE `tab` (
`c1` point NOT NULL,
SPATIAL KEY `idx1` (`c1`),
CONSTRAINT `tab_const` CHECK (cast(`c1` as char charset binary) > 0)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
SHOW INDEX FROM tab;
Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment Ignored
tab 1 idx1 1 c1 A NULL 32 NULL SPATIAL NO
set @g1 = ST_GeomFromText('POINT(-1 -2)');
SELECT ST_AsText(c1) FROM tab;
ST_AsText(c1)
DROP table tab;
create table `t1`(`a` geometry not null,`b` linestring not null,
primary key (`b`(192),`a`(141)),spatial key (`b`)) engine=innodb;
insert into `t1` values(
point(1,1),
linestring(point(1,1),point(1,1))
);
insert into `t1` values
(
polygon(
linestring(point(1,1),point(1,1)),
linestring(point(1,1),point(11,1))
),
linestring(point(1,1),point(1,1))
);
ERROR 23000: Column 'a' cannot be null
select 1 from t1 where st_intersects(
geometrycollection(point(1,-1)),b
);
1
drop table t1;
CREATE TABLE t1(c1 POINT NOT NULL);
DROP TABLE mysql.innodb_table_stats;
CALL mtr.add_suppression("InnoDB: Table `mysql`.`innodb_table_stats` not found.");
CALL mtr.add_suppression("InnoDB: Fetch of persistent statistics requested for table `test`.`t1` but the required system tables mysql.innodb_table_stats and mysql.innodb_index_stats are not present or have unexpected structure. Using transient stats instead.");
CREATE SPATIAL INDEX idx2 ON t1(c1);
DROP TABLE t1;
CREATE TABLE mysql.innodb_table_stats (
database_name varchar(64) COLLATE utf8_bin NOT NULL,
table_name varchar(199) COLLATE utf8_bin NOT NULL,
last_update timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
n_rows bigint(20) unsigned NOT NULL,
clustered_index_size bigint(20) unsigned NOT NULL,
sum_of_other_index_sizes bigint(20) unsigned NOT NULL,
PRIMARY KEY (`database_name`,`table_name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin STATS_PERSISTENT=0;
Reference in a new issue View git blame Copy permalink