在PHP中优化Haversine公式SQL调用 [英] Optimising a haversine formula SQL call in PHP

问题描述

我正在使用PHP创建MySQL调用，正在使用Haversine论坛计算距离:

I'm creating an MySQL call using PHP, I'm calculating distance using the haversine forumula:

SELECT name, id,
   (6371 * acos(cos(radians(' . $lat . ')) 
   * cos(radians(geoname.latitude)) 
   * cos(radians(geoname.longitude) - radians(' . $lon . ')) 
   + sin(radians(' . $lat . ')) 
   * sin(radians(geoname.latitude)))) AS distance 

我的问题是；最好用SQL进行所有这些计算吗?该查询搜索包含约1000条记录的表.用PHP而不是SQL进行一些数学运算会更有效吗?有没有更好的方法来优化此查询?

My question is; is it best to do all this calculation in SQL? This query searches a table with around 1000 records. Would it be more efficient to do some of the math in PHP rather than SQL? Is there a better way to optimise this query?

推荐答案

您正在使用球形法则而不是 Haversine公式(稍慢一些).

You're using the Spherical Law of Cosines formula, not the Haversine Formula (which is slightly slower).

在MySQL中进行数学运算可能会更快，我建议您阅读此问题我问了一段时间，关于速度优化，您绝对应该阅读出色的 《使用MySQL进行地理邻近搜索》 ， 特别注意第8-14页和第19页 .

Doing the math in MySQL will probably be much faster, I advise you to read this question I asked a while back, as for speed optimizations you should definitely read the excellent Geo Proximity Search with MySQL paper, pay special attention to pages 8-14 and 19.

这篇关于在PHP中优化Haversine公式SQL调用的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！