[Real MySQL 8.0 2] 12.2 확장 검색 - 공간 검색
MySQL 서버의 공간 데이터 관리 기능은 다른 RDBMS보다 조금 늦은 편이지만 최근 MySQL 8.0 버전으로 업그레이드되면서 빠르게 많은 기능을 보완하고 있다. 이는 요즘 서비스되는 응응 프로그램들의 요건을 빠르게 반영한다는 것을 의미하기도 한다. 공간 데이터(Spatial Data) 관리에 관련된 부분은 사용자들의 인지도가 높은 분야는 아니므로 사용되는 단어들이 다소 생소하거나 어려울 수 있다. 여기서는 공간 데이터 관리에 필요한 기본 지식이나 용어에 대한 부분을 먼저 살펴보고, MySQL 서버의 공간 데이터 관리 기능, 그리고 공간 검색을 위한 인덱스 활용에 대해서도 살펴보겠다.
1. 용어 설명
- OGC(Open Geospatial Consortium)
- 정기적으로 위치 기반 데이터 및 처리에 대한 표준을 제정하고 개선하는 단체
- OpenGIS
- OGC에서 제정한 지리 정보 시스템(GIS, Geographic Information System) 표준
- SRS와 GCS, PCS
- SRS(Spatial Reference System)는 일종의 좌표계(Coordinate System)으로 크게 GCS(Geographic Coordinate System)와 PCS(Projected Coordinate System)로 구분된다. GCS는 지구 구체상의 특정 위치나 공간을 표현하는 좌표계로, 흔히 위도(Latitude)와 경도(Longitude)와 같이 각도(Anguler unit) 단위의 숫자로 표시된다. PCS는 구체 형태의 지구를 종이 지도와 같은 평면으로 투영(Projection)시킨 좌표계를 의미하는데, 주로 미터(meter)와 같은 선형적인 단위로 표시된다.
- 교재 설명 편의를 위해 SRS는 간단하게 좌표계, GCS는 지리 좌표계 또는 구면 좌표계, PCS는 투영 좌표계로 줄여서 명명한다.
- SRID(SRS-ID)
- SRID는 Spatial Reference ID의 줄임말로서, 특정 SRS를 지칭하는 고유 번호를 의미
- WKT와 WKB
- WKT(Well-Known Text Format)와 WKB(Well-Known Binary Format)은 OpenGIS 표준에 명시된 공간 데이터 표기 방법
- MBR과 R-Tree
- MBR(Minimum Bounding Rectangle)은 어떤 도형을 감싸는 최소의 사각 상자로 MySQL 서버의 공간 인덱스(Spatial Index)가 이 MBR의 포함 관계를 이용한다. 이렇게 만들어진 인덱스를 R-Tree라고 한다.
2. SRS(Spatial Reference System)
MySQL 서버에서 지원하는 SRS는 5,000여 개가 넘는다. MySQL 서버에서 지원하는 SRS에 대한 정보는 information_schema 데이터베이스의 ST_SPATIAL_REFERENCE_SYSTEMS 테이블을 통해 확인할 수 있다.
1
2
3
4
5
6
7
8
9
10
11
mysql> DESC information_schema.ST_SPATIAL_REFERENCE_SYSTEMS;
+--------------------------+---------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------------------------+---------------+------+-----+---------+-------+
| SRS_NAME | varchar(80) | NO | | NULL | |
| SRS_ID | int unsigned | NO | | NULL | |
| ORGANIZATION | varchar(256) | YES | | NULL | |
| ORGANIZATION_COORDSYS_ID | int unsigned | YES | | NULL | |
| DEFINITION | varchar(4096) | NO | | NULL | |
| DESCRIPTION | varchar(2048) | YES | | NULL | |
+--------------------------+---------------+------+-----+---------+-------+
SRS_ID 컬럼은 SRID가 저장되며, DEFINITION 컬럼에는 해당 좌표계가 어떤 좌표계인지 정의돼 있다.
일반적으로 GPS가 사용하는 좌표계의 SRID는 4326인데 이를 조회하면 아래와 같이 나온다.
1
2
3
4
5
6
7
8
9
10
11
12
13
mysql> SELECT * FROM information_schema.ST_SPATIAL_REFERENCE_SYSTEMS WHERE SRS_ID=4326 \G
*************************** 1. row ***************************
SRS_NAME: WGS 84
SRS_ID: 4326
ORGANIZATION: EPSG
ORGANIZATION_COORDSYS_ID: 4326
DEFINITION: GEOGCS["WGS 84",DATUM["World Geodetic System 1984",
SPHEROID["WGS 84",6378137,298.257223563,
AUTHORITY["EPSG","7030"]],AUTHORITY["EPSG","6326"]],
PRIMEM["Greenwich",0,AUTHORITY["EPSG","8901"]],
UNIT["degree",0.017453292519943278,AUTHORITY["EPSG","9122"]],
AXIS["Lat",NORTH],AXIS["Lon",EAST],AUTHORITY["EPSG","4326"]]
DESCRIPTION: NULL
좌표계의 이름(SRS_NAME) WGS 84, 좌표계 고유 번호(SRS_ID) 4326, 입력되는 값의 단위(UNIT)가 각도라는 점을 알 수 있다. DEFINITION 컬럼의 값은 항상 GEOGCS 또는 PROJCS로 시작하는데 GEOGCS는 GCS, PROJCS는 PCS를 의미한다. WGS 84는 지구 전체를 구체 형태로 표현하기에 GCS로 정의된 것이지만 MySQL 서버가 지원하는 90%의 좌표계는 PCS다. 투영 때문에 오차를 걱정할 수 있는데 실제 특정 나라 또는 특정 지역을 위한 좌표계는 평면으로 투영해서 관리해도 오차가 크게 발생하지 않아서 이런 좌표계는 대부분 PCS로 정의돼 있다.
WGS 84 좌표계는 모바일 휴대폰의 GPS 장치가 수신하는 대부분의 좌표계이므로 다른 좌표계보다 자주 사용하게 될 것이니 기억해두자.
다음은 SRS_ID가 3857인 PCS 좌표계로 좌표계 이름은 동일하게 WGS 84인데 입력되는 값의 단위(UNIT)가 각도가 아니라 미터다. 이 좌표계는 웹 페이지에서 지도를 보여주기 위해서 사용되는 PCS로, 구글과 오픈 스트리트 맵(OSM), 그리고 다수의 웹 기반 지도 프로젝트에서 사용된다.
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
mysql> SELECT * FROM information_schema.ST_SPATIAL_REFERENCE_SYSTEMS WHERE SRS_ID=3857 \G
*************************** 1. row ***************************
SRS_NAME: WGS 84 / Pseudo-Mercator
SRS_ID: 3857
ORGANIZATION: EPSG
ORGANIZATION_COORDSYS_ID: 3857
DEFINITION: PROJCS["WGS 84 / Pseudo-Mercator",
GEOGCS["WGS 84",
DATUM["World Geodetic System 1984",
SPHEROID["WGS 84",6378137,298.257223563,AUTHORITY["EPSG","7030"]],
AUTHORITY["EPSG","6326"]],
PRIMEM["Greenwich",0,AUTHORITY["EPSG","8901"]],
UNIT["degree",0.017453292519943278,AUTHORITY["EPSG","9122"]],
AXIS["Lat",NORTH],AXIS["Lon",EAST],AUTHORITY["EPSG","4326"]],
PROJECTION["Popular Visualisation Pseudo Mercator",AUTHORITY["EPSG","1024"]],
PARAMETER["Latitude of natural origin",0,AUTHORITY["EPSG","8801"]],
PARAMETER["Longitude of natural origin",0,AUTHORITY["EPSG","8802"]],
PARAMETER["False easting",0,AUTHORITY["EPSG","8806"]],
PARAMETER["False northing",0,AUTHORITY["EPSG","8807"]],
UNIT["metre",1,AUTHORITY["EPSG","9001"]],AXIS["X",EAST],
AXIS["Y",NORTH],AUTHORITY["EPSG","3857"]]
DESCRIPTION: NULL
다음은 두 점(POINT(0 0)과 POINT(1 1)) 사이의 거리를 계산하는 간단한 쿼리다.
아래는 SRS_ID가 3857인 좌표계에서 두 점간 거리로 단위가 미터이며 평면 좌표계여서 두 점의 유클리드 거리인 약 1.414미터가 나왔다.
1
2
3
4
5
6
mysql> SELECT ST_Distance(ST_PointFromText('POINT(0 0)', 3857), ST_PointFromText('POINT(1 1)', 3857)) AS distance;
+--------------------+
| distance |
+--------------------+
| 1.4142135623730951 |
+--------------------+
아래는 SRS_ID가 4326인 좌표계에서 두 점간 거리로 단위가 각도이며 구면 좌표계여서 두 점의 유클리드 거리인 약 156,897미터가 나왔다.((0° 0°)와 (1° 1°)의 거리로 WGS 84 타원체에서 (0° 0°)에 대한 위도나 경도 1°는 대략 111km라 $\sqrt{111^2 + 111^2} \approx 157$)
1
2
3
4
5
6
mysql> SELECT ST_Distance(ST_PointFromText('POINT(0 0)', 4326), ST_PointFromText('POINT(1 1)', 4326)) AS distance;
+--------------------+
| distance |
+--------------------+
| 156897.79947260793 |
+--------------------+
3. 투영 좌표계와 평면 좌표계
MySQL 서버에서 투영 좌표계나 지리 좌표계에 속하지 않는 평면 좌표계가 있는데, 평면 좌표계는 투영 좌표계와 비슷한 특성을 가진다. SRID=0인 좌표계도 평면에 표시되는 좌표계지만 투영 좌표계라고 하지 않고 평면 좌표계라고 한다. 평면 좌표계는 단위를 가지지 않으며 X축과 Y축의 값이 제한을 가지지 않아서 무한 평면 좌표계라고도 불린다. MySQL 서버에서 특별히 SRID를 지정하지 않으면 기본 값 0을 가지게 된다.
4. 지리 좌표계
1. 지리 좌표계 데이터 관리
다음은 지리 좌표계를 활용한 예제로 먼저 아래와 같이 테이블을 생성해줬다. SPATIAL INDEX로 공간 인덱스를 생성할 수 있으며 공간 인덱스를 생성하는 컬럼은 반드시 NOT NULL이어야 한다. 좌표계는 4326으로 설정했다.
1
2
3
4
5
6
7
CREATE TABLE sphere_coord (
id INT NOT NULL AUTO_INCREMENT,
name VARCHAR(20),
location POINT NOT NULL SRID 4326,
PRIMARY KEY (id),
SPATIAL INDEX sx_location(location)
);
아래와 같이 공간 데이터를 삽입해줬다.
1
2
3
4
5
INSERT INTO sphere_coord VALUES
(NULL, '노량진 맥도날드', ST_PointFromText('POINT(37.513462 126.943489)', 4326)),
(NULL, '노들섬 라이브하우스', ST_PointFromText('POINT(37.518032 126.958244)', 4326)),
(NULL, '용산역 CGV', ST_PointFromText('POINT(37.530625 126.965539)', 4326)),
(NULL, '노량진 수산시장', ST_PointFromText('POINT(37.514713 126.937773)', 4326));
아래는 공간 데이터를 검색하는 가장 일반적인 형태 중 하나인 특정 위치를 기준으로 반경 몇 km 이내의 데이터를 검색하는 쿼리다. ST_Distance_Sphere 함수를 통해 미터 단위의 거리차를 얻을 수 있다. 노량진역을 기준으로 반경 1, 2, 3km 이내의 위치를 조회하는 쿼리를 아래와 같이 작성할 수 있다.
1
2
3
4
5
6
SELECT id, name,
ST_AsText(location) AS location,
ROUND(ST_Distance_Sphere(location, ST_PointFromText('POINT(37.514168 126.941603)', 4326))) AS distance_meters
FROM sphere_coord
WHERE ST_Distance_Sphere(location,
ST_PointFromText('POINT(37.514168 126.941603)', 4326)) < 1000;
1
2
3
4
5
6
+----+------------------------+-----------------------------+-----------------+
| id | name | location | distance_meters |
+----+------------------------+-----------------------------+-----------------+
| 1 | 노량진 맥도날드 | POINT(37.513462 126.943489) | 184 |
| 4 | 노량진 수산시장 | POINT(37.514713 126.937773) | 343 |
+----+------------------------+-----------------------------+-----------------+
1
2
3
4
5
6
SELECT id, name,
ST_AsText(location) AS location,
ROUND(ST_Distance_Sphere(location, ST_PointFromText('POINT(37.514168 126.941603)', 4326))) AS distance_meters
FROM sphere_coord
WHERE ST_Distance_Sphere(location,
ST_PointFromText('POINT(37.514168 126.941603)', 4326)) < 2000;
1
2
3
4
5
6
7
+----+------------------------------+-----------------------------+-----------------+
| id | name | location | distance_meters |
+----+------------------------------+-----------------------------+-----------------+
| 1 | 노량진 맥도날드 | POINT(37.513462 126.943489) | 184 |
| 2 | 노들섬 라이브하우스 | POINT(37.518032 126.958244) | 1529 |
| 4 | 노량진 수산시장 | POINT(37.514713 126.937773) | 343 |
+----+------------------------------+-----------------------------+-----------------+
1
2
3
4
5
6
SELECT id, name,
ST_AsText(location) AS location,
ROUND(ST_Distance_Sphere(location, ST_PointFromText('POINT(37.514168 126.941603)', 4326))) AS distance_meters
FROM sphere_coord
WHERE ST_Distance_Sphere(location,
ST_PointFromText('POINT(37.514168 126.941603)', 4326)) < 3000;
1
2
3
4
5
6
7
8
+----+------------------------------+-----------------------------+-----------------+
| id | name | location | distance_meters |
+----+------------------------------+-----------------------------+-----------------+
| 1 | 노량진 맥도날드 | POINT(37.513462 126.943489) | 184 |
| 2 | 노들섬 라이브하우스 | POINT(37.518032 126.958244) | 1529 |
| 3 | 용산역 CGV | POINT(37.530625 126.965539) | 2794 |
| 4 | 노량진 수산시장 | POINT(37.514713 126.937773) | 343 |
+----+------------------------------+-----------------------------+-----------------+
노량진역과 노량진 맥도날드와의 거리가 약 183m, 노량진 수산시장과의 거리가 약 430m, 노들섬 라이브하우스와의 거리가 약 1.5km, 용산역 CGV와의 거리가 약 2.6km로 거리별로 잘 나온 것을 볼 수 있었다.(좌표 데이터를 OSM과 네이버 지도에서 어림으로 찍어서 약간의 오차는 있다.)
3개의 쿼리 모두 실행 계획은 아래와 같다.
1
2
3
4
5
+----+-------------+--------------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+--------------+------------+------+---------------+------+---------+------+------+----------+-------------+
| 1 | SIMPLE | sphere_coord | NULL | ALL | NULL | NULL | NULL | NULL | 5 | 100.00 | Using where |
+----+-------------+--------------+------------+------+---------------+------+---------+------+------+----------+-------------+
type이 ALL인 것에서 풀 테이블 스캔이 발생한 것을 알 수 있는데 MySQL 서버는 다른 RDBMS와 달리 인덱스를 이용한 반경 검색 기능은 아직 없다고 한다. 쿼리에서도 ST_Distance_Sphere 함수의 결과를 상수와 비교하는 형태인데 이는 이전에 인덱스를 사용할 수 없는 조건 형태이다. 대신 차선책으로 MBR을 활용하면 해당 반경을 포함하는 외접 사각형을 통해 공간 인덱스를 활용해 후보를 줄이고 이후 거리 계산을 통해 반경 내에 들어오는 위치들을 조회할 수 있다.
2. 지리 좌표계 주의 사항
MySQL 서버의 GIS 기능은 도입된지는 오래됐지만 지리 좌표계나 SRS 관리 기능이 도입된 것은 MySQL 8.0이 처음이다. MySQL 8.0 버전에서도 지리 좌표계의 데이터 검색 및 변환 기능, 그리고 성능은 미숙한 부분이 보인다. 그래서 MySQL 서버를 이용해 지리 좌표계를 활용하고자 한다면 기능의 정확성이나 성능에 대해 조금은 주의가 필요할 수도 있다.
Ref
- Real MySQL 8.0 2 - p.229 ~ p.261