Find features within given coordinates and 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 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

    http://stackoverflow.com/a/40272394/1281385 Should be useful in speeding this query up (if needed)

    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. ![enter image description here](https://i.stack.imgur.com/FGnCC.png)

  • Mapperz

    Mapperz Correct answer

    8 years ago

    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

    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?

    Found: I had the lng variable empty! sorry!

    Exactly what i wanted, but whats the query performance overload for thousands of records? and how about the accuracy?

    much better to replace it to 6371392.896 for searching by meters

    Is't possible to reduce the distance result like in my case from 2.71250308462937983478013848070986568927764892578125 to 2.71 ?

License under CC-BY-SA with attribution


Content dated before 6/26/2020 9:53 AM