Search code examples
mysqlsqlgpshaversine

SQL query and calculations; formatting query correctly to select only id


I have a query that selects all locations within a 30 mile radius of a GPS pt. I want to get the id's of those locations, but it's also returning the distance from the center pt.

Is there anyway to perform the distance calculation without returning it?

Query:

SELECT id, 3956 * 2 * ASIN(SQRT(
  POWER(SIN((34.1844709 - abs(dest.lat)) * pi()/180 / 2),
  2) + COS(37.7749295 * pi()/180 ) * COS(abs(dest.lat) *
  pi()/180) * POWER(SIN((-118.131809 - dest.lng) *
  pi()/180 / 2), 2) )) as distance
FROM location dest
having distance < 30
ORDER by distance
LIMIT 30

Output:

---------------------------
id    |    distance   
---------------------------
 1    |    2.310
 2    |    2.356
 17   |    4.298

Query based off: http://www.notaires.fr/sites/default/files/geo_searchjkkjkj_0.pdf


Solution

  • Can you just do another select on this?

    Select id
    From (SELECT id, 3956 * 2 * ASIN(SQRT(
      POWER(SIN((34.1844709 - abs(dest.lat)) * pi()/180 / 2),
      2) + COS(37.7749295 * pi()/180 ) * COS(abs(dest.lat) *
      pi()/180) * POWER(SIN((-118.131809 - dest.lng) *
      pi()/180 / 2), 2) )) as distance
    FROM location dest
    having distance < 30
    ORDER by distance
    LIMIT 30) dst