diff options
Diffstat (limited to 'mysql-test/suite/innodb_gis/r/point_basic.result')
| -rw-r--r-- | mysql-test/suite/innodb_gis/r/point_basic.result | 1570 |
1 files changed, 1570 insertions, 0 deletions
diff --git a/mysql-test/suite/innodb_gis/r/point_basic.result b/mysql-test/suite/innodb_gis/r/point_basic.result new file mode 100644 index 00000000000..faaef07c112 --- /dev/null +++ b/mysql-test/suite/innodb_gis/r/point_basic.result @@ -0,0 +1,1570 @@ +# +# Test for basic POINT operation +# +CREATE TABLE gis_point(p1 POINT, p2 POINT) ENGINE=InnoDB; +INSERT INTO gis_point VALUES +(ST_PointFromText('POINT(0 0)'), ST_PointFromText('POINT(0 0)')), +(ST_PointFromText('POINT(10 -20)'), ST_PointFromText('POINT(10 -20)')), +(ST_PointFromText('POINT(3.1415926 3.535897)'), ST_PointFromText('POINT(-3.932626 -3.488272)')), +(ST_PointFromText('POINT(-111.9876 234.1357)'), ST_PointFromText('POINT(-957.1914 958.1919)')); +SELECT ST_X(p1), ST_Y(p2) FROM gis_point; +ST_X(p1) ST_Y(p2) +0 0 +10 -20 +3.1415926 -3.488272 +-111.9876 958.1919 +DROP TABLE gis_point; +# +# Test when POINT is not on any indexes +# +CREATE TABLE gis_point(p1 POINT, p2 POINT) ENGINE=InnoDB; +INSERT INTO gis_point VALUES +(ST_PointFromText('POINT(100.32374832 101.23741821)'), ST_PointFromText('POINT(100.32374832 101.23741821)')), +(ST_PointFromText('POINT(105.34523342 103.18492302)'), ST_PointFromText('POINT(100.32374832 101.23741821)')), +(ST_PointFromText('POINT(100.32374832 101.23741821)'), ST_PointFromText('POINT(200.32247328 101.86728201)')), +(ST_PointFromText('POINT(100.32374832 101.23741821)'), ST_PointFromText('POINT(100.32374832 101.98527111)')); +EXPLAIN SELECT ST_AsText(p1), ST_AsText(p2) FROM gis_point GROUP BY p1, p2; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE gis_point ALL NULL NULL NULL NULL 4 # +SELECT ST_AsText(p1), ST_AsText(p2) FROM gis_point GROUP BY p1, p2; +ST_AsText(p1) ST_AsText(p2) +POINT(100.32374832 101.23741821) POINT(100.32374832 101.98527111) +POINT(100.32374832 101.23741821) POINT(100.32374832 101.23741821) +POINT(100.32374832 101.23741821) POINT(200.32247328 101.86728201) +POINT(105.34523342 103.18492302) POINT(100.32374832 101.23741821) +SELECT ST_AsText(p1) FROM gis_point WHERE p1 = p2; +ST_AsText(p1) +POINT(100.32374832 101.23741821) +SELECT ST_AsText(p1), ST_AsText(p2) FROM gis_point WHERE p1 = ST_PointFromText('POINT(100.32374832 101.23741821)'); +ST_AsText(p1) ST_AsText(p2) +POINT(100.32374832 101.23741821) POINT(100.32374832 101.23741821) +POINT(100.32374832 101.23741821) POINT(200.32247328 101.86728201) +POINT(100.32374832 101.23741821) POINT(100.32374832 101.98527111) +SELECT ST_AsText(p1), ST_AsText(p2) FROM gis_point WHERE p2 = ST_PointFromText('POINT(200.32247328 101.86728201)'); +ST_AsText(p1) ST_AsText(p2) +POINT(100.32374832 101.23741821) POINT(200.32247328 101.86728201) +DROP TABLE gis_point; +# +# Test some ALTER TABLE operations on POINT tables +# +CREATE TABLE t1 ( +p POINT NOT NULL, +g GEOMETRY NOT NULL +) ENGINE=InnoDB; +INSERT INTO t1 VALUES(ST_PointFromText('POINT(0 1)'), ST_PointFromText('POINT(10 11)')); +INSERT INTO t1 VALUES(ST_PointFromText('POINT(1 1)'), ST_PointFromText('POINT(10 12)')); +INSERT INTO t1 VALUES(ST_PointFromText('POINT(1 0)'), ST_PointFromText('POINT(10 13)')); +INSERT INTO t1 VALUES(ST_PointFromText('POINT(0 0)'), ST_PointFromText('POINT(10 14)')); +SELECT ST_AsText(p), ST_AsText(g) FROM t1; +ST_AsText(p) ST_AsText(g) +POINT(0 1) POINT(10 11) +POINT(1 1) POINT(10 12) +POINT(1 0) POINT(10 13) +POINT(0 0) POINT(10 14) +SELECT name, mtype, prtype, len FROM INFORMATION_SCHEMA.INNODB_SYS_COLUMNS WHERE name = 'p' OR name = 'g'; +name mtype prtype len +p 14 1535 12 +g 14 1535 12 +ALTER TABLE t1 ADD COLUMN p1 POINT, ADD COLUMN p2 POINT, ADD KEY(p); +SELECT name, mtype, prtype, len FROM INFORMATION_SCHEMA.INNODB_SYS_COLUMNS WHERE name = 'p' OR name = 'g' OR name = 'p1' OR name = 'p2'; +name mtype prtype len +p 14 1535 12 +g 14 1535 12 +p1 14 1279 12 +p2 14 1279 12 +# NOT NULL POINT will use '' +SELECT count(*) AS `Expect 4` FROM t1 WHERE p1 = ''; +Expect 4 +0 +SELECT count(*) AS `Expect 4` FROM t1 WHERE p2 = ''; +Expect 4 +0 +SELECT ST_AsText(p), ST_AsText(p1) FROM t1 WHERE p = p1; +ST_AsText(p) ST_AsText(p1) +SELECT ST_AsText(p), ST_AsText(p1) FROM t1 WHERE p = p2; +ST_AsText(p) ST_AsText(p1) +ALTER TABLE t1 DROP COLUMN p2; +# NULLABLE POINT will use NULL +ALTER TABLE t1 ADD COLUMN p2 POINT, ADD KEY(p2); +SELECT name, mtype, prtype, len FROM INFORMATION_SCHEMA.INNODB_SYS_COLUMNS WHERE name = 'p' OR name = 'g' OR name = 'p1' OR name = 'p2'; +name mtype prtype len +p 14 1535 12 +g 14 1535 12 +p1 14 1279 12 +p2 14 1279 12 +SELECT count(*) AS `Expect 4` FROM t1 WHERE p2 IS NULL; +Expect 4 +4 +UPDATE t1 SET p2 = ST_PointFromText('POINT(10 20)'); +UPDATE t1 SET p1 = ST_PointFromText('POINT(10 20)'); +ALTER TABLE t1 DROP COLUMN p2; +SELECT name, mtype, prtype, len FROM INFORMATION_SCHEMA.INNODB_SYS_COLUMNS WHERE name = 'p' OR name = 'g' OR name = 'p1'; +name mtype prtype len +p 14 1535 12 +g 14 1535 12 +p1 14 1279 12 +SELECT ST_AsText(p), ST_AsText(p1) FROM t1 WHERE p = p1; +ST_AsText(p) ST_AsText(p1) +INSERT INTO t1 VALUES (ST_PointFromText('POINT(0.5 0.5)'), ST_PointFromText('POINT(0 1)'), ST_PointFromText('POINT(10 19)')); +SELECT ST_AsText(p1) FROM t1; +ST_AsText(p1) +POINT(10 20) +POINT(10 20) +POINT(10 20) +POINT(10 20) +POINT(10 19) +DELETE FROM t1 WHERE p1 = ST_PointFromText('POINT(10 19)'); +SELECT ST_AsText(p1) FROM t1; +ST_AsText(p1) +POINT(10 20) +POINT(10 20) +POINT(10 20) +POINT(10 20) +# Add spatial keys on the table +ALTER TABLE t1 ADD SPATIAL(p), ADD SPATIAL(p1); +ERROR 42000: All parts of a SPATIAL index must be NOT NULL +SELECT name, mtype, prtype, len FROM INFORMATION_SCHEMA.INNODB_SYS_COLUMNS WHERE name = 'p' OR name = 'g' OR name = 'p1'; +name mtype prtype len +p 14 1535 12 +g 14 1535 12 +p1 14 1279 12 +SELECT ST_AsText(p), ST_AsText(p1) FROM t1 WHERE p1 = ST_PointFromText('POINT(10 30)'); +ST_AsText(p) ST_AsText(p1) +SELECT ST_AsText(p), ST_AsText(p1) FROM t1 WHERE p1 = ST_PointFromText('POINT(10 20)'); +ST_AsText(p) ST_AsText(p1) +POINT(0 1) POINT(10 20) +POINT(1 1) POINT(10 20) +POINT(1 0) POINT(10 20) +POINT(0 0) POINT(10 20) +SELECT ST_AsText(p), ST_AsText(p1) FROM t1 WHERE MBRWithin(p1, ST_GeomFromText('POLYGON((5 5, 20 5, 20 21, 5 21, 5 5))')); +ST_AsText(p) ST_AsText(p1) +POINT(0 1) POINT(10 20) +POINT(1 1) POINT(10 20) +POINT(1 0) POINT(10 20) +POINT(0 0) POINT(10 20) +SELECT ST_AsText(p), ST_AsText(p1) FROM t1 WHERE MBRWithin(p, ST_GeomFromText('POLYGON((-1 0.5, -1 -0.5, 1 -0.5, 1 0.5, -1 0.5))')); +ST_AsText(p) ST_AsText(p1) +POINT(1 0) POINT(10 20) +POINT(0 0) POINT(10 20) +# Drop spatial keys on the table +ALTER TABLE t1 DROP KEY p, DROP KEY p1; +ERROR 42000: Can't DROP INDEX `p1`; check that it exists +SELECT name, mtype, prtype, len FROM INFORMATION_SCHEMA.INNODB_SYS_COLUMNS WHERE name = 'p' OR name = 'g' OR name = 'p1'; +name mtype prtype len +p 14 1535 12 +g 14 1535 12 +p1 14 1279 12 +SELECT ST_AsText(p), ST_AsText(p1) FROM t1 WHERE p1 = ST_PointFromText('POINT(10 30)'); +ST_AsText(p) ST_AsText(p1) +SELECT ST_AsText(p), ST_AsText(p1) FROM t1 WHERE p1 = ST_PointFromText('POINT(10 20)'); +ST_AsText(p) ST_AsText(p1) +POINT(0 1) POINT(10 20) +POINT(1 1) POINT(10 20) +POINT(1 0) POINT(10 20) +POINT(0 0) POINT(10 20) +SELECT ST_AsText(p), ST_AsText(p1) FROM t1 WHERE MBRWithin(p1, ST_GeomFromText('POLYGON((5 5, 20 5, 20 21, 5 21, 5 5))')); +ST_AsText(p) ST_AsText(p1) +POINT(0 1) POINT(10 20) +POINT(1 1) POINT(10 20) +POINT(1 0) POINT(10 20) +POINT(0 0) POINT(10 20) +SELECT ST_AsText(p), ST_AsText(p1) FROM t1 WHERE MBRWithin(p, ST_GeomFromText('POLYGON((-1 0.5, -1 -0.5, 1 -0.5, 1 0.5, -1 0.5))')); +ST_AsText(p) ST_AsText(p1) +POINT(1 0) POINT(10 20) +POINT(0 0) POINT(10 20) +TRUNCATE t1; +ALTER TABLE t1 DROP COLUMN p, DROP COLUMN p1; +SELECT name, mtype, prtype, len FROM INFORMATION_SCHEMA.INNODB_SYS_COLUMNS WHERE name = 'p' OR name = 'g' OR name = 'p1'; +name mtype prtype len +g 14 1535 12 +ALTER TABLE t1 ADD COLUMN p POINT, ADD COLUMN p1 POINT; +SELECT name, mtype, prtype, len FROM INFORMATION_SCHEMA.INNODB_SYS_COLUMNS WHERE name = 'p' OR name = 'g' OR name = 'p1'; +name mtype prtype len +g 14 1535 12 +p 14 1279 12 +p1 14 1279 12 +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `g` geometry NOT NULL, + `p` point DEFAULT NULL, + `p1` point DEFAULT NULL +) ENGINE=InnoDB DEFAULT CHARSET=latin1 +INSERT INTO t1 VALUES(ST_PointFromText('POINT(0.5 0.5)'), ST_PointFromText('POINT(0 1)'), ST_PointFromText('POINT(10 19)')); +INSERT INTO t1 VALUES(ST_PointFromText('POINT(0.6 0.6)'), ST_PointFromText('POINT(1 1)'), ST_PointFromText('POINT(10 20)')); +INSERT INTO t1 VALUES(ST_PointFromText('POINT(0.7 0.7)'), ST_PointFromText('POINT(1 0)'), ST_PointFromText('POINT(10 21)')); +INSERT INTO t1 VALUES(ST_PointFromText('POINT(0.8 0.8)'), ST_PointFromText('POINT(0 0)'), ST_PointFromText('POINT(10 22)')); +SELECT ST_AsText(p), ST_AsText(p1) FROM t1 WHERE p1 = ST_PointFromText('POINT(10 30)'); +ST_AsText(p) ST_AsText(p1) +SELECT ST_AsText(p), ST_AsText(p1) FROM t1 WHERE p1 = ST_PointFromText('POINT(10 20)'); +ST_AsText(p) ST_AsText(p1) +POINT(1 1) POINT(10 20) +SELECT ST_AsText(p), ST_AsText(p1) FROM t1 WHERE MBRWithin(p1, ST_GeomFromText('POLYGON((5 5, 20 5, 20 21, 5 21, 5 5))')); +ST_AsText(p) ST_AsText(p1) +POINT(0 1) POINT(10 19) +POINT(1 1) POINT(10 20) +POINT(1 0) POINT(10 21) +SELECT ST_AsText(p), ST_AsText(p1) FROM t1 WHERE MBRWithin(p, ST_GeomFromText('POLYGON((-1 0.5, -1 -0.5, 1 -0.5, 1 0.5, -1 0.5))')); +ST_AsText(p) ST_AsText(p1) +POINT(1 0) POINT(10 21) +POINT(0 0) POINT(10 22) +ALTER TABLE t1 DROP COLUMN p1, ADD COLUMN p1 POINT, CHANGE COLUMN p pp POINT AFTER p1; +SELECT name, mtype, prtype, len FROM INFORMATION_SCHEMA.INNODB_SYS_COLUMNS WHERE name = 'pp' OR name = 'g' OR name = 'p1'; +name mtype prtype len +g 14 1535 12 +p1 14 1279 12 +pp 14 1279 12 +UPDATE t1 SET p1 = ST_PointFromText('POINT(5 5)'); +SELECT ST_AsText(pp), ST_AsText(p1) FROM t1; +ST_AsText(pp) ST_AsText(p1) +POINT(0 1) POINT(5 5) +POINT(1 1) POINT(5 5) +POINT(1 0) POINT(5 5) +POINT(0 0) POINT(5 5) +ALTER TABLE t1 ADD SPATIAL(p1), ADD SPATIAL(pp), ALGORITHM = COPY; +ERROR 42000: All parts of a SPATIAL index must be NOT NULL +SELECT name, mtype, prtype, len FROM INFORMATION_SCHEMA.INNODB_SYS_COLUMNS WHERE name = 'pp' OR name = 'g' OR name = 'p1'; +name mtype prtype len +g 14 1535 12 +p1 14 1279 12 +pp 14 1279 12 +SELECT ST_AsText(pp), ST_AsText(p1) FROM t1 WHERE p1 = ST_PointFromText('POINT(10 30)'); +ST_AsText(pp) ST_AsText(p1) +SELECT ST_AsText(pp), ST_AsText(p1) FROM t1 WHERE p1 = ST_PointFromText('POINT(10 20)'); +ST_AsText(pp) ST_AsText(p1) +SELECT ST_AsText(pp), ST_AsText(p1) FROM t1 WHERE MBRWithin(p1, ST_GeomFromText('POLYGON((5 5, 20 5, 20 21, 5 21, 5 5))')) ORDER BY pp; +ST_AsText(pp) ST_AsText(p1) +POINT(0 0) POINT(5 5) +POINT(0 1) POINT(5 5) +POINT(1 0) POINT(5 5) +POINT(1 1) POINT(5 5) +SELECT ST_AsText(pp), ST_AsText(p1) FROM t1 WHERE MBRWithin(pp, ST_GeomFromText('POLYGON((-1 0.5, -1 -0.5, 1 -0.5, 1 0.5, -1 0.5))')) ORDER BY pp; +ST_AsText(pp) ST_AsText(p1) +POINT(0 0) POINT(5 5) +POINT(1 0) POINT(5 5) +DROP TABLE t1; +# +# Test when the POINT is on B-TREE +# +CREATE TABLE gis_point(fid INTEGER NOT NULL PRIMARY KEY AUTO_INCREMENT, p POINT, KEY(p)) ENGINE=InnoDB; +INSERT INTO gis_point VALUES +(101, ST_PointFromText('POINT(10 10)')), +(102, ST_PointFromText('POINT(20 10)')), +(103, ST_PointFromText('POINT(20 20)')), +(104, ST_PointFromWKB(ST_AsWKB(ST_PointFromText('POINT(10 20)')))); +SELECT ST_AsText(p) FROM gis_point; +ST_AsText(p) +POINT(10 10) +POINT(20 10) +POINT(20 20) +POINT(10 20) +SELECT ST_AsText(p) FROM gis_point WHERE p = ST_PointFromText('POINT(20 20)'); +ST_AsText(p) +POINT(20 20) +INSERT INTO gis_point VALUES +(201, ST_PointFromText('POINT(100.32374832 101.23741821)')), +(202, ST_PointFromText('POINT(102.43287328 100.23489233)')), +(203, ST_PointFromText('POINT(101.43284962 100.45892392)')), +(204, ST_PointFromWKB(ST_AsWKB(ST_PointFromText('POINT(103.43718640 105.248206478)')))), +(205, ST_PointFromText('POINT(101.43284962 100.45892392)')), +(206, ST_PointFromWKB(ST_AsWKB(ST_PointFromText('POINT(103.43718640 105.248206478)')))); +'The ORDER BY will use filesort' +EXPLAIN SELECT ST_AsText(p) FROM gis_point ORDER BY p; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE gis_point ALL NULL NULL NULL NULL 10 # +SELECT ST_AsText(p) FROM gis_point ORDER BY p; +ST_AsText(p) +POINT(10 10) +POINT(10 20) +POINT(20 10) +POINT(20 20) +POINT(101.43284962 100.45892392) +POINT(101.43284962 100.45892392) +POINT(100.32374832 101.23741821) +POINT(102.43287328 100.23489233) +POINT(103.4371864 105.248206478) +POINT(103.4371864 105.248206478) +SELECT ST_AsText(p), COUNT(*) FROM gis_point GROUP BY p; +ST_AsText(p) COUNT(*) +POINT(10 10) 1 +POINT(10 20) 1 +POINT(20 10) 1 +POINT(20 20) 1 +POINT(101.43284962 100.45892392) 2 +POINT(100.32374832 101.23741821) 1 +POINT(102.43287328 100.23489233) 1 +POINT(103.4371864 105.248206478) 2 +TRUNCATE gis_point; +INSERT INTO gis_point VALUES +(101, ST_PointFromText('POINT(10 10)')), +(102, ST_PointFromText('POINT(20 10)')), +(103, ST_PointFromText('POINT(20 20)')), +(104, ST_PointFromWKB(ST_AsWKB(ST_PointFromText('POINT(10 20)')))); +# Check if we can create prefix index on POINT +ALTER TABLE gis_point ADD COLUMN g POINT, ADD KEY(g(8)); +INSERT INTO gis_point VALUES +(105, ST_PointFromText('POINT(25 15)'), ST_PointFromText('POINT(100 100)')), +(106, ST_PointFromText('POINT(25 25)'), ST_PointFromText('POINT(110 110)')); +SELECT ST_AsText(p) FROM gis_point; +ST_AsText(p) +POINT(10 10) +POINT(20 10) +POINT(20 20) +POINT(10 20) +POINT(25 15) +POINT(25 25) +SELECT ST_AsText(p), ST_AsText(g) FROM gis_point WHERE g IS NULL; +ST_AsText(p) ST_AsText(g) +POINT(10 10) NULL +POINT(20 10) NULL +POINT(20 20) NULL +POINT(10 20) NULL +UPDATE gis_point SET g = ST_PointFromText('POINT(200 200)') WHERE g IS NULL OR g = ST_PointFromText('POINT(110 110)'); +SELECT ST_AsText(g) FROM gis_point WHERE g IS NULL OR g = ST_PointFromText('POINT(200 200)'); +ST_AsText(g) +POINT(200 200) +POINT(200 200) +POINT(200 200) +POINT(200 200) +POINT(200 200) +# Check the information schema tables +SELECT table_name, column_name, data_type, column_type FROM INFORMATION_SCHEMA.COLUMNS WHERE table_name='gis_point' AND (column_name = 'p' OR column_name = 'g'); +table_name column_name data_type column_type +gis_point p point point +gis_point g point point +SELECT name, mtype, prtype, len FROM INFORMATION_SCHEMA.INNODB_SYS_COLUMNS WHERE name = 'p' OR name = 'g'; +name mtype prtype len +p 14 1279 12 +g 14 1279 12 +SELECT length(p) FROM gis_point; +length(p) +25 +25 +25 +25 +25 +25 +ALTER TABLE gis_point DROP COLUMN g, ALGORITHM = COPY; +TRUNCATE gis_point; +INSERT INTO gis_point VALUES +(101, ST_PointFromText('POINT(10 10)')), +(102, ST_PointFromText('POINT(20 10)')), +(103, ST_PointFromText('POINT(20 20)')), +(104, ST_PointFromWKB(ST_AsWKB(ST_PointFromText('POINT(10 20)')))); +# Check if we can create prefix index on POINT +ALTER TABLE gis_point ADD COLUMN g POINT, ADD KEY(g(8)); +INSERT INTO gis_point VALUES +(105, ST_PointFromText('POINT(25 15)'), ST_PointFromText('POINT(100 100)')), +(106, ST_PointFromText('POINT(25 25)'), ST_PointFromText('POINT(110 110)')); +SELECT ST_AsText(p) FROM gis_point; +ST_AsText(p) +POINT(10 10) +POINT(20 10) +POINT(20 20) +POINT(10 20) +POINT(25 15) +POINT(25 25) +SELECT ST_AsText(p), ST_AsText(g) FROM gis_point WHERE g IS NULL; +ST_AsText(p) ST_AsText(g) +POINT(10 10) NULL +POINT(20 10) NULL +POINT(20 20) NULL +POINT(10 20) NULL +UPDATE gis_point SET g = ST_PointFromText('POINT(200 200)') WHERE g IS NULL OR g = ST_PointFromText('POINT(110 110)'); +SELECT ST_AsText(g) FROM gis_point WHERE g IS NULL OR g = ST_PointFromText('POINT(200 200)'); +ST_AsText(g) +POINT(200 200) +POINT(200 200) +POINT(200 200) +POINT(200 200) +POINT(200 200) +CHECK TABLE gis_point; +Table Op Msg_type Msg_text +test.gis_point check status OK +SELECT name, mtype, prtype, len FROM INFORMATION_SCHEMA.INNODB_SYS_COLUMNS WHERE name = 'p' OR name = 'g'; +name mtype prtype len +p 14 1279 12 +g 14 1279 12 +DROP TABLE gis_point; +# +# Check the mtype of other geometry data types should be 15 +# +CREATE TABLE g ( +geom GEOMETRY NOT NULL, +l LINESTRING NOT NULL, +poly POLYGON NOT NULL, +mp MULTIPOINT NOT NULL, +ml MULTILINESTRING NOT NULL, +mpoly MULTIPOLYGON NOT NULL, +gc GEOMETRYCOLLECTION NOT NULL +) ENGINE=InnoDB; +SELECT name, mtype, prtype, len FROM INFORMATION_SCHEMA.INNODB_SYS_COLUMNS WHERE name = 'p' OR name = 'g' OR name = 'geom' OR name = 'l' OR name = 'poly' OR name = 'mp' OR name = 'ml' OR name = 'mpoly' OR name = 'gc'; +name mtype prtype len +geom 14 1535 12 +l 14 1535 12 +poly 14 1535 12 +mp 14 1535 12 +ml 14 1535 12 +mpoly 14 1535 12 +gc 14 1535 12 +DROP TABLE g; +# +# check the mtype and len with CREATE TABLE AS +# +CREATE TABLE t1 ( +p POINT NOT NULL, +g GEOMETRY NOT NULL +) ENGINE=InnoDB; +INSERT INTO t1 VALUES(ST_PointFromText('POINT(10 10)'),ST_GeomFromText('POLYGON((30 30,40 40,50 50,30 50,30 40,30 30))')); +SELECT name, mtype, prtype, len FROM INFORMATION_SCHEMA.INNODB_SYS_COLUMNS WHERE name = 'p' OR name = 'g' AND name='t1'; +name mtype prtype len +p 14 1535 12 +CREATE TABLE t2 AS SELECT * FROM t1; +DROP TABLE t1; +SELECT name, mtype, prtype, len FROM INFORMATION_SCHEMA.INNODB_SYS_COLUMNS WHERE name = 'p' OR name = 'g' AND name='t2'; +name mtype prtype len +DROP table t2; +# +# Test when POINT is used in spatial index +# +SET @ls1 = ST_GeomFromText('LINESTRING(0 20, 10 0)'); +SET @ls2 = ST_GeomFromText('LINESTRING(0 0, 10 20)'); +SET @ls3 = ST_GeomFromText('LINESTRING(20 -40, 21 -42)'); +SET @ls4 = ST_GeomFromText('LINESTRING(20 -42, 21 -40)'); +SET @poly1 = ST_GeomFromText('POLYGON((2 2, 2 10, 10 10, 10 2, 2 2))'); +SET @poly2 = ST_GeomFromText('POLYGON((0 0, -5 0, -4 -1, -6 -15, -3 -15, 0 0))'); +SET @poly3 = ST_GeomFromText('POLYGON((10.0 10.0, 20.5 20, 20.5 50, 32.0 64.0, 32.3 64.6, 5 60, 10 10))'); +SET @poly4 = ST_GeomFromText('POLYGON((0 10, -10 10, -10 -10, 0 -10, 0 10))'); +SET @p1 = ST_PointFromText('POINT(0 0)'); +SET @mpoly = ST_GeomFromText('MULTIPOLYGON(((3 3, 3 16, 16 16, 16 3, 3 3)), ((10 10, 10 50, 50 50, 50 10, 10 10)))'); +CREATE TABLE gis_point (p1 POINT NOT NULL, p2 POINT NOT NULL, SPATIAL KEY k1 (p1), SPATIAL KEY k2 (p2)) ENGINE=InnoDB; +INSERT INTO gis_point VALUES +(ST_PointFromText('POINT(1 2)'), ST_PointFromText('POINT(-1 -3)')), +(ST_PointFromText('POINT(2 4)'), ST_PointFromText('POINT(-2 -6)')), +(ST_PointFromText('POINT(3 6)'), ST_PointFromText('POINT(-3 -9)')), +(ST_PointFromText('POINT(4 8)'), ST_PointFromText('POINT(-4 -12)')), +(ST_PointFromText('POINT(5 10)'), ST_PointFromText('POINT(-5 -15)')), +(ST_PointFromText('POINT(6 12)'), ST_PointFromText('POINT(-6 -18)')), +(ST_PointFromText('POINT(7 14)'), ST_PointFromText('POINT(-7 -21)')), +(ST_PointFromText('POINT(8 16)'), ST_PointFromText('POINT(0 0)')), +(ST_PointFromText('POINT(9 18)'), ST_PointFromText('POINT(-4 2)')), +(ST_PointFromText('POINT(10 21)'), ST_PointFromText('POINT(-6 3)')), +(ST_PointFromText('POINT(20.5 41)'), ST_PointFromText('POINT(-8 4)')), +(ST_PointFromText('POINT(26.25 57)'), ST_PointFromText('POINT(1 2)')), +(ST_PointFromText('POINT(32.1234 64.2468)'), ST_PointFromText('POINT(-1 -1)')); +CREATE TABLE gis_point1 SELECT * FROM gis_point; +SELECT name, mtype, prtype, len FROM INFORMATION_SCHEMA.INNODB_SYS_COLUMNS WHERE name = 'p1' OR name = 'p2'; +name mtype prtype len +p1 14 1535 12 +p2 14 1535 12 +SELECT ST_AsText(p1), ST_AsText(p2) FROM gis_point1; +ST_AsText(p1) ST_AsText(p2) +POINT(1 2) POINT(-1 -3) +POINT(2 4) POINT(-2 -6) +POINT(3 6) POINT(-3 -9) +POINT(4 8) POINT(-4 -12) +POINT(5 10) POINT(-5 -15) +POINT(6 12) POINT(-6 -18) +POINT(7 14) POINT(-7 -21) +POINT(8 16) POINT(0 0) +POINT(9 18) POINT(-4 2) +POINT(10 21) POINT(-6 3) +POINT(20.5 41) POINT(-8 4) +POINT(26.25 57) POINT(1 2) +POINT(32.1234 64.2468) POINT(-1 -1) +SELECT ST_AsText(p1) FROM gis_point1 WHERE ST_Intersection(@ls1, @ls2) = p1; +ST_AsText(p1) +POINT(5 10) +SELECT ST_AsText(p1) FROM gis_point1 WHERE MBRWithin(p1, @poly1); +ST_AsText(p1) +POINT(2 4) +POINT(3 6) +POINT(4 8) +POINT(5 10) +SELECT ST_AsText(p2) FROM gis_point1 WHERE ST_Contains(@poly2, p2); +ST_AsText(p2) +POINT(-1 -3) +POINT(-2 -6) +POINT(-3 -9) +POINT(-4 -12) +POINT(-5 -15) +POINT(0 0) +POINT(-1 -1) +SELECT ST_AsText(p2) FROM gis_point1 WHERE ST_Equals(p2, @p1); +ST_AsText(p2) +POINT(0 0) +SELECT ST_AsText(p1) FROM gis_point1 WHERE ST_Touches(@poly3, p1) AND MBRWithin(p2, @poly4); +ST_AsText(p1) +POINT(20.5 41) +POINT(32.1234 64.2468) +SELECT ST_AsText(p1) FROM gis_point1 WHERE ST_Contains(@mpoly, p1); +ST_AsText(p1) +POINT(3 6) +POINT(4 8) +POINT(5 10) +POINT(6 12) +POINT(7 14) +POINT(8 16) +POINT(10 21) +POINT(20.5 41) +SELECT ST_AsText(p1) FROM gis_point1 WHERE ST_Contains(@mpoly, p1) AND NOT MBRWithin(p1, @mpoly); +ST_AsText(p1) +# Check functions that use MBR, with line type data +SELECT ST_AsText(p1) FROM gis_point1 WHERE MBRIntersects(p1, @ls1); +ST_AsText(p1) +POINT(1 2) +POINT(2 4) +POINT(3 6) +POINT(4 8) +POINT(5 10) +POINT(6 12) +POINT(7 14) +POINT(8 16) +POINT(9 18) +SELECT ST_AsText(p1) FROM gis_point1 WHERE MBRWithin(p1, @ls1); +ST_AsText(p1) +POINT(1 2) +POINT(2 4) +POINT(3 6) +POINT(4 8) +POINT(5 10) +POINT(6 12) +POINT(7 14) +POINT(8 16) +POINT(9 18) +SELECT ST_AsText(p1) FROM gis_point1 WHERE ST_Touches(p1, @ls1); +ST_AsText(p1) +SELECT ST_AsText(p1) FROM gis_point1 WHERE MBRDisjoint(p1, @ls1); +ST_AsText(p1) +POINT(10 21) +POINT(20.5 41) +POINT(26.25 57) +POINT(32.1234 64.2468) +# No matching records +SELECT ST_AsText(p1) FROM gis_point1 WHERE MBREquals(p1, @ls1); +ST_AsText(p1) +SELECT ST_AsText(p1) FROM gis_point1 WHERE MBRContains(p1, @ls1); +ST_AsText(p1) +SELECT ST_AsText(p1) FROM gis_point1 WHERE MBROverlaps(p1, @ls3); +ST_AsText(p1) +SELECT ST_AsText(p1) FROM gis_point1 WHERE ST_Crosses(p1, @ls1); +ST_AsText(p1) +# Check functions that use MBR, with polygon type data +SELECT ST_AsText(p1) FROM gis_point1 WHERE MBRIntersects(p1, @poly1); +ST_AsText(p1) +POINT(2 4) +POINT(3 6) +POINT(4 8) +POINT(5 10) +SELECT ST_AsText(p1) FROM gis_point1 WHERE MBRWithin(p1, @poly1); +ST_AsText(p1) +POINT(2 4) +POINT(3 6) +POINT(4 8) +POINT(5 10) +SELECT ST_AsText(p1) FROM gis_point1 WHERE ST_Touches(p1, @poly1); +ST_AsText(p1) +POINT(2 4) +POINT(5 10) +SELECT ST_AsText(p1) FROM gis_point1 WHERE ST_Crosses(p1, @poly1); +ST_AsText(p1) +SELECT ST_AsText(p1) FROM gis_point1 WHERE MBRDisjoint(p1, @poly1); +ST_AsText(p1) +POINT(1 2) +POINT(6 12) +POINT(7 14) +POINT(8 16) +POINT(9 18) +POINT(10 21) +POINT(20.5 41) +POINT(26.25 57) +POINT(32.1234 64.2468) +# No matching records +SELECT ST_AsText(p1) FROM gis_point1 WHERE MBREquals(p1, @poly1); +ST_AsText(p1) +SELECT ST_AsText(p1) FROM gis_point1 WHERE MBRContains(p1, @poly1); +ST_AsText(p1) +SELECT ST_AsText(p1) FROM gis_point1 WHERE MBROverlaps(p1, @poly1); +ST_AsText(p1) +# Check functions that use MBR, with point type data +SELECT ST_AsText(p1) FROM gis_point1 WHERE MBRIntersects(p1, @p1); +ST_AsText(p1) +SELECT ST_AsText(p1) FROM gis_point1 WHERE MBRWithin(p1, @p1); +ST_AsText(p1) +SELECT ST_AsText(p1) FROM gis_point1 WHERE ST_Touches(p1, @p1); +ST_AsText(p1) +SELECT ST_AsText(p1) FROM gis_point1 WHERE MBREquals(p1, @p1); +ST_AsText(p1) +SELECT ST_AsText(p1) FROM gis_point1 WHERE MBRContains(p1, @p1); +ST_AsText(p1) +SELECT ST_AsText(p1) FROM gis_point1 WHERE MBRDisjoint(p1, @p1); +ST_AsText(p1) +POINT(1 2) +POINT(2 4) +POINT(3 6) +POINT(4 8) +POINT(5 10) +POINT(6 12) +POINT(7 14) +POINT(8 16) +POINT(9 18) +POINT(10 21) +POINT(20.5 41) +POINT(26.25 57) +POINT(32.1234 64.2468) +# No matching records +SELECT ST_AsText(p1) FROM gis_point1 WHERE MBROverlaps(p1, @p1); +ST_AsText(p1) +SELECT ST_AsText(p1) FROM gis_point1 WHERE ST_Crosses(p1, @p1); +ST_AsText(p1) +# Check with MBR functions point type data +SELECT ST_AsText(p1) FROM gis_point1 WHERE MBRIntersects(p1, @p1); +ST_AsText(p1) +SELECT ST_AsText(p1) FROM gis_point1 WHERE MBRWithin(p1, @p1); +ST_AsText(p1) +SELECT ST_AsText(p1) FROM gis_point1 WHERE ST_Touches(p1, @p1); +ST_AsText(p1) +SELECT ST_AsText(p1) FROM gis_point1 WHERE MBRContains(p1, @p1); +ST_AsText(p1) +SELECT ST_AsText(p1) FROM gis_point1 WHERE MBRDisjoint(p1, @p1); +ST_AsText(p1) +POINT(1 2) +POINT(2 4) +POINT(3 6) +POINT(4 8) +POINT(5 10) +POINT(6 12) +POINT(7 14) +POINT(8 16) +POINT(9 18) +POINT(10 21) +POINT(20.5 41) +POINT(26.25 57) +POINT(32.1234 64.2468) +SELECT ST_AsText(p1) FROM gis_point1 WHERE MBRequals(p1, @p1); +ST_AsText(p1) +# No matching records +SELECT ST_AsText(p1) FROM gis_point1 WHERE MBROverlaps(p1, @p1); +ST_AsText(p1) +# Check with MBR functions point polygon data +SELECT ST_AsText(p1) FROM gis_point1 WHERE MBRIntersects(p1, @p1); +ST_AsText(p1) +SELECT ST_AsText(p1) FROM gis_point1 WHERE MBRWithin(p1, @p1); +ST_AsText(p1) +SELECT ST_AsText(p1) FROM gis_point1 WHERE ST_Touches(p1, @p1); +ST_AsText(p1) +SELECT ST_AsText(p1) FROM gis_point1 WHERE MBRContains(p1, @p1); +ST_AsText(p1) +SELECT ST_AsText(p1) FROM gis_point1 WHERE MBRDisjoint(p1, @p1); +ST_AsText(p1) +POINT(1 2) +POINT(2 4) +POINT(3 6) +POINT(4 8) +POINT(5 10) +POINT(6 12) +POINT(7 14) +POINT(8 16) +POINT(9 18) +POINT(10 21) +POINT(20.5 41) +POINT(26.25 57) +POINT(32.1234 64.2468) +SELECT ST_AsText(p1) FROM gis_point1 WHERE MBRequals(p1, @p1); +ST_AsText(p1) +# No matching records +SELECT ST_AsText(p1) FROM gis_point1 WHERE MBROverlaps(p1, @p1); +ST_AsText(p1) +# Check with MBR functions polygon type data +SELECT ST_AsText(p1) FROM gis_point1 WHERE MBRIntersects(p1, @poly1); +ST_AsText(p1) +POINT(2 4) +POINT(3 6) +POINT(4 8) +POINT(5 10) +SELECT ST_AsText(p1) FROM gis_point1 WHERE MBRWithin(p1, @poly1); +ST_AsText(p1) +POINT(2 4) +POINT(3 6) +POINT(4 8) +POINT(5 10) +SELECT ST_AsText(p1) FROM gis_point1 WHERE ST_Touches(p1, @poly1); +ST_AsText(p1) +POINT(2 4) +POINT(5 10) +SELECT ST_AsText(p1) FROM gis_point1 WHERE MBRDisjoint(p1, @poly1); +ST_AsText(p1) +POINT(1 2) +POINT(6 12) +POINT(7 14) +POINT(8 16) +POINT(9 18) +POINT(10 21) +POINT(20.5 41) +POINT(26.25 57) +POINT(32.1234 64.2468) +# No matching records +SELECT ST_AsText(p1) FROM gis_point1 WHERE MBROverlaps(p1, @poly1); +ST_AsText(p1) +SELECT ST_AsText(p1) FROM gis_point1 WHERE MBRContains(p1, @poly1); +ST_AsText(p1) +SELECT ST_AsText(p1) FROM gis_point1 WHERE MBRequals(p1, @poly1); +ST_AsText(p1) +# Check with MBR functions line type data +SELECT ST_AsText(p1) FROM gis_point1 WHERE MBRIntersects(p1, @ls1); +ST_AsText(p1) +POINT(1 2) +POINT(2 4) +POINT(3 6) +POINT(4 8) +POINT(5 10) +POINT(6 12) +POINT(7 14) +POINT(8 16) +POINT(9 18) +SELECT ST_AsText(p1) FROM gis_point1 WHERE MBRWithin(p1, @ls1); +ST_AsText(p1) +POINT(1 2) +POINT(2 4) +POINT(3 6) +POINT(4 8) +POINT(5 10) +POINT(6 12) +POINT(7 14) +POINT(8 16) +POINT(9 18) +SELECT ST_AsText(p1) FROM gis_point1 WHERE ST_Touches(p1, @ls1); +ST_AsText(p1) +SELECT ST_AsText(p1) FROM gis_point1 WHERE MBRDisjoint(p1, @ls1); +ST_AsText(p1) +POINT(10 21) +POINT(20.5 41) +POINT(26.25 57) +POINT(32.1234 64.2468) +SELECT ST_AsText(p1) FROM gis_point1 WHERE MBRequals(p1, @ls1); +ST_AsText(p1) +# No matching records +SELECT ST_AsText(p1) FROM gis_point1 WHERE MBROverlaps(p1, @ls1); +ST_AsText(p1) +SELECT ST_AsText(p1) FROM gis_point1 WHERE MBRContains(p1, @ls1); +ST_AsText(p1) +DROP TABLE gis_point1; +SELECT ST_AsText(p1), ST_AsText(p2) FROM gis_point; +ST_AsText(p1) ST_AsText(p2) +POINT(1 2) POINT(-1 -3) +POINT(2 4) POINT(-2 -6) +POINT(3 6) POINT(-3 -9) +POINT(4 8) POINT(-4 -12) +POINT(5 10) POINT(-5 -15) +POINT(6 12) POINT(-6 -18) +POINT(7 14) POINT(-7 -21) +POINT(8 16) POINT(0 0) +POINT(9 18) POINT(-4 2) +POINT(10 21) POINT(-6 3) +POINT(20.5 41) POINT(-8 4) +POINT(26.25 57) POINT(1 2) +POINT(32.1234 64.2468) POINT(-1 -1) +SELECT ST_AsText(p1) FROM gis_point WHERE ST_Intersection(@ls1, @ls2) = p1; +ST_AsText(p1) +POINT(5 10) +SELECT ST_AsText(p1) FROM gis_point WHERE MBRWithin(p1, @poly1); +ST_AsText(p1) +POINT(2 4) +POINT(3 6) +POINT(4 8) +POINT(5 10) +SELECT ST_AsText(p2) FROM gis_point WHERE ST_Contains(@poly2, p2); +ST_AsText(p2) +POINT(-1 -3) +POINT(-2 -6) +POINT(-3 -9) +POINT(-4 -12) +POINT(-5 -15) +POINT(0 0) +POINT(-1 -1) +SELECT ST_AsText(p2) FROM gis_point WHERE ST_Equals(p2, @p1); +ST_AsText(p2) +POINT(0 0) +SELECT ST_AsText(p1) FROM gis_point WHERE ST_Touches(@poly3, p1) AND MBRWithin(p2, @poly4); +ST_AsText(p1) +POINT(20.5 41) +POINT(32.1234 64.2468) +SELECT ST_AsText(p1) FROM gis_point WHERE ST_Contains(@mpoly, p1); +ST_AsText(p1) +POINT(3 6) +POINT(4 8) +POINT(5 10) +POINT(6 12) +POINT(7 14) +POINT(8 16) +POINT(10 21) +POINT(20.5 41) +SELECT ST_AsText(p1) FROM gis_point WHERE ST_Contains(@mpoly, p1) AND NOT MBRWithin(p1, @mpoly); +ST_AsText(p1) +# Check functions that use MBR, with line type data +SELECT ST_AsText(p1) FROM gis_point WHERE MBRIntersects(p1, @ls1); +ST_AsText(p1) +POINT(1 2) +POINT(2 4) +POINT(3 6) +POINT(4 8) +POINT(5 10) +POINT(6 12) +POINT(7 14) +POINT(8 16) +POINT(9 18) +SELECT ST_AsText(p1) FROM gis_point WHERE MBRWithin(p1, @ls1); +ST_AsText(p1) +POINT(1 2) +POINT(2 4) +POINT(3 6) +POINT(4 8) +POINT(5 10) +POINT(6 12) +POINT(7 14) +POINT(8 16) +POINT(9 18) +SELECT ST_AsText(p1) FROM gis_point WHERE ST_Touches(p1, @ls1); +ST_AsText(p1) +SELECT ST_AsText(p1) FROM gis_point WHERE MBRDisjoint(p1, @ls1); +ST_AsText(p1) +POINT(32.1234 64.2468) +POINT(26.25 57) +POINT(20.5 41) +POINT(10 21) +# No matching records +SELECT ST_AsText(p1) FROM gis_point WHERE MBREquals(p1, @ls1); +ST_AsText(p1) +SELECT ST_AsText(p1) FROM gis_point WHERE MBRContains(p1, @ls1); +ST_AsText(p1) +SELECT ST_AsText(p1) FROM gis_point WHERE MBROverlaps(p1, @ls3); +ST_AsText(p1) +SELECT ST_AsText(p1) FROM gis_point WHERE ST_Crosses(p1, @ls1); +ST_AsText(p1) +# Check functions that use MBR, with polygon type data +SELECT ST_AsText(p1) FROM gis_point WHERE MBRIntersects(p1, @poly1); +ST_AsText(p1) +POINT(2 4) +POINT(3 6) +POINT(4 8) +POINT(5 10) +SELECT ST_AsText(p1) FROM gis_point WHERE MBRWithin(p1, @poly1); +ST_AsText(p1) +POINT(2 4) +POINT(3 6) +POINT(4 8) +POINT(5 10) +SELECT ST_AsText(p1) FROM gis_point WHERE ST_Touches(p1, @poly1); +ST_AsText(p1) +POINT(2 4) +POINT(5 10) +SELECT ST_AsText(p1) FROM gis_point WHERE ST_Crosses(p1, @poly1); +ST_AsText(p1) +SELECT ST_AsText(p1) FROM gis_point WHERE MBRDisjoint(p1, @poly1); +ST_AsText(p1) +POINT(32.1234 64.2468) +POINT(26.25 57) +POINT(20.5 41) +POINT(10 21) +POINT(9 18) +POINT(8 16) +POINT(7 14) +POINT(6 12) +POINT(1 2) +# No matching records +SELECT ST_AsText(p1) FROM gis_point WHERE MBREquals(p1, @poly1); +ST_AsText(p1) +SELECT ST_AsText(p1) FROM gis_point WHERE MBRContains(p1, @poly1); +ST_AsText(p1) +SELECT ST_AsText(p1) FROM gis_point WHERE MBROverlaps(p1, @poly1); +ST_AsText(p1) +# Check functions that use MBR, with point type data +SELECT ST_AsText(p1) FROM gis_point WHERE MBRIntersects(p1, @p1); +ST_AsText(p1) +SELECT ST_AsText(p1) FROM gis_point WHERE MBRWithin(p1, @p1); +ST_AsText(p1) +SELECT ST_AsText(p1) FROM gis_point WHERE ST_Touches(p1, @p1); +ST_AsText(p1) +SELECT ST_AsText(p1) FROM gis_point WHERE MBREquals(p1, @p1); +ST_AsText(p1) +SELECT ST_AsText(p1) FROM gis_point WHERE MBRContains(p1, @p1); +ST_AsText(p1) +SELECT ST_AsText(p1) FROM gis_point WHERE MBRDisjoint(p1, @p1); +ST_AsText(p1) +POINT(32.1234 64.2468) +POINT(26.25 57) +POINT(20.5 41) +POINT(10 21) +POINT(9 18) +POINT(8 16) +POINT(7 14) +POINT(6 12) +POINT(5 10) +POINT(4 8) +POINT(3 6) +POINT(2 4) +POINT(1 2) +# No matching records +SELECT ST_AsText(p1) FROM gis_point WHERE MBROverlaps(p1, @p1); +ST_AsText(p1) +SELECT ST_AsText(p1) FROM gis_point WHERE ST_Crosses(p1, @p1); +ST_AsText(p1) +# Check with MBR functions point type data +SELECT ST_AsText(p1) FROM gis_point WHERE MBRIntersects(p1, @p1); +ST_AsText(p1) +SELECT ST_AsText(p1) FROM gis_point WHERE MBRWithin(p1, @p1); +ST_AsText(p1) +SELECT ST_AsText(p1) FROM gis_point WHERE ST_Touches(p1, @p1); +ST_AsText(p1) +SELECT ST_AsText(p1) FROM gis_point WHERE MBRContains(p1, @p1); +ST_AsText(p1) +SELECT ST_AsText(p1) FROM gis_point WHERE MBRDisjoint(p1, @p1); +ST_AsText(p1) +POINT(32.1234 64.2468) +POINT(26.25 57) +POINT(20.5 41) +POINT(10 21) +POINT(9 18) +POINT(8 16) +POINT(7 14) +POINT(6 12) +POINT(5 10) +POINT(4 8) +POINT(3 6) +POINT(2 4) +POINT(1 2) +SELECT ST_AsText(p1) FROM gis_point WHERE MBRequals(p1, @p1); +ST_AsText(p1) +# No matching records +SELECT ST_AsText(p1) FROM gis_point WHERE MBROverlaps(p1, @p1); +ST_AsText(p1) +# Check with MBR functions point polygon data +SELECT ST_AsText(p1) FROM gis_point WHERE MBRIntersects(p1, @p1); +ST_AsText(p1) +SELECT ST_AsText(p1) FROM gis_point WHERE MBRWithin(p1, @p1); +ST_AsText(p1) +SELECT ST_AsText(p1) FROM gis_point WHERE ST_Touches(p1, @p1); +ST_AsText(p1) +SELECT ST_AsText(p1) FROM gis_point WHERE MBRContains(p1, @p1); +ST_AsText(p1) +SELECT ST_AsText(p1) FROM gis_point WHERE MBRDisjoint(p1, @p1); +ST_AsText(p1) +POINT(32.1234 64.2468) +POINT(26.25 57) +POINT(20.5 41) +POINT(10 21) +POINT(9 18) +POINT(8 16) +POINT(7 14) +POINT(6 12) +POINT(5 10) +POINT(4 8) +POINT(3 6) +POINT(2 4) +POINT(1 2) +SELECT ST_AsText(p1) FROM gis_point WHERE MBRequals(p1, @p1); +ST_AsText(p1) +# No matching records +SELECT ST_AsText(p1) FROM gis_point WHERE MBROverlaps(p1, @p1); +ST_AsText(p1) +# Check with MBR functions polygon type data +SELECT ST_AsText(p1) FROM gis_point WHERE MBRIntersects(p1, @poly1); +ST_AsText(p1) +POINT(2 4) +POINT(3 6) +POINT(4 8) +POINT(5 10) +SELECT ST_AsText(p1) FROM gis_point WHERE MBRWithin(p1, @poly1); +ST_AsText(p1) +POINT(2 4) +POINT(3 6) +POINT(4 8) +POINT(5 10) +SELECT ST_AsText(p1) FROM gis_point WHERE ST_Touches(p1, @poly1); +ST_AsText(p1) +POINT(2 4) +POINT(5 10) +SELECT ST_AsText(p1) FROM gis_point WHERE MBRDisjoint(p1, @poly1); +ST_AsText(p1) +POINT(32.1234 64.2468) +POINT(26.25 57) +POINT(20.5 41) +POINT(10 21) +POINT(9 18) +POINT(8 16) +POINT(7 14) +POINT(6 12) +POINT(1 2) +# No matching records +SELECT ST_AsText(p1) FROM gis_point WHERE MBROverlaps(p1, @poly1); +ST_AsText(p1) +SELECT ST_AsText(p1) FROM gis_point WHERE MBRContains(p1, @poly1); +ST_AsText(p1) +SELECT ST_AsText(p1) FROM gis_point WHERE MBRequals(p1, @poly1); +ST_AsText(p1) +# Check with MBR functions line type data +SELECT ST_AsText(p1) FROM gis_point WHERE MBRIntersects(p1, @ls1); +ST_AsText(p1) +POINT(1 2) +POINT(2 4) +POINT(3 6) +POINT(4 8) +POINT(5 10) +POINT(6 12) +POINT(7 14) +POINT(8 16) +POINT(9 18) +SELECT ST_AsText(p1) FROM gis_point WHERE MBRWithin(p1, @ls1); +ST_AsText(p1) +POINT(1 2) +POINT(2 4) +POINT(3 6) +POINT(4 8) +POINT(5 10) +POINT(6 12) +POINT(7 14) +POINT(8 16) +POINT(9 18) +SELECT ST_AsText(p1) FROM gis_point WHERE ST_Touches(p1, @ls1); +ST_AsText(p1) +SELECT ST_AsText(p1) FROM gis_point WHERE MBRDisjoint(p1, @ls1); +ST_AsText(p1) +POINT(32.1234 64.2468) +POINT(26.25 57) +POINT(20.5 41) +POINT(10 21) +SELECT ST_AsText(p1) FROM gis_point WHERE MBRequals(p1, @ls1); +ST_AsText(p1) +# No matching records +SELECT ST_AsText(p1) FROM gis_point WHERE MBROverlaps(p1, @ls1); +ST_AsText(p1) +SELECT ST_AsText(p1) FROM gis_point WHERE MBRContains(p1, @ls1); +ST_AsText(p1) +# Modify the storage engine to Myisam, Check the spatial functions +ALTER TABLE gis_point ENGINE Myisam; +# Check functions that use MBR, with line type data +SELECT ST_AsText(p1) FROM gis_point WHERE MBRIntersects(p1, @ls1); +ST_AsText(p1) +POINT(1 2) +POINT(2 4) +POINT(3 6) +POINT(4 8) +POINT(5 10) +POINT(6 12) +POINT(7 14) +POINT(8 16) +POINT(9 18) +SELECT ST_AsText(p1) FROM gis_point WHERE MBRWithin(p1, @ls1); +ST_AsText(p1) +POINT(1 2) +POINT(2 4) +POINT(3 6) +POINT(4 8) +POINT(5 10) +POINT(6 12) +POINT(7 14) +POINT(8 16) +POINT(9 18) +SELECT ST_AsText(p1) FROM gis_point WHERE ST_Touches(p1, @ls1); +ST_AsText(p1) +SELECT ST_AsText(p1) FROM gis_point WHERE MBRDisjoint(p1, @ls1); +ST_AsText(p1) +POINT(10 21) +POINT(20.5 41) +POINT(26.25 57) +POINT(32.1234 64.2468) +# No matching records +SELECT ST_AsText(p1) FROM gis_point WHERE MBREquals(p1, @ls1); +ST_AsText(p1) +SELECT ST_AsText(p1) FROM gis_point WHERE MBRContains(p1, @ls1); +ST_AsText(p1) +SELECT ST_AsText(p1) FROM gis_point WHERE MBROverlaps(p1, @ls3); +ST_AsText(p1) +SELECT ST_AsText(p1) FROM gis_point WHERE ST_Crosses(p1, @ls1); +ST_AsText(p1) +# Check functions that use MBR, with polygon type data +SELECT ST_AsText(p1) FROM gis_point WHERE MBRIntersects(p1, @poly1); +ST_AsText(p1) +POINT(2 4) +POINT(3 6) +POINT(4 8) +POINT(5 10) +SELECT ST_AsText(p1) FROM gis_point WHERE MBRWithin(p1, @poly1); +ST_AsText(p1) +POINT(2 4) +POINT(3 6) +POINT(4 8) +POINT(5 10) +SELECT ST_AsText(p1) FROM gis_point WHERE ST_Touches(p1, @poly1); +ST_AsText(p1) +POINT(2 4) +POINT(5 10) +SELECT ST_AsText(p1) FROM gis_point WHERE ST_Crosses(p1, @poly1); +ST_AsText(p1) +SELECT ST_AsText(p1) FROM gis_point WHERE MBRDisjoint(p1, @poly1); +ST_AsText(p1) +POINT(1 2) +POINT(6 12) +POINT(7 14) +POINT(8 16) +POINT(9 18) +POINT(10 21) +POINT(20.5 41) +POINT(26.25 57) +POINT(32.1234 64.2468) +# No matching records +SELECT ST_AsText(p1) FROM gis_point WHERE MBREquals(p1, @poly1); +ST_AsText(p1) +SELECT ST_AsText(p1) FROM gis_point WHERE MBRContains(p1, @poly1); +ST_AsText(p1) +SELECT ST_AsText(p1) FROM gis_point WHERE MBROverlaps(p1, @poly1); +ST_AsText(p1) +# Check functions that use MBR, with point type data +SELECT ST_AsText(p1) FROM gis_point WHERE MBRIntersects(p1, @p1); +ST_AsText(p1) +SELECT ST_AsText(p1) FROM gis_point WHERE MBRWithin(p1, @p1); +ST_AsText(p1) +SELECT ST_AsText(p1) FROM gis_point WHERE ST_Touches(p1, @p1); +ST_AsText(p1) +SELECT ST_AsText(p1) FROM gis_point WHERE MBREquals(p1, @p1); +ST_AsText(p1) +SELECT ST_AsText(p1) FROM gis_point WHERE MBRContains(p1, @p1); +ST_AsText(p1) +SELECT ST_AsText(p1) FROM gis_point WHERE MBRDisjoint(p1, @p1); +ST_AsText(p1) +POINT(1 2) +POINT(2 4) +POINT(3 6) +POINT(4 8) +POINT(5 10) +POINT(6 12) +POINT(7 14) +POINT(8 16) +POINT(9 18) +POINT(10 21) +POINT(20.5 41) +POINT(26.25 57) +POINT(32.1234 64.2468) +# No matching records +SELECT ST_AsText(p1) FROM gis_point WHERE MBROverlaps(p1, @p1); +ST_AsText(p1) +SELECT ST_AsText(p1) FROM gis_point WHERE ST_Crosses(p1, @p1); +ST_AsText(p1) +# Check with MBR functions point type data +SELECT ST_AsText(p1) FROM gis_point WHERE MBRIntersects(p1, @p1); +ST_AsText(p1) +SELECT ST_AsText(p1) FROM gis_point WHERE MBRWithin(p1, @p1); +ST_AsText(p1) +SELECT ST_AsText(p1) FROM gis_point WHERE ST_Touches(p1, @p1); +ST_AsText(p1) +SELECT ST_AsText(p1) FROM gis_point WHERE MBRContains(p1, @p1); +ST_AsText(p1) +SELECT ST_AsText(p1) FROM gis_point WHERE MBRDisjoint(p1, @p1); +ST_AsText(p1) +POINT(1 2) +POINT(2 4) +POINT(3 6) +POINT(4 8) +POINT(5 10) +POINT(6 12) +POINT(7 14) +POINT(8 16) +POINT(9 18) +POINT(10 21) +POINT(20.5 41) +POINT(26.25 57) +POINT(32.1234 64.2468) +SELECT ST_AsText(p1) FROM gis_point WHERE MBRequals(p1, @p1); +ST_AsText(p1) +# No matching records +SELECT ST_AsText(p1) FROM gis_point WHERE MBROverlaps(p1, @p1); +ST_AsText(p1) +# Check with MBR functions point polygon data +SELECT ST_AsText(p1) FROM gis_point WHERE MBRIntersects(p1, @p1); +ST_AsText(p1) +SELECT ST_AsText(p1) FROM gis_point WHERE MBRWithin(p1, @p1); +ST_AsText(p1) +SELECT ST_AsText(p1) FROM gis_point WHERE ST_Touches(p1, @p1); +ST_AsText(p1) +SELECT ST_AsText(p1) FROM gis_point WHERE MBRContains(p1, @p1); +ST_AsText(p1) +SELECT ST_AsText(p1) FROM gis_point WHERE MBRDisjoint(p1, @p1); +ST_AsText(p1) +POINT(1 2) +POINT(2 4) +POINT(3 6) +POINT(4 8) +POINT(5 10) +POINT(6 12) +POINT(7 14) +POINT(8 16) +POINT(9 18) +POINT(10 21) +POINT(20.5 41) +POINT(26.25 57) +POINT(32.1234 64.2468) +SELECT ST_AsText(p1) FROM gis_point WHERE MBRequals(p1, @p1); +ST_AsText(p1) +# No matching records +SELECT ST_AsText(p1) FROM gis_point WHERE MBROverlaps(p1, @p1); +ST_AsText(p1) +# Check with MBR functions polygon type data +SELECT ST_AsText(p1) FROM gis_point WHERE MBRIntersects(p1, @poly1); +ST_AsText(p1) +POINT(2 4) +POINT(3 6) +POINT(4 8) +POINT(5 10) +SELECT ST_AsText(p1) FROM gis_point WHERE MBRWithin(p1, @poly1); +ST_AsText(p1) +POINT(2 4) +POINT(3 6) +POINT(4 8) +POINT(5 10) +SELECT ST_AsText(p1) FROM gis_point WHERE ST_Touches(p1, @poly1); +ST_AsText(p1) +POINT(2 4) +POINT(5 10) +SELECT ST_AsText(p1) FROM gis_point WHERE MBRDisjoint(p1, @poly1); +ST_AsText(p1) +POINT(1 2) +POINT(6 12) +POINT(7 14) +POINT(8 16) +POINT(9 18) +POINT(10 21) +POINT(20.5 41) +POINT(26.25 57) +POINT(32.1234 64.2468) +# No matching records +SELECT ST_AsText(p1) FROM gis_point WHERE MBROverlaps(p1, @poly1); +ST_AsText(p1) +SELECT ST_AsText(p1) FROM gis_point WHERE MBRContains(p1, @poly1); +ST_AsText(p1) +SELECT ST_AsText(p1) FROM gis_point WHERE MBRequals(p1, @poly1); +ST_AsText(p1) +# Check with MBR functions line type data +SELECT ST_AsText(p1) FROM gis_point WHERE MBRIntersects(p1, @ls1); +ST_AsText(p1) +POINT(1 2) +POINT(2 4) +POINT(3 6) +POINT(4 8) +POINT(5 10) +POINT(6 12) +POINT(7 14) +POINT(8 16) +POINT(9 18) +SELECT ST_AsText(p1) FROM gis_point WHERE MBRWithin(p1, @ls1); +ST_AsText(p1) +POINT(1 2) +POINT(2 4) +POINT(3 6) +POINT(4 8) +POINT(5 10) +POINT(6 12) +POINT(7 14) +POINT(8 16) +POINT(9 18) +SELECT ST_AsText(p1) FROM gis_point WHERE ST_Touches(p1, @ls1); +ST_AsText(p1) +SELECT ST_AsText(p1) FROM gis_point WHERE MBRDisjoint(p1, @ls1); +ST_AsText(p1) +POINT(10 21) +POINT(20.5 41) +POINT(26.25 57) +POINT(32.1234 64.2468) +SELECT ST_AsText(p1) FROM gis_point WHERE MBRequals(p1, @ls1); +ST_AsText(p1) +# No matching records +SELECT ST_AsText(p1) FROM gis_point WHERE MBROverlaps(p1, @ls1); +ST_AsText(p1) +SELECT ST_AsText(p1) FROM gis_point WHERE MBRContains(p1, @ls1); +ST_AsText(p1) +# Modify the storage engine to InnoDB again, do following testing +ALTER TABLE gis_point ENGINE InnoDB; +CHECK TABLE gis_point; +Table Op Msg_type Msg_text +test.gis_point check status OK +The ORDER BY for spatial index will use filesort +EXPLAIN SELECT ST_AsText(p1), ST_AsText(p2) FROM gis_point ORDER BY p1, p2; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE gis_point ALL NULL NULL NULL NULL 13 # +SELECT ST_AsText(p1), ST_AsText(p2) FROM gis_point ORDER BY p1, p2; +ST_AsText(p1) ST_AsText(p2) +POINT(2 4) POINT(-2 -6) +POINT(3 6) POINT(-3 -9) +POINT(4 8) POINT(-4 -12) +POINT(5 10) POINT(-5 -15) +POINT(6 12) POINT(-6 -18) +POINT(7 14) POINT(-7 -21) +POINT(8 16) POINT(0 0) +POINT(9 18) POINT(-4 2) +POINT(10 21) POINT(-6 3) +POINT(1 2) POINT(-1 -3) +POINT(26.25 57) POINT(1 2) +POINT(20.5 41) POINT(-8 4) +POINT(32.1234 64.2468) POINT(-1 -1) +Try to do IDU on the table and verify the result +DELETE FROM gis_point WHERE ST_Equals(p2, ST_PointFromText('POINT(-8 4)')); +INSERT INTO gis_point VALUES(ST_PointFromText('POINT(20.5 -41)'), ST_PointFromText('POINT(8 -4)')); +SELECT ST_AsText(p1) AS 'Expect (32.1234 64.2468)' FROM gis_point WHERE ST_Touches(@poly3, p1) AND MBRWithin(p2, @poly4); +Expect (32.1234 64.2468) +POINT(32.1234 64.2468) +UPDATE gis_point SET p1 = ST_PointFromText('POINT(20.5 41)'), p2 = ST_PointFromText('POINT(-8 4)') WHERE ST_Intersection(@ls3, @ls4) = p1; +SELECT ST_AsText(p1) AS 'Expect (32.1234 64.2468) AND (20.5 41)' FROM gis_point WHERE ST_Touches(@poly3, p1) AND MBRWithin(p2, @poly4); +Expect (32.1234 64.2468) AND (20.5 41) +POINT(32.1234 64.2468) +POINT(20.5 41) +CHECK TABLE gis_point; +Table Op Msg_type Msg_text +test.gis_point check status OK +Use a trx to test the IDU on the table and verify the result +START TRANSACTION; +DELETE FROM gis_point WHERE ST_Equals(p2, ST_PointFromText('POINT(-8 4)')); +INSERT INTO gis_point VALUES(ST_PointFromText('POINT(20.5 -41)'), ST_PointFromText('POINT(8 -4)')); +SELECT ST_AsText(p1) AS 'Expect (32.1234 64.2468)' FROM gis_point WHERE ST_Touches(@poly3, p1) AND MBRWithin(p2, @poly4); +Expect (32.1234 64.2468) +POINT(32.1234 64.2468) +UPDATE gis_point SET p1 = ST_PointFromText('POINT(20.5 49)'), p2 = ST_PointFromText('POINT(-8 4)') WHERE ST_Intersection(@ls3, @ls4) = p1; +SELECT ST_AsText(p1) AS 'Expect (32.1234 64.2468) AND (20.5 49)' FROM gis_point WHERE ST_Touches(@poly3, p1) AND MBRWithin(p2, @poly4); +Expect (32.1234 64.2468) AND (20.5 49) +POINT(32.1234 64.2468) +POINT(20.5 49) +ROLLBACK; +SELECT ST_AsText(p1) AS 'Expect (32.1234 64.2468) AND (20.5 41)' FROM gis_point WHERE ST_Touches(@poly3, p1) AND MBRWithin(p2, @poly4); +Expect (32.1234 64.2468) AND (20.5 41) +POINT(32.1234 64.2468) +POINT(20.5 41) +CHECK TABLE gis_point; +Table Op Msg_type Msg_text +test.gis_point check status OK +DROP TABLE gis_point; +# +# Test inserting/updating different type data into POINT field +# +CREATE TABLE gis_point (i INT, p POINT) ENGINE=InnoDB; +CREATE TABLE geom (i INT, g GEOMETRY NOT NULL, SPATIAL KEY(g)) ENGINE=InnoDB; +INSERT INTO gis_point VALUES(0, ST_PointFromText('POINT(1 1)')); +INSERT INTO gis_point VALUES(1, ST_PointFromText('POINT(2 2)')); +INSERT INTO gis_point VALUES(2, NULL); +ALTER TABLE gis_point ADD COLUMN j INT, ALGORITHM = COPY; +SELECT name, mtype, prtype, len FROM INFORMATION_SCHEMA.INNODB_SYS_COLUMNS WHERE name = 'p'; +name mtype prtype len +p 14 1279 12 +SELECT name, mtype, prtype, len FROM INFORMATION_SCHEMA.INNODB_SYS_COLUMNS WHERE name = 'p'; +name mtype prtype len +p 14 1279 12 +SELECT i, ST_AsText(p) FROM gis_point; +i ST_AsText(p) +0 POINT(1 1) +1 POINT(2 2) +2 NULL +UPDATE gis_point SET p = NULL WHERE p = ST_PointFromText('POINT(1 1)'); +UPDATE gis_point SET p = ST_PointFromText('POINT(1 2)') WHERE p = ST_PointFromText('POINT(2 2)'); +UPDATE gis_point SET p = ST_PointFromText('POINT(1 1)') WHERE p IS NULL; +SELECT i, ST_AsText(p) FROM gis_point; +i ST_AsText(p) +0 POINT(1 1) +1 POINT(1 2) +2 POINT(1 1) +INSERT INTO geom VALUES(0, ST_PointFromText('POINT(0 0)')); +INSERT INTO geom VALUES(1, ST_PointFromText('POINT(10 10)')); +INSERT INTO geom VALUES(2, ST_GeomFromText('POLYGON((7 1,6 2,6 3,10 3,10 1,7 1))')); +SELECT ST_AsText(g) FROM geom; +ST_AsText(g) +POINT(0 0) +POINT(10 10) +POLYGON((7 1,6 2,6 3,10 3,10 1,7 1)) +SELECT ST_AsText(p) FROM gis_point; +ST_AsText(p) +POINT(1 1) +POINT(1 2) +POINT(1 1) +DELETE FROM geom WHERE g = ST_GeomFromText('POLYGON((7 1,6 2,6 3,10 3,10 1,7 1))'); +From GEOMETRY to POINT, now ALL the data are POINT +ALTER TABLE geom MODIFY g POINT NOT NULL; +SHOW CREATE TABLE geom; +Table Create Table +geom CREATE TABLE `geom` ( + `i` int(11) DEFAULT NULL, + `g` point NOT NULL, + SPATIAL KEY `g` (`g`) +) ENGINE=InnoDB DEFAULT CHARSET=latin1 +SELECT ST_AsText(g) FROM geom; +ST_AsText(g) +POINT(0 0) +POINT(10 10) +From POINT to GEOMETRY, all data are POINT +ALTER TABLE geom MODIFY g GEOMETRY NOT NULL; +SHOW CREATE TABLE geom; +Table Create Table +geom CREATE TABLE `geom` ( + `i` int(11) DEFAULT NULL, + `g` geometry NOT NULL, + SPATIAL KEY `g` (`g`) +) ENGINE=InnoDB DEFAULT CHARSET=latin1 +TRUNCATE TABLE geom; +From GEOMETRY to POINT, the table is empty +ALTER TABLE geom MODIFY g POINT NOT NULL; +SHOW CREATE TABLE geom; +Table Create Table +geom CREATE TABLE `geom` ( + `i` int(11) DEFAULT NULL, + `g` point NOT NULL, + SPATIAL KEY `g` (`g`) +) ENGINE=InnoDB DEFAULT CHARSET=latin1 +SELECT ST_AsText(g) FROM geom; +ST_AsText(g) +CHECK TABLE gis_point; +Table Op Msg_type Msg_text +test.gis_point check status OK +CHECK TABLE geom; +Table Op Msg_type Msg_text +test.geom check status OK +DROP TABLE gis_point, geom; +# +# Test when a geom field, such as POINT, is also a primary key +# +CREATE TABLE t1 ( +a INT NOT NULL, +p POINT NOT NULL, +l LINESTRING NOT NULL, +g GEOMETRY NOT NULL, +PRIMARY KEY(p), +SPATIAL KEY `idx2` (p), +SPATIAL KEY `idx3` (l), +SPATIAL KEY `idx4` (g) +) ENGINE=InnoDB; +INSERT INTO t1 VALUES( +1, ST_GeomFromText('POINT(10 10)'), +ST_GeomFromText('LINESTRING(1 1, 5 5, 10 10)'), +ST_GeomFromText('POLYGON((30 30, 40 40, 50 50, 30 50, 30 40, 30 30))')); +INSERT INTO t1 VALUES( +2, ST_GeomFromText('POINT(20 20)'), +ST_GeomFromText('LINESTRING(2 3, 7 8, 9 10, 15 16)'), +ST_GeomFromText('POLYGON((10 30, 30 40, 40 50, 40 30, 30 20, 10 30))')); +SELECT a, ST_AsText(p), ST_AsText(l), ST_AsText(g) FROM t1; +a ST_AsText(p) ST_AsText(l) ST_AsText(g) +1 POINT(10 10) LINESTRING(1 1,5 5,10 10) POLYGON((30 30,40 40,50 50,30 50,30 40,30 30)) +2 POINT(20 20) LINESTRING(2 3,7 8,9 10,15 16) POLYGON((10 30,30 40,40 50,40 30,30 20,10 30)) +EXPLAIN UPDATE t1 SET p = ST_GeomFromText('POINT(30 30)') WHERE p = ST_GeomFromText('POINT(20 20)'); +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 range PRIMARY PRIMARY 27 NULL 1 # +UPDATE t1 SET p = ST_GeomFromText('POINT(30 30)') WHERE p = ST_GeomFromText('POINT(20 20)'); +SELECT a, ST_AsText(p), ST_AsText(l), ST_AsText(g) FROM t1; +a ST_AsText(p) ST_AsText(l) ST_AsText(g) +1 POINT(10 10) LINESTRING(1 1,5 5,10 10) POLYGON((30 30,40 40,50 50,30 50,30 40,30 30)) +2 POINT(30 30) LINESTRING(2 3,7 8,9 10,15 16) POLYGON((10 30,30 40,40 50,40 30,30 20,10 30)) +ALTER TABLE t1 DROP PRIMARY KEY; +ALTER TABLE t1 ADD PRIMARY KEY(a); +SELECT a, ST_AsText(p), ST_AsText(l), ST_AsText(g) FROM t1; +a ST_AsText(p) ST_AsText(l) ST_AsText(g) +1 POINT(10 10) LINESTRING(1 1,5 5,10 10) POLYGON((30 30,40 40,50 50,30 50,30 40,30 30)) +2 POINT(30 30) LINESTRING(2 3,7 8,9 10,15 16) POLYGON((10 30,30 40,40 50,40 30,30 20,10 30)) +ALTER TABLE t1 DROP PRIMARY KEY; +ALTER TABLE t1 ADD PRIMARY KEY(p); +SELECT a, ST_AsText(p), ST_AsText(l), ST_AsText(g) FROM t1; +a ST_AsText(p) ST_AsText(l) ST_AsText(g) +1 POINT(10 10) LINESTRING(1 1,5 5,10 10) POLYGON((30 30,40 40,50 50,30 50,30 40,30 30)) +2 POINT(30 30) LINESTRING(2 3,7 8,9 10,15 16) POLYGON((10 30,30 40,40 50,40 30,30 20,10 30)) +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` int(11) NOT NULL, + `p` point NOT NULL, + `l` linestring NOT NULL, + `g` geometry NOT NULL, + PRIMARY KEY (`p`(25)), + SPATIAL KEY `idx2` (`p`), + SPATIAL KEY `idx3` (`l`), + SPATIAL KEY `idx4` (`g`) +) ENGINE=InnoDB DEFAULT CHARSET=latin1 +SELECT a, ST_AsText(p), ST_AsText(l), ST_AsText(g) FROM t1; +a ST_AsText(p) ST_AsText(l) ST_AsText(g) +1 POINT(10 10) LINESTRING(1 1,5 5,10 10) POLYGON((30 30,40 40,50 50,30 50,30 40,30 30)) +2 POINT(30 30) LINESTRING(2 3,7 8,9 10,15 16) POLYGON((10 30,30 40,40 50,40 30,30 20,10 30)) +ALTER TABLE t1 DROP PRIMARY KEY; +ALTER TABLE t1 ADD PRIMARY KEY(p); +EXPLAIN SELECT a, ST_AsText(p) FROM t1 WHERE a = 2 AND p = ST_GeomFromText('POINT(30 30)'); +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 const PRIMARY,idx2 PRIMARY 27 const 1 # +SELECT a, ST_AsText(p) FROM t1 WHERE a = 2 AND p = ST_GeomFromText('POINT(30 30)'); +a ST_AsText(p) +2 POINT(30 30) +SELECT a, ST_AsText(p), ST_AsText(l), ST_AsText(g) FROM t1; +a ST_AsText(p) ST_AsText(l) ST_AsText(g) +1 POINT(10 10) LINESTRING(1 1,5 5,10 10) POLYGON((30 30,40 40,50 50,30 50,30 40,30 30)) +2 POINT(30 30) LINESTRING(2 3,7 8,9 10,15 16) POLYGON((10 30,30 40,40 50,40 30,30 20,10 30)) +CHECK TABLE t1; +Table Op Msg_type Msg_text +test.t1 check status OK +DROP TABLE t1; +# +# Test for foreign keys. +# +CREATE TABLE parent(p POINT, PRIMARY KEY(p)) ENGINE=InnoDB; +CREATE TABLE child(p POINT NOT NULL) ENGINE=InnoDB; +ALTER TABLE parent ADD SPATIAL INDEX idx1(p ASC); +ALTER TABLE child ADD SPATIAL INDEX idx2(p ASC); +SHOW CREATE TABLE parent; +Table Create Table +parent CREATE TABLE `parent` ( + `p` point NOT NULL, + PRIMARY KEY (`p`(25)), + SPATIAL KEY `idx1` (`p`) +) ENGINE=InnoDB DEFAULT CHARSET=latin1 +SHOW CREATE TABLE child; +Table Create Table +child CREATE TABLE `child` ( + `p` point NOT NULL, + SPATIAL KEY `idx2` (`p`) +) ENGINE=InnoDB DEFAULT CHARSET=latin1 +ALTER TABLE child ADD FOREIGN KEY(p) REFERENCES parent(p); +ERROR HY000: Can't create table `test`.`#sql-temporary` (errno: 150 "Foreign key constraint is incorrectly formed") +show warnings; +Level Code Message +Warning 150 Alter table '`test`.`child`' with foreign key constraint failed. There is no index in the referenced table where the referenced columns appear as the first columns near 'FOREIGN KEY(p) REFERENCES parent(p)'. +Error 1005 Can't create table `test`.`#sql-temporary` (errno: 150 "Foreign key constraint is incorrectly formed") +Warning 1215 Cannot add foreign key constraint +ALTER TABLE parent DROP INDEX idx1; +ALTER TABLE child ADD FOREIGN KEY(p) REFERENCES parent(p); +Got one of the listed errors +show warnings; +Level Code Message +Warning 150 Alter table '`test`.`child`' with foreign key constraint failed. There is no index in the referenced table where the referenced columns appear as the first columns near 'FOREIGN KEY(p) REFERENCES parent(p)'. +Error 1005 Can't create table `test`.`#sql-temporary` (errno: 150 "Foreign key constraint is incorrectly formed") +Warning 1215 Cannot add foreign key constraint +ALTER TABLE child DROP INDEX idx2; +ALTER TABLE child ADD FOREIGN KEY(p) REFERENCES parent(p); +Got one of the listed errors +show warnings; +Level Code Message +Warning 150 Alter table '`test`.`child`' with foreign key constraint failed. There is only prefix index in the referenced table where the referenced columns appear as the first columns near 'FOREIGN KEY(p) REFERENCES parent(p)'. +Error 1005 Can't create table `test`.`#sql-temporary` (errno: 150 "Foreign key constraint is incorrectly formed") +Warning 1215 Cannot add foreign key constraint +DROP TABLE child, parent; +# +# Bug#28763: Selecting geometry fields in UNION caused server crash. +# +CREATE TABLE t1(f1 GEOMETRY, f2 POINT, f3 GEOMETRY) ENGINE=InnoDB; +SELECT f1 FROM t1 UNION SELECT f1 FROM t1; +f1 +INSERT INTO t1 (f2,f3) VALUES (ST_GeomFromText('POINT(1 1)'), +ST_GeomFromText('POINT(2 2)')); +SELECT ST_AsText(f2),ST_AsText(f3) FROM t1; +ST_AsText(f2) ST_AsText(f3) +POINT(1 1) POINT(2 2) +SELECT ST_AsText(a) FROM (SELECT f2 AS a FROM t1 UNION SELECT f3 FROM t1) t; +ST_AsText(a) +POINT(1 1) +POINT(2 2) +CREATE TABLE t2 AS SELECT f2 AS a FROM t1 UNION SELECT f3 FROM t1; +DESC t2; +Field Type Null Key Default Extra +a geometry YES NULL +SELECT ST_AsText(a) FROM t2; +ST_AsText(a) +POINT(1 1) +POINT(2 2) +DROP TABLE t1, t2; |