地理距离MySQL [英] Geo distance MySQL

问题描述

要搜索距指定位置最近的位置，请按距离排序

To search nearest locations to given locations, order by distance

1. 我应该使用float还是Point?
2. 我应该预先计算cos/sin/sqrt的值 http://www.movable-type.co.uk/scripts/latlong -db.html
3. 我的搜索在一个城市中的不同位置.
4. 许多老帖子都告诉mysql没有适当的地理支持，最新的MySQL版本也是如此吗?

1. Should I use float or Point?
2. Should I pre-compute value of cos/sin/sqrt http://www.movable-type.co.uk/scripts/latlong-db.html
3. My searches are various locations within one city.
4. Many OLD posts are telling mysql is not having proper geo support, Is it true with latest MySQL version as well?

推荐答案

我们正在使用double存储latitude和longitude.此外，我们仅通过一个触发器对所有值进行预计算(通过触发器)，这些值仅在观察一个点时即可进行计算.我目前无法访问我们正在使用的公式，请稍后再添加.为实现最佳速度/精度平衡而进行了优化.

We are using double to store latitude and longitude. In addition we precomute (by triggers) all values which are precomputable when looking at one point only. I currently don't have access to the formula we are using, will add this later. This is optimized for an optimal speed / precision balance.

对于定义的区域搜索(给定x公里内的所有点)，我们还会存储lat/lng值乘以1e6(1,000,000)，因此我们可以通过比较整数范围(例如闪电般快)来限制为正方形.

For defined area searches (give me all points within x km) we additionally store the lat/lng value multiplied with 1e6 (1,000,000) so we can limit into a square by comparing integer ranges which is lightning fast e.g.

lat BETWEEN 1290000 AND 2344000
AND
lng BETWEEN 4900000 AND 4910000
AND
distformularesult < 20

这是PHP当前位置值的公式和预先计算.

Here's the formular and precalculation of values of the current place in PHP.

WindowSize是一个您必须使用的值，其度数因子为1e6，用于缩小中心附近正方形中的可能结果，加快结果查找的速度-不要忘记此值至少应为 >您的搜索半径大小.

WindowSize is a value you have to play with, it's degrees factor 1e6, used to narrow down the possible results in a square around the center, speeds up result finding - dont forget this should be at least your search radius size.

$paramGeoLon = 35.0000 //my center longitude
$paramGeoLat = 12.0000 //my center latitude

$windowSize = 35000;   

$geoLatSinRad = sin( deg2rad( $paramGeoLat ) );
$geoLatCosRad = cos( deg2rad( $paramGeoLat ) );
$geoLonRad    = deg2rad( $paramGeoLon );

$minGeoLatInt = intval( round( ( $paramGeoLat * 1e6 ), 0 ) ) - $windowSize;
$maxGeoLatInt = intval( round( ( $paramGeoLat * 1e6 ), 0 ) ) + $windowSize;
$minGeoLonInt = intval( round( ( $paramGeoLon * 1e6 ), 0 ) ) - $windowSize;
$maxGeoLonInt = intval( round( ( $paramGeoLon * 1e6 ), 0 ) ) + $windowSize;

搜索我中心特定范围内的所有行

Searching all rows within a specific range of my center

SELECT
          `e`.`id`
        , :earthRadius * ACOS ( :paramGeoLatSinRad * `e`.`geoLatSinRad` + :paramGeoLatCosRad * `m`.`geoLatCosRad` * COS( `e`.`geoLonRad` - :paramGeoLonRad ) ) AS `geoDist`

FROM
          `example` `e`
WHERE
        `e`.`geoLatInt` BETWEEN :paramMinGeoLatInt AND :paramMaxGeoLatInt
        AND
        `e`.`geoLonInt` BETWEEN :paramMinGeoLonInt AND :paramMaxGeoLonInt
HAVING `geoDist` < 20
ORDER BY 
        `geoDist`

配方设计师的准确性很高(低于一米，具体取决于您所在的位置以及点之间的距离)

The formular has a quite good accuracy (below a metre, depending where you are and what distance is between the point)

我已经在数据库表example

CREATE TABLE `example` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `geoLat` double NOT NULL DEFAULT '0',
  `geoLon` double NOT NULL DEFAULT '0',

  # below is precalculated with a trigger
  `geoLatInt` int(11) NOT NULL DEFAULT '0',
  `geoLonInt` int(11) NOT NULL DEFAULT '0',
  `geoLatSinRad` double NOT NULL DEFAULT '0',
  `geoLatCosRad` double NOT NULL DEFAULT '0',
  `geoLonRad` double NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`),
  KEY `example_cIdx_geo` (`geoLatInt`,`geoLonInt`,`geoLatSinRad`,`geoLatCosRad`,`geoLonRad`)  
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci ROW_FORMAT=DYNAMIC

示例触发器

DELIMITER $
CREATE TRIGGER 'example_before_insert' BEFORE INSERT ON `example` FOR EACH ROW
BEGIN
    SET NEW.`geoLatInt` := CAST( ROUND( NEW.`geoLat` * 1e6, 0 ) AS SIGNED INTEGER );
    SET NEW.`geoLonInt` := CAST( ROUND( NEW.`geoLon` * 1e6, 0 ) AS SIGNED INTEGER );
    SET NEW.`geoLatSinRad` := SIN( RADIANS( NEW.`geoLat` ) );
    SET NEW.`geoLatCosRad` := COS( RADIANS( NEW.`geoLat` ) );
    SET NEW.`geoLonRad` := RADIANS( NEW.`geoLon` );
END$

CREATE TRIGGER 'example_before_update' BEFORE UPDATE ON `example` FOR EACH ROW
BEGIN
    IF NEW.geoLat <> OLD.geoLat OR NEW.geoLon <> OLD.geoLon
    THEN
        SET NEW.`geoLatInt` := CAST( ROUND( NEW.`geoLat` * 1e6, 0 ) AS SIGNED INTEGER );
        SET NEW.`geoLonInt` := CAST( ROUND( NEW.`geoLon` * 1e6, 0 ) AS SIGNED INTEGER );
        SET NEW.`geoLatSinRad` := SIN( RADIANS( NEW.`geoLat` ) );
        SET NEW.`geoLatCosRad` := COS( RADIANS( NEW.`geoLat` ) );
        SET NEW.`geoLonRad` := RADIANS( NEW.`geoLon` );
    END IF;
END$
DELIMITER ;

有问题吗?否则玩得开心:)

Questions? Otherwise have fun :)

这篇关于地理距离MySQL的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！