如何优化通过经纬度计算距离的SQL查询?
我有一个结构如下的表:
table name: shop
id_shop int(10)
name varchar(200)
latitude double
longitude double
我想计算给定坐标与数据库中保存的坐标之间的距离。
我当前的查询:
SELECT *
FROM `shop` AS `s`
WHERE
(
( 6371
* ACOS(
SIN( RADIANS( latitude ) )
* SIN( RADIANS( 53.5353010379 ) )
+ COS( RADIANS( latitude ) )
* COS( RADIANS( 53.5353010379 ) )
* COS( RADIANS( 14.7984442616 ) - RADIANS( longitude ) )
)
)
<= 25
)
加上一些JOIN LEFT
来获取一些数据。
有什么办法可以优化该查询吗? 使用连接大约需要 13 毫秒。
我还需要在此处添加一些 LIMIT
和 COUNT(*)
作为分页的商店总数。
I have a table with structure like that:
table name: shop
id_shop int(10)
name varchar(200)
latitude double
longitude double
And I'd like to calculate distance between given coordinates and coordinates saved in database.
My current query:
SELECT *
FROM `shop` AS `s`
WHERE
(
( 6371
* ACOS(
SIN( RADIANS( latitude ) )
* SIN( RADIANS( 53.5353010379 ) )
+ COS( RADIANS( latitude ) )
* COS( RADIANS( 53.5353010379 ) )
* COS( RADIANS( 14.7984442616 ) - RADIANS( longitude ) )
)
)
<= 25
)
plus some JOIN LEFT
's for some data.
Is there any way to optimize that query ?
With joins it takes about 13msec.
I need to add here also some LIMIT
and COUNT(*)
for total amount of shops for pagination.
如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱,如果其他用户或者作者回复了您的评论,将不能在第一时间通知您!
发布评论
评论(2)
这里有一些想法,其中一些可能并不适用,具体取决于您的具体情况。
Here's a few ideas, some of which may not apply depending on your exact situation.
好吧,对于初学者来说,当您存储纬度和经度时,您可以将预先计算的内容存储在数据库中。例如,如果您将纬度和经度预先存储为弧度,则在存储每个位置时只需计算 RADIANS(latitude) 和 RADIANS(longitude) 一次,而不是每次需要进行距离计算时 (大概不止一次。)
也许当你第一次填充该行时,通过存储 SIN(RADIANS(latitude)) 和 COS(RADIANS(latitude)) 来减少更多...
我猜你正在做随着时间的推移,进行很多很多“最接近 X 的事情”计算——这就是人们在面对这种计算时通常会做的事情——而预先计算你能做的通常是首先要尝试的事情。
Well, for starters, you could store pre-calculated stuff in the database when you store the latitude and longitude. If you pre-store the latitude and longitude as radians, for example, you'll only need to calculate RADIANS(latitude) and RADIANS(longitude) once, when you store each location, not every time you need to do your distance calculation (presumably far more than once.)
Maybe shave off a bit more by storing the SIN(RADIANS(latitude)) and COS(RADIANS(latitude)) when you first populate the row, too...
I'm guessing you're doing many, many "nearest thing to X" calculations over time -- that's what people are generally doing when faced with this calculation -- and pre-calculating what you can is normally the first thing to try.