위치 기반 프로젝트를 준비하며

학교에서 진행하는 캡스톤에서 위치 기반 프로젝트를 진행하게 되어 해당 프로젝트를 위해 준비하였던 것을 이번 글을 통해 공유해보려 합니다.

 

GIS(Geographic Information System)

GIS는 위치 정보를 컴퓨터 데이터로 변환하여 효율적으로 활용하기 위한 정보시스템입니다.

해당 시스템을 지원하는 데이터베이스로 MySQL GIS와 PostgreSQL PostGIS가 존재합니다.

 

MySQL GIS보다 PostgreSQL PostGIS가 보다 더 좋은 성능을 보이고 보다 많은 공간 데이터를 위한 연산 함수를 지원하지만 데이터 베이스 종류로는 MySQL을 선택하였습니다.

해당 프로젝트에서 공간 데이터를 처음 다루며 만날 문제에 대해 고민하고 해결하는 과정에서 아직 학습되지 않은 PostgreSQL은 한계가 있을 것이라 판단하였기 때문입니다.

대신 공간 데이터를 다른 데이터와 독립적으로 구성하여 PostgreSQL에 대한 학습 이후 데이터 베이스 변경에 용이한 코드를 작성해보려 합니다.

 

MySQL GIS

MySQL의 공간 확장에는 크게 아래 세 가지 기능을 제공합니다.

• 공간 데이터를 저장할 수 있는 데이터 타입
• 공간 데이터의 검색을 위한 공간 인덱스(R-Tree 알고리즘)
• 공간 데이터의 연산 함수(거리 또는 포함 관계의 처리)

 

공간 타입

MySQL이 제공하는 공간 타입의 종류는 아래와 같습니다.

• Point: 좌표 공간의 한 지점
• LineString: 다수의 Point를 연결해 주는 선분
• Polygon: 다수의 선분이 연결되어 닫혀있는 상태

 

데이터 타입 중 가장 작은 단위인 Point는 Point (x y) 형태를 가지고 있습니다.

하지만 좌표만으로는 위치를 특정할 수 없고 해당 좌표가 어떤 좌표계의 값인지 알 수 있어야 합니다.

이를 위해 데이터 타입 칼럼을 생성 시 좌표계의 종류를 특정하기 위한 SRID(Spatial Reference IDentifier)를 함께 선언합니다.

이를 선언하지 않는다면 기본으로 0으로 설정되고 해당 프로젝트에서는 WGS84 좌표계의 SRID 값인 4326을 설정해 주었습니다.

추가로 SRID 값이 동일하여야 쿼리 실행 시 인덱스가 적용된다고 합니다.

 

WGS84(SRID 4326)

WGS84 좌표 시스템은 지구를 3차원 공간으로 표현하며, 위도, 경도, 고도의 좌표를 사용하여 지구상의 어떤 지점이든 정확하게 표시할 수 있습니다.

특히 GPS 및 다른 위성 네트워크에서 가장 널리 사용되는 기준으로서, 전 세계적으로 일관성 있게 위치를 정확하게 파악하는 데 사용됩니다.

해당 프로젝트에서 사용하는 외부 API인 SK OpenAPI 그리고 KaKaoMaps 역시 WGS84를 지원하고 있습니다.

 

공간 인덱스

MySQL에서의 공간 인덱스는 R-Tree라는 자료구조를 통해 관리됩니다.

R-Tree는 각 도형의 포함 관계를 이용해 만들어진 인덱스입니다.

루트에서부터 조회하려는 노드가 어떤 노드에 포함되어 있는지 확인 후, 포함하고 있는 노드를 따라 하향하여 검색합니다.

 

 

MySQL은 공간을 MBR(최소 경계 사각형)을 단위로 다루며 첫 번째 사진에서 각 타입에서 MBR이 어떻게 적용되는지 확인할 수 있습니다.

두 번째 사진에서는 MBR 단위의 공간 데이터를 R-Tree 정렬하고 있는 것을 나타내었습니다.

 

공간 데이터 연산 함수

MySQL GIS에서는 많지는 않지만 연산 함수를 제공합니다.

연산 함수를 사용할 때는 해당 함수가 공간 데이터의 포함관계를 활용하는지 생각해 보면 조금 더 좋은 쿼리를 작성할 수 있습니다.

 

일정 거리 안의 데이터 조회 쿼리 (테스트 데이터: 1,000,000건)

# ST_DISTANCE_SPHERE 활용
SELECT *
FROM point_tb
WHERE ST_DISTANCE_SPHERE(@point, point_value) <= 500
ORDER BY id;

