当前位置： 文江博客话题详情

查找两个纬度/经度点之间距离的最快方法

发布于 2024-07-24 00:08:18 字数 617 浏览 4 评论 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。

原文

I currently have just under a million locations in a mysql database all with longitude and latitude information.

I am trying to find the distance between one point and many other points via a query. It's not as fast as I want it to be especially with 100+ hits a second.

Is there a faster query or possibly a faster system other than mysql for this? I'm using this query:

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;

Note: The provided distance is in Miles. If you need Kilometers, use 6371 instead of 3959.

如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

扫码二维码加入Web技术交流群

发布评论

需要 登录 才能够评论， 你可以免费 注册 一个本站的账号。

评论（16）

书间行客 2024-07-31 00:08:19

  • 使用 MyISAM 表中 Geometry 数据类型的 Point 值创建点。 从 Mysql 5.7.5 开始，InnoDB 表现在也支持 SPATIAL 索引。

  • 创建一个 SPATIAL这些点的索引

  • 使用 MBRContains() 查找值：

    <前><代码>选择*
    从表
    WHERE MBRContains(LineFromText(CONCAT(
    '('
    , @lon + 10 / ( 111.1 / cos(弧度(@lat)))
    , ''
    ，@lat + 10 / 111.1
    , ','
    , @lon - 10 / ( 111.1 / cos(弧度(@lat)))
    , ''
    ，@lat - 10 / 111.1
    , ')')
    ，我的观点）

、 或，在 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 公里，@lon +/- 10km)。

这实际上不是一个盒子，而是一个球形矩形：球体的纬度和经度边界部分。 这可能与弗朗茨约瑟夫地上的普通矩形不同，但在大多数有人居住的地方非常接近。

  • 应用额外的过滤来选择圆圈内的所有内容（不是正方形）

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

  • Create your points using Point values of Geometry data types in MyISAM table. As of Mysql 5.7.5, InnoDB tables now also support SPATIAL indices.

  • Create a SPATIAL index on these points

  • Use MBRContains() to find the values:

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

, or, in MySQL 5.1 and above:

    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
                    )

This will select all points approximately within the box (@lat +/- 10 km, @lon +/- 10km).

This actually is not a box, but a spherical rectangle: latitude and longitude bound segment of the sphere. This may differ from a plain rectangle on the Franz Joseph Land, but quite close to it on most inhabited places.

  • Apply additional filtering to select everything inside the circle (not the square)

  • Possibly apply additional fine filtering to account for the big circle distance (for large distances)

回复 原文
酒解孤独 2024-07-31 00:08:19

不是 MySql 特定的答案，但它会提高 sql 语句的性能。

您实际上所做的是计算到表中每个点的距离，看看它是否在给定点的 10 个单位之内。

在运行此 SQL 之前，您可以做的是创建四个点，在一边绘制一个 20 个单位的盒子，您的点位于中心，即。 (x1,y1) 。 。 。 (x4, y4)，其中 (x1,y1) 是 (givenlong + 10 单位，givenLat + 10 单位) 。 。 。 （给定长 - 10 个单位，给定纬度 -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，我可以构建一个 SQL 语句，声明四个浮点数（X1、Y1、X2、Y2）并在“主”选择语句之前计算它们，就像我说的，我不知道这是否可以用MySql。 不过，我仍然倾向于在 C# 中构建这四个点并将它们作为参数传递给 SQL 查询。

抱歉，如果有人能回答 MySQL & 问题，我无法提供更多帮助。 PHP 的具体部分，请随意编辑此答案。

Not a MySql specific answer, but it'll improve the performance of your sql statement.

What you're effectively doing is calculating the distance to every point in the table, to see if it's within 10 units of a given point.

What you can do before you run this sql, is create four points that draw a box 20 units on a side, with your point in the center i.e.. (x1,y1 ) . . . (x4, y4), where (x1,y1) is (givenlong + 10 units, givenLat + 10units) . . . (givenLong - 10units, givenLat -10 units).
Actually, you only need two points, top left and bottom right call them (X1, Y1) and (X2, Y2)

