EVOLUTION-MANAGER

Edit File: point_basic.result

#
# 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`.`child` (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`.`child` (errno: 150 "Foreign key constraint is incorrectly formed")
Warning	1215	Cannot add foreign key constraint for `child`
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`.`child` (errno: 150 "Foreign key constraint is incorrectly formed")
Warning	1215	Cannot add foreign key constraint for `child`
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`.`child` (errno: 150 "Foreign key constraint is incorrectly formed")
Warning	1215	Cannot add foreign key constraint for `child`
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;