MySQL - Find points within radius from database

Question

I have a table which has a POINT column containing the latitude and longitude of various locations.

I then also have a users location from geo-location in the browser.

What I need to be able to do is find all records from the table where the POINT value in the is within a 10 km radius (or X km radius), ordered by distance with the closest first.

My table has a SPATIAL index on the POINT column.

You can't find points within a circle using an index - but if you define a bounding box for the indexed lookup then filter based on the distance from the centre you'll get fast results. — symcbean, Mar 18 '17 at 13:29
The solutions given here do not perform well for large datasets. If that is an issue, see http://mysql.rjweb.org/doc.php/find_nearest_in_mysql — Rick James, May 01 '20 at 20:41

Bas van Dijk · Accepted Answer · 2017-03-15T00:58:18.007

21

I'm currently working on a project where I'm calculating distances between multiple locations. I'm using the following query for selecting object_id's which are within a given radius.

SELECT id, 
( 6371 * 
    ACOS( 
        COS( RADIANS( db_latitude ) ) * 
        COS( RADIANS( $user_latitude ) ) * 
        COS( RADIANS( $user_longitude ) - 
        RADIANS( db_longitude ) ) + 
        SIN( RADIANS( db_latitude ) ) * 
        SIN( RADIANS( $user_latitude) ) 
    ) 
) 
AS distance FROM the_table HAVING distance <= $the_radius ORDER BY distance ASC"

I can't explain the ACOS formula itself because I got it from research.

db_latitude = database latitude field
db_longitude = database longitude field
$user_latitude = browser latitude coördinate
$user_longitude = browser longitude coördinate
$the_radius = the radius that you want to search in

This is in kilometers.

edited Mar 15 '17 at 00:58

answered Mar 15 '17 at 00:46

Bas van Dijk

816
7
20

1

Hi @Bas and thanks for this but my Lat/Long is stored as a MySQL `POINT` field rather than two seperate floats. My understanding was that this isthe correct way to store this information so I can then have a `SPATIAL` index for efficiency. – tip2tail Mar 15 '17 at 19:37
1

Hi @tip2tail, I'm sorry I overlooked that detail. I'm not familiar with the 'POINT' field. Found this stack post hope it helps you http://stackoverflow.com/a/21170928/5567106 – Bas van Dijk Mar 15 '17 at 20:19
This was the answer although I had to get my POINT out to lat and long via the X() and Y() functions of MySQL. – tip2tail Mar 25 '17 at 10:15
1

If you need the range in MILES, replace "6371" with "3959". – Maverick_Java Jul 02 '17 at 15:28
Wouldn't this require calculating ALL the existing points in database one by one? seems like it just wont work at all if you have millions of points stored? – Evren Yurtesen Nov 11 '17 at 08:12
@EvrenYurtesen it does look at every record. I don't know how fast it would be with so many records. I've used this with a database where I had 17k records and that was pretty fast. – Bas van Dijk Nov 12 '17 at 11:47
1

@Bas van Dijk , you are selecting latitude and longitude for every row then calculate 'distance' to the user entered point (not to mention, you are using expensive trigonometry functions). Then filter results by radius. Yes, you go through every row in your table, every time you run the query. Use EXPLAIN to see how many rows are used. But for 17k rows, and only few queries at a time, this will work. Not when you have a large number of rows and more access, it simply won't scale. I wouldn't use it in production. – Evren Yurtesen Nov 12 '17 at 13:16
@EvrenYurtesen when you have larger data sets you could categorize the data. – Bas van Dijk Nov 13 '17 at 12:33
Yes, sorry I read your previous answer errenously as 'it does not look', should sleep more. I guess I would first use a square area to filter data with min/max latitude/longitude and then use your formula on remaining points. That is if I need ultimate accuracy. If I can get away with a a square area, I could avoid all those calculations. Anyway, thanks for the response. – Evren Yurtesen Nov 13 '17 at 12:39
i am surprised there is no way to just sort by Point and need to perform the calculation each time – ina Jul 05 '20 at 09:56
I can understand your frustration, maybe there is a better solution 3 years later? If you find a better solution, please let me know :) – Bas van Dijk Jul 06 '20 at 12:13

score 1 · Answer 2 · answered Sep 25 '17 at 07:53

The query below actually worked for me :

$query = "SELECT *,
    ( 6371 * 
    acos( 
    cos( radians( ".$user_lat." ) ) * 
      cos( radians( lat ) ) * 
      cos( radians( lng ) - 
      radians( ".$user_lng." ) ) + 
        sin( radians( ".$user_lat." ) ) * 
          sin( radians( lat ) ) ) ) 
          AS distance FROM parkings 
          HAVING distance <= ".$radius." ORDER BY distance ASC";

  $stmt = $conn->execute($query);

  $rows = $stmt->fetchAll('assoc');

where: $user_lat and $user_lng is browser's lat and lng, $radius = 10, table name is parkings

score -1 · Answer 3 · answered Mar 15 '17 at 00:44

May be this help for you, https://ru.scribd.com/presentation/2569355/Geo-Distance-Search-with-MySQL

For Django I use this

    dist = 20 #дистанция 20 км
    mylon = 51.5289156201 # долгота центра
    mylat = 46.0209384922 # широта 
    lon1 = mylon-dist/abs(math.cos(math.radians(mylat))*111.0) # 1 градус широты = 111 км
    lon2 = mylon+dist/abs(math.cos(math.radians(mylat))*111.0)
    lat1 = mylat-(dist/111.0)
    lat2 = mylat+(dist/111.0)
    profiles = UserProfile.objects.filter(lat__range=(lat1, lat2)).filter(lon__range=(lon1, lon2))

It search all users in squar 20km.

Hi @Nickita and thanks for this. This looks very similar to the answer above, please see my comments there. — tip2tail, Mar 15 '17 at 19:39

MySQL - Find points within radius from database

3 Answers3

Linked