找到两个lat/长点之间距离的最快方法

• 使用创建点 Point 价值观 Geometry 数据类型 MyISAM 表。 As of Mysql 5.7.5, InnoDB tables now also support SPATIAL indices.

• 创建一个 空间的 这些点的索引

• 使用 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) .

这实际上不是一个方框,而是一个球面矩形:球面的经度和纬度界限部分。这可能不同于 法兰士约瑟夫地群岛 但在大多数有人居住的地方离它很近。

• 应用附加过滤来选择圆内的所有内容(而不是正方形)

• 可能应用额外的精细过滤来考虑大圆距离(对于大距离)

不是特定于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语句与我的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)的SQL语句,并在“main”select语句之前计算它们,就像我说的,我不知道这是否可以用MySQL完成。不过,我仍然倾向于在C中构建这四个点,并将它们作为参数传递给SQL查询。

很抱歉,我不能提供更多帮助,如果有人可以回答MySQL&php特定部分,请随意编辑此答案。

请检查此演示文稿以获得正确的答案。 基本上,它显示了注释中显示的两种不同的方法,详细解释了为什么/何时应该使用其中一种方法,以及为什么“在框中”计算可能非常有趣。

Geo Distance Search with MySQL

在 this blog post ,发布了以下mysql函数。我没有做过太多的测试,但是从我从邮件中收集到的信息来看, if your latitude and longitude fields are indexed ,这可能对您很好:

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 with fields latitude&longitude的表:

选择“获取地理位置之间的距离”(-34.017330, 22.809500,纬度、经度),表示与输入点之间的距离;

全部的 snagged from this post

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中到点之间的距离计算查询,我在一个很长的数据库中使用过,它工作得很好!注意:根据您的要求进行更改(数据库名、表名、列等)。

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);

source

如果您使用的是MySQL5.7.*,那么您可以使用 St_距离球(点,点) .

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

   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;

完整的代码以及如何安装为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;
}

快速、简单和准确(对于较小的距离)的近似可以通过 spherical projection . 至少在我的路由算法中,与正确的计算相比,我得到了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(抱歉!).

确保您了解限制(断言的第三个参数等于以公里为单位的精度):

    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);

这里是一个非常详细的描述地理距离搜索与MySQL的解决方案的基础上实现的Haversine公式到MySQL。完整的解决方案描述,包括理论、实现和进一步的性能优化。尽管在我的例子中,空间优化部分没有正确工作。 http://www.scribd.com/doc/2569355/Geo-Distance-Search-with-MySQL

读一读 Geo Distance Search with MySQL 一个解决方案 基于对MySQL的Haversine公式实现。这是一个完整的解决方案 描述与理论,实现和进一步的性能优化。 尽管在我的例子中,空间优化部分没有正确工作。

我注意到这其中有两个错误:

1. 使用 abs 在第8页的select语句中。我只是省略了 防抱死制动系统 它奏效了。

2. p27上的空间搜索距离函数不转换为弧度或经度乘以 cos(latitude) ,除非他的空间数据考虑到了这一点(不能从文章的上下文中看出),但他在p26上的示例表明他的空间数据 POINT 未加载弧度或度数。

一个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`

我需要解决类似的问题(从单个点按距离过滤行),通过将原始问题与答案和评论结合起来,我提出了一个在MySQL5.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在 协调 字段快速选择矩形内的所有行,然后计算所有筛选位置的实际距离,以排除矩形角中的位置,只保留圆内的位置。

这是我的结果的可视化:

灰色的星星将地图上的所有点可视化,黄色的星星是MySQL查询返回的。矩形内角(但外圆)内的灰色星星由 MBRContains() 然后被取消选择 HAVING 条款。

使用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

$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";