Last Updated on 12월 12, 2022 by Jade(정현호)
안녕하세요
이번 포스팅에서는 MySQL 의 공간 검색(Spatial Data Types)에 관련된 기능에 대해서 확인 해보려고 합니다.
전체적인 내용은 Real MySQL 8.0 책 과 MySQL Document 를 정리 한 내용 으로 확장 검색에 대한 내용 중 두 번째 파트로 공간 검색(Spatial Data Types) 에 대한 내용을 다루고 있습니다.
공간 검색
MySQL 서버의 공간 데이터 관리 기능은 지원은 다른 RDBMS 보다 조금 늦은 편이긴 합니다.
다만 최근 MySQL 8.0 버전 버전 부터 많은 기능이 포함, 보완되고 있습니다.
이 부분은 최근에 서비스 되는 애플리케이션의 요건을 반영한다는 것을 의미 하게 됩니다.
(최근의 모바일 기반의 애플리케이션에서 GPS 를 이용한 위치 정보 또는 지도 서비스를 활용한 서비스가 많기 때문이라고 생각함)
공간 데이터(Spatial Data Type) 의 관리에 관련된 부분은 보통적으로 범용적 또는 많이 사용 되는 기능이 아니긴 합니다.
그래서 DB 사용자에서도 인지도가 높은 기능 분야가 아님에 따라서 사용되는 단어가 생소하고 어려울 수도 있긴 합니다.
해당 포스팅에서도 관련된 선행 기본 지식이나 용어에 대해서 먼저 살펴 보도록 하겠습니다.
공간 데이터 관련 용어
공간 데이터 관련된 몇가지 용어에 대해서 설명 하도록 하겠습니다.
OGC
- OGC 는 Open Geospatial Consortium (개방형 공간 정보 컨소시엄) 의 약자로 위치 기반 데이터에 대한 표준을 수립하는 단체 입니다.
정부와 기업체, 그리고 학교 등 모든 기관이 자유롭게 가입할 수 있으며, 전 세계 500여개 이상의 학교나 기업, 정부 기간이 참여를 하고 있습니다.
OGC 에서는 정기적으로 위치 기반 데이터 및 처리에 대한 표준을 제정하고 개선 하고 있습니다.
OepnGIS
- OGC 에서 제정한 지리 정보 시스템(GIS, Geographic Information System) 표준으로, WKT 나 WKB 같은 지리 정보 데이터를 표기하는 방법과 저장하는 방법, 그리고 SRID 와 같은 표준을 포함 합니다.
OpenGIS 표준을 준수하는 응용 프로그램의 위치 기반 데이터는 상호 변환 없이 교환 가능하도록 설계되어 있습니다.
SRS 와 GCS, PCS
- SRS 는 Spatial Reference System 약자로 한글로는 "공간 참조 시스템" 으로 번역이 되며, 보통은 좌표계 라고 이해를 하시면 됩니다. SRS 에는 크게 GCS(Geographic Coordinate System) 와 PCS(Projected Coordinate System) 으로 구분되게 됩니다.
GCS는 지구 구체상의 특정 위치나 공간을 표현하는 좌표계를 의미 합니다. 위도(Latitude) 와 경도(Longitude) 와 같이 각도(Angular unit) 단위의 숫자로 표시 됩니다.
PCS 는 구체 형태의 지구를 종이 지도와 같은 평면으로 투영(프로젝션, Projection) 시킨 좌표계 의미하며, 보통 미터(meter) 와 같은 선형 적인 단위로 표시하게 됩니다.
GCS 는 지구와 같은 구체 표면에서 특정 위치를 정의 하는 반면에, PCS 는 GCS 위치 데이터를 2차원 평면인 종이에 어떻게 표현을 할지를 정의 하게 됩니다.
동일한 지점이라도 어떤 SRS(공간 좌표계)를 사용하느냐에 따라 표시 방법이 달라 지게 됩니다.
SRID 와 SRS-ID
- SRID 는 Spatial Reference ID 의 줄임말로 특정 SRS 를 지칭하는 고유 번호를 의미하며, SRS-ID 와 SRID 는 동의어 입니다.
MySQL 서버에서는 SRS 고유 번호를 저장하는 컬럼의 이름으로 SRS_ID 로 사용하고 있으며, 함수의 인자나 식별자로 사용될 경우 SRID 로 사용 됩니다.
WKT 와 WKB
- WKT(Well-Known Text format) 와 KWB(Well-Known Binary format) 은 OGC 에서 제정한 OpenGIS 에서 명시한 위치 좌표의 표현 방법입니다.
WKT는 사람의 눈으로 쉽게 식별할 할 수 있는 텍스트 포맷
WKB 는 컴퓨터에 저장할 수 있는 형태의 이진 포맷의 저장 표준
둘다 OpenGIS 표준에 명시된 공간 데이터 표기 방법이며, MySQL 서버가 내부적으로 공간 데이터를 처리하거나 저장할 때는 WKT 나 WKB 포맷으로 저장하지는 않습니다.
MySQL 서버가 내부적으로 사용하는 이진 데이터 포맷은 WKB 포맷과 흡사하지만 완전히 동일 하지는 않습니다.
MBR 과 R-Tree
- MBR은 Minimum Bounding Rectangle 약자로 어떤 도형을 감싸는 최소의 사각 상자를 의미 합니다.
MBR 이 중요한 이유로 공간 인덱스(Spatial Index)가 도형의 포함 관계를 이용하기 때문입니다.
이렇게 도형들의 포함 관계를 이용해서 만들어진 인덱스를 R-Tree 라고 합니다.
SRS
SRS(좌표계)는 GCS(지리 좌표계) 와 PCS(투영 좌표계) 로 구분된다는 것을 위에서 언급하였습니다.
MySQL 서버에서 지원하는 SRS 는 5000 여개가 넘으며, 지원하는 SRS 에 대한 정보는 information_schema 의 st_spatial_reference_systems 딕셔너리 테이블에서 확인 할 수 있습니다.
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 | | +--------------------------+---------------+------+-----+---------+-------+
st_spatial_reference_systems 테이블 컬럼 중에서 중요한 컬럼은 SRS_ID 컬럼과 DEFINITION 컬럼 입니다.
• SRS_ID 컬럼 : 해당 좌표계를 지칭하는 고유 번호가 저장
• DEFINITION 컬럼 : 해당 좌표계가 어떤 좌표계인지에 대한 정의가 저장
SRS_ID 컬럼의 고유 번호는 컬럼에 데이터를 저장 하거나 검색할 때 필요하기 때문에 사용자는 저장하는 공간 데이터가 어느 좌표계를 사용하는지를 알 고 있어야 합니다.
아래 조회 결과는 일반적인 GPS를 사용하는 좌표계 정보에 대해서 st_spatial_reference_systems 테이블에 어떻게 저장되어 있는지를 보여주고 있습니다.
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
위에서 조회한 내용의 좌표계 이름은 WGS 84 이며, 좌표계 고유 번호는 4326 이며, 입력 되는 값의 단위는 각도(degree) 라는 것을 확인 할 수 있습니다.
참고) WGS 84 는 1984년에 만든 Word Geodetic System 을 말하며, 세계 지구 좌표 시스템(World Geodetic System, WGS) 을 뜻합니다.
WGS 1984, EPSG:4326, WGS84 라고 부르며, 2004년에 마지막으로 개정되었습니다.
DEFINITION 컬럼의 값은 항상 GEOGCS 또는 PROJCS 로 시작 합니다.
- GEOGCS 는 지리 좌표계(GCS) 를 의미
- PROJCS 는 투영 좌표계(PCS) 를 의미
MySQL 에서는 지리 좌표계는 대략 483개, 투영 좌표계는 4668 개를 지원 하고 있습니다.
투영 좌표계
투영 좌표계가 많은 이유로 실제 특정 나라 또는 특정 지역을 위한 좌표계는 평면으로 투영해서 좌표를 관리 해도 오차가 크게 발생하지는 않습니다. 그래서 특정 지역을 위해서 만들어진 좌표계는 대부분 투영 좌표계로 정의되어 있습니다.
지리 좌표계
WGS 84 좌표계는 지구 전체를 구체 형태로 표현하기 때문에 지리 좌표계로 정의 하고 있습니다.
지구는 약간 부풀려진 타원체 모양을 하고 있으며, 지구의 모든 지역에는 각각의 이름이 지정되어 있는 것이 특징이며, 지리 좌표계(WGS 84) 는 지구의 특정 위치를 한 점으로 기준을 삼으며, 선택한 점을 기준으로 가로선(경도) 와 세로선(위도) 로 나누어서 표기를 합니다.
WGS 84 좌표계의 DEFINITION 컬럼에서 하나 더 중요한 내용은 AXIS 이며, 예제의 DEFINITION 컬럼에는 AXIS 가 두번 표시 됩니다.
두 AXIS 는 위도 와 경도 순서로 나열되어 있습니다.
그래서 WGS 84 좌표계를 사용하는 위치 정보에서 특정 위치를 표시할 때에는 "POINT(위도 경도)" 와 같이 표현 해야 합니다.
나열 된 순서대로 첫번째는 X 축, 두번째는 Y 축에 해당 하며, ST_X() 함수는 위도 값을 반환 하며, ST_Y() 함수는 경도 값을 반환 합니다.
-> AXIS["Lat",NORTH],AXIS["Lon",EAST]
-> Lat : 위도(latitude) , Lon : 경도(longitude)
아래는 SRS_ID가 3857인 투영 좌표계의 상세 정의를 보여주고 있습니다. 좌표계의 이름도 WGS 84로 되어 있습니다.
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
이 좌표계는 입력되는 값의 단위(UNIT)가 미터(meter)라는 것을 알 수 있습니다.
(UNIT["metre" , 영국식 metre , 미국식 meter)
해당 좌표계는 웹 페이지에서 지도를 보여주기 위해서 사용 되는 투영 좌표계로 구글 과 오픈 스트리트 맵(Open Street Map), 그리고 다수의 웹 기반 지도 프로젝트에서 사용 됩니다.
이전에 조회한 srs_id=4326 과는 반대로 srs_id=3857 의 경우 AXIS 의 내용이 AXIS["X",EAST],AXIS["Y",NORTH] 으로 POINT(경도 위도) 순서로 되어있는 것을 확인 할 수 있습니다.
(srs_id=4326 은 위도 경도 순이었음)
MySQL 8.0 이전 버전까지는 공간 데이터를 저장할 때 별도의 srs_id 를 지정하거나 사용하지 않았지만 8.0 버전 부터는 SRID 의 지원이 추가 되었습니다.
8.0 에서 SRID 를 별도로 지정하지 않으면 SRID 는 0 으로 자동 인식 됩니다.
SRID 에 따른 차이
SRID 를 0으로 설정된 데이터에 대한 거리 계산은 실제 지구 구면체 상의 거리 계산을 하는 것은 아니며, SRID 이 0 인 공간 데이터는 단위를 가지지 않기 때문 입니다.
아래 예제에서는 두 점 사이의 거리를 계산하는 내용으로 st_pointfromtext() 함수는 MySQL 서버의 공간 데이터(POINT 객체)를 생성하는 함수입니다.
이 함수의 첫 번째 파라미터는 점의 위치이며 두 번재 파라미터는 첫 번째 파라미터에 명시된 점이 사용하는 공간 좌표계(SRS) 의 아이디(SRID) 값을 입력 합니다.
-- 평면 좌표계(SRID=0) 를 사용하는 공간 데이터
mysql> select st_distance(st_pointfromtext('POINT(0 0)',0),
st_pointfromtext('POINT(1 1)',0)) as distance;
+--------------------+
| distance |
+--------------------+
| 1.4142135623730951 |
+--------------------+
-- 웹 기반 지도 좌표계(SRID=3857) 를 사용하는 공간 데이터
mysql> select st_distance(st_pointfromtext('POINT(0 0)',3857),
st_pointfromtext('POINT(1 1)',3857)) as distance;
+--------------------+
| distance |
+--------------------+
| 1.4142135623730951 |
+--------------------+
-- WGS 84 지리 좌표계(SRID=4326) 를 사용하는 공간 데이터
mysql> select st_distance(st_pointfromtext('POINT(0 0)',4326),
st_pointfromtext('POINT(1 1)',4326)) as distance;
+--------------------+
| distance |
+--------------------+
| 156897.79947260793 |
+--------------------+
위의 예제의 첫 번째는 SRID 가 0 이며 그렇기 때문에 st_distance 함수의 결과가 1.4142135623730951 라는 수치가 나왔으며 해당 값은 단위가 없으며 피타고라스의 정리에 의한 수식으로 계산된 거리 값 입니다. 즉 실생활에서 사용할 수 있는 수치가 아니라는 의미 입니다.
두 번째 와 세 번째는 SRID 가 0 이 아닌 값으로 명시화 되어있기 때문에 st_distance 함수의 결과값이 단위를 가지는 결과 값이 되게 되며 단위는 미터(meter) 입니다.
첫 번째와 두 번째는 SRID 가 다르지만 둘다 지구 구체를 고려하지 않고 평면에서의 거리를 계산한 것이기 때문에 거리 계산 값이 동일한 것이며, 세 번째는 지리 좌표계를 사용하였으며 그에 따라서 지구 구체 기반이기 때문에 거리 값이 첫 번째,두 번째와 완전히 다르게 계산 되어 출력 된 것 입니다.
MySQL 8.0 에서 SRID 를 별도로 명시하지 않거나 SRID 을 0으로 지정한 공간 데이터라고 해서 실제로 km 이나 meter 단위로 계산을 못하는 것은 아닙니다. 다만 MySQL 이 자동으로 필요한 값을 계산할 수 없다는 것을 의미하는 것 입니다.
MySQL8.0 에서 지원되는 공간 함수들이 모두 SRID를 지원하는 것은 아님으로 많은 공간 함수는 SRID 가 0 인 경우에만 작동할 수 있으며 일부 함수만 WGS 84 좌표계 데이터를 처리 할 수 있는 상태 입니다.
투영 좌표계와 평면 좌표계
MySQL 서버에서는 투영 좌표계나 지리 좌표계에 속하지 않는 평면 좌표계가 있으며, 평면 좌표계는 투영 좌표계와 비슷한 특성을 가지고 있습니다.
투영(Projection) 좌표계는 지구 구체 전체 또는 일부를 평면으로 투영 해서 표현하여 투영 좌표계 라고 합니다.
SRID=0 인 좌표계도 평면에 표시되는 좌표계이지만, SRID=0 인 좌표계는 투영 좌표계라고 하지 않고 평면 좌표계 라고 합니다.
위에서 설명한 내용과 같이 SRID=0 인 좌표계는 단위(km 또는 미터 등) 을 가지지 않으며 X 축과 Y 축의 값이 제한을 가지지 않기 때문에 "무한 평면 좌표계" 라고도 불리고 있습니다.
하지만 SRID=0 인 좌표계와 대표적인 투영 좌표계인 SRID=3857 은 값의 단위 그리고 최대, 최소값을 제외하면 거의 차이가 없습니다.
아래 그림 1 에서 X 축과 Y 축의 단위를 미터로 표현하면 SRID=3857 인 투영 좌표계가 됩니다.
그림 1 - [나무위키 : 좌표계]
계속 설명 한 내용과 같이 MySQL 에서 SRID 를 별도로 지정하지 않으면 SRID 는 기본값 0 을 가지게 되며, 기본값 0인 평면 좌표계와 투영 좌표계에서 거리 계산은 피타고라스 정리 수식에 의해서 좌표 간의 거리가 계산됩니다.
평면 좌표계에서는 두 점 간의 거리는 아무런 단위를 가지지 않지만, 투영 좌표계에서의 두 점 간의 거리는 투영 좌표계의 단위에 따라서 결정되게 됩니다.
위에서 언급된 SRID=3857 에서 계산된 단위는 미터 값 입니다.
평면 좌표계와 투영 좌표계를 이용하기 위해서 아래와 같은 테이블을 생성하도록 하겠습니다.
평면 좌표계(SRID=0) 에서는 ST_PointFromText() 함수를 이용해서 POINT(X Y) 문자열(WKT) 을 Geometry 타입 값으로 변환할 때 특별히 SRID를 설정하지 않아도 자동으로 SRID=0 으로 설정이 되게 됩니다.
하지만 SRID=3857 인 투영 좌표계에서 ST_PointFromText() 함수를 이용해서 POINT(경도 위도) 문자열(WKT)을 Geometry 타입으로 변환할 때에는 반드시 SRID=3857 을 명시적으로 입력 해야 합니다.
-- 평면 좌표계 사용 예제
mysql> create table plain_coord (
id bigint not null auto_increment,
location point srid 0,
primary key(id)
);
mysql> insert into plain_coord
values (1,st_pointfromtext('point(0 0)'));
mysql> insert into plain_coord
values (2,st_pointfromtext('point(5 5)',0));
-- 투영 좌표계 사용 예재
mysql> create table projection_coord (
id bigint not null auto_increment,
location point srid 3857,
primary key(id)
);
mysql> insert into projection_coord
values (1,st_pointfromtext('point(14133791.066622 4509381.876958)',3857));
mysql> insert into projection_coord
values (2,st_pointfromtext('point(14133793.43554 4494917.464846)',3857));
위에서 테이블을 생성 하면서 평면좌표계 테이블에서는 location 컬럼의 SRID 를 0 으로 지정 하였고, 투영 좌표계 테이블에서는 location 컬럼의 SRID 를 3857 로 지정하였습니다.
두 테이블 모두 location 을 저장할 때 명시적으로 평면 좌표계 또는 투영 좌표계를 사용한다는 것을 SRID 값을 통해서 설정 하였습니다.
아래 예제는 SRID 를 0이 아닌 WGS 84 좌표계인 SRID=4326 을 참조하는 공간 데이터를 저장하려고 입력을 시도 하였습니다.
mysql> insert into plain_coord
values (3,st_pointfromtext('point(5 5)',4326));
ERROR 3643 (HY000): The SRID of the geometry does not match the SRID of the column 'location'.
The SRID of the geometry is 4326,
but the SRID of the column is 0. Consider changing the SRID of the geometry or the SRID property of the column.
이와 같이 SRID=0 이 아닌 다른 좌표계를 참조하는 공간 데이터가 입력하게 될 경우 SRID 불일치 관련 메세지와 함께 에러가 발생되게 됩니다.
테이블을 생성할 때 명시적으로 SRID 를 설정하지 않았다면 해당 컬럼은 모든 SRID 를 저장할 수 있게 됩니다.
다만 하나의 컬럼에 저장된 데이터의 SRID가 제 각각이라면 인덱스를 이용한 빠른 검색을 수행할 수 없게 됩니다.
테이블에 저장된 평면 좌표 데이터와 투영 좌표 데이터는 아래와 같이 조회를 하게 됩니다.
-- 평면 좌표계
mysql> select id, location, st_aswkb(location)
from plain_coord\G
*************************** 1. row ***************************
id: 1
location: 0x00000000010100000000000000000000000000000000000000
st_aswkb(location): 0x010100000000000000000000000000000000000000
*************************** 2. row ***************************
id: 2
location: 0x00000000010100000000000000000014400000000000001440
st_aswkb(location): 0x010100000000000000000014400000000000001440
mysql> select id, st_astext(location) as location_wkt,
st_x(location) as location_x, st_y(location) as location_y
from plain_coord;
+----+--------------+------------+------------+
| id | location_wkt | location_x | location_y |
+----+--------------+------------+------------+
| 1 | POINT(0 0) | 0 | 0 |
| 2 | POINT(5 5) | 5 | 5 |
+----+--------------+------------+------------+
-- 투영 좌표계
mysql> select id,location, st_aswkb(location)
from projection_coord\G
*************************** 1. row ***************************
id: 1
location: 0x110F0000010100000076C421E243F56A4172142078B1335141
st_aswkb(location): 0x010100000076C421E243F56A4172142078B1335141
*************************** 2. row ***************************
id: 2
location: 0x110F00000101000000F10EF02D44F56A417009C05D91255141
st_aswkb(location): 0x0101000000F10EF02D44F56A417009C05D91255141
mysql> select id, st_astext(location) as location_wkt,
st_x(location) as location_x, st_y(location) as location_y
from projection_coord;
+----+---------------------------------------+-----------------+----------------+
| id | location_wkt | location_x | location_y |
+----+---------------------------------------+-----------------+----------------+
| 1 | POINT(14133791.066622 4509381.876958) | 14133791.066622 | 4509381.876958 |
| 2 | POINT(14133793.435554 4494917.464846) | 14133793.435554 | 4494917.464846 |
+----+---------------------------------------+-----------------+----------------+
각 좌표계의 첫 번째 쿼리는 location 컬럼에 저장된 값을 그대로 조회 하였고, 결과는 이진 데이터가 표시가 되었습니다.
MySQL 내부적으로 사용 되는 이진 포맷으로 st_aswkb 함수 사용의 결과 포맷 공간 데이터 입니다.
(WKB - Well Known Binary)
MySQL 서버의 내부 이진 포맷이나 WKB 포맷으로 출력된 결과는 알아보기 쉽지 않기 때문에 식별이 가능한 다른 포맷으로 출력이 필요하며, 위의 예제의 각 좌표계 두 번째 쿼리는 st_astext() 함수를 사용해서 WKT(Well Known Text) 포맷으로 변환하여 내용을 확인 하고 있습니다.
각 좌표계 두 번째 쿼리의 두 번째, 세 번째 컬럼에 대해서는 st_x() , st_y() 함수를 사용하였고, 공간 좌표 데이터의 x 축과 y 축 값을 출력 하게 됩니다.
평면 좌표계와 투영 좌표계에서 두 점 간의 거리를 계산하면 단순히 피타고라스의 정리에 의한 거리 계산 결과가 보이는 것을 확인 할 수 있으며, SRID=3857 인 투영 좌표계라도 계산된 거리 값은 지구 구체상의 실제 거리와는 오차가 있을 수 있습니다.
-- 평면 좌표계
mysql> select st_distance(st_pointfromtext('point(0 0)'),
st_pointfromtext('point(5 5)')) as distance;
+--------------------+
| distance |
+--------------------+
| 7.0710678118654755 |
+--------------------+
-- 투명 좌표계
mysql> select st_distance(st_geomfromtext('point(14133790.0 4509380.0)',3857),
st_geomfromtext('point(14133795.0 4509385.0)',3857)) as distance;
+--------------------+
| distance |
+--------------------+
| 7.0710678118654755 |
+--------------------+
구면체 상의 두 점 간의 거리를 계산하기 위해서는 st_distance_sphere() 함수를 사용하면 되지만, SRID=4326 과 같은 지리 좌표에 대해서만 사용할 수 있으며, 평면 좌표계와 투영 좌표계를 사용하는 거리 계산에서는 사용할 수 없습니다.
실제로 평면 좌표계는 많이 사용 되지 않지만 투영 좌표계는 지도나 화면에 지도를 표현하는 경우 자주 사용 되게 됩니다.
각 좌표계 마다 사용방법은 크게 다르지 않아서 지리 좌표 데이터를 저장하고 조회할 때도 평면 좌표계나 투영 좌표계에서 확인한 함수들을 동일하게 사용이 되고 SRID 에 따라서 함수에 사용 되는 파라미터(인자)가 X축-Y축 또는 좌표값 또는 위도-경도 좌표값인지의 차이가 있을 뿐 입니다.
지리 좌표계 데이터 관리
테스트를 위해서 테이블을 생성 하도록 하겠습니다.
mysql> create table sphere_coord (
id bigint not null auto_increment primary key,
name varchar(20),
location point not null SRID 4326,
spatial index spx_location(location)
);
mysql> insert into sphere_coord values
(null,'서울숲',st_pointfromtext('point(37.544738 127.039074)', 4326)),
(null,'한양대학교',st_pointfromtext('point(37.555363 127.044521)', 4326)),
(null,'덕수궁',st_pointfromtext('point(37.565922 126.975164)', 4326)),
(null,'남산',st_pointfromtext('point(37.548930 126.993945)', 4326));
위의 테이블 생성시 공간 인덱스(spatial index) 도 같이 생성 하였으며 공간 인덱스를 생성하는 컬럼은 NOT NULL 이어야 합니다.
GPS 로 부터 받는 위치 정보를 저장하기 위해 WGS 84 좌표계(SRID=4826) 으로 컬럼을 정의 하였습니다.
테이블 생성시 저장하는 컬럼이 WGS 84 좌표계로 정의 되었기 때문에 데이터 입력시에도 동일하게 공간 데이터는 WGS 84 좌표계 데이터만 저장할 수 있습니다.
공간 데이터를 검색하는 일반적인 형태는 특정 위치를 기준으로 반경 몇 km 이내의 데이터를 검색하는 작업이 되게 됩니다.
지리 좌표계에서 두 점(POINT) 의 거리는 st_distance_sphere() 함수를 이용하는 것이며, 아래 쿼리는 특정 위치에서 1km 이내에 있는 레코드를 검색하는 쿼리 입니다.
-- 실행 결과
mysql> select id,name,
st_astext(location) as locaiton,
round(st_distance_sphere(location,st_pointfromtext('POINT(37.547027 127.047337)', 4326))) as distance_meters
from sphere_coord
where st_distance_sphere(location,st_pointfromtext('POINT(37.547027 127.047337)', 4326)) < 1000;
+----+-----------------+-----------------------------+-----------------+
| id | name | locaiton | distance_meters |
+----+-----------------+-----------------------------+-----------------+
| 1 | 서울숲 | POINT(37.544738 127.039074) | 772 |
| 2 | 한양대학교 | POINT(37.555363 127.044521) | 960 |
+----+-----------------+-----------------------------+-----------------+
-- 실행 계획
mysql> explain select id,name,
st_astext(location) as locaiton,
round(st_distance_sphere(location,st_pointfromtext('POINT(37.547027 127.047337)', 4326))) as distance_meters
from sphere_coord
where st_distance_sphere(location,st_pointfromtext('POINT(37.547027 127.047337)', 4326)) < 1000;
+----+-------------+--------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+--------------+------+---------------+------+---------+------+------+----------+-------------+
| 1 | SIMPLE | sphere_coord | ALL | NULL | NULL | NULL | NULL | 4 | 100.00 | Using where |
+----+-------------+--------------+------+---------------+------+---------+------+------+----------+-------------+
st_distance_sphere() 함수는 두 위치 간의 거리를 미터 단위로 반환 하기 때문에 where 조건에 1000 보다 작은 레코드만 검색 하였으며, 사용된 point 는 2호선 뚝섬역의 위치 입니다.
위의 실행계획에서 확인 할 수 있듯이 테이블 풀 스캔으로 실행되고 있으며, MySQL 서버에서는 아직 인덱스를 이용한 반경 검색 기능(함수)가 없습니다.
st_distance_sphere() 함수의 결과를 상수와 비교하는 형태는 인덱스를 사용할 수 없는 형태임을 확인 할 수 있습니다.
대안으로 MBR(Minimum Bounding Rectangle) 을 이용한 st_within() 함수를 사용할 수 있으며, 2개의 공간 데이터를 파라미터로 입력 받으며, 첫 번째 파라미터로 입력된 공간 데이터가 두 번째로 입력된 파라미터의 공간 데이터에 포함 되는지를 확인 하는 함수 입니다.
해당 포스팅은 여기서 마무리 하며 Real MySQL 8.0 책의 많은 내용 중에서 일부분의 내용만 함축적으로 정리한 것으로 모든 내용 확인 및 이해를 위해서 직접 책을 통해 모든 내용을 확인하시는 것을 권해 드립니다.
Reference
Reference Book
• Real MySQL 8.0
Reference URL
• mysql.com/spatial-analysis-functions
• wikipedia.org/세계_주기_좌표_시스템
연관된 다른 글
Principal DBA(MySQL, AWS Aurora, Oracle)
핀테크 서비스인 핀다에서 데이터베이스를 운영하고 있어요(at finda.co.kr)
Previous - 당근마켓, 위메프, Oracle Korea ACS / Fedora Kor UserGroup 운영중
Database 외에도 NoSQL , Linux , Python, Cloud, Http/PHP CGI 등에도 관심이 있습니다
purityboy83@gmail.com / admin@hoing.io
