将Haversine公式与PostgreSQL和PDO一起使用 [英] Using the Haversine formula with PostgreSQL and PDO
问题描述
在我的网站上，我试图找到附近的位置.
On my site I'm trying to get locations nearby.
我正在尝试使用Haversine公式.
I'm trying to use the Haversine formula for this.
- http://en.wikipedia.org/wiki/Haversine_formula
- MySQL Great Circle Distance (Haversine formula)
- Calculate zipcodes in range
我正在使用以下查询来获取半径25公里以内的所有位置.
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
但是我认为某些功能可能仅是MySQL，因为出现以下错误:
However I think some functions may be MySQL only, because I get the following error:
警告:PDOStatement :: execute()[pdostatement.execute]:SQLSTATE [42883]:未定义函数:7错误:函数弧度(文本)不存在LINE 1:... id，(6371 * acos(cos (radians(51.8391))* cos(radians(l ... ^提示:没有函数与给定的名称和参数类型匹配.您可能需要在...中添加显式类型强制转换.
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 ...
或者可能与事实上我必须在查询中更改文本lat有关.但是我不知道该怎么办.
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和4.6265是我的起点"的漫长和滞后.
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 :-)
编辑
问题似乎出在我尝试做的事情:radians(lat).
It looks like that the problem is where I try to do: radians(lat).
lat是我表中的一列.
lat is a column in my table.
当我尝试使用rad()作为提示时，错误更改为:function rad(numeric) does not exist
When I try to use rad() as hakre suggested the error changes to: function rad(numeric) does not exist
编辑2
现在我们要去某个地方.
Now we're getting somewhere.
实际上设置为文本的列的数据类型(如mu所建议的太短).
The datatype of the columns where indeed set as text (as suggested by mu is too short).
我将其更改为双精度.
但是现在我又遇到另一个错误:
However now I get another error:
警告:PDOStatement :: execute()[pdostatement.execute]:SQLSTATE [42703]:未定义的列:7错误:列距离"不存在LINE 1:... adians(lat))))AS距离距商店的距离< ... ^ in ...
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.
推荐答案
PostgreSQL does have a radians function:
radians(dp)
弧度
radians(dp)
degrees to radians
但是radians需要一个浮点参数，而您试图给它一个某种形式的字符串:
but radians wants a floating point argument and you are trying to give it a string of some sort:
未定义函数:7错误:函数弧度(文本)
[...]提示:没有函数与给定名称和参数类型相匹配.您可能需要添加显式类型强制转换.
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.
强调我的.显然，您的lat和lng列是char(n)，varchar(n)或text列.您应该将lat和lng的列类型固定为numeric，float或其他一些播放您的手动输入字符串，并希望您没有任何损坏的数据:
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进行了大量隐式类型转换，而PostgreSQL则更为严格，要求您准确说明您的意思.
MySQL does a lot of implicit type conversions, PostgreSQL is rather more strict and requires you to say exactly what you mean.
第二个问题的更新:HAVING子句在SELECT子句之前进行求值，因此SELECT中的列别名通常在查询中的其他任何地方都不可用.您有两种选择，可以重复使用大型丑陋的Haversine:
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
这篇关于将Haversine公式与PostgreSQL和PDO一起使用的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
