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

Question

提问by Ryan Detzel

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

我目前在 mysql 数据库中有不到一百万个位置，所有位置都带有经度和纬度信息。

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.

我试图通过查询找到一个点和许多其他点之间的距离。它没有我想要的那么快，尤其是每秒 100 次以上的点击。

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

是否有比 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;

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

注意：提供的距离以英里为单位。如果您需要公里数，请使用6371代替3959。

Answer 1

采纳答案by Quassnoi

Create your points using Pointvalues of Geometrydata types in MyISAMtable. As of Mysql 5.7.5, InnoDBtables now also support SPATIALindices.
Create a SPATIALindex on these points

Use MBRContains()to find the values:

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)

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

, or, in MySQL 5.1and above:

，或，在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
                    )

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

这将选择框内的所有点(@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.

这实际上不是一个盒子，而是一个球形矩形：球体的经纬度边界段。这可能与Franz Joseph Land上的普通矩形不同，但在大多数有人居住的地方非常接近。

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)

应用额外的过滤来选择圆圈内的所有内容（不是正方形）
可能应用额外的精细过滤来解决大圆距离（大距离）

Answer 2

回答by Binary Worrier

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

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

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.

你有效地做的是计算到表中每个点的距离，看看它是否在给定点的 10 个单位内。

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)

在运行此 sql 之前，您可以做的是创建四个点，在一侧绘制一个 20 个单位的框，您的点位于中心即。(x1,y1 ) 。. . (x4, y4)，其中 (x1,y1) 是 (givenlong + 10 units, givenLat + 10units) 。. . （givenLong - 10 个单位，givenLat -10 个单位）。 实际上，你只需要两个点，左上角和右下角分别称为 (X1, Y1) 和 (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.

现在，您的 SQL 语句使用这些点从给定点排除肯定超过 10u 的行，它可以使用纬度和经度上的索引，因此将比您目前拥有的速度快几个数量级。

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.

盒子方法可能会返回误报（您可以在盒子的角落中拾取距离给定点 > 10u 的点），因此您仍然需要计算每个点的距离。然而，这又会快得多，因为您已经大大限制了要测试的点数到框中的点数。

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

我称这种技术为“在盒子里思考”:)

EDIT:Can this be put into one SQL statement?

编辑：这可以放入一个 SQL 语句中吗？

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.

我不知道 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;

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.

我知道使用 MS SQL 我可以构建一个 SQL 语句，声明四个浮点数（X1、Y1、X2、Y2）并在“主”选择语句之前计算它们，就像我说的，我不知道这是否可以用数据库。但是我仍然倾向于在 C# 中构建四个点并将它们作为参数传递给 SQL 查询。

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.

抱歉，我无法提供更多帮助，如果有人可以回答此问题的 MySQL 和 PHP 特定部分，请随时编辑此答案。

Answer 3

回答by Brad Parks

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:

以下 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 ;

Sample usage:

示例用法：

Assuming a table called placeswith fields latitude& longitude:

假设一个places用字段latitude&调用的表longitude：

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

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

Answer 4

回答by alriyami

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

如果您使用的是 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

Answer 5

回答by Māris Kise?ovs

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.

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

coordinatesis field with type POINTand has SPATIALindex
6371is for calculating distance in kilometres
56.946285is latitude for central point
24.105078is longitude for central point
15is maximum distance in kilometers

coordinates是带类型POINT和SPATIAL索引的字段
6371用于计算以公里
56.946285为单位的距离中心点的纬度中心点
24.105078的经度
15以公里为单位的最大距离

In my tests, MySQL uses SPATIAL index on coordinatesfield 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.

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

This is visualisation of my result:

这是我的结果的可视化：

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 HAVINGclause.

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

Answer 6

回答by Sanni Poriya

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.

这是MySQL中点之间的距离计算查询，我在一个很长的数据库中使用过它，它工作完美！注意：根据您的要求进行更改（数据库名称、表名称、列等）。

Answer 7

回答by Abhigyan

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

来源

Answer 8

回答by user3113927

   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 9

回答by Robert

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

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

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

要以不同的格式返回值，请将6371000函数中的替换为您选择的单位中的地球半径。例如，公里是6371，英里是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:

要使用该函数，只需像在 MySQL 中调用任何其他函数一样调用它。例如，如果您有一个 table 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 10

回答by Luca Sepe

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

有关如何安装为 MySQL 插件的详细信息的完整代码在这里：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.

我去年发布了这个作为评论。由于好心的@TylerCollier 建议我发布答案，所以在这里。

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

另一种方法是编写一个自定义 UDF 函数，该函数返回两点的半正弦距离。这个函数可以接受输入：

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:

获取距离小于 40 公里的所有记录。或者：

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.

获取距离小于 25 英尺的所有记录。

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

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

提问by Ryan Detzel

采纳答案by Quassnoi

回答by Binary Worrier

回答by Brad Parks

Sample usage:

示例用法：

回答by alriyami

回答by Māris Kise?ovs

回答by Sanni Poriya

回答by Abhigyan

回答by user3113927

回答by Robert

回答by Luca Sepe

相关推荐

最近更新

标签

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

提问by Ryan Detzel

采纳答案by Quassnoi

回答by Binary Worrier

回答by Brad Parks

Sample usage:

示例用法：

回答by alriyami

回答by Māris Kise?ovs

回答by Sanni Poriya

回答by Abhigyan

回答by user3113927

回答by Robert

回答by Luca Sepe

相关推荐

MySQL 创建跨不同数据库的视图

未经身份验证的用户在 MYSQL 中是什么意思？

如何重置 MySql 表中的自动增量编号/列

MySQL 如何获得mysql随机整数范围？

相关推荐

最近更新

标签