CODEWITHSUNDEEP
mysqldatabasegeolocation
Harikrishnan
Harikrishnan

Reputation: 8065

MySQL MIN query not working for calculated distance

I have a table of locations in my database. I need a query to find out the nearest location, provided any coordinates. I wrote the following query to get all rows, along with their respective distance from a given coordinate(distance in meters):

SELECT *, 111111 * DEGREES(ACOS(LEAST(COS(RADIANS(dest.latitude)) * COS(RADIANS(8.584710)) * COS(RADIANS(dest.longitude - 76.868735)) + SIN(RADIANS(dest.latitude)) * SIN(RADIANS(8.584710)), 1.0))) as  distance FROM offer dest;

It gives the following output:

+----+------------------------+----------+-----------+------------+---------------------+
| id | description            | latitude | longitude | name       | distance            |
+----+------------------------+----------+-----------+------------+---------------------+
|  2 | Location 1 Description | 8.574858 | 76.874748 | Location 1 |   1278.565430298969 |
| 12 | Location 2 Description | 8.584711 | 76.868738 | Location 2 | 0.35494725284463646 |
+----+------------------------+----------+-----------+------------+---------------------+

It is all working fine. Now to get the Minimum distance, I added HAVING MIN(distance) to this query. Now the query looks like below:

SELECT *, 111111 * DEGREES(ACOS(LEAST(COS(RADIANS(dest.latitude)) * COS(RADIANS(8.584710)) * COS(RADIANS(dest.longitude - 76.868735)) + SIN(RADIANS(dest.latitude)) * SIN(RADIANS(8.584710)), 1.0))) as  distance FROM offer dest having MIN(distance);

Now, this query is supposed to return 1 row and that should be Location 2, as it has the the minimum location, but this is returning location 1 instead as seen below:

+----+------------------------+----------+-----------+------------+---------------------+
| id | description            | latitude | longitude | name       | distance            |
+----+------------------------+----------+-----------+------------+---------------------+
|  2 | Location 1 Description | 8.574858 | 76.874748 | Location 1 |   1278.565430298969 |
+----+------------------------+----------+-----------+------------+---------------------+

Why is this behaving so? Is there something wrong with my query? IF yes, what is it and how do I get the location with minimum distance.

Upvotes: 0

Views: 29

Answers (1)

slaakso
slaakso

Reputation: 9050

A HAVING-clause is used to filter conditions for a group. A group is defined with an aggregate function in SELECT-part and with a GROUP BY. As you do not have either of those, you should not use HAVING.

If you want to show the minimum distance from set of rows order by the distance and limit the result set just to one row.

SELECT *, 
  111111 * DEGREES(ACOS(LEAST(COS(RADIANS(dest.latitude)) *
  COS(RADIANS(8.584710)) * COS(RADIANS(dest.longitude - 76.868735)) + 
  SIN(RADIANS(dest.latitude)) * SIN(RADIANS(8.584710)), 1.0))) as  distance
FROM offer dest 
ORDER BY distance
LIMIT 1;

Upvotes: 2

Related Questions