使用PostgreSQL和PDO结合Haversine公式

Question

使用PostgreSQL和PDO结合Haversine公式

5

在我的网站上，我试图获取附近的位置。

我尝试使用Haversine公式来实现这一点。

我正在使用以下查询来获取25公里半径内的所有位置。

SELECT id, ( 6371 * acos( cos( radians(51.8391) ) * cos( radians( lat ) ) * cos( radians( lng ) - radians(4.6265) ) + sin( radians(51.8391) ) * sin( radians( lat ) ) ) ) AS distance
FROM shops
HAVING distance < 25
ORDER BY name asc

然而，我认为一些函数可能仅适用于MySQL，因为我得到以下错误：

警告：PDOStatement :: execute() [pdostatement.execute]：SQLSTATE [42883]：未定义的函数：7 ERROR：函数radians（text）不存在LINE 1：... id，（6371 * acos（cos（radians（51.8391））* cos（radians（l ... ^ HINT：没有函数匹配给定的名称和参数类型。您可能需要添加显式类型转换。在...

或者可能与我必须更改查询中的文本lat有关。但我不知道应该是什么。

51.8391和4.6265是我“起点”的经度和纬度。

非常感谢任何帮助，因为我不知道该怎么改变:-）

编辑

看起来问题出在我尝试做的地方：

radians（lat）。

lat是我的表中的一列。
当我尝试使用hakre建议的rad()时，错误会更改为： function rad（numeric）不存在

编辑2

现在我们有所进展。

这些列的数据类型确实被设置为文本（如mu is too short所建议的）。

我已将其更改为双精度。

然而，现在我遇到了另一个错误：


警告：PDOStatement::execute() [pdostatement.execute]：SQLSTATE[42703]：未定义的列：7 ERROR：列“distance”不存在 LINE 1：...adians( lat ) ) ) ) AS distance FROM shops HAVING distance <... ^ in ...


但我认为我在选择中创建了别名。有什么想法吗？

如果你们认为这应该放在另一个问题中，请让我知道，我会关闭这个问题。

- PeeHaa

@hakre：没有，没运气。请看编辑。 - PeeHaa

只是一个快速尝试而已（很抱歉它没成功：）），看看如何在PostgreSQL中定义函数，这样您就可以更轻松（更快）地在查询中应用它们（还可以参考http://pgfoundry.org/projects/pgsphere/）。 - hakre

1

注意：这不是Haversine公式-这是更简单的基于余弦的公式。Haversine公式更加复杂，但除非使用高精度（例如double）算术，否则更准确。 - Alnitak

2

个人认为新位应该是一个单独的问题，但是这里已经有足够多关于“having vs where”的问题了。 - Alnitak

2个回答

2

将角度转换为弧度是很简单的:

radians(n) = n * PI / 180.0

- Alnitak

那可以用列（-data）来实现吗？因为我认为那就是需要做的事情。不过我不确定...另外请查看我的编辑。 - PeeHaa

1

PostgreSQL有一个名为radians的函数，问题在于lat和lng不是数字。 - mu is too short

1

当然 - 可以将弧度数据存储在您的数据库中，而不是角度。更好的方法是查看此答案：https://dev59.com/wmw15IYBdhLWcg3wxugh#6409253 - Alnitak

3

像@mu所说的一样-将数字存储为数字，而不是文本！！ - Alnitak

@Alnitak：是的，是的，那只是我太蠢了 :) - PeeHaa

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- mu is too short · Accepted Answer

PostgreSQL有一个名为radians的函数：（链接）

radians(dp)
将角度转换为弧度。

但是，radians需要浮点数作为参数，而你却想给它一个字符串类型的参数：

未定义的函数：7 ERROR: function radians(text)
[...] 提示：没有匹配给定名称和参数类型的函数。您可能需要添加显式类型转换。

强调是我的。显然，你的lat和lng列是char(n), varchar(n), 或者 text类型的列。你应该将lat和lng列的类型修复为numeric、float或其他浮点类型；同时，你也可以通过手动强制类型转换来解决问题，并希望你没有损坏的数据。

radians(cast(lat as double precision))

MySQL会进行很多隐式类型转换，而PostgreSQL则更加严格，需要您确切地表达您的意思。

第二个问题的更新：在HAVING子句之前评估SELECT子句，因此SELECT中的列别名通常在查询的其他任何地方都不可用。您有几个选项，可以重复使用您的大而丑陋的Haversine：

SELECT id, ( 6371 * acos( cos( radians(51.8391) ) * cos( radians( lat ) ) * cos( radians( lng ) - radians(4.6265) ) + sin( radians(51.8391) ) * sin( radians( lat ) ) ) ) AS distance
FROM shops
HAVING ( 6371 * acos( cos( radians(51.8391) ) * cos( radians( lat ) ) * cos( radians( lng ) - radians(4.6265) ) + sin( radians(51.8391) ) * sin( radians( lat ) ) ) ) < 25
ORDER BY name asc

或者使用派生表来避免重复：

select id, distance
from (
    select id, name, ( 6371 * acos( cos( radians(51.8391) ) * cos( radians( lat ) ) * cos( radians( lng ) - radians(4.6265) ) + sin( radians(51.8391) ) * sin( radians( lat ) ) ) ) as distance
    from shops
) as dt
where distance < 25.0
order by name asc