MySQL: Return first three entries from table sorted by distance: giving latitude and logitude as par-CodePudding

I tried:

var latitude = K.params.latitude
var longitude = K.params.longitude

var sqlString = "SELECT name, latitude, longitude, SQRT( \
    POW(69.1 * (latitude - '" latitude "'), 2)   \
    POW(69.1 * ('" longitude "' - longitude) * COS(latitude / 57.3), 2)) AS distance \
FROM 10561_12865_tblEvents HAVING distance < 25 ORDER BY distance"

var results = K.query(sqlString, [])

But this returns only 1 result, sometimes none when it should have.

Do you have a better solution for me?

Thank you :)

CodePudding user response：

You can use ST_Distance_Sphere(point(lonA,latA), point(lonB,latB))

For example:

SELECT ST_Distance_Sphere(point(0, 0), point(180, 0)) as distance

You can find out more at https://dev.mysql.com/doc/refman/5.7/en/spatial-convenience-functions.html#function_st-distance-sphere

CodePudding user response：

I managed to solve it myself using the following function

var latitude = K.params.latitude
var longitude = K.params.longitude
var start = K.params.start
var count = K.params.count
var maxDistanceKM = K.params.maxDistanceInKM

var sqlString = "SELECT *,\
    ( 6371.25 * acos( \
            cos( radians('" latitude "') ) * cos( radians( latitude ) ) * cos( radians( longitude ) - radians('" longitude "') )   sin( radians('" latitude "') )  * sin( radians( latitude ) ) )  ) AS distance \
FROM 10561_12865_tblEvents \
HAVING distance < '" maxDistanceKM "' \
ORDER BY distance \
LIMIT " start " , " count