Real My SQL 8.0 [공간 데이터]

최근 당근의 지역기반 데이터 조회에 대한 궁금증이 생겨서 어떻게 공간데이터를 처리하는 지 알아보기위해

먼저 공간 데이터에 대한 학습이 필요하여 정리하게 되었다. 

 

공간 데이터 타입

mysql 서버는 OpenGIS에서 제시하는 표준을 준수

OpenGIS에서 제공하는 WKT(Well Known Text), WKB(Well Known Binary)를 이용해

공간 데이터를 관리할 수 있게 지원

 

OpenGIS는 지리 공간 정보 기술의 개방형 표준이다. Open Geospatial Consortium(OGC)에서 개발한 지리 공간 데이터 및 서비스에 대한 일련의 기술 사양을 의미한다.

 

WKT : 공간데이터를 글자로 적음, EX) POINT(1,1)

 

 

데이터 타입

 

GEOMETRY는 슈퍼 타입으로 셋 중 아무거나 다 받을 수 있으며

COLLECTION도 마찬가지로 다 받을 수 있다. (여러개 가능)

 

 

mysql 서버의 GEOMETRY 타입과 모든 자식 타입은 mysql 서버의 메모리에서는 BLOB객체로 관리, 클라이언트에 전송될때도 BLOB으로 전송

 

디스크로 저장될 때도 BLOB으로 저장됨

 

BLOB타입이라 하더라도 POINT나 간단한 POLYGON 데이터라면 몇백 바이트 수준이므로 성능을 크게 걱정 할 필요 없다. 

이런 상황이면, Off-page 로 저장하지 않는다. 

 

JDBC 표준에서는 아직 공간데이터를 공식적으로 지원 X
POINT, POLYGON 같은 자바 클래스 사용 불가, 대신 ORM 라이브러리들은 JTS 같은 오픈소스 공간 데이터 라이브러리 활용

 

Off-page : innodb에서 본문 페이지에 저장하지 못한 큰 데이터를 따로 저장하는 외부 페이지, 본문에는 포인터를 둠

 

 

공간 데이터 생성

공간 데이터를 생성할 때는 다음과 같은 함수들을 이용해

WKT 포맷을 MYSQL 서버가 처리할 수 있는 이진 데이터 포맷의 데이터로 변환 가능

 

객체 생성 함수에서 x와 y필드에는 사용하는 좌표 시스템(SRS)에 따라

위도나 경도, 또는 단순 정숫값 등 다양한 값 이 저장될 수 있음

 SRS (Spatial Reference System) : 공간 참조 시스템 (좌표 기반)

 

# POINT 타입
WKT 포맷 : POINT(x y)

객체 생성 : ST_PointFromText('POINT(x y)', [SRID])

# WKB를 이용한 공간데이터 생성
ST_PointFromWKB(BinaryData)

# LINESTRING 타입
WKT 포맷 : LINESTRING(x0 y0, x1 y1, x2 y2, ...)

객체 생성 : ST_LineStringFromText('LINESTRING(x0 y0, x1 y1, x2 y2, ...)')

#POLYGON 타입
WKT 포맷 : POLYGON((x0 y0, x1 y1, x2 y2, x0 y0))

객체 생성 : ST_PolygonFromText('POLYGON((x0 y0, x1 y1, x2 y2, x0 y0))')

# MULTIPOINT 타입
WKT 포맷 : MULTIPOINT(x0 y0, x1 y1, x2 y2)

객체 생성 : ST_MultiPointFromText('MULTIPOINT(x0 y0, x1 y1, x2 y2)')

# MULTILINESTRING 타입
WKT 포맷 : MULTILINESTRING((x0 y0, x1 y1),
                           (x2 y2, x3 y3))

객체 생성 : ST_MultiLineStringFromText('MULTILINESTRING((x0 y0, x1 y1),(x2 y2, x3 y3))')

# MULTIPOLYGON 타입
WKT 포맷 : MULTIPOLYGON(((x0 y0, x1 y1, x2 y2, x0 y0)),
				        ((x0 y0, x1 y1, x2 y2, x0 y0)))
                        
객체 생성 : ST_MultiPolygonFromText('MULTIPOLYGON(((x0 y0, x1 y1, x2 y2, x0 y0)),
                                                  ((x0 y0, x1 y1, x2 y2, x0 y0)))')

# GEOMETRYCOLLECTION 타입
WKT 포맷 : GEOMETRYCOLLECTION(POINT(x0 y0), POINT(x1 y1), LINESTRING(x0 y0, x1 y1))

객체 생성 : ST_GeometryCollectionFromText('GEOMETRYCOLLECTION(POINT(x0 y0), 
                                                             POINT(x1 y1), 
                                                             LINESTRING(x0 y0, x1 y1))')

 

다른 데이터베이스 서버로부터 OpenGIS 표준 WKB를 가져왔다면

ST_PointFromWKB()나 ST_PolygonFromWKB() 함수를 이용해 MySQL 서버에서 처리 가능한 공간 데이터 객체 생성 가능

 