Now your SQL statement use these points to exclude rows that definitely are more than 10u from your given point, it can use indexes on the latitudes & longitudes, so will be orders of magnitude faster than what you currently have.

e.g.

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

The box approach can return false positives (you can pick up points in the corners of the box that are > 10u from the given point), so you still need to calculate the distance of each point. However this again will be much faster because you have drastically limited the number of points to test to the points within the box.

I call this technique "Thinking inside the box" :)

EDIT: Can this be put into one SQL statement?

I have no idea what mySql or Php is capable of, sorry.
I don't know where the best place is to build the four points, or how they could be passed to a mySql query in Php. However, once you have the four points, there's nothing stopping you combining your own SQL statement with mine.

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;

I know with MS SQL I can build a SQL statement that declares four floats (X1, Y1, X2, Y2) and calculates them before the "main" select statement, like I said, I've no idea if this can be done with MySql. However I'd still be inclined to build the four points in C# and pass them as parameters to the SQL query.

Sorry I can't be more help, if anyone can answer the MySQL & Php specific portions of this, feel free to edit this answer to do so.

回复 原文
静水深流 2024-07-31 00:08:19

我需要解决类似的问题（按距单点的距离过滤行），并通过将原始问题与答案和评论相结合，我想出了在 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

坐标是类型为POINT的字段，并具有SPATIAL索引
6371 用于计算距离（以公里为单位）
56.946285 是中心点的纬度
24.105078 是中心点的经度
15是以公里为单位的最大距离

在我的测试中，MySQL使用坐标字段上的SPATIAL索引来快速选择矩形内的所有行，然后计算所有过滤位置的实际距离排除矩形角的位置，只保留圆内的位置。

这是我的结果的可视化：

map

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

I needed to solve similar problem (filtering rows by distance from single point) and by combining original question with answers and comments, I came up with solution which perfectly works for me on both MySQL 5.6 and 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 is field with type POINT and has SPATIAL index
6371 is for calculating distance in kilometres
56.946285 is latitude for central point
24.105078 is longitude for central point
15 is maximum distance in kilometers

In my tests, MySQL uses SPATIAL index on coordinates field to quickly select all rows which are within rectangle and then calculates actual distance for all filtered places to exclude places from rectangles corners and leave only places inside circle.

This is visualisation of my result:

map

Gray stars visualise all points on map, yellow stars are ones returned by MySQL query. Gray stars inside corners of rectangle (but outside circle) were selected by MBRContains() and then deselected by HAVING clause.

回复 原文
森林散布 2024-07-31 00:08:19

以下 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 & 经度：

选择 get_distance_in_miles_ Between_geo_locations(-34.017330, 22.809500, 
  纬度、经度) AS distance_from_input FROM 地点；

The following MySQL function was posted on this blog post. I haven't tested it much, but from what I gathered from the post, if your latitude and longitude fields are indexed, this may work well for you:

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 ;

Sample usage:

Assuming a table called places with fields latitude & longitude:

SELECT get_distance_in_miles_between_geo_locations(-34.017330, 22.809500,
latitude, longitude) AS distance_from_input FROM places;
回复 原文
二智少女 2024-07-31 00:08:19

如果您使用 MySQL 5.7.*，则可以使用 st_distance_sphere(POINT, POINT)。

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

if you are using MySQL 5.7.*, then you can use st_distance_sphere(POINT, POINT).

Select st_distance_sphere(POINT(-2.997065, 53.404146 ), POINT(58.615349, 23.56676 ))/1000  as distcance
回复 原文
心碎的声音 2024-07-31 00:08:19
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中点之间的距离计算查询，我已经在一个很长的数据库中使用了它，它工作完美！ 注意：根据您的要求进行更改（数据库名称、表名称、列等）。

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

