On my site I'm trying to get locations nearby.
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
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.
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
Conversion to radians is trivial:
radians(n) = n * PI / 180.0
精彩评论