MySQL-从数据库中查找半径内的点 [英] MySQL - Find points within radius from database

问题描述

我有一个表，该表的POINT列包含各个位置的纬度和经度.

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.

我需要做的是从表中找到所有记录，其中的POINT值在10 km半径(或X km半径)内，并按距离最近的顺序排序.

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.

我的表在POINT列上有一个SPATIAL索引.

My table has a SPATIAL index on the POINT column.

推荐答案

我目前正在研究一个项目，该项目正在计算多个位置之间的距离.我正在使用以下查询来选择给定半径内的object_id.

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"

我无法解释ACOS公式本身，因为我是从研究中得到的.

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

单位为公里.

这篇关于MySQL-从数据库中查找半径内的点的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！