# Find points within a distance using MySQL

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?

# 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
);
``````

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;
``````

``````SELECT id, name, latitude, longitude