| 1 |
# This is to test create GIS index with algorithm=inplace. |
| 2 |
|
| 3 |
--source include/have_innodb.inc |
| 4 |
--source include/have_debug.inc |
| 5 |
--source include/have_sequence.inc |
| 6 |
--source include/no_valgrind_without_big.inc |
| 7 |
|
| 8 |
# Create table with geometry column |
| 9 |
CREATE TABLE t1 (c1 INT, c2 GEOMETRY NOT NULL, c3 GEOMETRY NOT NULL) ENGINE=INNODB; |
| 10 |
CREATE TEMPORARY TABLE t LIKE t1; |
| 11 |
|
| 12 |
# Insert enough values |
| 13 |
INSERT INTO t VALUES |
| 14 |
(1, Point(1,1), ST_GeomFromText('POLYGON (( 0 -1, 0 1, 2 1, 2 -1, 0 -1))')), |
| 15 |
(2, Point(2,2), ST_GeomFromText('POLYGON (( 0 -3, 0 -1, 2 -1, 2 -3, 0 -3))')), |
| 16 |
(3, Point(3,3), ST_GeomFromText('POLYGON (( 1 0, 1 2, 3 2, 3 0, 1 0))')), |
| 17 |
(4, Point(4,4), ST_GeomFromText('POLYGON (( -3 0, -3 2, -1 2, -1 0, -3 0))')), |
| 18 |
(5, Point(5,5), ST_GeomFromText('POLYGON (( 0 0, 0 1, 1 1, 1 0, 0 0))')), |
| 19 |
(6, Point(6,6), ST_GeomFromText('POLYGON (( 2 0, 2 2, 4 2, 4 0, 2 0))')), |
| 20 |
(7, Point(7,7), ST_GeomFromText('POLYGON (( 0 3, 0 5, 2 5, 2 3, 0 3))')), |
| 21 |
(8, Point(8,8), ST_GeomFromText('POLYGON (( 0 1, 0 3, 2 3, 2 1, 0 1))')), |
| 22 |
(9, Point(9,9), ST_GeomFromText('POLYGON (( 0 0, 0 3, 3 3, 3 0, 0 0))')); |
| 23 |
|
| 24 |
INSERT INTO t1 SELECT t.* FROM t, seq_1_to_512; |
| 25 |
DROP TEMPORARY TABLE t; |
| 26 |
|
| 27 |
# create spatial index |
| 28 |
CREATE SPATIAL INDEX idx ON t1(c2); |
| 29 |
|
| 30 |
SELECT COUNT(*) FROM t1; |
| 31 |
|
| 32 |
SET @g1 = ST_GeomFromText('Polygon((0 0,0 100,100 100,100 0,0 0))'); |
| 33 |
SELECT COUNT(*) FROM t1 WHERE MBRWithin(t1.c2, @g1); |
| 34 |
|
| 35 |
SET @g1 = ST_GeomFromText('Polygon((10 10,10 800,800 800,800 10,10 10))'); |
| 36 |
SELECT COUNT(*) FROM t1 WHERE MBRWithin(t1.c2, @g1); |
| 37 |
|
| 38 |
ALTER TABLE t1 DROP INDEX idx, ADD SPATIAL INDEX idx3(c2); |
| 39 |
|
| 40 |
SET SESSION debug_dbug="+d,row_merge_instrument_log_check_flush"; |
| 41 |
ALTER TABLE t1 DROP INDEX idx3, ADD SPATIAL INDEX idx4(c2), ADD SPATIAL INDEX idx5(c3); |
| 42 |
ALTER TABLE t1 FORCE; |
| 43 |
|
| 44 |
# Clean up. |
| 45 |
DROP TABLE t1; |