1

I have a MySQL table

Users(State, City, Zip, Lat, Long)

I would like to get a list of user who are inside the circle or square of a given latitude and longitude with given distance. For example my input Lat= 78.3232 and Long = 65.3234 and distance = 5 kilometers. I would like to get the list of users who are inside 5 kilometers distance from the point 78.3232 and 65.3234. Is it possible to solve this with single query?

1

Using GIS

Assuming MySQL 8+

SELECT u.*,
  ST_Distance(
    ST_SRID(Point(65.3234, 78.3232),4326), 
    ST_SRID(Point(u.lon,u.lat),4326)
  )
FROM Users AS u
WHERE ST_Intersects(
  ST_Buffer(ST_SRID(Point(65.3234, 78.3232),4326), 5000), 
  ST_SRID(Point(u.lon,u.lat),4326)
);

If you're not using MySQL 8+ you can't use ST_SRID(geom,SRID). Also, if you store GEOMETERY types on the table, rather than silly lat and long columns, this looks a lot cleaner.

SELECT u.*,
  ST_Distance(
    ST_SRID(Point(65.3234, 78.3232),4326), 
    u.geom
  )
FROM Users AS u
WHERE ST_Intersects(
  ST_Buffer(ST_SRID(Point(65.3234, 78.3232),4326), 5000), 
  u.geom
);
0

The magic query would be the following:

SELECT id, name, latitude, longitude
FROM   users
WHERE  (((acos(sin(($lat*pi()/180)) * sin((latitude*pi()/180)) + cos(($lat*pi()/180)) * cos((latitude*pi()/180)) * cos((($lng - longitude)*pi()/180))))*180/pi())*60*2.133) <= $distance;

For your example:

SELECT id, name, latitude, longitude
FROM   users
WHERE  (((acos(sin((78.3232*pi()/180)) * sin((78.3232*pi()/180)) + cos((78.3232*pi()/180)) * cos((78.3232*pi()/180)) * cos(((65.3234 - longitude)*pi()/180))))*180/pi())*60*2.133) <= 5;

You must be aware that this is not the most optimized query if you have a lot of data because of the amount of computation, but it works. There is some ways to improve the overall execution of that query but it's another topic.

  • Than k you , i will see and get back to u , how it will work – Pydi Raju Sep 30 '14 at 13:52

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.