postgresql: input is out of range

Question

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?

JGH · Accepted Answer · 2019-05-15 11:23:04Z

1

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

answered May 15, 2019 at 11:23

JGH

18.3k5 gold badges42 silver badges58 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Yen Sheng Over a year ago

Nice catch! Didn't realize I keyed in the wrong formula.

Laurenz Albe · Accepted Answer · 2019-05-15 10:16:40Z

1

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.

answered May 15, 2019 at 10:16

Laurenz Albe

259k22 gold badges317 silver badges396 bronze badges

2 Comments

Yen Sheng Over a year ago

So basically the query is not a good example to get distance?

Laurenz Albe Over a year ago

No, there seems to be a bug somewhere.

Tik · Accepted Answer · 2019-05-15 11:23:43Z

1

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;

answered May 15, 2019 at 11:23

Tik

8826 silver badges14 bronze badges

Collectives™ on Stack Overflow

postgresql: input is out of range

3 Answers 3

1 Comment

2 Comments

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

2 Comments

Comments

Linked

Related