[MySQL] 특정 위,경도를 기준으로 거리/반경 구하기

by 호쌤(이광호) on November 28, 2024

MySQL과 MariaDB는 몇 가지 GIS 관련 함수를 제공한다. 이 중에서 ST_DISTANCE_SPHERE 함수는 두 점 사이의 거리를 구하는데 사용된다. 구의 표면을 이용하여 두 지점 사이의 최단 거리를 계산하고 이를 미터값으로 반환해 준다.

#01. ST_DISTANCE_SPHERE 함수의 사용법

$ ST_DISTANCE_SPHERE(point(lon1, lat1), point(lon2, lat2))

위 구문에서 lon1, lat1,  lon2, lat2는 경도와 위도 값을 나타내다. 이 함수는 두 지점 사이의 거리를 미터 단위로 반환한다.   부산시청(경도: 129.0758, 위도: 35.1800)과 서울시청(경도: 126.9783, 위도: 37.5666) 사이의 거리를 다음과 같이 측정해 볼 수 있다.

$ SELECT ST_DISTANCE_SPHERE(point(129.0758, 35.1800), point(126.9783,37.5666))

아래값을 반환하는데 325,078m 즉 최단거리 기준 325km 정도 나온다.

+-----------------------------------------------------------------------+
| ST_DISTANCE_SPHERE(point(129.0758, 35.1800), point(126.9783,37.5666)) |
+-----------------------------------------------------------------------+
|                                                      325077.798889716 |
+-----------------------------------------------------------------------+

#02. 사용 예시

1. 예시 테이블

서울시의 버스정류소에 대한 경도와 위도를 저장하고 있는 테이블을 다음과 같이 만든다.

CREATE TABLE `bus_stations` (
    `NODE_ID` INT(11) NOT NULL PRIMARY KEY AUTO_INCREMENT,
    `ARS_ID` CHAR(5) NOT NULL,
    `정류소명` VARCHAR(255) NOT NULL,
    `경도` DOUBLE NOT NULL,
    `위도` DOUBLE NOT NULL,
    `정류소타입` VARCHAR(20) NOT NULL,
) ENGINE=InnoDB DEFAULT CHARSET utf8 COMMENT='버스정류소'

데이터는 다음과 같이 들어 있다.

+-----------+--------+------------+----------------+---------------+------------+
| NODE_ID   | ARS_ID | 정류소명   | 경도           | 위도          | 정류소타입 |
+-----------+--------+------------+----------------+---------------+------------+
| 100000006 | 01101  | 경기상고   | 126.9693634554 | 37.5875502159 | 일반차로   |
| 100000007 | 01102  | 신교동     | 126.9702626865 | 37.5832955233 | 일반차로   |
| 100000008 | 01103  | 경기상고   | 126.9697701102 | 37.5871907861 | 일반차로   |
| 100000009 | 01104  | 경복고교   | 126.9708802468 | 37.5851600316 | 일반차로   |
| 100000010 | 01105  | 청운중학교 | 126.9726741314 |  37.587810579 | 일반차로   |
+-----------+--------+------------+----------------+---------------+------------+

2. 현재 위치로부터 버스 정류소 조회하기

아래와 같이 처리하면 현재 내 위치(127.0284056, 37.4995448)를 기준으로 500미터 이내의 버스 정류소를 구할 수 있다.

SELECT
    NODE_ID, ARS_ID,  정류소명,  정류소타입, 경도,  위도,
    ST_DISTANCE_SPHERE(POINT(127.0284056, 37.4995448), POINT(경도, 위도)) AS 거리
FROM bus_stations
WHERE ST_DISTANCE_SPHERE(POINT(127.0284056, 37.4995448), POINT(경도, 위도)) <= 500
ORDER BY 거리 ASC;

#03. 이번 프로젝트에서의 활용

서울시의 아파트 목록을 저장하고 있는 apartments 테이블과 버스 정류소 목록을 저장하고 있는 bus_stations 테이블이 있었다. 각각의 데이터는 공공데이터로 부터 수집하였으며 수집된 데이터를 전처리하고 있었다.

apartments 테이블에 인근버스정류장수라는 컬럼을 추가하고 이 컬럼에 각각의 아파트를 기준으로 인근 500m 이내의 버스정류소가 몇 개인지 업데이트를 해야 했다.

그래서 다음과 같이 SQL문을 수행하여 완료 할 수 있었다.

UPDATE apartments a SET 인근지하철역수 = (
    SELECT COUNT(*) FROM bus_stations b
    WHERE ST_DISTANCE_SPHERE(POINT(a.경도, a.위도),  POINT(b.경도, b.위도)) <= 500
);