MySQL学习笔记15

案例学习

计算两点之间的距离

不建议用户使用MySQL做太复杂的空间信息存储——PostgreSQL在这方面是不错的选择。

这里将介绍一些常用的计算模式。一个典型的例子是计算以某个点为中心，一定半径内的所有点。典型的实际案例可能是查找某个点附近所有可以出租的房子，或者社交网站中“匹配”附近的用户，等等。

CREATE TABLE locations (
      id   INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
      name VARCHAR(30),
      lat  FLOAT NOT NULL,
      lon  FLOAT NOT NULL
    );
    INSERT INTO locations(name, lat, lon)
    VALUES('Charlottesville, Virginia', 38.03, −78.48),
          ('Chicago, Illinois',         41.85, −87.65),
          ('Washington, DC',            38.89, −77.04);

这里经度和纬度的单位是“度”，通常我们假设地球是圆的，然后使用两点所在最大圆（半正矢）公式来计算两点之间的距离。现在有坐标latA和lonA、latB和lonB，那么点A和点B的距离计算公式如下： ACOS( COS(latA) * COS(latB) * COS(lonA - lonB) + SIN(latA) * SIN(latB) )。

假设我们需要找出所有距离Charlottesville 100英里以内的点：

如果不需要太高的精度，直接使用一个正方形代替圆。

SELECT * FROM locations
    WHERE lat BETWEEN  38.03 - DEGREES(0.0253) AND  38.03 + DEGREES(0.0253)
      AND lon BETWEEN −78.48 - DEGREES(0.0253) AND −78.48 + DEGREES(0.0253);

说明：
0.0253表示弧度值，弧度值=距离/半径。此处地球半径是3959英里。100/3959=0.025258=>0.0253

如何使用索引来优化这个查询：

新增两个列，用来存储坐标的近似值FLOOR()，然后在查询中使用IN()将所有点的整数值都放到列表中。

现在我们可以根据坐标的一定范围的近似值来搜索了，这个近似值包括地板值和天花板值：

SELECT * FROM locations
    WHERE lat BETWEEN  38.03 - DEGREES(0.0253) AND  38.03 + DEGREES(0.0253)
      AND lon BETWEEN −78.48 - DEGREES(0.0253) AND −78.48 + DEGREES(0.0253)
      AND lat_floor IN(36,37,38,39,40) AND lon_floor IN(-80,-79,-78,-77);

事实上，到这时我们就无须根据正方形的近似来过滤数据了，我们可以使用最大圆公式或者毕达哥拉斯定理来计算:

SELECT * FROM locations
    WHERE lat_floor IN(36,37,38,39,40) AND lon_floor IN(-80,-79,-78,-77)
     AND 3979 * ACOS(
       COS(RADIANS(lat)) * COS(RADIANS(38.03)) * COS(RADIANS(lon) - RADIANS(-78.48))
         + SIN(RADIANS(lat)) * SIN(RADIANS(38.03))
     ) <= 100;

这时计算精度再次回到前面——使用一个精确的圆周——不过，现在的做法更快。

只要能够高效地过滤掉大部分的点，例如使用近似整数和索引，之后再做精确数学计算的代价并不大。

确保在你的设计中尽可能地让查询都用上合适的索引，使用近似计算（例如本案例中，认为地球是平的，使用一个正方形来近似圆周）来避免复杂的计算。