mysql 根据经纬度检索排序的问题

[v2eb]

关键字：mysql 经纬度 附近 1km 距离排序

有 v 友做过类似的需求嘛，如何实现效率好点.

使用的方案：

1. 新增时：保存经纬度所在的地区行政区划编码
2. 查询时：根据请求参数中的经纬度,获得所在地区行政区划编码,like 查询缩小范围. 再依据 mysql st_distance() 函数计算距离

屏幕前可能的疑问：

1. 经纬度获取地区行政编码依赖于腾讯地图 WebServie 逆地址解析.
2. MySQL 5.6以下版本不支持 st_distance 函数.需要自己创建函数或升级版本.

CREATE FUNCTION `st_distance`(`lat1` double,`lng1` double,`lat2` double,`lng2` double)
RETURNS double
BEGIN
	DECLARE distance double;
	SELECT
	round(
		6378.138 * 2 * asin(sqrt(pow(sin(
						(lat1 * pi() / 180 - lat2 * pi() / 180) / 2
					),2) + cos(lat1 * pi() / 180) * cos(lat2 * pi() / 180) * pow(
					 sin((lng1 * pi() / 180 - lng2 * pi() / 180) / 2),2))
		) * 1000
	) INTO distance;
	RETURN distance;
END;

其他参考资料

1. https://tech.meituan.com/2014/09/05/lucene-distance.html
2. https://github.com/kungfoo/geohash-java

[SjwNo1]

redis geo

[em70]

用 mongodb 比较适合,直接支持经纬度索引

[liuzhaowei55]

终极解决方案 pgsql ，可以上生产的方案 mongodb ，勉强用 redis ，不太推荐用 mysql 自身的地理空间解决方案。
老手艺的话就根据已知点和距离算个正方形出来，直接和库里数据比较一下，取出来再比较距离。
需要注意的是 mongodb redis 两点间距离计算并没有根据墨卡托投影优化，两点距离越远算出来的值偏差就越大

[chenliang0571]

geohash

[des]

geohash 前缀匹配正解

[CEBBCAT]

提问前 Google 的好处不止「减少社区帖子」一个好处，对自己还能锻炼自学能力，以及「兼听则明」，实在是好处多多

[v2eb]

@CEBBCAT 基本的技术方案已经有了，不过想听下百家之言，看看有无更好的方式而已。你上来就给我带个帽子，挺无聊的。

[yggd]

geohash, S2, H3

[zhangwugui]

mysql 我记得很早就支持的，5.6 还是 5.7 之后进行了优化，有专门的函数，应该主要是 st_distance 还是 st_distance_spehre 函数，以前我还写过的；
这个方法 一个是返回距离的度数，一个是返回距离的米；

大概长这样吧：
SELECT s.*, (st_distance(point(lng, lat), point(122.00, 37.00) ) * 111195) AS distance
FROM test s ORDER BY distance

[zhangwugui]

不过如果说 mysql 之外的方案，那可就多了去了，之前我们生产用 pgsql 库，pgsql 天然就适合这种操作；

[CEBBCAT]

@v2eb 既然已经有了方案那放上来供大家检查岂不一步到位？抛砖引玉说的不就是这种情况吗？

[tool2d]

如果静态化数据，可以自己提出来到内存，专门做地理索引。数据量大了后，应该会比 st_distance 好用一点。

[dzdh]

elasticsearch > postgresql > mongodb > redis

除了 mysql 外随便挑

[dzdh]

postgresql+postgis 1.2e 经纬度搜索排序 无压力

[dzdh]

postgresql yyds

[liuguangxuan]

@dzdh #14 老哥，在这里 E 是个什么单位？

另外我用 PostgreSQL+PostGIS 测试了一下，随机在经度（-180°~180°），纬度（-90°~90°）生成 1 亿个点，查询 32°*32°的矩形区域，搜索出来的点数大约为 150 万个，耗时需要 15 分钟。这个属于正常的吗？有没有什么优化手段可以把查询时间降下去？

[v2eb]

@liuguangxuan 150w 个点都要展示出来吗？

[dzdh]

@liuguangxuan 同问。150w 个点都 select 出来吗

[liuguangxuan]

@v2eb #17
@dzdh #18 不需要全部都展示出来。

我的需求跟 OP 的有点儿类似，都属于地理空间查询。但是我可能比 OP 多了一些其它的字段属性，比如高度，速度，老哥们可以理解为飞机的轨迹，我不太需要关注每个实时的点，我更关注飞机的整体航迹。然后在地图上快速的从 1 亿个点里面把飞机的整体轨迹筛选出来。老哥们有没有比较快速的办法？