# ST_CONTAINS 활용
# @round는 @point를 ST_BUFFER을 활용하여 늘린 객체
SELECT *
FROM point_tb
WHERE ST_CONTAINS(@round, point_value)
ORDER BY id;

첫 번째 쿼리는 전체 데이터를 조회하여 ST_DISTANCE_SPHERE를 수행하고 해당 값이 500 이하인지 확인합니다.

두 번째 쿼리는 전체 데이터 중 @round에 속하는 데이터를 조회합니다.

두 쿼리 중 데이터의 포함관계를 활용하는 쿼리는 두 번째 쿼리이고 이는 실행 계획 분석을 통해서도 확인할 수 있습니다.

 

# 첫 번째 쿼리
-> Filter: (st_distance_sphere(<cache>((@`point`)),point_tb.point_value) <= 500)  (cost=94366.45 rows=932847) (actual time=504.439..7085.949 rows=75 loops=1)
    -> Index scan on point_tb using PRIMARY  (cost=94366.45 rows=932847) (actual time=0.605..468.017 rows=1000000 loops=1)

인덱스를 활용하여 풀 스켄을 하고 있는 것을 볼 수 있습니다.

 

# 두 번째 쿼리
-> Sort: point_tb.id  (cost=0.71 rows=1) (actual time=4.004..4.008 rows=20 loops=1)
    -> Filter: st_contains(<cache>((@round)),point_tb.point_value)  (cost=0.71 rows=1) (actual time=2.064..3.693 rows=20 loops=1)
        -> Index range scan on point_tb using idx_point_tb_point_value  (cost=0.71 rows=1) (actual time=1.690..2.849 rows=20 loops=1)

인덱스를 활용하여 범위를 특정하여 조회하고 있는 것을 확인할 수 있습니다.

 

스프링 부트에서 테스트

쿼리를 작성하는 것으로 테스트를 진행할 수 있지만 프로젝트에서 스프링 부트를 사용할 것이기에 스프링 부트에서 1,000,000건의 데이터가 존재할 때 쿼리 실행 속도를 테스트해보려 합니다.

 

테스트를 위한 준비

의존성

관련 코드 바로가기

// hibernate spatial
implementation 'org.hibernate:hibernate-spatial'

기본적인 JPA 의존성에 위의 hiberate spatial 관련 의존성을 추가합니다.

 

엔티티

관련 코드 바로가기

public class XXXPointEntity {

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;

    @Column(name = "name", nullable = false)
    private String name;

    @Column(name = "point_value", columnDefinition = "POINT SRID 4326", nullable = false)
    private Point pointValue;
  ...
}

public class XXXLatLng {

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;

    @Column(name = "name", nullable = false)
    private String name;

    @Column(name = "lat_value", nullable = false)
    private Double lat;

    @Column(name = "lng_value", nullable = false)
    private Double lng;
  ...
}

XXX는 인덱스 여부를 나타냅니다.

• NonIndexed
• Indexed
  • Point
    • point_value
  • LatLng
    • lat_value
    • lng_value

 

테스트 클래스

테스트 클래스에서는 EntityManager를 활용하여 쿼리를 작성하고 실행합니다.

 

거리 계산

테스트 클래스에서 거리 계산에는 하버사인 공식을 활용합니다.

하버사인 공신이란?

 

하버사인 공식 메서드

public double calculateHaverSineDistance(double lat1, double lng1, double lat2, double lng2) {
    final int R = 6371;
    double latDistance = Math.toRadians(lat2 - lat1);
    double lngDistance = Math.toRadians(lng2 - lng1);
    double a =
          Math.sin(latDistance / 2) * Math.sin(latDistance / 2)
                + Math.cos(Math.toRadians(lat1))
                      * Math.cos(Math.toRadians(lat2))
                      * Math.sin(lngDistance / 2)
                      * Math.sin(lngDistance / 2);

    double c = 2 * Math.atan2(Math.sqrt(a), Math.sqrt(1 - a));
    return R * c * 1000;
}

 

Point 타입 테스트 목록

Point Test 바로가기

1. 포인트 객체로 위치를 조회합니다.
2. 두 포인트 객체의 거리를 계산합니다.
3. 포인트 버퍼 객체를 생성하여 일정 거리 이내의 위치를 조회합니다.
4. 사각형을 만들어 필터링 후 포인트 버퍼 객체를 활용하여 일정 거리 이내의 위치를 조회합니다.
5. 사각형을 만들어 필터링 후 ST_DISTANCE_SPHERE를 활용하여 일정 거리 이내의 위치를 조회합니다.

 

