쿠...sal: [컴][db] MySQL의 spatial data types, 공간 데이터 타입

공간데이터 / rdbms 공간 타입 저장 / 기하학 정보 저장 /

MySQL의 spatial data types, 공간 데이터 타입

MySQL 에서는 OGC specification 을 따라서, 공간관련 확장(spatial extension)을 구현했다. 이것은 SQL with Geometry Types 의 부분집합이다. 다음 3가지를 지원한다.

data types : 공간 값을 표현하는 data types
functions: 공간 값들을 다룰 수 있는 함수들
spatial indexing: 공간 칼럼들의 access time을 증가시켜줄 index 들

지원하는 data types

GEOMETRY, GEOMETRYCOLECTTION : GEOMETRY 가 부모 class 이다.(참고) Geometry type 은 모든 다른 spatial type 을 포함할 수 있다.
POINT , MULTIPOINT
LINESTRING, MULTILINESTRING
POLYGON, MULTIPOLYGON

table 생성

다음과 같이 생성할 수 있다. 이 때 SRID 를 사용하는데, 이것이 없어도 되지만, 이것이 없으면, spatial index 를 만들수 었다. 그리고 SRID 가 정해지면, insert 시에 SRID 도 같이 명시해서 insert 해줘야 한다.

CREATE TABLE geom (
    p POINT SRID 0,
    g GEOMETRY NOT NULL SRID 4326
);

query 에서 geometry object들을 표현하기 위해서 2가지 공간데이터 포맷이 사용되어진다. MySQL 내부적으로 geometry 값들을 저장할 때는 WKB format가 비슷한 format을 사용한다. 그러나 똑같진 않다. 첫4byte 가 SRID 를 가리킨다.(참고: Internal Geometry Storage Format)

WKT format(Wll-known Text) : WKT 는 geometry data를 ASCII 모양으로 교환하기 위해 디자인됐다.
WKB format(Wll-known Binary) : geometry data 를 BLOB같은 binary stream들로 교환하기 위해 사용된다. 여기 에서 WKB format에 대한 설명을 보면 된다. 대략적으로 이야기하면, 0101000000000000000000F03F000000000000F0BF 이런식으로 표현되는 방식이다.

WKT

아래처럼 POINT(15 20) text 를 ST_GeomFromText() 를 사용해서 Point object로 변환한다. 그리고 여기서 ST_X() 를 이용해서 X 값을 얻어내는 것이다.

mysql> SELECT ST_X(ST_GeomFromText('POINT(15 20)'));
+---------------------------------------+
| ST_X(ST_GeomFromText('POINT(15 20)')) |
+---------------------------------------+
|                                    15 |
+---------------------------------------+

ST_IsValid()

from : 11.4.4 Geometry Well-Formedness and Validity

잘못된 기하학적 형상(geometrically invalid geometries)을 insert, select, update 할 수 있지만 구성이 잘 되어 있어야 한다.(syntactically well-formed).
계산 비용 때문에 MySQL은 기하학적 타당성(geometric validity)을 명시적으로 확인하지 않는다.
공간 계산(Spatial computations)은 잘못된 기하학의 몇몇 사례를 감지하고 오류를 발생시킬 수 있지만, invalidity 를 발견하지 않은채로 undefined result를 return할 수도 있다.
기하학적으로 유효한 geomery들이 필요한 애플리케이션은 ST_IsValid() 함수를 사용하여 geometry들을 확인해야 합니다.

SRS 지원(Spatial Reference System)

공간데이터를 윈한 SRS(Spatial Reference System, 공간 참조 시스템)은 지리적인 위치들을 위한 좌표기반의 시스템이다.

projected SRS 는 평평한 표면에 지구본을 투영한 것. 그래서 경도, 위도가 아니라 미터,피트등으로 좌표가 표시된다. 이때 세계는 타원위에 있다. (흔히 보는 세계지도를 생각하면 된다. -> 지도(map)예)
geographic SRS 은 non-projected SRS 이다. 위도-경도 좌표로 타원위에 표시된다.
MySQL에서 ’SRID 0으로 표시된 SRS’는 축에 단위(unit)가 할당되지 않은 무한 데카르트 평면(Cartesian plane)을 나타낸다.
projected SRS 와 다르게, 그것은 지리적으로 참조되지 않으며 반드시 지구를 나타내는 것은 아니다.
그것은 무엇이든 사용할 수 있는 추상적인 평면(abstract plane)이다.
SRID 0은 MySQL의 공간 데이터에 대한 기본 SRID 이다.

다음처럼 query 를 날리면, SRS 정보를 확인할 수 있다.

SELECT * FROM INFORMATION_SCHEMA.ST_SPATIAL_REFERENCE_SYSTEMS

MariaDB에서는 [SPATIAL_REF_SYS Table](https://mariadb.com/kb/en/information-schema-spatial_ref_sys-table/)를 사용해야 한다. 그리고 SRID 0 이 아니라 SRID -1 값이 할당되지 않은 SRID 값으로 보인다.

SELECT * FROM INFORMATION_SCHEMA.SPATIAL_REF_SYS;

사용법

CREATE TABLE geom (g GEOMETRY);


ALTER TABLE geom ADD pt POINT;
ALTER TABLE geom DROP pt;

INSERT INTO geom VALUES (ST_GeomFromText('POINT(1 1)'));

SET @g = 'POINT(1 1)';
INSERT INTO geom VALUES (ST_GeomFromText(@g));

SELECT ST_AsText(g) FROM geom;
SELECT ST_AsBinary(g) FROM geom;

index 생성

SPATIAL INDEX 는 R-tree 를 생성한다. 그냥 일반적인 B-tree index 도 사용할 수 있는데, 이것은 특정 값을 찾는곳에서는 유용하다. 그런데 범위를 scan 하는 용도에는 적합하지 않다.

spatial index는 geometry의 최소 경계 직사각형(minimum bounding rectangle, MBR)을 사용하여 만든다. 대부분의 geomery들에서 MBR은 geometry들을 둘러싸는 가장작은 직사각형이다. 수평선, 수직선에서 MBR은 ‘선’(linestring)이 되고, ’점(point)’에서 MBR은 ’점’이 된다.

CREATE TABLE geom (g GEOMETRY NOT NULL SRID 4326, SPATIAL INDEX(g));

// spatial index 생성시 column 은 NOT NULL 이어야만 한다.
ALTER TABLE geom ADD SPATIAL INDEX(g);  // add index
CREATE SPATIAL INDEX g ON geom (g)  // 위 add index와 같은 것.

ALTER TABLE geom DROP INDEX g;
DROP INDEX g ON geom;

spatial index 가 되어 있으면 아래처럼 query 를 날릴 수 있다. 참고로 spatial index가 없어도 아래 query 는 동작한다. 다만 더 느리다.

mysql> SET @poly =
    -> 'Polygon((30000 15000,
                 31000 15000,
                 31000 16000,
                 30000 16000,
                 30000 15000))';
mysql> SELECT fid,ST_AsText(g) FROM geom WHERE
    -> MBRContains(ST_GeomFromText(@poly),g);

References

MySQL :: MySQL 8.0 Reference Manual :: 11.4 Spatial Data Types

쿠...sal

[컴][db] MySQL의 spatial data types, 공간 데이터 타입