MySQL计算距离(简单解决方案) [英] MySQL calculating distance (simple solution)

问题描述

我下一个查询是获取给定距离和给定邮政编码内的地址.根据经度和纬度数据计算距离.

I have the next query for getting addresses within a given distance and given postal code. Distance is calculated, based upon longitude and latitude data.

在此示例中，我仅用值(lat = 52.64，long = 6.88 en所需距离= 10km)替换了用户输入

In this example i have replaced the user-input for just values (lat=52.64, long=6.88 en desired distance=10km)

查询:

SELECT *,
ROUND( SQRT( POW( ( (69.1/1.61) * ('52.64' - latitude)), 2) + POW(( (53/1.61) * ('6.88' - longitude)), 2)), 1) AS distance
FROM lp_relations_addresses distance
WHERE distance < 10
 ORDER BY `distance`  DESC

给出未知的列距离作为错误消息. 当忽略where clausule时，我会获得表的每条记录，包括计算出的距离.在这种情况下，我必须获取整个表.

gives unknown column distance as error message. when leaving out the where clausule i get every record of the table including their calculated distance. In this case i have to fetch the whole table.

我如何只获取所需的记录?

How do i get only the desired records to fetch??

预先感谢您的任何评论！

Thanks in advance for any comment!

推荐答案

您不能从sql语句的另一部分中的select子句中引用别名.您需要将整个表达式放在where子句中:

You can't reference an alias in the select clause from another part of the sql statement. You need to put the whole expression in your where clause:

WHERE
    ROUND( SQRT( POW( ( (69.1/1.61) * ('52.64' - latitude)), 2)
        + POW(( (53/1.61) * ('6.88' - longitude)), 2)), 1) < 10

更清洁的解决方案是使用子查询来生成计算数据:

A cleaner solution would be to use a sub-query to generate the calculated data:

  SELECT *, distance
    FROM (
       SELECT *,
           ROUND( SQRT( POW( ( (69.1/1.61) * ('52.64' - latitude)), 2)
               + POW(( (53/1.61) * ('6.88' - longitude)), 2)), 1) AS distance
           FROM lp_relations_addresses
       ) d
   WHERE d.distance < 10
ORDER BY d.distance DESC

演示: http://www.sqlize.com/q96p2mCwnJ

这篇关于MySQL计算距离(简单解决方案)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！