DAY 20 / [solvesql] 연습문제 1개 풀기

난이도	풀기	문제 이름
난이도 2	0개	x
난이도 3	0개	x
난이도 4	0개	x
난이도 5	1개	- 폐쇄할 따릉이 정류소 찾기 1

 

 

폐쇄할 따릉이 정류소 찾기 1

WITH cross_tab AS ( 
  SELECT s1.station_id AS s1_id
       , s1.name AS s1_name
       , RADIANS(s1.lat) AS s1_lat
       , RADIANS(s1.lng) AS s1_lng
       , s1.updated_at AS s1_at
       , s2.station_id AS s2_id
       , s2.name AS s2_name
       , RADIANS(s2.lat) AS s2_lat
       , RADIANS(s2.lng) AS s2_lng
       , s2.updated_at AS s2_at
  FROM station AS s1
  CROSS JOIN station AS s2
  WHERE s1.station_id != s2.station_id
) -- 1. CROSS JOIN으로 모든 정류소 조합 만들기

SELECT s1_id AS station_id
     , s1_name AS name
FROM (
    SELECT s1_id
         , s1_name
         , SUM(CASE WHEN s1_at < s2_at THEN 1 ELSE 0 END) AS cnt_close
    FROM (
        SELECT *
            , 2*6356*ASIN(SQRT((POW(SIN((s1_lat-s2_lat)/2), 2)) + (COS(s1_lat)*COS(s2_lat)*POW(SIN((s1_lng-s2_lng)/2), 2)))) AS dist
        FROM cross_tab
        ) AS tmp     -- 2. 하버사인 공식으로 구한 거리 컬럼 추가하기
    WHERE dist <= 0.3
    GROUP BY s1_id, s1_name
    ) AS tmp2        -- 3. 300m 이내 정류소만 포함 -> 나중에 업데이트된 정류소 세기
WHERE cnt_close >= 5 -- 4. 나중에 업데이트된 정류소가 5개 이상인 경우만 포함

 

 

[작성 순서]

 

1. CROSS JOIN을 사용하여 station 테이블의 모든 정류소의 조합을 만드는 테이블을 CTE로 생성한다.

-- (1) 뒤에서 하버사인 공식(Haversine formula)으로 정류소간 거리를 구해야 하기 때문에, 위도와 경도는 RADIANS() 함수를 이용하여 degree에서 radian으로 단위를 변경한다. 

-- (2) 같은 정류소 조합은 제외한다.

 

2. 위에서 만든 테이블을 이용하여 하버사인 공식(Haversine formula)으로 거리를 구하는 컬럼을 추가한다. 위도와 경도는 반드시 라디안 단위로 변환하여 계산해야 한다.

-- SIN(), COS(), ASIN(), POW(), SQRT() 함수 사용

 

 

Q. 하버사인 공식이란?
A. 하버사인 공식(Haversine Formula)은 지구와 같은 구형 표면 위에서 두 지점 간의 최단 거리인 대원거리(great-circle distance)를 계산하는 데 사용되는 수학 공식. 이 공식은 위도와 경도 정보를 이용하여 두 지점 사이의 곡률을 고려한 거리를 정확하게 구할 수 있어, 항공 항법, GPS 기반 거리 측정, 지도 서비스 등 다양한 분야에서 활용된다.

하버사인 공식은 지구를 완전한 구형으로 가정하므로, 극지방이나 매우 긴 거리에서는 약간의 오차가 발생할 수 있다는 점을 주의해야 한다. 더 높은 정확도가 필요한 경우, 지구를 타원체로 모델링한 Vincenty 공식 등을 사용할 수도 있다고 한다. 

 

 

3. 300m 이내에 있는 정류소만 포함되도록 필터링한 후, 더 나중에 업데이트된 정류소가 몇 개인지 카운트해 준다.

 

4. 최종적으로 나중에 업데이트된 정류소가 5개 이상인 정류소만 남도록 필터링해준다.

 

 

[참고]

https://limhm4907.tistory.com/148

[MySQL] JOIN