쿼리 분석과 테스트 결과

Query 1

# 포인트 객체로 위치를 조회합니다.
SELECT *
FROM api.point_tb AS pe
WHERE pe.point_value = @point;

-> Filter: (pe.point_value = <cache>((@`point`)))  (cost=94882.84 rows=1) (actual time=39.379..404.631 rows=1 loops=1)
    -> Table scan on pe  (cost=94882.84 rows=932847) (actual time=0.467..358.122 rows=1000000 loops=1)

type: All

테이블 풀 스캔을 통해 포인트 객체를 조회하고 있는 것을 확인할 수 있습니다.

	Indexed
Query1	249ms

 

	NonIndexed
Query1	221ms

 

 

Query 3

# 포인트버퍼객체로 일정 거리 이내의 위치를 조회합니다
EXPLAIN ANALYZE
SELECT *
FROM api.point_tb AS pe
WHERE ST_CONTAINS(@round, pe.point_value);

-> Filter: st_contains(<cache>((@round)),pe.point_value)  (cost=2.01 rows=1) (actual time=2.761..6.562 rows=20 loops=1)
    -> Index range scan on pe using idx_point_tb_point_value  (cost=2.01 rows=1) (actual time=2.228..5.352 rows=20 loops=1)

type: range

포인트버퍼객체를 기준으로 인덱스의 범의를 특정하여 조회하고 있는 것을 확인할 수 있습니다.

 

Indexed	300m	500m	1000m	10000m
Query 3	4ms	3ms	7ms	227ms
전체 조회 개수	20	55	250	13615

 

NonIndexed	300m	500m	1000m	10000m
Query 3	9601ms	9931ms	9690ms	9592ms
전체 조회 개수	20	55	250	13615

 

Query 4

# 사각형을 만들어 필터링 후 버퍼객체를 활용하여 일정 거리 이내의 위치를 조회합니다
EXPLAIN ANALYZE
SELECT *
FROM point_tb
WHERE ST_CONTAINS(@square, point_value)
  AND ST_CONTAINS(@round, point_value);

-> Filter: (st_contains(<cache>((@square)),point_tb.point_value) and st_contains(<cache>((@round)),point_tb.point_value))  (cost=0.95 rows=1) (actual time=1.029..2.193 rows=20 loops=1)
    -> Index range scan on point_tb using idx_point_tb_point_value  (cost=0.95 rows=1) (actual time=0.869..1.580 rows=20 loops=1)

type: range

사각형을 기준으로 인덱스의 범의를 특정하여 필터링하고 버퍼객체에 포함되는 값을 조회하고 있는 것을 확인할 수 있습니다.

 

Indexed	300m	500m	1000m	10000m
Query 4	3ms	4ms	10ms	380ms
전체 조회 개수	20	55	250	13615

 

NonIndexed	300m	500m	1000m	10000m
Query 4	7678ms	7612ms	7662ms	7886ms
전체 조회 개수	20	55	250	13615

 

Query 5

# 사각형을 만들어 필터링 후 ST_DISTANCE_SPHERE를 활용하여 일정 거리 이내의 위치를 조회합니다
EXPLAIN ANALYZE
SELECT *
FROM point_tb
WHERE ST_CONTAINS(@square, point_value)
  AND ST_DISTANCE_SPHERE(@point, point_value) <= 500;

-> Filter: (st_contains(<cache>((@square)),point_tb.point_value) and (st_distance_sphere(<cache>((@`point`)),point_tb.point_value) <= 500))  (cost=0.95 rows=1) (actual time=1.189..3.595 rows=20 loops=1)
    -> Index range scan on point_tb using idx_point_tb_point_value  (cost=0.95 rows=1) (actual time=0.889..1.900 rows=20 loops=1)

type: range

사각형을 기준으로 인덱스의 범의를 특정하여 필터링하고 ST_DISTANCE_SPHERE 함수를 활용해 500 이하의 값을 조회하고 있는 것을 확인할 수 있습니다.

 

Indexed	300m	500m	1000m	10000m
Query 5	2ms	3ms	7ms	305ms
전체 조회 개수	20	75	290	16240

 

NonIndexed	300m	500m	1000m	10000m
Query 5	7621ms	7611ms	7663ms	7802ms
전체 조회 개수	20	75	290	16240

 

전체 결과

