论坛 › MySQL运维 › 使用mysql空间索引，搜索附近的坐标地址并按距离排序

robin 发表于 2016-10-25 07:05:11

使用mysql空间索引，搜索附近的坐标地址并按距离排序

SELECT
address,X(address_loc),Y(address_loc),
(2 * 6378.137 * ASIN(SQRT(POW(SIN(PI() * (:lat - X(p.shop_point)) / 360),2) + COS(PI() * :lat / 180) * COS(X(p.shop_point) * PI() / 180) * POW(SIN(PI() * (:lng - Y(p.shop_point)) / 360),2)))) as distance
FROM
address
WHERE
MBRContains (
LineString (
Point (
30.620076 + 10 / (
111.1 / COS(RADIANS(104.067221))
),
104.067221 + 10 / 111.1
),
Point (
30.620076 - 10 / (
111.1 / COS(RADIANS(104.067221))
),
104.067221 - 10 / 111.1
)
),
address_loc
)
ORDER BY distance;

查看完整版本: 使用mysql空间索引，搜索附近的坐标地址并按距离排序