SQL성능관련 0 2 1,254

by 김선우 PostgreSQL [2018.03.01 16:07:36]


안녕하세요

 

두 지점의 위도 경도가 일정거리 이내인가 아닌가를 판단하고자 합니다.

loca_mst 테이블

loca_name      m_latitude     m_longitude      

A0001                32.12345      123.23456

A0002                32.23456      123.34567

B0003               33.01234      122.02345

------

X0500                33.45678     123.23123

 

loca_info 테이블

user_id          latitude           longitude 

A1                 23.12345       234.12345

D1                 23.2345         123.23456

E1                  30.45678       123.53234

 

위와 같이 두개의 테이블에 존재하는 위도 경도 정보에서  반경100m이내에 위치한 정보를 추출해내는 SQL을 작성하고자합니다.

그래서 일단 반경거리를 계산해서 주어진 반경 이내라고 한다면 1을 그렇지 않다면 0을 되돌리는 함수 checkDist를 정의했습니다.

______________________________________________________________

CREATE or REPLACE FUNCTION CheckDist(
  pLat1 numeric(20, 16)
, pLon1 numeric(20, 16)
, pLat2 numeric(20, 17)
, pLon2 numeric(20, 17)
, pDist numeric(22, 17)
) returns numeric(1) as $$
DECLARE
  return_value numeric(1);
  aaa double precision;
  bbb double precision;
  mmm double precision;
  nnn double precision;
  eee double precision;
  esq double precision;
  dx double precision;
  dy double precision;
  myuy double precision;
  www double precision;
  dst double precision;
BEGIN
  aaa := 6378137;
  bbb := 6356752.314245;
  esq := (aaa ^ 2 - bbb ^ 2) / aaa ^ 2;
  eee := sqrt(esq);
  dx := (pLon2 - pLon1) * pi() / 180;
  dy := (pLat2 - pLat1) * pi() / 180;
  myuy := (pLat2 + pLat1) / 2 * pi() / 180;
  www := sqrt(1 - esq * (sin(myuy) ^ 2));
  mmm := aaa * (1 - esq) / (www ^ 3);
  nnn := aaa / www;
  dst := sqrt((dy * mmm) ^ 2 + (dx * nnn * cos(myuy)) ^ 2);
  if dst <= pDist then
    return_value := 1;
  else
    return_value := 0;
  end if;
  return return_value;
END;
$$ language plpgsql;
______________________________________________________________

위와 같이 함수 정의 후에 이제 함수를 통해서 SQL에서 실행을 하는데 다음과 같이 했습니다.

SELECT

  *

FROM

(

  SELECT

    *

    ,CheckDist(a.latitude ,a.longitude ,b.m_atitude ,b.m_longitude ,250) as chk_flg

  FROM

    loca_info a , loca_mst b

) A

WHERE

  chk_flg = 1

그런데 문제는 loca_info에 약 4억건 loca_mst에 500건의 데이타가 있다는 겁니다.

loca_info와 loca_mst에 연결할 만한 키가 없는 상태입니다.

 

좀더 효율적인 방법이 없을까요?

 

by 마농 [2018.03.02 09:09:32]

loca_info 에 (위도, 경도) 인덱스 있어야 하구요.
loca_mst 의 (위도, 경도) 중심으로 상하좌우 100M 확장한 정사각형을 만들어 줍니다.
이 정사각형 범위로 인덱스 조인하세요
조인 후 함수를 이용해 원형 범위로 다시 한번 걸러내세요.
아래 예시 쿼리에서 (- 100M, +100M) 표시는 의미상 그렇다는 거구요.
실제 사용하실 때는 100M 에 해당하는 위도와 경도로 환산해 주셔야 합니다.
참고 : http://gurubee.net/article/63127

SELECT *
  FROM loca_mst  a
     , loca_info b
 WHERE b.m_latitude  BETWEEN a.latitude  - 100M AND a.latitude  + 100M
   AND b.m_longitude BETWEEN a.longitude - 100M AND a.longitude + 100M
   AND CheckDist(a.latitude, a.longitude, b.m_atitude, b.m_longitude, 100) = 1
;

 


by 김선우 [2018.03.02 11:08:32]

마농님 항상 감사합니다.

역시 발상의 전환이 필요하네요 다시 한번 감사드립니다.

댓글등록
SQL문을 포맷에 맞게(깔끔하게) 등록하려면 code() 버튼을 클릭하여 작성 하시면 됩니다.
로그인 사용자만 댓글을 작성 할 수 있습니다. 로그인, 회원가입