用 MySQL 实现对坐标的范围查询

mybatis 代码 实现对范围的查询

30.4138 经度

104.234 纬度

<select id="xx" parameterType="xx" resultType="xx">
SELECT
    (
    6378137 * ACOS (
    COS ( RADIANS(#{lat}) )
    * COS( RADIANS( si.store_lat ) )
    * COS( RADIANS( si.store_lot ) - RADIANS(#{lot}) )
    + SIN ( RADIANS(#{lat}) )
    * SIN( RADIANS( si.store_lat ) )
    )
    ) AS distance
    FROM
    shop_store_info si
    WHERE
    1=1
    <if test="distance != null and lat != null and lot != null">
        AND (
            6378137 * ACOS (
                COS ( RADIANS(#{lat}) )
                * COS( RADIANS( si.store_lat ) )
                * COS( RADIANS( si.store_lot ) - RADIANS(#{lot}) )
                + SIN ( RADIANS(#{lat}) )
                * SIN( RADIANS( si.store_lat ) )
            )
        ) &lt;= #{distance}
    </if>
  </select>

查询坐标范围

mysql 语句 查询坐标范围

SELECT *,
(
    6371 * acos (
        cos ( radians('30.4138') )
        * cos( radians(latitude) )
        * cos( radians(longitude) - radians('104.234') )
        + sin ( radians('30.4138') )
        * sin( radians(latitude) )
    )
) AS distance 
FROM address
GROUP BY distance

使用索引

sql括号内的可以删除 这里只是为了查询方便看距离

此处有用索引 但不能实现范围查询

select *,
(
    6371 * acos (
            cos ( radians('30.4138') )
            * cos( radians(latitude) )
            * cos( radians(longitude) - radians('104.234') )
            + sin ( radians('30.4138') )
            * sin( radians(latitude) )
    )
) as km
from address 
where latitude > 30.4138-1 
	and latitude < 30.4138 + 1 
	and longitude > 104.234 - 1 
	and longitude < 104.234 + 1 
order by 
ACOS(SIN((30.4138 * 3.1415) / 180 ) * SIN((latitude * 3.1415) / 180 ) + 
COS((30.4138* 3.1415) / 180 ) * COS((latitude * 3.1415) / 180 ) *
COS((104.234* 3.1415) / 180 - (longitude * 3.1415) / 180 ) ) * 6380 asc 
limit 10;

varchar 类型的范围查询

(6条消息) MySQL之根据经纬度查询多少公里范围内的数据_星期三 的博客-CSDN博客

浮点型的范围查询

mysql根据经纬度获取附近商户 - 知乎 (zhihu.com)