This is the distance calculation query between to points in MySQL, I have used it in a long database, it it working perfect! Note: do the changes (database name, table name, column etc) as per your requirements.

回复 原文
南城旧梦 2024-07-31 00:08:19
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);

来源

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

回复 原文
×纯※雪 2024-07-31 00:08:19
   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;

   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;
回复 原文
月棠 2024-07-31 00:08:19

返回两个坐标之间的米数的 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`

A MySQL function which returns the number of metres between the two coordinates:

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

To return the value in a different format, replace the 6371000 in the function with the radius of Earth in your choice of unit. For example, kilometres would be 6371 and miles would be 3959.

To use the function, just call it as you would any other function in MySQL. For example, if you had a table city, you could find the distance between every city to every other 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`
回复 原文
朦胧时间 2024-07-31 00:08:19

有关如何安装为 MySQL 插件的详细信息的完整代码如下： https://github.com/lucasepe/lib_mysqludf_haversine< /a>

我去年发布了这个作为评论。 由于@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;
}

The full code with details about how to install as MySQL plugin are here: https://github.com/lucasepe/lib_mysqludf_haversine

I posted this last year as comment. Since kindly @TylerCollier suggested me to post as answer, here it is.

Another way is to write a custom UDF function that returns the haversine distance from two points. This function can take in input:

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

So we can write something like this:

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

to fetch all records with a distance less then 40 kilometers. Or:

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

to fetch all records with a distance less then 25 feet.

The core function is:

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;
}
回复 原文
梦初启 2024-07-31 00:08:19

可以使用球形投影进行快速、简单且准确（对于较小距离）的近似。 至少在我的路由算法中，与正确的计算相比，我得到了 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 的第三个参数表示以公里为单位的精度）：

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

A fast, simple and accurate (for smaller distances) approximation can be done with a spherical projection. At least in my routing algorithm I get a 20% boost compared to the correct calculation. In Java code it looks like:

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

Not sure about MySQL (sorry!).

Be sure you know about the limitation (the third param of assertEquals means the accuracy in kilometers):

    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);
回复 原文
笑着哭最痛 2024-07-31 00:08:19

这是使用 MySQL 进行地理距离搜索的非常详细的描述，这是基于将半正矢公式实现到 mysql 的解决方案。 完整的解决方案描述，包括理论、实现和进一步的性能优化。 尽管空间优化部分在我的情况下无法正常工作。
http://www.scribd.com/doc/2569355/Geo -使用 MySQL 进行距离搜索

Here is a very detailed description of Geo Distance Search with MySQL a solution based on implementation of Haversine Formula to mysql. The complete solution description with theory, implementation and further performance optimization. Although the spatial optimization part didn't work correct in my case.
http://www.scribd.com/doc/2569355/Geo-Distance-Search-with-MySQL

回复 原文
指尖上的星空 2024-07-31 00:08:19

阅读使用 MySQL 进行地理距离搜索，解决方案
基于半正矢公式到 MySQL 的实现。 这是一个完整的解决方案
描述理论、实现和进一步的性能优化。
尽管空间优化部分在我的情况下无法正常工作。

我注意到其中有两个错误：

  1. 在 p8 的 select 语句中使用 abs。 我只是省略了 abs 并且它起作用了。

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

Have a read of Geo Distance Search with MySQL, a solution
based on implementation of Haversine Formula to MySQL. This is a complete solution
description with theory, implementation and further performance optimization.
Although the spatial optimization part didn't work correctly in my case.

I noticed two mistakes in this:

  1. the use of abs in the select statement on p8. I just omitted abs and it worked.

  2. the spatial search distance function on p27 does not convert to radians or multiply longitude by cos(latitude), unless his spatial data is loaded with this in consideration (cannot tell from context of article), but his example on p26 indicates that his spatial data POINT is not loaded with radians or degrees.

回复 原文
笔落惊风雨 2024-07-31 00:08:19
$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";

$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";
回复 原文
触ぅ动初心 2024-07-31 00:08:19

