Find features within given coordinates and distance using MySQL

Question

I have a MySQL table with user name, latitude and longitude of the user.

I would like to get a list of users 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?

Why not PostGIS ? If you are starting geo project you can still change your stack — simpleuser001, Jan 14 '13 at 07:32
http://stackoverflow.com/a/40272394/1281385 Should be useful in speeding this query up (if needed) — exussum, Oct 26 '16 at 21:30
I don't think he calculates it correctly. I am trying to calculate the distance between airports Bourgas (BOJ - 42.416668, 27.283333) and Varna (VAR - 43.237260, 27.829096). With this request ( 6371 * acos ( cos (radians (43.237260)) * cos (radians (lat)) * cos (radians (lon) - radians (27.829096)) + sin (radians (43.237260)) * sin (radians (lat)) ) ) AS distance the result is 101,52135729600961 KM and in google maps the distance is 78.88 km. — Sergei Martinov, May 12 '20 at 08:03

score 46 · Answer 1 · edited Feb 06 '19 at 23:29

46

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(78.3232) )
      * cos( radians( lat ) )
      * cos( radians( lng ) - radians(65.3234) )
      + sin ( radians(78.3232) )
      * sin( radians( lat ) )
    )
) AS distance
FROM markers
HAVING distance < 30
ORDER BY distance
LIMIT 0 , 20;

edited Feb 06 '19 at 23:29

PolyGeo

65,136
29
109
338

answered Jul 22 '13 at 13:51

Marek Čačko

581
5
7

Your answer should be ordered 1st. – Amit Shah Feb 05 '19 at 12:06
@AmitShah If you think that you could ping the asker (@shihabK who has not been active on the site for nearly 6 years) and/or vote for https://meta.stackexchange.com/questions/268666/keeping-special-status-for-accepted-answers-without-sticking-them-to-top-forever – PolyGeo Feb 06 '19 at 23:33
This should be the accepted answer. – catbadger Sep 28 '19 at 13:08
Worked like a charm! – Gilberto Albino Jan 24 '21 at 22:03
This is not working in MariaDB server version – Loganathan Natarajan Sep 08 '23 at 06:02

score 38 · Accepted Answer · edited Jan 06 '14 at 22:25

38

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(78.3232) )
      * cos( radians( lat ) )
      * cos( radians( lng ) - radians(65.3234) )
      + sin ( radians(78.3232) )
      * sin( radians( lat ) )
    )
  ) AS distance
FROM markers
HAVING distance < 30
ORDER BY distance
LIMIT 0 , 20;

This is using the Google Maps API v3 with a MySQL backend which your already have.

https://developers.google.com/maps/articles/phpsqlsearch_v3#findnearsql

edited Jan 06 '14 at 22:25

sachleen

103
4

answered Aug 16 '12 at 16:52

Mapperz

49,701
9
73
132

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 02: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 05 '19 at 12:01
1

much better to replace it to 6371392.896 for searching by meters – Vasilii Suricov Feb 06 '19 at 22:32
Is't possible to reduce the distance result like in my case from 2.71250308462937983478013848070986568927764892578125 to 2.71 ? – Ali Adil Feb 28 '20 at 19:45
@AliAdil use Select Round (Number, 2) > https://www.w3schools.com/sqL/trymysql.asp?filename=trysql_func_mysql_round – Mapperz Feb 29 '20 at 02:36

score 4 · Answer 3 · answered Aug 16 '20 at 06:56

4

It's 2020, you should be using the built in spatial function of the RDBS. In this case you are after the ST_Distance function.

-- geo column in users table is of Geometry data type, with a spatial index
SET @g1 = ST_SRID(POINT(78.3232, 65.3234), 4326);
SELECT * from users WHERE ST_Distance(users.geo, @g1, 'foot') < 158400; -- 5280 feet per mile

answered Aug 16 '20 at 06:56

John C

159
5

What about if apart from that, we also want to sort them by distance? First one, closest one. Do we use ORDER BY...? Thanks a lot @JohnC – Ricardo Oct 24 '20 at 15:53
1

@Ricardo correct, you would put the spatial function within the order by clause. Be sure that you also have a spatial index as well. – John C Oct 28 '20 at 06:11

score 2 · Answer 4 · edited Jan 08 '20 at 03:18

2

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(pnt_lat))
                * cos(radians(lat))
                * cos(radians(lng) - radians(pnt_lng))
            + sin(radians(pnt_lat))
                    * sin(radians(lat))
          );

    RETURN dist;

END

edited Jan 08 '20 at 03:18

Maciej Paprocki

103
3

answered Oct 18 '16 at 22:55

eDriven_Levar

121
1

answer will be upvoted if you fix codestyle. it's right way – Vasilii Suricov Feb 06 '19 at 23:31

score 0 · Answer 5 · edited Jan 08 '20 at 07:53

0

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`

edited Jan 08 '20 at 07:53

Nisse Engström

105
3

answered Apr 23 '14 at 05:18

dexxtr

1

5

It is easier but ignores the fact that the earth is curved. – Tim Rijavec Jun 03 '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 '19 at 14:12
2

@DennisBraga - if so, then perhaps this question is off-topic, better suited to http://globalthermonuclearwar.stackexchange.com...? – ashleedawg Aug 22 '19 at 09:37

Find features within given coordinates and distance using MySQL

5 Answers5

Linked