在半径MySQL(纬度经度)内查找大表的最快方法是什么 [英] Whats the fastest way to lookup big tables for points within radius MySQL (latitude longitude)

查看:95
本文介绍了在半径MySQL(纬度经度)内查找大表的最快方法是什么的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!

问题描述

目前,我有一些表,每行100k +.我正在尝试查找如下数据.

SELECT
*, SQRT(POW(69.1 * (latitude - '49.1044302'), 2) + POW(69.1 * ('-122.801094' - longitude) * COS(latitude / 57.3), 2)) AS distance
FROM stops
HAVING distance < 5
ORDER BY distance limit 100

但是目前这种方法在高负载下会变慢.有些查询需要20秒钟以上才能完成.

如果有人知道有更好的优化方法,那就太好了.

解决方案

首先,如果您有很多地理空间数据,则应该使用mysql的地理空间扩展,而不是像这样的计算.然后,您可以创建空间索引最多可以查询很多查询,而不必像上面的查询那样编写冗长的查询.

ST_Dwithin 尚未在mysql中实现. >

Currently I have a few tables with 100k+ rows. I am trying to lookup the data like follows.

SELECT
*, SQRT(POW(69.1 * (latitude - '49.1044302'), 2) + POW(69.1 * ('-122.801094' - longitude) * COS(latitude / 57.3), 2)) AS distance
FROM stops
HAVING distance < 5
ORDER BY distance limit 100

But currently this method slows with high load. Some queries are taking 20+ seconds to complete.

If anyone knows any better ways to optimize this would be great.

解决方案

Well first of all if you have a lot of geospatial data, you should be using mysql's geospatial extensions rather than calculations like this. You can then create spatial indexes that would speed up many queries and you don't have to write long drawn out queries like the one above.

Using a comparision with ST_Distance or creating a geometry with the radius of interest along with ST_within might give you good results and could be a lot faster than the current. However the best and fastest way to achieve this, ST_Dwithin isn't implemented yet in mysql.

这篇关于在半径MySQL(纬度经度)内查找大表的最快方法是什么的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!

查看全文
登录 关闭
扫码关注1秒登录
发送“验证码”获取 | 15天全站免登陆