MySQL计算距离（简单解决方案）

发布于 2024-12-08 21:04:01 字数 484 浏览 1 评论 0原文

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

在此示例中，我已将用户输入替换为仅值（纬度= 52.64，长= 6.88，所需距离= 10公里）

查询：

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 子句时，我得到了表的每条记录，包括它们的计算距离。在这种情况下，我必须获取整个表。

我如何才能只获取所需的记录？

预先感谢您的任何评论！

原文

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.

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

the query:

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

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!

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

但可醉心 2024-12-15 21:04:01

您不能从 sql 语句的其他部分引用 select 子句中的别名。您需要将整个表达式放在 where 子句中：

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

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

  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

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

Demo: http://www.sqlize.com/q96p2mCwnJ

回复收藏 0 原文

此岸叶落 2024-12-15 21:04:01

正如 mellamokb 注释，您不能在 <代码>WHERE子句。但是，您可以在 中执行此操作HAVING 子句

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
HAVING distance < 10
ORDER BY distance DESC

：如果您有很多地址，您可能需要考虑通过尽早排除其中一些地址来优化查询。例如，使用合适的索引，以下版本可能会快得多：

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
WHERE latitude > '52.64' - 10 / (69.1/1.61)
  AND latitude < '52.64' + 10 / (69.1/1.61)
  AND longitude > '6.88' - 10 / (53/1.61)
  AND longitude < '6.88' + 10 / (53/1.61)
HAVING distance < 10
ORDER BY distance DESC

As mellamokb notes, you can't reference column aliases in the WHERE clause. You can, however, do it in a HAVING clause:

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
HAVING distance < 10
ORDER BY distance DESC

Ps. If you have lots of addresses, you might want to consider optimizing the query by ruling out some of them early. For example, with suitable indexes, the following version might be considerably faster:

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
WHERE latitude > '52.64' - 10 / (69.1/1.61)
  AND latitude < '52.64' + 10 / (69.1/1.61)
  AND longitude > '6.88' - 10 / (53/1.61)
  AND longitude < '6.88' + 10 / (53/1.61)
HAVING distance < 10
ORDER BY distance DESC

回复收藏 0 原文

围归者 2024-12-15 21:04:01

您将计算别名为“距离”，但您也将表“lp_relations_addresses”别名为“距离”。尝试给它们起一个不同的名称，如下所示：

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 addr
WHERE distance < 10
ORDER BY `distance`  DESC

You are aliasing the calculation as 'distance', but you are also aliasing table 'lp_relations_addresses' as 'distance'. Try giving them a different name like this:

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 addr
WHERE distance < 10
ORDER BY `distance`  DESC

回复收藏 0 原文

~没有更多了~