쿼리 타입	Indexed	NonIndexed
Query1	249ms	221ms
Query3 300m	4ms	9,601ms
Query3 500m	3ms	9,931ms
Query3 1,000m	7ms	9,690ms
Query3 10,000m	227ms	9,592ms
Query4 300m	3ms	7,678ms
Query4 500m	4ms	7,612ms
Query4 1,000m	10ms	7,662ms
Query4 10,000m	380ms	7,886ms
Query5 300m	2ms	7,621ms
Query5 500m	3ms	7,611ms
Query5 1,000m	7ms	7,663ms
Query5 10,000m	305ms	7,802ms

 

Lat Lng 타입 테스트 목록

LatLng Test 바로가기

1. Lat, Lng로 위치를 조회합니다.
2. Lat, Lng로 일정 거리 이내의 위치를 계산 후 조회합니다.
3. Lat, Lng로 필터링한 값을 테이블로 만들어 조인하고 일정 거리 이내의 위치를 계산 후 조회합니다.

 

쿼리 분석과 테스트 결과

Query 1

# LAT LNG로 위치를 조회합니다
SELECT * FROM lat_lng_tb ll WHERE ll.lat_value=@lat AND ll.lng_value=@lng;

-> Filter: ((ll.lat_value = <cache>((@lat))) and (ll.lng_value = <cache>((@lng))))  (cost=2.11 rows=1) (actual time=1.219..1.227 rows=1 loops=1)
    -> Index range scan on ll using intersect(idx_lat_lng_tb_lng_value,idx_lat_lng_tb_lat_value)  (cost=2.11 rows=1) (actual time=1.211..1.218 rows=1 loops=1)

type: index_merge

인덱스 머지 타입으로 idx_lat_lng_tb_lat_value,idx_lat_lng_tb_lng_value가 병합되어 조회하고 있는 것을 확인할 수 있습니다.

 

	Indexed
Query1	3ms

 

	NonIndexed
Query1	164ms

 

Query 2

# LAT LNG로 일정 거리 이내의 위치를 계산 후 조회합니다
SELECT *
FROM lat_lng_tb ll
WHERE (6371 * acos(cos(radians(@lat)) * cos(radians(ll.lat_value)) * cos(radians(ll.lng_value) - radians(@lng)) +
                   sin(radians(@lat)) * sin(radians(ll.lat_value)))) * 1000 <= @distance
ORDER BY ll.id;

-> Filter: (((6371 * acos((((<cache>(cos(radians((@lat)))) * cos(radians(ll.lat_value))) * cos((radians(ll.lng_value) - <cache>(radians((@lng)))))) + (<cache>(sin(radians((@lat)))) * sin(radians(ll.lat_value)))))) * 1000) <= <cache>((@distance)))  (cost=102261.13 rows=1010492) (actual time=462.460..462.460 rows=0 loops=1)
    -> Table scan on ll  (cost=102261.13 rows=1010492) (actual time=1.041..300.963 rows=1000000 loops=1)

type: All

인덱스가 설정되어 있음에도 하버사인 공식을 수행하여 값에 변경이 일어나 테이블 풀 스캔을 하여 조건에 만족하는 값을 조회하고 있는 것을 확인할 수 있습니다.

 

Indexed	300m	500m	1000m	10000m
Query 2	348ms	301ms	347ms	357ms
전체 조회 개수	30	75	290	17880

 

NonIndexed	300m	500m	1000m	10000m
Query 2	294ms	286ms	297ms	381ms
전체 조회 개수	30	75	290	17880

 

Query 3

# LAT LNG로 필터링한 값을 테이블로 만들어 조인하고 일정 거리 이내의 위치를 계산 후 조회합니다
SELECT *
FROM lat_lng_tb ll
JOIN (SELECT id FROM lat_lng_tb ll WHERE ll.lat_value=@lat AND ll.lng_value=@lng) ill
ON ll.id = ill.id
WHERE (6371 * acos(cos(radians(@lat)) * cos(radians(ll.lat_value)) * cos(radians(ll.lng_value) - radians(@lng)) +
                   sin(radians(@lat)) * sin(radians(ll.lat_value)))) * 1000 <= @distance
ORDER BY ll.id;

