좌표 데이터 저장 및 조회 방법
캡슐 데이터를 지도에서 표시해야합니다. 표시하는 데이터의 기준은, 사용자의 현 위치로부터 특정 반경(1KM)내 저장한 캡슐 데이터입니다.
-
MySQL의 Point 타입으로 좌표 데이터를 저장하고 ST_Contains 메서드를 사용하여 특정 반경 내 데이터를 조회한다.
- 서버의 데이터베이스로 MySQL을 사용중이므로, 좌표 데이터 처리를 위한 다른 기술의 추가적인 비용이 필요 없는 장점이 있습니다.
- 좌표 데이터를 처리하기 위해 특화되진 않았습니다.
-
좌표 데이터용 저장 및 조회하기 위한 PostgreSQL DB를 추가한다.
- 좌표 데이터를 활용한 풍부한 기능을 제공합니다.
- 좌표 데이터용으로 PostgreSQL을 추가하면 그만큼 클라우드 서버 비용이 발생합니다.
-
Redis의 Geospatial 자료구조를 사용한다.
- Redis는 인 메모리 데이터베이스로서 좌표 데이터를 기반으로 빠른 조회가 가능합니다.
- 캡슐에서 좌표 데이터는 중요한 정보로 누락되거나 손실되면 안됩니다.
- 저희는 좌표 데이터를 처리하기 위해 MySQL에서 제공해주는 기술로 해결하고자 합니다. 그 이유는 좌표 데이터용을 위해 PostgreSQL DB을 사용하는 것은 비용에 부담이 컸고, Redis의 Geospatial 자료구조가 빠른 장점이 있지만, 중요한 좌표 데이터가 누락되거나 손실되었을 때의 추가적인 문제가 발생할 수 있기 때문입니다. MySQL에서 제공하는 공간 처리 기술로 충분히 해결 할 수 있다고 판단하였고, PostgreSQL, Redis 사용은 오버엔지니어일 수 있다고 생각됩니다. 추후 캡슐 데이터양이 많아지고, 좌표를 저장 및 조회하는데 적합하지 않다면 그 때 PosgtreSQL과 Redis로 개선하면 좋을 것 같습니다.
캡슐의 위치 저장을 위해 Mysql의 Point 타입을 사용해야 했습니다. Mysql을 데이터베이스로 사용하는 현 상태에서, 내 위치 반경 내의 저장된 캡슐을 조회하는 탐구를 하면서 고려해야 할 부분을 적어보려 합니다.
그럼 Mysql에서 공간 정보를 저장하고, 조회한 방식에 대해 살펴보겠습니다.
SRID 4326 - 지리 좌표계, 위도와 경도로 구성된 좌표계입니다. 또한,값의 단위가 각도이기에 어떤 위치에서 지구 중심에서 각도 또한 고려한다고 볼 수 있습니다.
SRID 3857 - 투영 좌표계, 지구 구체를 평면으로 투영해서 만든 좌표계입니다. 값의 단위는 미터 단위입니다. 값의 단위는 경도와 위도로 구성되어 있습니다.(순서 주의)
Table을 만들 때, 공간 함수를 쓸 때 SRID를 명시해줘야 합니다. 그래야 내가 원하는 정보를 공간 함수를 통해 계산할 수 있습니다. 기본적으로 SRID가 명시되어 있지 않으면 Mysql에서는 SRID 0이 되기에 사용할 SRID를 명시해야 합니다. SRID가 0이면 좌표계마다 추가적인 계산이 필요할 수 있기 때문에 내가 사용하는 좌표계에 대한 원하는 결과를 얻기 위해선 좌표계를 지정해줘야 합니다.
Mysql에서 공간 정보 저장을 위해 Point, Line, Polygon, Geometry들을 사용합니다. 이때 이러한 도형들을 감싸는 최소 사각형이 있는데 이러한 사각형의 크기에 따라 계층적으로 구성한 것이 R-Tree를 사용한 Spatial Index입니다. 이 Spatil Index는 Mysql에서 ST_Cotains(), ST_Within() 등과 같은 함수의 사용에 인덱스를 이용합니다. 함수의 사용법은 다음과 같습니다.
ST_Contains(a, b)는 a가 더 큰 다각형이고 b가 그 다각형 안에 포함되는지를 확인해주는 함수입니다.
인덱스를 걸었을 때와 안 걸었을 때 성능 차이가 엄청납니다. 2초 ~ 3초 정도 걸리던 쿼리가 20밀리초 안쪽으로 줄어들었습니다. Mysql에서 explain을 붙여서 결과를 보면 ALL에서 range(인덱스 사용)로 변한 것을 볼 수 있습니다.
인덱스 있을 때 쿼리 플랜
인덱스 없을 때 쿼리 플랜
인덱스 있을 때 속도
인덱스 제거 후 속도
특정 좌표를 기준으로 반경 3km 원을 둘러싸는 사각형을 구하는 엔드포인트가 존재합니다. 이때 SRID 4326으로 특정 좌표를 기준으로 3km 원을 감싸는 사각형을 계산했을 때 오차가 발생합니다. 지구가 구면체이기에 위도에 따라서 경도 1도에 해당하는 거리가 달라집니다. 그래서 SRID 4326은 사각형을 구하는 방식이 조금 다릅니다. 구면체이기 때문에 위도에 따른 경도의 변화를 고려해서 계산해야 합니다.(Real Mysql 2편 244p 참조) SRID 3857은 손쉽게 사각형을 구할 수 있습니다. SRID 4326의 사각형을 구했을 때 서로 다른 사각형이 구해집니다. 이는 실제 지구에서 곡률과 위도에 따른 경도 변화를 고려하기에 웹, 앱에서 보는 지도에 마커를 찍어보면
SRID 4326
private static final double KM_TO_DEGREE = 1 / 110.574;
private Polygon getDistanceMBR4326(Point p, double distance) {
double x = p.getX();
double y = p.getY();
double v_deltaLon = distance / Math.abs(Math.cos(Math.toRadians(y)) * 111.32);
double v_Lat1 = y - (distance * KM_TO_DEGREE);
double v_Lat2 = y + (distance * KM_TO_DEGREE);
double v_Lon1 = x - v_deltaLon;
double v_Lon2 = x + v_deltaLon;
return geometryFactory4326.createPolygon(new Coordinate[]{
new Coordinate(v_Lon1, v_Lat1),
new Coordinate(v_Lon2, v_Lat1),
new Coordinate(v_Lon2, v_Lat2),
new Coordinate(v_Lon1, v_Lat2),
new Coordinate(v_Lon1, v_Lat1)
});
}
SRID 3857
private Polygon getDistanceMBR3857(Point p, double distance) {
double x = p.getX();
double y = p.getY();
double minX = x - distance * 1000;
double minY = y - distance * 1000;
double maxX = x + distance * 1000;
double maxY = y + distance * 1000;
return geometryFactory3857.createPolygon(new Coordinate[]{
new Coordinate(minX, minY),
new Coordinate(maxX, minY),
new Coordinate(maxX, maxY),
new Coordinate(minX, maxY),
new Coordinate(minX, minY)
});
}
Real Mysql 2에서 테스트한 성능 주의 사항으로는 SRID 4326과 SRID 3857로 100만 번 소요 시간을 측정했을 때 약 3배 정도 SRID 3857이 빠른 성능을 보였다고 합니다. 그래서 직접 100만 개의 데이터를 넣어두고 실험을 해봤습니다. 데이터 생성 코드
drop table if exists location3857;
drop table if exists location4326;
create table location3857
(
id bigint not null auto_increment,
name varchar(255) not null,
location POINT SRID 3857 not null,
primary key (id)
) engine=InnoDB;
create table location4326
(
id bigint not null auto_increment,
name varchar(255) not null,
location POINT SRID 4326 not null,
primary key (id)
) engine=InnoDB;
create spatial index location4326_location_idx on location4326(location);
create spatial index location3857_location_idx on location3857(location);SET @g = 'POLYGON (( 37.52565696761596 127.0203875414764, 37.56839090772926 127.0203875414764, 37.52565696761596 127.07428645852356, 37.56839090772926 127.07428645852356, 37.52565696761596 127.0203875414764))'
explain format=json SELECT id, name, ST_X(location) as x, ST_Y(location) as y
FROM location4326
WHERE ST_Contains(ST_PolygonFromText(@g, 4326), location);
set @g = 'POLYGON ((14139844.861481423 4512632.073033471, 14145844.861481423 4512632.073033471, 14145844.861481423 4518632.073033471, 14139844.861481423 4518632.073033471, 14139844.861481423 4512632.073033471))'
explain format=json SELECT id, name, ST_X(location) as x, ST_Y(location) as y
FROM location3857
WHERE ST_Contains(ST_PolygonFromText(@g, 3857), location)
{
"query_block": {
"select_id": 1,
"cost_info": {
"query_cost": "27.89"
},
"table": {
"table_name": "location3857",
"access_type": "range",
"possible_keys": [
"location3857_location_idx"
],
"key": "location3857_location_idx",
"used_key_parts": [
"location"
],
"key_length": "34",
"rows_examined_per_scan": 40,
"rows_produced_per_join": 40,
"filtered": "100.00",
"cost_info": {
"read_cost": "23.89",
"eval_cost": "4.00",
"prefix_cost": "27.89",
"data_read_per_join": "40K"
},
"used_columns": [
"id",
"name",
"location"
],
"attached_condition": "st_contains(<cache>(st_polygonfromtext((@`g`),3857)),`spatial`.`location3857`.`location`)"
}
}
}{
"query_block": {
"select_id": 1,
"cost_info": {
"query_cost": "50.91"
},
"table": {
"table_name": "location4326",
"access_type": "range",
"possible_keys": [
"location4326_location_idx"
],
"key": "location4326_location_idx",
"used_key_parts": [
"location"
],
"key_length": "34",
"rows_examined_per_scan": 42,
"rows_produced_per_join": 42,
"filtered": "100.00",
"cost_info": {
"read_cost": "46.71",
"eval_cost": "4.20",
"prefix_cost": "50.91",
"data_read_per_join": "42K"
},
"used_columns": [
"id",
"name",
"location"
],
"attached_condition": "st_contains(<cache>(st_polygonfromtext((@`g`),4326)),`spatial`.`location4326`.`location`)"
}
}
}SRID 3857을 이용했을 때는 지도 상에서 정사각형이 되지만 SRID 4326을 사용하면 더 큰 사각형이 만들어지므로 더 많은 레코드가 조회되는 SRID 4326에 비해 SRID 3857을 이용한 쿼리 성능이 좋습니다.
아래와 같이 백만개의 데이터를 삽입할 때는 SRID 3857의 성능이 SRID 4326 보다 약 2배 이상 빠릅니다.
저희는 안드로이드에서 SRID 4326 좌표를 받아서 처리를 해야 합니다. 만약 SRID 3857을 처리해야 한다면 다음과 같은 방법으로 진행해야 합니다.
- 안드로이드로부터 현재 위치의 SRID 4326 좌표와 검색을 원하는 반경을 받는다.
- 해당 좌표로부터 SRID 3857 좌표계로 변환 후 반경 크기의 원을 감싸는 사각형을 구한다.
- Mysql에서 SRID 3857로 ST_Conatins 쿼리를 날려서 사각형 안에 포함되는 요소들을 추출한다.
- 추출한 요소의 좌표들을 다시 SRID 4326 좌표로 바꿔준다.
SRID 4326 -> SRID 3857 -> SRID 4326과 같은 흐름으로 진행해야 했기에 단순히 Mysql에서 두 좌표계만의 성능을 비교할 순 없었습니다. 또한,아무래도 SRID 3857을 이용하게 되면 좌표 변환에 필요한 추가적인 코드와 라이브러리에 대한 학습이 필요합니다.(GeoTools, JTS 등)
테스트에는 Jmeter를 이용했습니다.
이 결과는 미리 각각의 엔드포인트에 3000개의 스레드 그룹으로 테스트한 후에 진행했습니다.
SRID 4326
SRID 3857
딱 DB에서 쿼리 코스트만큼 차이가 났습니다. 1000개 스레드 그룹으로 했을 때는 거의 차이가 없었습니다.
실제 ARchive 개발 환경에서 테스트(1000개 쓰레드 그룹)
SRID 3857
SRID 4326
정확성이 필요하다면 주의해야할 수도 있습니다. 두 좌표계 간의 정확도를 비교해시는 것을 추천합니다.
웹, 앱 지도 상에서 srid 4326의 MBR이 반경보다 더 큰 직사각형이 됩니다. 더 많은 데이터를 가져오는 결과를 가져왔습니다 -> 이는 srid 4326이 지리 좌표계라 지구에서 곡률과 위도에 따른 경도 변화를 고려해 최소 사각형 계산에서 발생하는 문제입니다. 실제 위치와는 가까울 수 있지만 웹, 앱 지도 상에서 반경보다 큰 데이터가 조회됩니다.
저희 팀은 최종적으로 SRID 3857을 사용하려합니다. 아무래도 DB에서 쿼리 플랜과 API 응답속도에서 차이가 나기에 이 방식을 택했습니다. 실제로 좌표 변환하는 로직이 추가되긴 하지만 DB에 부하가 가는 것 보다는 좋다고 판단했습니다. 또한 요구사항이 지도에서 사용자가 보는 화면 반경만큼 사용자 데이터를 보여주는 것 입니다. 그러므로 앱 지도 상에서 해당하는 반경만큼의 데이터를 가져오는 srid 3857을 최종적으로 선택했습니다.
이외에도 좌표계 관련해서 찾아보고 탐구할 부분이 많았습니다. 특히 도움이 많이 된 자료는 Real Mysql 1, 2편입니다. 좌표계 변환 관련해서는 GeoTools, JTS 라이브러리를 찾아봤습니다. 전체 코드는 여기 레포지토리 가시면 볼 수 있습니다. 예제 링크