今天学习mysql，发现mysql5.7之后也支持空间地理数据类型，也支持相关的搜索。今天就来记录一下，如何经纬度范围查询。
首先建立一张表，表里面需要有两个字段，经度（x）和纬度（y）。
1、距离计算
已知当前位置经纬度（106.550474,29.563755），计算数据库中的点，与当前位置的距离。
mysql<5.7
SELECT shop_name, ((6371 * ACOS(COS(RADIANS(29.563755) ) * COS( RADIANS(`y`)) * COS( RADIANS(`x`) - RADIANS(106.550474)) + SIN(RADIANS(29.563755)) * SIN( RADIANS(`y`))))) * 1000 AS distance FROM shop
或者
SELECTshop_name,ROUND(6371 * 2 * ASIN(SQRT(POW(SIN((29.563755 * PI() / 180 - `y` * PI() / 180) / 2),2) + COS(29.563755 * PI() / 180) * COS(`y` * PI() / 180) * POW(SIN((106.550474 * PI() / 180 - `x` * PI() / 180) / 2),2))) * 1000) AS distanceFROM `shop`ORDER BY distance
该计算方法得到的距离是米。
mysql≥5.7
SELECT shop_name,st_distance_sphere(POINT(106.550474,29.563755), POINT(`x`, `y`)) AS distance FROM shop
该计算方法得到的距离是米。
2、范围搜索
mysql<5.7
SELECT shop_name, ((6371 * ACOS(COS(RADIANS(29.563755) ) * COS( RADIANS(`y`)) * COS( RADIANS(`x`) - RADIANS(106.550474)) + SIN(RADIANS(29.563755)) * SIN( RADIANS(`y`))))) * 1000 AS distance FROM `shop`HAVING distance < 100ORDER BY distance;
或者
SELECTshop_name,ROUND(6371 * 2 * ASIN(SQRT(POW(SIN((29.563755 * PI() / 180 - `y` * PI() / 180) / 2),2) + COS(29.563755 * PI() / 180) * COS(`y` * PI() / 180) * POW(SIN((106.550474 * PI() / 180 - `x` * PI() / 180) / 2),2))) * 1000) AS distanceFROM `shop`HAVING distance < 100ORDER BY distance
mysql≥5.7
SELECT shop_name,st_distance_sphere(POINT(106.550474,29.563755), POINT(`x`, `y`)) AS distance FROM `shop` HAVING distance < 100ORDER BY distance;
