建表,注意数据库表索引必须是MyISAM
CREATE TABLE address (
address CHAR(80) NOT NULL,
address_loc POINT NOT NULL,
PRIMARY KEY(address)
)ENGINE=MyISAM;
空间索引:
ALTER TABLE address ADD SPATIAL INDEX(address_loc);
插入数据:(注:此处Point(纬度,经度) 标准写法)
INSERT INTO address VALUES('Foobar street 12', GeomFromText('POINT(30.620076 104.067221)'));
INSERT INTO address VALUES('Foobar street 13', GeomFromText('POINT(31.720076 105.167221)'));
查询: 查找(30.620076,104.067221)附近 10 公里
SELECT *
FROM address
WHERE MBRContains
(
LineString
(
Point
(
30.620076 + 10 / ( 111.1 / COS(RADIANS(104.067221))),
104.067221 + 10 / 111.1
),
Point
(
30.620076 - 10 / ( 111.1 / COS(RADIANS(104.067221))),
104.067221 - 10 / 111.1
)
),
address_loc
)
mysql函数大全:
http://www.cnblogs.com/waterystone/p/5606423.html