위도/론 두 표 사이의 거리 계산

위도/론 두 표 사이의 거리 계산

아래 두 개의 테이블이 있습니다

도시들

id, lat, lon

산들

id, 명령어, 삭제

SELECT cities.id, 
    (SELECT id FROM mountains 
    WHERE SQRT(POW(69.1 * ( latitude -  cities.lat ) , 2 ) + 
    POW( 69.1 * (cities.lon - longitude ) * 
    COS( latitude / 57.3 ) , 2 ) )<20 LIMIT 1) as mountain_id 
FROM cities

(쿼리는 0.5060초 소요되었습니다).

복잡함을 위해 질의의 일부(예: 주문 기준, 장소)를 삭제했습니다.그러나 실제로 실행 시간에 영향을 미치지는 않습니다.

아래에 설명하겠습니다.

id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY cities ALL NULL NULL NULL NULL 478379
2 DEPENDENT SUBQUERY mountains ALL NULL NULL NULL NULL 15645 Using where

SELECT를 사용하는 것 자체는 문제가 되지 않지만 지정된 결과를 사용하려고 할 때...

id mountain_id 

588437 NULL
588993 4269
589014 4201
589021 4213
589036 4952
589052 7625
589113 9235
589125 NULL
589176 1184
589210 4317

...테이블을 업데이트하려면 모든 것이 매우 느려집니다.제가 아는 건 거의 다 해봤어요종속 서브쿼리가 최적이 아니라는 것은 알지만 어떻게 그것을 없애야 할지 모르겠다.

제 질문을 개선할 수 있는 방법이 있나요?JOIN으로 바꿀까?

두 표 자체는 위도와 경도가 다르며 계산을 사용할 때만 관련이 있습니다.

MariaDB의 공간 거리 검색(km, 마일)은 아직 사용할 수 없는 것 같습니다.

이러한 종류의 연산을 고속화하는 비결은 가능한 모든 위도/론 포인트 쌍에 대해 모든 연산을 수행하지 않도록 하는 것입니다.그러기 위해서는 경계 상자 작업을 통합해야 합니다.

먼저 JOIN을 사용해 보겠습니다.의사 코드에서는 이런 것이 필요하지만, 다른 것들보다 멀리 떨어져 있는 한 몇 쌍을 더 잡아도 상관 없습니다.

    SELECT c.city_id, m.mountain_id
      FROM cities c
      JOIN mountains m ON distance_in_miles(c, m) < 20

그래서 우리는 어떻게 하면 ON 조항을 빠르게 만들 수 있는지 알아내야 합니다. (Woody Guthrie에게 사과하며) 모든 도시와 산을 돌아다니지 말고 색인을 사용하도록 해야 합니다.

ON 절에 대해 이것을 시도해 봅시다.이 도구는 +/- 20마일의 정사각형 경계 상자 내에서 가까운 쌍을 검색합니다.

    SELECT c.city_id, m.mountain_id
      FROM cities c
      JOIN mountains m
                  ON m.lat BETWEEN c.lat - (20.0 / 69.0)
                               AND c.lat + (20.0 / 69.0)
                 AND  m.lon BETWEEN c.lon - (20.0 / (69.0 * COS(RADIANS(c.lat))))
                                AND c.lon + (20.0 / (69.0 * COS(RADIANS(c.lat))))

이 쿼리에서20.0는 비교제한반경입니다.69.0위도당 법령 마일을 정의하는 상수입니다.

그런 다음 복합 인덱스를 추가합니다.(lat, lon, id)두 테이블 모두, 그리고 당신의JOIN작업을 통해 인덱스 범위 검색을 사용하여 쿼리를 보다 효율적으로 수행할 수 있습니다.

마지막으로 이러한 종류의 구를 사용하여 유사 코드로 쿼리를 증강할 수 있습니다.

       ORDER BY  dist_in_miles (c,m) ASC
          LIMIT  1

여기서는 거리 공식을 사용해야 합니다.질문의 데카르트 거리 공식은 극 근처에 있지 않는 한 충분히 잘 작동하는 근사치입니다.대신 훌륭한 원 공식을 사용하는 것이 좋습니다.그것들은 구면 코사인 법칙, 해버사인 또는 빈센티 공식이라고 불립니다.

언급URL : https://stackoverflow.com/questions/48053080/distance-calculation-between-two-tables-of-lat-lon