Geek Answers Handbook

Using the Haversin Formula with PostgreSQL and PDO

On my site I am trying to find places nearby.

I am trying to use the Haversin formula for this.

I use the following query to get all locations within a 25 km 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 that some functions can only be MySQL, because I get the following error:

Warning: PDOStatement :: execute () [pdostatement.execute]: SQLSTATE [42883]: Undefined function: 7 ERROR: radians function (text) does not exist LINE 1: ... id, (6371 * acos (cos (radians (51.8391 )) * cos (radians (l ... ^ TIP: no function matches the specified name and argument types. You may need to add explicit cast types to ...

Or maybe this is due to the fact that I need to change the lat text in my request. But I do not know what it should be.

51.8391 and 4.6265 are long and lats of my "starting" point.

Any help is greatly appreciated since I don't know what to change :-)

EDIT

It seems like the problem is where I am trying to do: radians(lat) .

lat is the column in my table.

When I try to use rad() since hakre suggested that the error changed to: function rad(numeric) does not exist

EDIT 2

Now we get somewhere.

The data type of the columns that are really specified as text (as suggested by mu too short).

I changed it to double precision.

However, now I get another error:

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

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

Also, if you guys think this should go on a different issue, just let me know and I will close this one.

+4
source share
2 answers

PostgreSQL has a radians function:

radians(dp)
degrees to radians

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

Undefined Function: 7 ERROR: radians function ( text )
[...] TIP: no function matches the specified name and argument types . You may need to add explicit types of stocks .

The emphasis is mine. Apparently your lat and lng columns are char(n) , varchar(n) or text columns. You must fix the column types for lat and lng like numeric , float or some other floating point type ; at the same time, you can drop your lines manually and hope that you will not have any broken data:

 radians(cast(lat as double precision))

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

Update for the second problem . The HAVING is evaluated before the SELECT , so column aliases in SELECT are usually not available anywhere in the query. You have a couple of 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 view to avoid repetition:

 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
+7
source

Converting to radians is trivial:

 radians(n) = n * PI / 180.0
+2
source

More articles:


All Articles