0

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?

Jim Jones
  • 18,404
  • 3
  • 35
  • 44
Yen Sheng
  • 695
  • 1
  • 12
  • 28

3 Answers3

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.

Laurenz Albe
  • 209,280
  • 17
  • 206
  • 263
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 )

JGH
  • 15,928
  • 4
  • 31
  • 48
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;
Tik
  • 822
  • 6
  • 14