mysql空间查询方法_mysql空间查询

今天要做一个根据经纬度获取位置信息的功能(百度地图也提供了相应接口，由于请求频率限制，没有使用)，于是用到了mysql的空间查询：

1.创建表：

CREATE TABLE `xiaoqu` (

`ID` int(11) NOT NULL,

`name` varchar(255) DEFAULT NULL,

`code` varchar(255) DEFAULT NULL,

`type` int(11) DEFAULT NULL,

`the_geom` geometry DEFAULT NULL,

PRIMARY KEY (`ID`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8;

2.插入一条测试数据(墨卡托)：

INSERT INTO `xiaoqu` VALUES ('0', '博香苑小区', '650106015003001', '0', GeomFromText('MULTIPOLYGON(((9742726.24133861 5439705.01541711, 9742721.16132846 5439476.41495991, 9742533.20095253 5439352.80137935, 9742526.42760566 5439430.69486847, 9742463.77414701 5439437.46821535, 9742427.74526178 5439437.17087792, 9742423.13406573 5439342.64135902, 9742407.89403525 5439335.86801215, 9742109.86677253 5439334.17467543, 9742118.963493 5439592.06366672, 9742423.13406573 5439586.48184671, 9742477.49416709 5439585.64443215, 9742489.17419782 5439711.78876399, 9742726.24133861 5439705.01541711)))'));

3.执行查询：

SELECT NAME FROM xiaoqu where mbrwithin(GEOMFROMTEXT('Point(9742477.49416709 5439585.64443215)') ,the_geom);

4.运行结果：