0

I have table in MYSQL along with lat & lon values.

I want to fetch the users in the radius of 10km in context of current lat & lon.

I am trying following query but it gives me empty output.

SELECT
`id`,
`name`,
ACOS( SIN( RADIANS( `latitude` ) ) * SIN( RADIANS( $fLat ) ) + COS( RADIANS( `latitude` ) )
* COS( RADIANS( $fLat )) * COS( RADIANS( `longitude` ) - RADIANS( $fLon )) ) * 6380 AS `distance`
FROM `users`
WHERE
ACOS( SIN( RADIANS( `latitude` ) ) * SIN( RADIANS( $fLat ) ) + COS( RADIANS( `latitude` ) )
* COS( RADIANS( $fLat )) * COS( RADIANS( `longitude` ) - RADIANS( $fLon )) ) * 6380 < 10
ORDER BY `distance`

where latitude and longitude are columns names & $fLat & $fLon are current lat and lon values.

Dharman
  • 30,962
  • 25
  • 85
  • 135
Mr x
  • 828
  • 1
  • 8
  • 25
  • See https://meta.stackoverflow.com/questions/333952/why-should-i-provide-an-mcve-for-what-seems-to-me-to-be-a-very-simple-sql-query – Strawberry Apr 29 '17 at 08:41
  • Possible duplicate of [MySQL Great Circle Distance (Haversine formula)](http://stackoverflow.com/questions/574691/mysql-great-circle-distance-haversine-formula) – Peter O. Apr 29 '17 at 11:48
  • You can try to do the calculations in php – Nadir Latif Apr 30 '17 at 03:28
  • Do some testing on a test db with a few distances >= 10 and a few under 10. Use a distance calculator on the web to obtain the correct distances. Compare with your SQL without the where clause. Nut out what's wrong. – John Machin Apr 30 '17 at 08:40
  • I'm pretty sure 6380 is the wrong factor. Follow the [latitude-longitude] tag for many discussions on this topic, and many formulas (most of which are correct). – Rick James Apr 30 '17 at 18:38

1 Answers1

2

Use this query your problem will be solved:

SELECT `id`, `name`, `phone`, `latitude`, `longitude`, SQRT( POW(69.1 * (`latitude` - 24.900363), 2) + POW(69.1 * (67.099760 - `longitude`) * COS(`latitude` / 57.3), 2))
AS `distance`
FROM `users` HAVING `distance` < 25
ORDER BY `distance`
Das_Geek
  • 2,775
  • 7
  • 20
  • 26
Ahtesham Shah
  • 65
  • 8
  • 1
    Hi and welcome to Stackoverflow, and thank you for answering! However I'm afraid your answer is not formatted properly, making the code almost unreadable. Could you please [edit](https://stackoverflow.com/posts/59875602/edit) your question and include ``` tags around your code to format it as code to make it more legible? Also, code only answers aren't the best practise, could you also include a short explanation as to what the issue was you solved and how you solved it? – Plutian Jan 23 '20 at 09:56