Using the Haversine formula with PostgreSQL and PDO

Question

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.

Answer 1

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

Answer 2

Conversion to radians is trivial:

radians(n) = n * PI / 180.0