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


当前回答

我真的很喜欢@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;

其他回答

我真的很喜欢@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的一个解决方案的基础上实现的Haversine公式到MySQL。完整的解决方案描述,包括理论、实现和进一步的性能优化。尽管空间优化部分在我的案例中没有正确工作。 http://www.scribd.com/doc/2569355/Geo-Distance-Search-with-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);

下面的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的表,其中包含纬度和经度字段:

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

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