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.

Chiquita

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. – Simaroubaceous

The solutions given here do not perform well for large datasets. If that is an issue, see mysql.rjweb.org/doc.php/find_nearest_in_mysql – Bronze

Answer 1

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.

Stoecker

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. – Chiquita

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 https://mcmap.net/q/628399/-use-mysql-spatial-extensions-to-select-points-inside-circle – Stoecker

This was the answer although I had to get my POINT out to lat and long via the X() and Y() functions of MySQL. – Chiquita

If you need the range in MILES, replace "6371" with "3959". – Willawillabella

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? – Newsworthy

@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. – Stoecker

@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. – Newsworthy

@EvrenYurtesen when you have larger data sets you could categorize the data. – Stoecker

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. – Newsworthy

i am surprised there is no way to just sort by Point and need to perform the calculation each time – Antiquary

I can understand your frustration, maybe there is a better solution 3 years later? If you find a better solution, please let me know :) – Stoecker

Answer 2

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

Pasture

Answer 3

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.

Trucker

Hi @Nickita and thanks for this. This looks very similar to the answer above, please see my comments there. – Chiquita