mysql根据给定坐标查询半径几公里内的所有坐标
1. 根据表主键进行查询
注意:gis_longitude 经度、gis_latitude 纬度、JOIN里面可以扩展为根据另一个表的经纬度查询业务表的坐标、land_info换成自己的业务表
SELECT
land_id,
land_name,
gis_longitude,
gis_latitude,
ROUND(
6378.138 * 2 * ASIN(
SQRT(
POW( SIN(( origin.latitude * PI() / 180 - gis_latitude * PI() / 180 ) / 2 ), 2 ) + COS( origin.latitude * PI() / 180 ) * COS( gis_latitude * PI() / 180 ) * POW( SIN(( origin.longitude * PI() / 180 - gis_latitude * PI() / 180 ) / 2 ), 2 )))) AS distance
FROM
land_info
JOIN ( SELECT gis_longitude AS longitude, gis_latitude AS latitude FROM land_info WHERE land_id = 2 ) AS origin
WHERE
ROUND(
6378.138 * 2 * ASIN(
SQRT(
POW( SIN(( origin.latitude * PI() / 180 - gis_latitude * PI() / 180 ) / 2 ), 2 ) + COS( origin.latitude * PI() / 180 ) * COS( gis_latitude * PI() / 180 ) * POW( SIN(( origin.longitude * PI() / 180 - gis_longitude * PI() / 180 ) / 2 ), 2 )))) <= 10 ###(修改改制范围)
2. 根据传入经纬度坐标进行查询
注意:
(1)@origin.latitude:是传入的纬度
(2)@origin.longitude:是传入的经度
(3)@kilometer:是范围(公里)
(3)land_info修改为自己的业务表
set @origin.latitude = 39.924987; ###纬度
set @origin.longitude = 116.466755; ###经度
set @kilometer = 10; ###范围
SELECT
land_id,
land_name,
gis_longitude,
gis_latitude,
ROUND(
6378.138 * 2 * ASIN(
SQRT(
POW( SIN(( @origin.latitude * PI() / 180 - gis_latitude * PI() / 180 ) / 2 ), 2 ) + COS( @origin.latitude * PI() / 180 ) * COS( gis_latitude * PI() / 180 ) * POW( SIN(( @origin.longitude * PI() / 180 - gis_latitude * PI() / 180 ) / 2 ), 2 )))) AS distance
FROM
land_info
WHERE
ROUND(
6378.138 * 2 * ASIN(
SQRT(
POW( SIN(( @origin.latitude * PI() / 180 - gis_latitude * PI() / 180 ) / 2 ), 2 ) + COS( @origin.latitude * PI() / 180 ) * COS( gis_latitude * PI() / 180 ) * POW( SIN(( @origin.longitude * PI() / 180 - gis_longitude * PI() / 180 ) / 2 ), 2 )))) <= @kilometer