我目前在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。


当前回答

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)

,或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 +/- 10km, @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)

,或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 +/- 10km, @lon +/- 10km)。

这实际上不是一个盒子,而是一个球面矩形:纬度和经度绑定的球面段。这可能与弗朗茨约瑟夫土地上的普通矩形不同,但在大多数有人居住的地方都很接近。

应用额外的过滤来选择圆内的所有内容(不是正方形) 可能会应用额外的精细过滤来考虑大的圆距离(对于大的距离)

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

我真的很喜欢@Māris kiseovs的解决方案,但我喜欢许多其他人可能会从他的例子中得到Lat和lng的POINTS。在概括它时,我想我会分享它。在我的情况下,我需要找到所有的开始点,在end_point的一定半径内。

我希望这能帮助到一些人。

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;

我需要解决类似的问题(从单点的距离过滤行),并通过结合原始问题的答案和评论,我想出了解决方案,这对我来说完美的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类型的字段,具有空间索引 6371是用千米来计算距离的 56.946285为中心点纬度 24.105078为中心点经度 15是最大距离,单位为千米

在我的测试中,MySQL使用SPATIAL index on coordinates字段来快速选择矩形内的所有行,然后为所有过滤过的地方计算实际距离,以排除矩形角落中的地方,只留下圆圈内的地方。

这是我的结果的可视化:

灰色星形表示地图上的所有点,黄色星形表示MySQL查询返回的点。矩形(但圆圈外)角内的灰色星星由MBRContains()选择,然后由HAVING子句取消选择。

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