将半正矢公式与 PostgreSQL 和 PDO 结合使用

发布于 2024-11-28 21:27:34 字数 1684 浏览 0 评论 0原文

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

我正在尝试使用半正矢公式来实现此目的。

我使用以下查询来获取 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 错误：函数弧度（文本）不存在第 1 行：...id, ( 6371 * acos( cos ( radians(51.8391) ) * cos( radians( l... ^ 提示：没有函数与给定名称和参数类型匹配。您可能需要在...中添加显式类型转换。

文本 lat 相关，但我不知道它应该是什么。

也许这与我必须更改查询中的是我的“起始”点的长和纬度，

非常感谢任何帮助，因为我不知道要更改什么:-)

编辑

看来问题出在我尝试的地方。做： radians(lat)。

当

我尝试使用 rad() 作为 hakre 时，错误更改为：function rad(numeric)。 ) 不存在

编辑2

现在我们已经找到了

太短了）。

确实设置为文本的列的数据类型（按照 mu 的建议双精度。

但是现在我收到另一个错误：

警告：PDOStatement::execute() [pdostatement.execute]：SQLSTATE[42703]：未定义列：7 错误：列“距离”不存在第 1 行：...adians( lat ) ) ) ) AS 距离距离商店 <... ^ 在...

但我想我在选择中做了一个别名。有什么想法吗？

另外，如果你们认为这应该放在另一个问题中，请告诉我，我将关闭这个问题。

原文

On my site I'm trying to get locations nearby.

I'm trying to use the Haversine formula for this.

I'm using the following query to get all the locations within a 25km radius.

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

However I think some functions may be MySQL only, because I get the following error:

Warning: PDOStatement::execute() [pdostatement.execute]: SQLSTATE[42883]: Undefined function: 7 ERROR: function radians(text) does not exist LINE 1: ...id, ( 6371 * acos( cos( radians(51.8391) ) * cos( radians( l... ^ HINT: No function matches the given name and argument types. You might need to add explicit type casts. in ...

Or perhaps it has to do with tha fact I have to change the text lat in my query. But I don't know what it should be.

51.8391 and 4.6265 are the long and lat of my 'starting' point.

Any help is much appreciated since I don't have any idea what to change :-)

EDIT

It looks like that the problem is where I try to do: radians(lat).

lat is a column in my table.

When I try to use rad() as hakre suggested the error changes to: function rad(numeric) does not exist

EDIT 2

Now we're getting somewhere.

The datatype of the columns where indeed set as text (as suggested by mu is too short).

I've changed it to double precision.

However now I get another error:

Warning: PDOStatement::execute() [pdostatement.execute]: SQLSTATE[42703]: Undefined column: 7 ERROR: column "distance" does not exist LINE 1: ...adians( lat ) ) ) ) AS distance FROM shops HAVING distance <... ^ in ...

But I thought I made an alias in the select. Any ideas?

Also if you guys think this should go in another question just let me know and I will close this one.

分享到QQ

分享到微博

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

发布评论

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

心奴独伤 2024-12-05 21:27:34

PostgreSQL 确实有一个 radians< /代码>功能：

弧度(dp)
度到弧度

但弧度想要一个浮点参数，并且您试图给它某种字符串：

未定义的函数：7 错误：函数弧度（文本）
[...]提示：没有函数与给定名称和参数类型匹配。您可能需要添加显式类型转换。

强调我的。显然，您的 lat 和 lng 列是 char(n)、varchar(n) 或 text 列。您应该将 lat 和 lng 的列类型修复为 numeric、float 或其他一些浮点类型；同时，您可以转换您的手动输入字符串，希望你没有任何损坏的数据：

radians(cast(lat as double precision))

MySQL 做了很多隐式类型转换，PostgreSQL 更严格，要求你准确地说出你的意思。

第二个问题的更新：HAVING 子句在 SELECT 子句之前评估，因此 SELECT 中的列别名是通常在查询的其他地方不可用。你有几个选择，你可以重复你的丑陋的半正矢：

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

PostgreSQL does have a radians function:

radians(dp)
degrees to radians

but radians wants a floating point argument and you are trying to give it a string of some sort:

Undefined function: 7 ERROR: function radians(text)
[...] HINT: No function matches the given name and argument types. You might need to add explicit type casts.

Emphasis mine. Apparently your lat and lng columns are char(n), varchar(n), or text columns. You should fix the column types for lat and lng to be numeric, float, or some other floating point type; in the mean time, you can cast your strings by hand and hope that you don't have any broken data:

radians(cast(lat as double precision))

MySQL does a lot of implicit type conversions, PostgreSQL is rather more strict and requires you to say exactly what you mean.

Update for the second problem: The HAVING clause is evaluated before the SELECT clause so column aliases in the SELECT are not generally available anywhere else in the query. You have a couple options, you can repeat your big ugly 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

Or use a derived table to avoid repeating yourself:

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

回复收藏 0 原文