현재 경도와 위도 정보가있는 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.
답변
-
표의 데이터 유형
Point값을 사용하여 포인트를 작성하십시오 . Mysql 5.7.5 부터는 이제 테이블도 인덱스 를 지원 합니다.GeometryMyISAMInnoDBSPATIAL -
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)
또는 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).
이것은 실제로 상자가 아니라 구형 사각형입니다 : 구의 위도와 경도 경계 세그먼트. 이것은 프란츠 조셉 랜드 (Franz Joseph Land) 의 평범한 사각형과 다를 수 있지만, 대부분의 거주 지역의 사각형과 매우 비슷합니다.
-
추가 필터링을 적용하여 원 안의 모든 것을 선택하십시오 (사각형 아님)
-
원거리가 큰 경우 (원거리의 경우) 추가 미세 필터링을 적용 할 수 있습니다.
답변
MySql 전용 답변은 아니지만 SQL 문의 성능을 향상시킵니다.
효과적으로하는 것은 테이블의 모든 지점까지의 거리를 계산하여 주어진 지점의 10 단위 내에 있는지 확인하는 것입니다.
이 SQL을 실행하기 전에 할 수있는 일은 포인트가 중앙에있는 측면에 20 단위의 상자를 그리는 4 개의 포인트를 만드는 것입니다. (x1, y1). . . (x4, y4), 여기서 (x1, y1)은 (Lat + 10 단위로 주어진 +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의 기능이 무엇인지 잘 모르겠습니다. 죄송합니다. 가장 좋은 장소가 4 점을 구축하는 방법 또는 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을 사용하면 4 개의 float (X1, Y1, X2, Y2)을 선언하고 “main”select 문 전에 계산하는 SQL 문을 작성할 수 있습니다. MySql. 그러나 나는 여전히 C #에서 4 개의 지점을 작성하고 매개 변수로 SQL 쿼리에 전달하는 경향이 있습니다.
누군가가 MySQL & Php의 특정 부분에 대답 할 수 있다면 더 도움이 될 수 없습니다.이 답변을 자유롭게 편집하십시오.
답변
다음 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필드로 불리는 테이블을 가정 :latitudelongitude
SELECT get_distance_in_miles_between_geo_locations(-34.017330, 22.809500, latitude, longitude) AS distance_from_input FROM places;
답변
비슷한 문제 (단일 지점에서 거리로 행을 필터링)를 해결하고 원래의 질문과 답변 및 의견을 결합하여 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 distancecoordinates유형이있는 필드 POINT이고 SPATIAL색인
6371이 거리 (킬로미터)를 계산
56.946285하기위한 것입니다. 중심점의 위도입니다. 중심점의
24.105078경도
15는 킬로미터입니다
필자의 테스트에서 MySQL은 SPATIAL index on coordinatesfield를 사용하여 직사각형 내에있는 모든 행을 신속하게 선택한 다음 모든 필터링 된 장소에 대한 실제 거리를 계산하여 사각형 모서리에서 장소를 제외하고 원 안에 자리를 남겨 둡니다.
이것은 내 결과의 시각화입니다.
회색 별은지도의 모든 지점을 시각화하고 노란색 별은 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답변
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);