CODEWITHSUNDEEP
sqlpostgresql
Yen Sheng
Yen Sheng

Reputation: 725

postgresql: input is out of range

I am trying to create a query to get the distance between 2 locations.

https://developers.google.com/maps/solutions/store-locator/clothing-store-locator

Sample from the link:

SELECT id, ( 3959 * acos( cos( radians(37) ) * cos( radians( lat ) ) * cos( radians( lng ) - radians(-122) ) + sin( radians(37) ) * sin( radians( lat ) ) ) ) AS distance FROM markers HAVING distance < 25 ORDER BY distance LIMIT 0 , 20;

Based on the sample link I have created the following query.

select ( 6371 * acos( cos( radians(3.139003) ) * cos( radians( 3.10726 ) ) * cos( radians( 101.60671 ) - radians(101.68685499999992) ) + sin( radians(3.139003) ) * sin( radians( 101.60671 ) ) ) ) AS distance from requests

Location 1: 3.139003, 101.68685499999992

Location 2: 3.10726, 101.60671

However, the query always failed with input is out of range, which seems to be due to acos.

Anyone can provide some pointers on this?

Upvotes: 0

Views: 889

Answers (3)

Tik
Tik

Reputation: 882

I know this doesn't particulary answer the question however this is the query i have saved for postgres that calculates the distance between 2 points in km using the haversine formula. I find its fairly simple and accurate.

SELECT asin(
  sqrt(
    sin(radians(latB - latA)/2)^2 +
    sin(radians(lonB - lonA)/2)^2 *
    cos(radians(latA)) *
    cos(radians(latB))
  )
) * (6371 * 2) AS distance;

Upvotes: 1

JGH
JGH

Reputation: 17876

The last sin should be sin(radian(lat)) but you used the longitude instead. Replace sin( radians( 101.60671 ) with sin( radians( 3.10726 )

Upvotes: 1

Laurenz Albe
Laurenz Albe

Reputation: 247043

Yes, this is fairly trivial.

The argument to your leftmost acos function is

cos( radians(3.139003) )
* cos( radians( 3.10726 ) )
* cos( radians( 101.60671 )
       - radians(101.68685499999992) )
+ sin( radians(3.139003) ) * sin( radians( 101.60671 ) )

which evaluates to 1.05066948199501.

But anything outside the range [-1,1] is not a valid argument for acos.

Upvotes: 1

Related Questions