MySQL索引之空间索引（SPATIAL）

CREATE TABLE tb_geo(
id INT PRIMARY KEY AUTO_INCREMENT,
NAME VARCHAR(128) NOT NULL,
pnt POINT NOT NULL,
SPATIAL INDEX `spatIdx` (`pnt`)
)ENGINE=MYISAM DEFAULT CHARSET=utf8;















DESCRIBE tb_geo;



INSERT INTO `tb_geo` VALUES(
NULL,
'a test string',
POINTFROMTEXT('POINT(15 20)'));





SELECT id,NAME,ASTEXT(pnt) FROM tb_geo;


SELECT id,NAME,X(pnt),Y(pnt) FROM tb_geo;





SELECT ASTEXT(pnt) FROM tb_geo WHERE MBRWITHIN(pnt,GEOMFROMTEXT('Polygon((0 0,0 30,30 30,30 0,0 0))'));



DROP TABLE tb_geo;





SET @g1 = GEOMFROMTEXT('Polygon((0 0,0 3,3 3,3 0,0 0))');
SET @g2 = GEOMFROMTEXT('Point(1 1)');




SELECT MBRCONTAINS(@g1,@g2), MBRCONTAINS(@g2,@g1), MBRCONTAINS(@g1,@g1)；

SELECT MBRWITHIN(@g2,@g1),MBRWITHIN(@g1,@g2);

SELECT MBRDISJOINT(@g1,@g2);

SELECT MBREQUAL(@g1,@g2);

SELECT MBRINTERSECTS(@g1,@g2);

SELECT MBROVERLAPS(@g1,@g2);

SELECT MBRTOUCHES(@g1,@g2);