3分钟了解Mysql空间搜GeoHash-DBAspace

hti

　　简单介绍：
　　Mysql 内置函数方案，适合于已有业务，新增加LBS功能，增加经纬度字段方可，避免数据迁移，在5.7.5后实现更多功能实现INNODB的空间搜方法，之前版本主要是对MYISAM的支持。
　　在此之前，InnoDB将几何数据存储为BLOB（二进制大对象）数据，在空间数据上只能创建前缀索引，当涉及空间搜索时非常低效，尤其是在涉及复杂的几何数据时。在大多数情况下，获得结果的唯一方式是扫描表。
　　新版本MySQL中，InnoDB支持空间索引，通过R树来实现，使得空间搜索变得高效，如使用内置函数(MBRWITHIN MBRCONTAINS)效率非常好。但目前空间索引只支持两个维度的数据。
　　应用场景介绍:
　　公司新业务的需求，希望能够实现当前位置快速显示共享信息数据，针对5.7新特性的支持度调研，利用GeoHash封装成内置数据库函数的简易方案，实现前期的初期业务。
　　实现过程:
　　1、构建表
　　CREATE TABLE `tongzhou` (
　　`id` INT(11) NOT NULL AUTO_INCREMENT,
　　`cname` VARCHAR(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
　　`oint` POINT NOT NULL,
　　PRIMARY KEY (`id`),
　　SPATIAL KEY `sp_index` (`oint`)
　　) ENGINE=INNODB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
　　2、构造简单数据
　　insert into `tongzhou` (`cname`, oint) values('景欣园',GEOMFROMTEXT('POINT(39.8885917679 116.6576038966)'));
　　insert into `tongzhou` (`cname`, oint) values('京通罗斯福广场',GEOMFROMTEXT('POINT(39.8890214887 116.6473661241)'));
　　insert into `tongzhou` (`cname`, oint) values('中国北京市北京市通州区',GEOMFROMTEXT('POINT(39.8898670523 116.6566729546)'));
　　insert into `tongzhou` (`cname`, oint) values('中国北京市北京市通州区',GEOMFROMTEXT('POINT(39.8883852752 116.655728817)'));
　　insert into `tongzhou` (`cname`, oint) values('杨庄路22号院',GEOMFROMTEXT('POINT(39.8984936518 116.6339063644)'));
　　insert into `tongzhou` (`cname`, oint) values('中国北京市北京市朝阳区 ',GEOMFROMTEXT('POINT(39.8975388526 116.613779068)'));
　　insert into `tongzhou` (`cname`, oint) values('北京市朝阳区第三医院 约31米',GEOMFROMTEXT('POINT(39.8873809413 116.603307724)'));
　　insert into `tongzhou` (`cname`, oint) values('双桥温泉北里小区',GEOMFROMTEXT('POINT(39.8918427053 116.6076636314)'));
　　insert into `tongzhou` (`cname`, oint) values('京客隆配送中心',GEOMFROMTEXT('POINT(39.8916616061 116.5908622742)'));
　　insert into `tongzhou` (`cname`, oint) values('世纪宾馆',GEOMFROMTEXT('POINT(39.8918591688 116.6024065018)'));
　　insert into `tongzhou` (`cname`, oint) values('双桥六号井小区-北区 约48米',GEOMFROMTEXT('POINT(39.8899164443 116.6050457954)'));
　　insert into `tongzhou` (`cname`, oint) values('翠屏里小区 约190米',GEOMFROMTEXT('POINT(39.8883194176 116.6495060921)'));
　　insert into `tongzhou` (`cname`, oint) values('京通罗斯福广场',GEOMFROMTEXT('POINT(39.8894719148 116.6584646702)'));
　　insert into `tongzhou` (`cname`, oint) values('life新生活广场 约87米',GEOMFROMTEXT('POINT(39.8895542353 116.65579319)'));
　　insert into `tongzhou` (`cname`, oint) values('life新生活广场 约74米',GEOMFROMTEXT('POINT(39.8896242077 116.6568982601)'));
　　insert into `tongzhou` (`cname`, oint) values('life新生活广场',GEOMFROMTEXT('POINT(39.8901428239 116.6564154625)'));
　　insert into `tongzhou` (`cname`, oint) values('漫春园',GEOMFROMTEXT('POINT(39.8913364496 116.658115983)'));
　　insert into `tongzhou` (`cname`, oint) values('7天连锁酒店(北京通州果园环岛店) 约58米',GEOMFROMTEXT('POINT(39.8915998677 116.6560935974)'));
　　insert into `tongzhou` (`cname`, oint) values('北京金松宾馆 约77米',GEOMFROMTEXT('POINT(39.9036172391 116.6577833891)'));
　　insert into `tongzhou` (`cname`, oint) values('新华南路小区-东区 约32米',GEOMFROMTEXT('POINT(39.9034444015 116.6588240862)'));
　　insert into `tongzhou` (`cname`, oint) values('红旗小区 约61米',GEOMFROMTEXT('POINT(39.9046707164 116.6598540545)'));
　　insert into `tongzhou` (`cname`, oint) values('祥云天地家园 约62米',GEOMFROMTEXT('POINT(39.9067899674 116.659129858)'));
　　insert into `tongzhou` (`cname`, oint) values('梨园地铁站 约40米',GEOMFROMTEXT('POINT(39.8832975966 116.6687965393)'));
　　3、搜索当前1公里范围内的位置信息
　　SET @jl=1;  //多少公里范围内的
　　SET @jd=118.1964111328;
　　SET @wd=39.5591182422;  //当前的位置信息
　　oint是实际已经存在的经纬度信息
　　SELECT *,ASTEXT(oint)FROM  tongzhou WHERE  MBRCONTAINS( LINESTRING(POINT( @wd + @jl / ( 111.12 / COS(RADIANS(@jd))),
　　@jd + @jl / 111.12  ),
　　POINT  ( @wd - @jl / ( 111.12 / COS(RADIANS(@jd))),@jd - @jl / 111.12)  ), oint)
　　###经纬弧度（1° latitude = 111.12 kilometers）即 10/111.12，表示查找附近10公里。
　　4、测试
　　通过百度地图获取一些数据，对
　　SET @jd=118.1964111328
　　SET @wd=39.5591182422
　　值的更新，分析获取范围内的景点信息
　　5、问题：
　　如果直接在WHERE采用一系列的表达式，这样会导致无法使用空间索引，如图所示:

　　解决办法:
　　1、把WHERE后的表达式处理成一个变量进行引用，然后进行周边搜
　　SET @aa=(SELECT LINESTRING(POINT( 39.5591182422 + 10/ ( 111.12 / COS(RADIANS(118.1964111328))),
　　118.1964111328 + 10 / 111.12  ),
　　POINT  ( 39.5591182422 - 10 / ( 111.12 / COS(RADIANS(118.1964111328))),118.1964111328 -10 / 111.12)) AS  heji)
　　2、执行周边搜
　　SELECT ASTEXT(oint)FROM  tongzhou WHERE  MBRWITHIN(@aa,oint)
　　顺带执行计划效果:

　　其他SQL写法补充:
　　表示3公里范围内的点:
　　DDL：
　　CREATE TABLE `shop_id` (
　　`id` int(11) NOT NULL AUTO_INCREMENT,
　　`lat` double DEFAULT NULL,
　　`lng` double DEFAULT NULL,
　　`oint` point NOT NULL,
　　PRIMARY KEY (`id`),
　　SPATIAL KEY `sp_index` (`oint`)
　　) ENGINE=InnoDB AUTO_INCREMENT=275915 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci

　　SELECT>　　WHERE ST_Contains( ST_MakeEnvelope( POINT((-73.951368+(3/111)), (40.716743+(3/111))),
　　POINT((-73.951368-(3/111.12)), (40.716743-(3/111.12))) ), oint )