MySQL通过中心点和半径算_MySQL-从数据库中查找半径内的点

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.

解决方案

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.