mysql 空间索引 使用_mysql 空间索引的使用

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);