之间找到两个纬度/长点的距离最快的方法(Fastest Way to Find Distance B

2019-11-01 10:09发布

站内文章 / 移动开发

25 0

神经病院院长

女 | 书童

私信

我现在有一个mysql数据库不到一万个地点的所有经度和纬度信息。

我试图通过查询发现一个点和许多其他点之间的距离。我想这是特别100+命中第二个它没有那么快。

是否有一个更快的查询或者有可能比MySQL这个其他更快的系统？我使用这个查询：

SELECT 
  name, 
   ( 3959 * acos( cos( radians(42.290763) ) * cos( radians( locations.lat ) ) 
   * cos( radians(locations.lng) - radians(-71.35368)) + sin(radians(42.290763)) 
   * sin( radians(locations.lat)))) AS distance 
FROM locations 
WHERE active = 1 
HAVING distance < 10 
ORDER BY distance;

注：所提供的距离是万里。 如果你需要公里，使用6371 ，而不是3959 。

Answer 1:

创建使用您的积分Point的值Geometry数据类型MyISAM表。在MySQL 5.7.5中， InnoDB表，现在也支持SPATIAL索引。
创建一个SPATIAL上的这些点指数

使用MBRContains()找到值：

 SELECT * FROM table WHERE MBRContains(LineFromText(CONCAT( '(' , @lon + 10 / ( 111.1 / cos(RADIANS(@lon))) , ' ' , @lat + 10 / 111.1 , ',' , @lon - 10 / ( 111.1 / cos(RADIANS(@lat))) , ' ' , @lat - 10 / 111.1 , ')' ) ,mypoint)

或者，在MySQL 5.1和以上：

    SELECT  *
    FROM    table
    WHERE   MBRContains
                    (
                    LineString
                            (
                            Point (
                                    @lon + 10 / ( 111.1 / COS(RADIANS(@lat))),
                                    @lat + 10 / 111.1
                                  ),
                            Point (
                                    @lon - 10 / ( 111.1 / COS(RADIANS(@lat))),
                                    @lat - 10 / 111.1
                                  ) 
                            ),
                    mypoint
                    )

这将选择大约内框的所有点(@lat +/- 10 km, @lon +/- 10km) 。

这实际上不是一个盒子，而是一个球形矩形：经度和纬度结合球的部分。这可能对弗兰茨·约瑟夫土地纯矩形不同，但很接近它的大多数居住的地方。

应用额外的滤波来选择圆（而不是正方形）内的一切
可能适用额外的精细过滤，占了个大圈距离（长距离）

Answer 2:

不是MySQL的具体的答案，但它会提高你的SQL语句的性能。

什么你切实做好是计算每一个点的距离在桌子上，看它是否是在10个单位给定点的。

你可以做你运行该SQL之前，是创建画一个方框20个单位的一侧，在中心即贵点四点。（X1，Y1）。。。（X4，Y4），其中（X1，Y1）是（givenlong + 10个单位，givenLat + 10单位）。。。（givenLong - 10单位，givenLat〜10个单位）。 其实，你只需要两个点，左上角和右下角称他们为（X1，Y1）和（X2，Y2）

现在你的SQL语句中使用这些点排除行那绝对比10U更从给定的点，它可以在纬度和经度值使用索引，所以将数量级比你目前拥有更快。

如

select . . . 
where locations.lat between X1 and X2 
and   locations.Long between y1 and y2;

这个盒子的方法可以返回误报（你可以在那些箱子的角落捡点>从给定的点10U），所以你仍然需要计算每一个点的距离。然而，这一次会快很多，因为你已经严重地限制了点的数量来测试到框内点。

我把这种方法“的盒子里思考” :)

编辑：是否可以将其放在一个SQL语句？

我不知道MySQL或PHP是能够，对不起。我不知道在哪里的最好的地方是建立四个点，或他们如何被传递到在PHP MySQL查询。然而，一旦你有四点，没有什么能阻止你与我结合自己的SQL语句。

select name, 
       ( 3959 * acos( cos( radians(42.290763) ) 
              * cos( radians( locations.lat ) ) 
              * cos( radians( locations.lng ) - radians(-71.35368) ) 
              + sin( radians(42.290763) ) 
              * sin( radians( locations.lat ) ) ) ) AS distance 
from locations 
where active = 1 
and locations.lat between X1 and X2 
and locations.Long between y1 and y2
having distance < 10 ORDER BY distance;

我知道有MS SQL我可以建立声明四个浮点（X1，Y1，X2，Y2）和“主” SELECT语句前计算他们，就像我说的，我不知道，如果这是可以做到的SQL语句MySQL的。不过，我还是会倾向于建立在C＃中的4分，它们作为参数传递给SQL查询。

对不起，我不能更多的帮助，如果任何人都可以回答这个MySQL的和PHP特定部分，随意编辑这个答案这样做。

Answer 3:

勾选此演示了一个很好的答案。基本上，它显示了在评论中所示的两种不同的方法，对为什么/什么时候应该使用一个或另一个，为什么“盒子”的计算是非常有趣的一个详细的解释。

地理距离搜索与MySQL

Answer 4:

下面的MySQL功能被张贴在此博客文章。我没有测试过很多，但是从我从帖子云集，如果您的纬度和经度字段建立索引，这可能会为你工作得好：

DELIMITER $$

DROP FUNCTION IF EXISTS `get_distance_in_miles_between_geo_locations` $$
CREATE FUNCTION get_distance_in_miles_between_geo_locations(
  geo1_latitude decimal(10,6), geo1_longitude decimal(10,6), 
  geo2_latitude decimal(10,6), geo2_longitude decimal(10,6)) 
returns decimal(10,3) DETERMINISTIC
BEGIN
  return ((ACOS(SIN(geo1_latitude * PI() / 180) * SIN(geo2_latitude * PI() / 180) 
    + COS(geo1_latitude * PI() / 180) * COS(geo2_latitude * PI() / 180) 
    * COS((geo1_longitude - geo2_longitude) * PI() / 180)) * 180 / PI()) 
    * 60 * 1.1515);
END $$

DELIMITER ;

用法示例：

假设一台叫places与领域的latitude和longitude ：

 SELECT get_distance_in_miles_between_geo_locations(-34.017330, 22.809500, latitude, longitude) AS distance_from_input FROM places;

Answer 5:

SELECT * FROM (SELECT *,(((acos(sin((43.6980168*pi()/180)) * 
sin((latitude*pi()/180))+cos((43.6980168*pi()/180)) * 
cos((latitude*pi()/180)) * cos(((7.266903899999988- longitude)* 
pi()/180))))*180/pi())*60*1.1515 ) as distance 
FROM wp_users WHERE 1 GROUP BY ID limit 0,10) as X 
ORDER BY ID DESC

这是在MySQL点之间的距离计算的查询，我已经在很长的数据库中使用它，它，它的工作完美！注意：做的修改（数据库名，表名，列等）按您的要求。

Answer 6:

set @latitude=53.754842;
set @longitude=-2.708077;
set @radius=20;

set @lng_min = @longitude - @radius/abs(cos(radians(@latitude))*69);
set @lng_max = @longitude + @radius/abs(cos(radians(@latitude))*69);
set @lat_min = @latitude - (@radius/69);
set @lat_max = @latitude + (@radius/69);

SELECT * FROM postcode
WHERE (longitude BETWEEN @lng_min AND @lng_max)
AND (latitude BETWEEN @lat_min and @lat_max);

资源

Answer 7:

   select
   (((acos(sin(('$latitude'*pi()/180)) * sin((`lat`*pi()/180))+cos(('$latitude'*pi()/180)) 
    * cos((`lat`*pi()/180)) * cos((('$longitude'- `lng`)*pi()/180))))*180/pi())*60*1.1515) 
    AS distance
    from table having distance<22;

Answer 8:

如果你正在使用MySQL 5.7。*，那么你可以使用st_distance_sphere（点，点）。

Select st_distance_sphere(POINT(-2.997065, 53.404146 ), POINT(58.615349, 23.56676 ))/1000  as distcance

Answer 9:

有关如何安装像MySQL插件的详细信息的完整代码在这里： https://github.com/lucasepe/lib_mysqludf_haversine

我这个去年张贴评论。由于好心@TylerCollier建议我张贴的答案，在这里。

另一种方法是编写从两点返回半正矢距离自定义UDF功能。该功能可以在输入：

lat1 (real), lng1 (real), lat2 (real), lng2 (real), type (string - optinal - 'km', 'ft', 'mi')

因此，我们可以写这样的事情：

SELECT id, name FROM MY_PLACES WHERE haversine_distance(lat1, lng1, lat2, lng2) < 40;

与小于40公里的距离获取的所有记录。要么：

SELECT id, name FROM MY_PLACES WHERE haversine_distance(lat1, lng1, lat2, lng2, 'ft') < 25;

以更少随后的25英尺的距离读取所有记录。

核心功能是：

double
haversine_distance( UDF_INIT* initid, UDF_ARGS* args, char* is_null, char *error ) {
    double result = *(double*) initid->ptr;
    /*Earth Radius in Kilometers.*/ 
    double R = 6372.797560856;
    double DEG_TO_RAD = M_PI/180.0;
    double RAD_TO_DEG = 180.0/M_PI;
    double lat1 = *(double*) args->args[0];
    double lon1 = *(double*) args->args[1];
    double lat2 = *(double*) args->args[2];
    double lon2 = *(double*) args->args[3];
    double dlon = (lon2 - lon1) * DEG_TO_RAD;
    double dlat = (lat2 - lat1) * DEG_TO_RAD;
    double a = pow(sin(dlat * 0.5),2) + 
        cos(lat1*DEG_TO_RAD) * cos(lat2*DEG_TO_RAD) * pow(sin(dlon * 0.5),2);
    double c = 2.0 * atan2(sqrt(a), sqrt(1-a));
    result = ( R * c );
    /*
     * If we have a 5th distance type argument...
     */
    if (args->arg_count == 5) {
        str_to_lowercase(args->args[4]);
        if (strcmp(args->args[4], "ft") == 0) result *= 3280.8399;
        if (strcmp(args->args[4], "mi") == 0) result *= 0.621371192;
    }

    return result;
}

Answer 10:

一个MySQL函数返回的两个坐标之间米数：

CREATE FUNCTION DISTANCE_BETWEEN (lat1 DOUBLE, lon1 DOUBLE, lat2 DOUBLE, lon2 DOUBLE)
RETURNS DOUBLE DETERMINISTIC
RETURN ACOS( SIN(lat1*PI()/180)*SIN(lat2*PI()/180) + COS(lat1*PI()/180)*COS(lat2*PI()/180)*COS(lon2*PI()/180-lon1*PI()/180) ) * 6371000

要以不同的格式返回值，更换6371000与地球在您选择单位的半径的功能。例如，公里是6371哩是3959 。

要使用该功能，只是把它作为你会在MySQL的其它任何功能。举例来说，如果你有一个表city ，你会发现每一个城市的每一个城市之间的距离：

SELECT
    `city1`.`name`,
    `city2`.`name`,
    ROUND(DISTANCE_BETWEEN(`city1`.`latitude`, `city1`.`longitude`, `city2`.`latitude`, `city2`.`longitude`)) AS `distance`
FROM
    `city` AS `city1`
JOIN
    `city` AS `city2`

Answer 11:

我需要解决类似的问题，并结合答案和注释原来的问题（由单点距离过滤行），我想出了解决方案，完美的作品对我来说两个MySQL的5.6和5.7。

SELECT 
    *,
    (6371 * ACOS(COS(RADIANS(56.946285)) * COS(RADIANS(Y(coordinates))) 
    * COS(RADIANS(X(coordinates)) - RADIANS(24.105078)) + SIN(RADIANS(56.946285))
    * SIN(RADIANS(Y(coordinates))))) AS distance
FROM places
WHERE MBRContains
    (
    LineString
        (
        Point (
            24.105078 + 15 / (111.320 * COS(RADIANS(56.946285))),
            56.946285 + 15 / 111.133
        ),
        Point (
            24.105078 - 15 / (111.320 * COS(RADIANS(56.946285))),
            56.946285 - 15 / 111.133
        )
    ),
    coordinates
    )
HAVING distance < 15
ORDER By distance

coordinates是用类型字段POINT并且具有SPATIAL索引
6371是用于计算公里距离
56.946285是纬度为中心点
24.105078是经度中心点
10是在公里的最大距离

在我的测试中，MySQL使用空间索引的coordinates场快速选择它们矩形内的所有行，然后计算所有过滤地排除矩形角落的地方，离开圈子内唯一的地方，实际距离。

这是我的结果的可视化：

灰色星星可视化地图上的所有点，黄星是由MySQL查询返回的。内部矩形（但外圆圈）的角灰色分被选择MBRContains()然后通过取消选择HAVING子句。

Answer 12:

一种快速，简便，准确（用于较小的距离）近似可以用做球面投影。至少在我的路由算法我得到提振20％，较正确的计算。在Java代码中，它看起来像：

public double approxDistKm(double fromLat, double fromLon, double toLat, double toLon) {
    double dLat = Math.toRadians(toLat - fromLat);
    double dLon = Math.toRadians(toLon - fromLon);
    double tmp = Math.cos(Math.toRadians((fromLat + toLat) / 2)) * dLon;
    double d = dLat * dLat + tmp * tmp;
    return R * Math.sqrt(d);
}

不知道关于MySQL（对不起！）。

要确保你知道的限制（的assertEquals的第三PARAM意味着公里的准确度）：

    float lat = 24.235f;
    float lon = 47.234f;
    CalcDistance dist = new CalcDistance();
    double res = 15.051;
    assertEquals(res, dist.calcDistKm(lat, lon, lat - 0.1, lon + 0.1), 1e-3);
    assertEquals(res, dist.approxDistKm(lat, lon, lat - 0.1, lon + 0.1), 1e-3);

    res = 150.748;
    assertEquals(res, dist.calcDistKm(lat, lon, lat - 1, lon + 1), 1e-3);
    assertEquals(res, dist.approxDistKm(lat, lon, lat - 1, lon + 1), 1e-2);

    res = 1527.919;
    assertEquals(res, dist.calcDistKm(lat, lon, lat - 10, lon + 10), 1e-3);
    assertEquals(res, dist.approxDistKm(lat, lon, lat - 10, lon + 10), 10);

Answer 13:

这里是地球距离搜索的一个很详细的说明与MySQL基础上实现haversine公式的到MySQL的解决方案。完整的解决方案的描述与理论，实施和进一步的性能优化。虽然空间优化部分并没有我的情况下正常运行的。 http://www.scribd.com/doc/2569355/Geo-Distance-Search-with-MySQL

Answer 14:

有一个读地理距离搜索与MySQL的基础上，实现haversine公式到MySQL的解决方案。这是一个完整的解决方案的描述与理论，实施和进一步的性能优化。虽然空间优化部分并没有我的情况下正常工作。

我注意到在这两个错误：

采用abs在P8 select语句。我只是省略了abs和它的工作。
在P27空间搜索距离函数不会转换为弧度或乘经度由cos(latitude) ，除非他的空间数据是针对装入该（不能从文章的上下文中告诉），但他对p26的例子表明，他的空间数据POINT不加载弧度或学位。

Answer 15:

$objectQuery = "SELECT table_master.*, ((acos(sin((" . $latitude . "*pi()/180)) * sin((`latitude`*pi()/180))+cos((" . $latitude . "*pi()/180)) * cos((`latitude`*pi()/180)) * cos(((" . $longitude . "- `longtude`)* pi()/180))))*180/pi())*60*1.1515  as distance FROM `table_post_broadcasts` JOIN table_master ON table_post_broadcasts.master_id = table_master.id WHERE table_master.type_of_post ='type' HAVING distance <='" . $Radius . "' ORDER BY distance asc";

Answer 16:

用mysql

SET @orig_lon = 1.027125;
SET @dest_lon = 1.027125;

SET @orig_lat = 2.398441;
SET @dest_lat = 2.398441;

SET @kmormiles = 6371;-- for distance in miles set to : 3956

SELECT @kmormiles * ACOS(LEAST(COS(RADIANS(@orig_lat)) * 
 COS(RADIANS(@dest_lat)) * COS(RADIANS(@orig_lon - @dest_lon)) + 
 SIN(RADIANS(@orig_lat)) * SIN(RADIANS(@dest_lat)),1.0)) as distance;

请参阅： https://andrew.hedges.name/experiments/haversine/

请参阅： https://stackoverflow.com/a/24372831/5155484

请参阅： http://www.plumislandmedia.net/mysql/haversine-mysql-nearest-loc/

注： LEAST被用来避免空值作为一个评论所说的https://stackoverflow.com/a/24372831/5155484

文章来源: Fastest Way to Find Distance Between Two Lat/Long Points

标签： mysql location gis

神经病院院长

女 | 书童

私信

收藏的人(0)

Ta的文章更多文章

0条评论

还没有人评论过~