[GIS] 공간 데이터

관광 데이터를 API로 받다가, DB에 수집해서 사용중인데, 위치 기반 검색(mapx, mapy, dist(반경))을 API로 조회하지 말고, DB에서 조회할 수 있는 방법을 찾고있다.

1. 기존 mapx, mapy 컬럼과 수학적 계산을 사용하면 모든 행에 대해 복잡한 삼각함수 계산을 수행해야 하므로 매우 느리다고 한다. (FULL TABLE SCAN 발생 가능성 높음).

2. MySQL의 공간 확장(Spatial Extensions) 기능 활용
MySQL은 GIS 데이터를 효율적으로 저장하고 쿼리하기 위한 공간 데이터 타입, 공간 함수, 공간 인덱스를 제공하기 때문에 이 기능을 활용하는 것이 성능 면에서 가장 좋을 것 같다.

예시:

단계 1. 공간 데이터 타입 컬럼 추가: content 테이블에 POINT 또는 GEOMETRY 타입의 컬럼을 추가. (예: location)

ALTER TABLE content ADD COLUMN location POINT SRID 4326;
-- SRID 4326은 위도/경도에 사용되는 표준 WGS 84 좌표계를 의미합니다.

 

단계 2.  데이터 변환 및 저장: 기존 mapx, mapy 데이터를 사용하여 새로 추가된 location 컬럼

주의: MySQL의 POINT 함수는 일반적으로 POINT(경도, 위도) 순서로 인자를 받음. mapx가 경도, mapy가 위도라고 가정.

-- 기존 데이터 업데이트 (Null 값 처리 등 필요할 수 있음)
UPDATE content SET location = ST_PointFromText(CONCAT('POINT(', mapx, ' ', mapy, ')'), 4326) WHERE mapx IS NOT NULL AND mapy IS NOT NULL;
-- 또는 직접 POINT 생성자 사용 (MySQL 버전에 따라 함수가 다를 수 있음)
-- UPDATE content SET location = ST_SRID(POINT(mapx, mapy), 4326) WHERE mapx IS NOT NULL AND mapy IS NOT NULL;

-- 새 데이터 삽입 시 location 값도 함께 생성
-- INSERT INTO content (..., mapx, mapy, location) VALUES (..., lon_val, lat_val, ST_SRID(POINT(lon_val, lat_val), 4326));

단계3. 공간 인덱스 생성: location 컬럼에 공간 인덱스(Spatial Index)를 생성하여 공간 쿼리 성능을 최적화

CREATE SPATIAL INDEX idx_content_location ON content(location);

쿼리 예시 (공간 함수 사용).

반경 검색 (Radius Search): 특정 지점으로부터 일정 거리 내의 장소 검색

-- 검색 기준점 및 반경(미터 단위)
SET @center_point = ST_SRID(POINT(126.9780, 37.5665), 4326); -- 경도, 위도 순서
SET @radius_meters = 5000; -- 5km = 5000m

SELECT
    contentid,
    title,
    addr1,
    ST_Distance_Sphere(location, @center_point) AS distance_meters -- 구면 거리 계산 (미터)
FROM
    content
WHERE
    -- 공간 인덱스를 효율적으로 사용하기 위해 MBRContains 등을 먼저 사용하는 것이 더 좋을 수 있음
    -- (MySQL 버전에 따라 최적화 방식 상이)
    -- 하지만 ST_Distance_Sphere 만으로도 공간 인덱스 활용 가능성 있음
    ST_Distance_Sphere(location, @center_point) <= @radius_meters
ORDER BY
    distance_meters;

가장 가까운 장소 검색 (Nearest Neighbor): 특정 지점에서 가장 가까운 N개 장소 검색

SET @center_point = ST_SRID(POINT(126.9780, 37.5665), 4326);
SET @limit_count = 10; -- 가장 가까운 10개 검색

SELECT
    contentid,
    title,
    addr1,
    ST_Distance_Sphere(location, @center_point) AS distance_meters
FROM
    content
ORDER BY
    -- 공간 인덱스가 이 ORDER BY + LIMIT 구문을 최적화할 수 있음
    ST_Distance_Sphere(location, @center_point)
LIMIT @limit_count;

 

 

 

 

 

참고:
- https://kong-dev.tistory.com/245

공간 인덱스로 조회속도 32배 개선하기(요즘 카페 지도 기능 개발)

-