[Mysql]如何实现按距离排序、范围查
总结：
1.适合场景： 查询范围为某个具体距离范围内，如1公⾥范围内
2.缺点：查的是距离范围内的，如果要按距离排序，sql语句在下⽂所属中加上下⾯语句
order by abs(htl.lng -" + lng + ")+abs(htl.lat -"+lat+")
3.我尝试了区间查，采⽤spatial4j.jar
- 源码：
- 下载jar包：
4. spatial的原理图
不是矩形的范围，是圆型的范围
下⾯转载⾃⽂章：
简介
现在⼏乎所有的O2O应⽤中都会存在“按范围搜素、离我最近、显⽰距离”等等基于位置的交互，那这样的功能是怎么实现的呢？本⽂提供的实现⽅式，适⽤于所有。
实现
为了⽅便下⾯说明，先给出⼀个初始表结构，我使⽤的是：
实现过程主要分为四步：
1. 搜索
在数据库中搜索出接近指定范围内的商户，如：搜索出1公⾥范围内的。 2. 过滤
搜索出来的结果可能会存在超过1公⾥的，需要再次过滤。如果对精度没有严格要求，可以跳过。 3. 排序
距离由近到远排序。如果不需要，可以跳过。 4. 分页
如果需要2、3步，才需要对分页特殊处理。如果不需要，可以在第1步直接SQL 分页。第1步数据库完成，后3步应⽤程序完成。
step1 搜索
搜索可以⽤下⾯两种⽅式来实现。
区间查
customer 表中使⽤两个字段存储了经度和纬度，如果提前计算出经纬度的范围，然后在这两个字段上加上索引，那搜索性能会很不错。 那怎么计算出经纬度的范围呢？已知条件是移动设备所在的经纬度，还有满⾜业务要求的半径，这很像初中的⼀道平⾯⼏何题：给定圆⼼坐标和半径，求该圆外切正⽅形四个顶点的坐标。⽽我们⾯对的是⼀个球体，可以使⽤来计算。
计算出经纬度范围之后，SQL 是这样：
需要给lon 、lat 两个字段建⽴联合索引：
geohash
CREATE  TABLE  `customer` (
`id` INT (11) UNSIGNED NOT  NULL  AUTO_INCREMENT COMMENT '⾃增主键',    `name` VARCHAR (5) NOT  NULL  COMMENT '名称',    `lon` DOUBLE (9,6) NOT  NULL  COMMENT '经度',    `lat` DOUBLE (8,6) NOT  NULL  COMMENT '纬度',    PRIMARY  KEY  (`id`))
COMMENT='商户表'CHARSET=utf8mb4ENGINE=InnoDB ;
1234567891011<dependency >
<groupId >com.spatial4j </groupId >    <artifactId >spatial4j </artifactId >    <version >0.5</version ></dependency >12345// 移动设备经纬度
double lon = 116.312528, lat = 39.983733;// 千⽶int radius = 1;
SpatialContext geo = SpatialContext .GEO ;
Rectangle rectangle = geo .getDistCalc ().calcBoxByDistFromPt (
geo .makePoint (lon, lat), radius * DistanceUtils .KM _TO_DEG, geo, null);System .out.println (rectangle .getMinX () + "-" + rectangle .getMaxX ());// 经度范围System .out.println (rectangle .getMinY () + "-" + rectangle .getMaxY ());// 纬度范围
12345678910
SELECT  id, name FROM  customer
WHERE  (lon BETWEEN ? AND  ?) AND  (lat BETWEEN ? AND  ?);
123
INDEX `idx_lon_lat` (`lon`, `lat`)
1
geohash 的原理不讲了，详细可以看，讲的很详细。geohash 能把⼆维的经纬度编码成⼀维的字符串，它的特点是越相近的经纬度编码后越相似，所以可以通过前缀like 的⽅式去匹配周围的商户。
customer 表要增加⼀个字段，来存储每个商户的geohash 编码，并且建⽴索引。
在新增或修改⼀个商户的时候，维护好geo_code ，那geo_code 怎么计算呢？也提供了⼀个⼯具类deLatLon(lat, lon)，默认精度是12位。这个存储做好后，就可以通过geo_code 去搜索了。拿到移动设备的经纬度，计算geo_code ，这时可以指定精度计算，那指定多长呢？我们需要⼀个geo_code 长度和距离的对照表：
geohash length width height 15,009.4km 4,992.6km 21,252.3km 624.1km 3156.5km 156km 439.1km 19.5km 5  4.9km    4.9km 6  1.2km 609.4m 7152.9m 152.4m 838.2m 19m 9  4.8m    4.8m 10  1.2m 59.5cm 1114.9cm 14.9cm 12
3.7cm
1.9cm
假设我们的需求是1公⾥范围内的商户，geo_code 的长度设置为5就可以了，deLatLon(lat, lon, 5)。计算出移动设备经纬度的geo_code 之后，SQL 是这样：
CREATE  TABLE  `customer` (
`id` INT (11) UNSIGNED NOT  NULL  AUTO_INCREMENT COMMENT '⾃增主键',    `name` VARCHAR (5) NOT  NULL  COMMENT '名称' COLLATE  'latin1_swedish_ci',    `lon` DOUBLE (9,6) NOT  NULL  COMMENT '经度',    `lat` DOUBLE (8,6) NOT  NULL  COMMENT '纬度',
`geo_code` CHAR (12) NOT  NULL  COMMENT 'geohash 编码',    PRIMARY  KEY  (`id`),
INDEX `idx_geo_code` (`geo_code`))
COMMENT='商户表'CHARSET=utf8mb4ENGINE=InnoDB ;
12345678910111213
SELECT  id, name FROM  customer
WHERE  geo_code LIKE  CONCAT(?, '%');
123
这样会⽐区间查快很多，并且得益于geo_code 的相似性，可以对热点区域做缓存。但这样使⽤geohash 还存在⼀个问题，geohash 最终是在地图上铺上了⼀个⽹格，每⼀个⽹格代表⼀个geohash 值，当传⼊的坐标接近当前⽹格的边界时，⽤上⾯的搜索⽅式就会丢失它附近的数据。⽐如下图中，在绿点的位置搜索不到⽩家⼤院，绿点和⽩家⼤院在划分的时候就分到了两个格⼦中。
解决这个问题思路也⽐较简单，我们查询时，除了使⽤绿点的geohash 编码进⾏匹配外，还使⽤周围8个⽹格的geohash 编码，这样可以避免这个问题。那怎么计算出周围8个⽹格的geohash 呢，可以使⽤来解决。
最终我们的sql 变成了这样：
原来的1次查询变成了9次查询，性能肯定会下降，这⾥可以优化下。还⽤上⾯的需求场景，搜索1公⾥范围内的商户，从上⾯的表格知道，geo_code 长度为5时，⽹格宽⾼是4.9KM ，⽤9个geo_code 查询时，范围太⼤了，所以可以将geo_code 长度设置为6，即缩⼩了查询范围，也满⾜了需求。还可以继续优化，在存储geo_code 时，只计算到6位，这样就可以将sql 变成这样：
这样将前缀匹配换成了直接匹配，速度会提升很多。
<dependency >
<groupId >ch.hsr </groupId >    <artifactId >geohash </artifactId >    <version >1.3.0</version ></dependency >12345// 移动设备经纬度
double lon = 116.312528, lat = 39.983733;
GeoHash geoHash = GeoHash .withCharacterPrecision (lat, lon, 10);// 当前
System .out.println (geoHash .toBase 32());// N, NE, E, SE, S, SW, W, NW
GeoHash[] adjacent = geoHash .getAdjacent ();for (GeoHash hash : adjacent) {    System .out.println (hash .toBase 32());}
12345678910
SELECT  id, name FROM  customer
WHERE  geo_code LIKE  CONCAT(?, '%')OR  geo_code LIKE  CONCAT(?, '%')OR  geo_code LIKE  CONCAT(?, '%')OR  geo_code LIKE  CONCAT(?, '%')OR  geo_code LIKE  CONCAT(?, '%')OR  geo_code LIKE  CONCAT(?, '%')OR  geo_code LIKE  CONCAT(?, '%')OR  geo_code LIKE  CONCAT(?, '%')OR  geo_code LIKE  CONCAT(?, '%');
1234567891011
SELECT  id, name FROM  customer
WHERE  geo_code IN  (?, ?, ?, ?, ?, ?, ?, ?, ?);
123
step2 过滤
上⾯两种搜索⽅式，都不是精确搜索，只是尽量缩⼩搜索范围，提升响应速度。所以需要在应⽤程序中做过滤，把距离⼤于1公⾥的商户过滤掉。计算距离同样使⽤。mysql下载jar包
过滤代码就不写了，遍历⼀遍搜索结果即可。
step3 排序
同样，排序也需要在应⽤程序中处理。排序基于上⾯的过滤结果做就可以了Collections.sort(list, comparator)。
step4 分页
如果需要2、3步，只能在内存中分页，做法也很简单，可以参考。
总结
全⽂的重点都在于搜索如何实现，更好的利⽤数据库的索引，两种搜索⽅式以百万数据量为分割线，第⼀种适⽤于百万以下，第⼆种适⽤于百万以上，未经过严格验证。可能有⼈会有疑问，过滤和排序都在应⽤层做，内存占⽤会不会很严重？这是个潜在问题，但⼤多数情况下不会。看我们⼤部分的应⽤场景，都是单⼀种类POI(Point Of Interest)的搜索，如酒店、美⾷、KTV 、电影院等等，这种数据密度是很⼩，1公⾥内的酒店，能有多少家，50家都算多的，所以最终要看具体业务数据密度。本⽂没有分析原理，只讲了具体实现，有关分析的⽂章可以看参考链接。
参考
// 移动设备经纬度
double  lon1 = 116.3125333347639, lat1 = 39.98355521792821;// 商户经纬度
double  lon2 = 116.312528, lat2 = 39.983733;SpatialContext geo = SpatialContext.GEO;
double  distance  = geo.calcDistance(geo.makePoint(lon1, lat1), geo.makePoint(lon2, lat2))    * DistanceUtils.DEG_TO_KM;System.out .println(distance );// KM
123456789

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。