使用 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/haveversine-mysql-nearest-loc/

注意：< code>LEAST 用于避免空值，作为 https://stackoverflow.com/a/24372831/ 上建议的注释5155484

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

See: https://andrew.hedges.name/experiments/haversine/

See: https://stackoverflow.com/a/24372831/5155484

See: http://www.plumislandmedia.net/mysql/haversine-mysql-nearest-loc/

NOTE: LEAST is used to avoid null values as a comment suggested on https://stackoverflow.com/a/24372831/5155484

回复 原文
季末如歌 2024-07-31 00:08:19

我真的很喜欢@Māris Kiseļovs 解决方案，但我喜欢许多其他人可能将 Lat 和 lng 的 POINTS 与他的示例相反。 总的来说，我想我会分享它。 就我而言，我需要找到 end_point 特定半径内的所有 start_points。

我希望这可以帮助别人。

SELECT @LAT := ST_X(end_point), @LNG := ST_Y(end_point) FROM routes  WHERE route_ID = 280;
SELECT 
  *,
  (6371e3 * ACOS(COS(RADIANS(@LAT)) * COS(RADIANS(ST_X(start_point))) 
  * COS(RADIANS(ST_Y(start_point)) - RADIANS(@LNG)) + SIN(RADIANS(@LAT))
  * SIN(RADIANS(ST_X(start_point))))) AS distance 
FROM routes
WHERE MBRContains
 (
  LineString
    (
    Point (
            @LNG + 15 / (111.320 * COS(RADIANS(@LAT))),
            @LAT + 15 / 111.133
    ),
    Point (
    @LNG - 15 / (111.320 * COS(RADIANS(@LAT))),
        @LAT - 15 / 111.133
    )
 ),
 POINT(ST_Y(end_point),ST_X(end_point))
)
HAVING distance < 100
ORDER By distance;

I really liked @Māris Kiseļovs solution, but I like many others may have the Lat and lng's POINTS reversed from his example. In generalising it I though I would share it. In my case I need to find all the start_points that are within a certain radius of an end_point.

I hope this helps someone.

SELECT @LAT := ST_X(end_point), @LNG := ST_Y(end_point) FROM routes  WHERE route_ID = 280;
SELECT 
  *,
  (6371e3 * ACOS(COS(RADIANS(@LAT)) * COS(RADIANS(ST_X(start_point))) 
  * COS(RADIANS(ST_Y(start_point)) - RADIANS(@LNG)) + SIN(RADIANS(@LAT))
  * SIN(RADIANS(ST_X(start_point))))) AS distance 
FROM routes
WHERE MBRContains
 (
  LineString
    (
    Point (
            @LNG + 15 / (111.320 * COS(RADIANS(@LAT))),
            @LAT + 15 / 111.133
    ),
    Point (
    @LNG - 15 / (111.320 * COS(RADIANS(@LAT))),
        @LAT - 15 / 111.133
    )
 ),
 POINT(ST_Y(end_point),ST_X(end_point))
)
HAVING distance < 100
ORDER By distance;
回复 原文
~没有更多了~

关于作者

猫腻

暂无简介

0 文章
0 评论
23 人气
发私信

相关话题

更多

热门标签

操作系统 程序设计 IT运维 Linux系统管理 JavaScript 服务器应用 solaris C/C++ PHP Shell BSD Vue.js aix Oracle Python HTML 系统管理 HTML5 CSS 前端
更多

推荐作者

linfzu01

文章 0 评论 0

§对你不离不弃

文章 0 评论 0

可遇━不可求

文章 0 评论 0

枕梦

文章 0 评论 0

qq_3LFa8Q

文章 0 评论 0

JP

文章 0 评论 0

更多

友情链接

文江博客
    我们使用 Cookies 和其他技术来定制您的体验包括您的登录状态等。通过阅读我们的 隐私政策 了解更多相关信息。 单击 接受 或继续使用网站，即表示您同意使用 Cookies 和您的相关数据。
    原文