-1

Hey is there a way to get a list of nearest 10 destinations in order of their distance, my MySQL database has the latitude and longitude of the user and the places. I don't want to specify the distance between the user and the place aka no max or min distance, just the nearest 10 I need. Both users and places share the same table.

Andy the android
  • 169
  • 1
  • 2
  • 16
  • yes - it is possible – Professor Abronsius Jul 30 '16 at 10:58
  • There is, but it's slow.... and you can make it more efficient by specifying a bounding box, and executing a series of queries with ever increasing bounding boxes until you've got your 10 entries – Mark Baker Jul 30 '16 at 10:59
  • I wrote 10 for instance, actually I need 100 at least, so its a not a good idea I guess :( – Andy the android Jul 30 '16 at 11:01
  • 1
    10 could just as easily be 100...... you retrieve all those within the bounding box, and if it is less than your number, you increase the bounding box and run the query again, excluding those you have already retrieved, and if necessary again until you have reached the required number.... but there's no ultra-quick way unless you're actually using a database with geospatial functionality – Mark Baker Jul 30 '16 at 11:04
  • oh yeah gotcha, thanx – Andy the android Jul 30 '16 at 11:10

1 Answers1

2

You can use the Haversine formula:

SELECT t.*, (6371 * ACOS(COS(RADIANS(lat_user)) * COS(RADIANS(lat)) *
            COS(RADIANS(lng) - RADIANS(lng_user) ) + SIN( RADIANS(lat_user)) *
            SIN(RADIANS(lat)))) AS distance    -- distance in kilometers
FROM yourTable
ORDER BY distance ASC
LIMIT 10

In the above query I assume that lat_user and lng_user are the latitude and longitude of the user. And lat and lng are the latitude and longitude of a given place (record).

Read this SO question for more information.

Community
  • 1
  • 1
Tim Biegeleisen
  • 502,043
  • 27
  • 286
  • 360
  • Except that this will go through every single entry in the database table.... which will make it very, very slow... Haversine is an expensive, slow formula to calculate – Mark Baker Jul 30 '16 at 11:05
  • I am not passing the lat and long of the place, I have to fetch places from the database – Andy the android Jul 30 '16 at 11:06
  • @MarkBaker Yes this will not scale well for a large database with either frequent or many users. But for a few hundred or thousand records, it should be viable. Some sort of geographic partition is a better way to go long term. – Tim Biegeleisen Jul 30 '16 at 11:07
  • the records could be thousands, we will be picking a hundred, but yeah so its a large database – Andy the android Jul 30 '16 at 11:08
  • @Andytheandroid If your question is about a learning problem, then this answer is fine. If you are building a production system then you should read about the spatial features which MySQL has. – Tim Biegeleisen Jul 30 '16 at 11:08