-> Nested loop inner join  (cost=0.64 rows=0) (actual time=1.070..1.070 rows=0 loops=1)
    -> Filter: (ll.lat_value = <cache>((@lat)))  (cost=0.62 rows=0) (actual time=0.976..0.979 rows=1 loops=1)
        -> Index lookup on ll using idx_lat_lng_tb_lng_value (lng_value=(@lng))  (cost=0.62 rows=2) (actual time=0.973..0.976 rows=2 loops=1)
    -> Filter: (((6371 * acos((((<cache>(cos(radians((@lat)))) * cos(radians(ll.lat_value))) * cos((radians(ll.lng_value) - <cache>(radians((@lng)))))) + (<cache>(sin(radians((@lat)))) * sin(radians(ll.lat_value)))))) * 1000) <= <cache>((@distance)))  (cost=2.31 rows=1) (actual time=0.061..0.061 rows=0 loops=1)
        -> Single-row index lookup on ll using PRIMARY (id=ll.id)  (cost=2.31 rows=1) (actual time=0.017..0.017 rows=1 loops=1)

type: ref

type: eq_ref

하버사인 공식을 적용할 대상을 좁히고 하버사인 공식을 적용하여 조건에 만족하는 값을 조회합니다.

Query 1을 수행하여 하버사인 공식을 적용할 대상을 좁히기 때문에 Query 2에 비해 월등히 빠른 조회 성능을 확인할 수 있습니다.

 

Indexed	300m	500m	1000m	10000m
Query 3	6ms	10ms	23ms	181ms
전체 조회 개수	30	75	290	17880

 

NonIndexed	300m	500m	1000m	10000m
Query 3	385ms	375ms	383ms	367ms
전체 조회 개수	30	75	290	17880

 

 

전체 결과

쿼리 타입	Indexed	NonIndexed
Query1	3ms	164ms
Query2 300m	348ms	294ms
Query2 500m	301ms	286ms
Query2 1,000m	347ms	297ms
Query2 10,000m	357ms	381ms
Query3 300m	6ms	385ms
Query3 500m	10ms	375ms
Query3 1,000m	23ms	383ms
Query3 10,000m	181ms	367ms

 

결론

테스트 결과 단순히 위도와 경도를 통해 위치를 조회하는 경우에는 위도와 경도를 사용한 쿼리가 더 빠른 조회 성능을 보임을 확인할 수 있었습니다.

하지만 공간의 포함 관계에 관련된 경우에는 공간 타입을 적용한 쿼리가 더 빠른 조회 성능을 보임을 확인할 수 있었습니다.

그리고 MySQL에서 지원하는 ST_CONTAINS, ST_DISTANCE_SPHERE와 같은 공간 데이터 연산 함수에 역시 사용해 볼 수 있었습니다.

 

만약 단순히 조회 용도로만 공간을 사용한다면 위도와 경도를 사용하는 것이 좋은 선택이라 생각합니다.

하지만 공간의 포함 관계를 활용한다면 공간 타입 도입하는 것이 좋은 선택이라 생각합니다.

제가 이번에 진행하는 캡스톤 프로젝트에는 공간의 포함 관계를 활용하는 요구사항이 공간 타입 도입하여 프로젝트를 진행하려 합니다.

 

아쉬운 점

동일한 거리에 대한 쿼리를 실행하였는데 결과가 다르게 나온 부분이 아쉬움으로 남습니다.

Double double_difference = p_distance / (111.32 * 1000);

위의 코드를 통해 미터를 도로 변환하는 과정에서의 오차,

Geometry geometry = point.buffer(double_difference, 2);

혹은 위의 코드를 통해 만든 버퍼 객체에서 만들어지는 오차에 의해 위와 같은 결과가 나왔을 것이라 예상하고 있습니다.

 

PostgreSQL 테스트 결과

쿼리 타입	300m (ms)	500m (ms)	1000m (ms)	10000m (ms)
Query1 (Indexed)	69	X	X	X
Query3 (Indexed)	3	2	3	54
Query4 (Indexed)	2	2	6	34
Query5 (Indexed)	1	2	3	49

 

spgist 타입의 인덱스를 사용하여 동일한 쿼리를 수행한 결과입니다.

MySQL에 비해 압도적인 성능 차이를 확인할 수 있습니다.

PostgreSQL에 익숙하거나 공간 데이터가 중요한 프로젝트에서는 PostgreSQL이 가장 우선될 선택지가 될 것이라 생각합니다.

 

 

---

참고 링크

• JPA와 MySQL로 위치 데이터 다루기
• MySQL GIS vs PostgreSQL PostGIS (feat. GIS, 위치 기반 검색)
• 위치 기반 서비스를 위한 사전 지식
• 공간 인덱스로 조회속도 32배 개선하기(요즘 카페 지도 기능 개발)
• 공간 인덱스 적용기
• Spatial Query Index 최적화(공간 쿼리 인덱스 최적화)
• 공간 데이터 개념부터 적용까지
• [GIS] 좌표 정의 및 변환 방법, 자주쓰는 좌표계