CODEWITHSUNDEEP
mysqlsqlgpshaversine
Matthew
Matthew

Reputation: 2135

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

Upvotes: 2

Views: 101

Answers (1)

Arsen Mkrtchyan
Arsen Mkrtchyan

Reputation: 50752

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

Upvotes: 5

Related Questions