# Find points within a distance using MySQL

I have a mySQL table with user name, latitude and longitude of the user. 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 = 30 miles. I would like to get the list of users who are inside 30 miles distance from the point 78.3232 and 65.3234. Is it possible to solve this with single query? Or can you give me a hint start solving this query? I am new to the geo based information.

The SQL statement that will find the closest 20 locations that are within a radius of 30 miles to the 78.3232, 65.3234 coordinate. It calculates the distance based on the latitude/longitude of that row and the target latitude/longitude, and then asks for only rows where the distance value is less than 30 miles, orders the whole query by distance, and limits it to 20 results. To search by kilometers instead of miles, replace 3959 with 6371.

``````SELECT
id, (
3959 * acos (
* cos( radians( lat ) )
* sin( radians( lat ) )
)
) AS distance
FROM markers
HAVING distance < 30
ORDER BY distance
LIMIT 0 , 20;
``````

• I'm getting a syntax error in my select using this, "#1582 - Incorrect parameter count in the call to native function 'radians' what could it be? – bluantinoo Nov 29 '14 at 2:09
• Found: I had the lng variable empty! sorry! – bluantinoo Nov 29 '14 at 10:38
• Exactly what i wanted, but whats the query performance overload for thousands of records? and how about the accuracy? – Amit Shah Feb 5 at 12:01
• much better to replace it to 6371392.896 for searching by meters – Vasilii Suricov Feb 6 at 22:32

Mapperz's answer is invalid. Sinus must be calculated from latitude and NOT from longitude. So corect SQL statement is:

``````SELECT
id, (
3959 * acos (
* cos( radians( lat ) )
* sin( radians( lat ) )
)
) AS distance
FROM markers
HAVING distance < 30
ORDER BY distance
LIMIT 0 , 20;
``````

It might be base to create a function .. so you can reuse it other other areas. Also would make your query a bit cleaner... At least that is my 2 cents.

``````DELIMITER \$\$

create function calcDistance(lat float, lng float, pnt_lat float, pnt_lng float)

Returns float
BEGIN

Declare dist float;
SET dist =
3959 * acos (
* cos( radians( lat ) )
* sin( radians( lat ) )
);

RETURN dist;

END
``````
• answer will be upvoted if you fix codestyle. it's right way – Vasilii Suricov Feb 6 at 23:31

Here is my variant of query, seems a little bit easier (http://dexxtr.com/post/83498801191/how-to-determine-point-inside-circle-using-mysql)

``````SELECT
*
FROM
`locator`
WHERE
SQRT(POW(X(`center`) - 49.843317 , 2) + POW(Y(`center`) - 24.026642, 2)) * 100 < `radius`
``````
• It is easier but ignores the fact that the earth is curved. – Tim Rijavec Jun 3 '15 at 13:35
• Need a formula to be accurate. Maybe this would be good on short distances only :D – Jethro Dec 27 '15 at 16:49
• Ya'll gonna launch a missile, or something? – Dennis Braga Jun 11 at 14:12
• @DennisBraga - if so, then perhaps this question is off-topic, better suited to http://globalthermonuclearwar.stackexchange.com...? – ashleedawg Aug 22 at 9:37