SRID은 기본값이 0으로 설정됨

 

 

ST_ 접두사가 안붙은 함수들은 OpenGIS 표준이 아니기에 ST_접두사를 가진 함수들을 우선 사용

 

각 공간 인스턴스에는 SRID(공간 참조 식별자)가 있습니다. SRID는 SQL 데이터베이스 엔진 공간 데이터의 평면 지구 매핑 또는 둥근 지구 매핑에 사용되는 특정 타원을 기반으로 하는 공간 참조 시스템에 해당합니다.

 

SRID GPT ver

POINT, LINESTRING, POLYGON 같은 공간 데이터가
어떤 좌표계/지도 투영법을 기준으로 정의되었는지를 나타내는 ID 

 

• SRID = 0
  • 좌표계 정보 없음 (단순한 x, y 좌표값만 저장).
  • MySQL에서 기본값.
• SRID = 4326 (WGS 84)
  • GPS에서 사용하는 위도/경도 좌표계.
  • 보통 위도(lat), 경도(lon)를 저장할 때 많이 사용.
  • 예: POINT(127.0 37.5) → 서울 좌표.
• SRID = 3857 (Web Mercator)
  • 구글맵, 네이버지도, 카카오맵 같은 웹 지도 서비스가 쓰는 투영 좌표계.
  • 단위가 미터

 

 

공간 데이터 조회

공간 데이터 조회 방법은 여러가지가 존재

공간 데이터 타입과 관계 없이 

ST_AsText( loc )

ST_AsWKT( loc )

 

ST_AsBinary( loc )

ST_AsWKB( loc )

 

함수를 이용해 조회 가능

 

아무런 함수를 사용하지 않고 공간 데이터 칼럼 조회시 mysql 서버가 내부적으로 사용하는 이진 포맷 공간 데이터 확인 가능

 

실습은 더보기

 

데이터를 저장할떄는 POINT, SRID를 넣을때는 ST_SRID 함수를 사용

 

처음이 mysql 서버가 내부적으로 사용하는 이진 포맷 공간 데이터

두번째가 WKT

세번째가 WKB

 

 

 

공간 데이터의 각 속성을 구분해서 조회

ST_SRID( loc )    정수반

------------------- 아래는 DOUBLE 타입 값 반환

ST_X( loc )         

ST_Y( loc )

ST_Latitude( loc )

ST_Longitude( loc )

 

ST_X() 함수와 ST_Latitude() 함수는 지리 좌표 시스템에 정의된 첫 번째 축의 값을 반환

ST_Y() 함수와 ST_Longitude() 함수는 두 번째 축의 값을 반환

 

ST_X()와 ST_Y() 함수는 SRID와 무관하게 사용 가능

ST_Latitude()와 ST_Longitude() 함수는 위도와 경도를 사용하는 좌표 시스템의 공간 데이터에서만 사용 가능

 

 

 

LINESTRING, MULTILINESTRING 타입 속성 함수

ST_StartPoint(@line) : 선의 시작점(POINT) 반환

ST_EndPoint(@line) : 선의 끝점(POINT) 반환

ST_PointN(@line, N) : 선의 N 번째 점(POINT) 반환

ST_IsClosed(@line) : 선이 닫혀 있는지 (시작점=끝점) 확인 (0 또는 1)

ST_Length(@line) : 선의 총 길이 계산 및 반환.ST_NumPoints(@line)선을 구성하는 점의 총 개수 반환.

ST_Length()함수는 라인의 전체 길이를 반환

 

SRID 를 설정 하지 않았으므로 0이고, 단순히 카테시안 평면상의 거리 계산임, 따라서 어떤 단위도 없음

WGS 84 좌표계인 4326으로 설정하면 다음과 같이 미터 단위의 거리 합이 표시

 

미터 단위의 거리 합

 

하지만 ST_Length()함수는 지구가 구면체인 것을 가정하지 않은 결과임

따라서 이를 고려한 ST_Distance_Sphere() 함수를 사용해야함

Polygon

ST_Area(@poly) : 주어진 Polygon 또

는 MultiPolygon 지오메트리의 면적을 계산하여 반환

ST_AsText(g) : 지오메트리 객체를 WKT(Well-Known Text) 형식의 문자열로 변환하여 반환

ST_ExteriorRing(@poly) : Polygon의 외곽 링(Exterior Ring)을 나타내는 LineString 지오메트리를 반환

ST_InteriorRingN(@poly, N) : Polygon의 N 번째 내부 링(Interior Ring)을 나타내는 LineString 지오메트리를 반환

ST_NumInteriorRings(@poly) : Polygon 내부의 홀(Hole) 또는 내부 링의 개수를 반환

 

POLYGON 데이터의 중심점을 찾는 ST_Centroid() 함수도 있지만 아직 WGS84 구면 좌표계의 위경도 좌표를 지원 안함

 

 

 

참고 

WGS84 (4326) 기준 위도 - 경도 순으로 인식

🔖 참조

microsoft_learn : SRID