Mysql 공간테이블 - sshome-25/safeRent-Backend GitHub Wiki
MySQL 공간 데이터 개념 및 함수 가이드
목차
기본 개념
공간 데이터란?
공간 데이터는 지구상의 위치와 관련된 정보를 나타내는 데이터입니다. MySQL은 OpenGIS 컨소시엄(OGC)의 표준을 따라 공간 데이터를 지원합니다.
MySQL 공간 데이터의 특징
- OGC 표준 준수
- 다양한 공간 데이터 타입 지원
- R-tree 기반 공간 인덱싱
- WKT(Well-Known Text)와 WKB(Well-Known Binary) 형식 지원
- SRID(Spatial Reference ID) 기반 좌표계 지원
공간 데이터 타입
MySQL에서 지원하는 공간 데이터 타입은 다음과 같습니다
기본 데이터 타입
| 데이터 타입 | 설명 | 형태 |
|---|---|---|
GEOMETRY |
모든 공간 데이터 타입의 기본 클래스 | 모든 공간 객체 |
POINT |
단일 위치(x, y 좌표) | (x, y) |
LINESTRING |
점들이 연결된 선 | 선 |
POLYGON |
닫힌 선으로 이루어진 다각형 영역 | 면적 |
복합 데이터 타입
| 데이터 타입 | 설명 | 형태 |
|---|---|---|
MULTIPOINT |
여러 개의 POINT 집합 | 점들의 집합 |
MULTILINESTRING |
여러 개의 LINESTRING 집합 | 선들의 집합 |
MULTIPOLYGON |
여러 개의 POLYGON 집합 | 다각형들의 집합 |
GEOMETRYCOLLECTION |
여러 공간 객체의 집합 | 여러 객체 집합 |
데이터 타입 선언 예시
-- POINT 타입 컬럼 선언
coordinates POINT NOT NULL SRID 4326
-- POLYGON 타입 컬럼 선언
area_boundary POLYGON NOT NULL SRID 4326
-- 공간 데이터 테이블 생성 예시
CREATE TABLE properties (
id INT PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(255),
location POINT NOT NULL SRID 4326,
SPATIAL INDEX(location)
);
공간 데이터 함수
MySQL에서 제공하는 주요 공간 데이터 함수는 다음과 같이 구분할 수 있습니다
데이터 변환 함수
| 함수 | 설명 | 서명 |
|---|---|---|
ST_GeomFromText |
WKT 형식의 문자열을 공간 객체로 변환 | ST_GeomFromText(wkt[, srid]) |
ST_PointFromText |
WKT 형식의 문자열을 POINT 객체로 변환 | ST_PointFromText(wkt[, srid]) |
ST_LineFromText |
WKT 형식의 문자열을 LINESTRING 객체로 변환 | ST_LineFromText(wkt[, srid]) |
ST_PolyFromText |
WKT 형식의 문자열을 POLYGON 객체로 변환 | ST_PolyFromText(wkt[, srid]) |
ST_GeomFromWKB |
WKB 형식의 바이너리를 공간 객체로 변환 | ST_GeomFromWKB(wkb[, srid]) |
ST_AsText |
공간 객체를 WKT 형식으로 변환 | ST_AsText(g) |
ST_AsBinary |
공간 객체를 WKB 형식으로 변환 | ST_AsBinary(g) |
우리가 사용할 함수는 ST_PointFromText 와 ST_AsText를 주로 활용합니다. 이때의 WKT값 이란 point(위도, 경도) 입니다.
객체 속성 함수
| 함수 | 설명 | 서명 |
|---|---|---|
ST_Dimension |
객체의 차원 반환 (0=점, 1=선, 2=면) | ST_Dimension(g) |
ST_GeometryType |
객체의 타입 반환 | ST_GeometryType(g) |
ST_SRID |
객체의 SRID 값 반환 | ST_SRID(g) |
ST_X |
POINT 객체의 X 좌표 반환 | ST_X(p) |
ST_Y |
POINT 객체의 Y 좌표 반환 | ST_Y(p) |
ST_Length |
LINESTRING 객체의 길이 반환 | ST_Length(ls) |
ST_Area |
POLYGON 객체의 면적 반환 | ST_Area(poly) |
ST_NumPoints |
LINESTRING의 점 개수 반환 | ST_NumPoints(ls) |
ST_NumInteriorRings |
POLYGON의 내부 링 개수 반환 | ST_NumInteriorRings(poly) |
공간 인덱스
공간 인덱스는 공간 데이터에 대한 빠른 검색을 가능하게 합니다.
공간 인덱스 생성
-- 테이블 생성 시 공간 인덱스 추가
CREATE TABLE locations (
id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(255),
location POINT NOT NULL SRID 4326,
SPATIAL INDEX(location)
);
-- 기존 테이블에 공간 인덱스 추가
ALTER TABLE locations ADD SPATIAL INDEX(location);
공간 인덱스 요구 사항
- 인덱싱할 컬럼은 NOT NULL로 선언되어야 함
- MySQL 8.0 이상에서는 SRID 값이 명시되어야 함
- InnoDB 엔진 사용 시 모든 레코드의 SRID 값이 동일해야 함
- R-tree 형태로 인덱스가 구현됨
공간 관계 연산
공간 객체 간의 관계를 확인하는 함수들입니다.
주요 공간 관계 함수
| 함수 | 설명 | 서명 | 반환값 |
|---|---|---|---|
ST_Contains |
A가 B를 완전히 포함하는지 | ST_Contains(g1, g2) |
1 또는 0 |
ST_Within |
A가 B 내에 완전히 포함되는지 | ST_Within(g1, g2) |
1 또는 0 |
ST_Intersects |
A와 B가 교차하는지 | ST_Intersects(g1, g2) |
1 또는 0 |
ST_Overlaps |
A와 B가 일부 겹치는지 | ST_Overlaps(g1, g2) |
1 또는 0 |
ST_Disjoint |
A와 B가 겹치지 않는지 | ST_Disjoint(g1, g2) |
1 또는 0 |
ST_Equals |
A와 B가 공간적으로 동일한지 | ST_Equals(g1, g2) |
1 또는 0 |
ST_Touches |
A와 B가 접촉하는지(내부는 겹치지 않음) | ST_Touches(g1, g2) |
1 또는 0 |
ST_Crosses |
A와 B가 교차하는지(특정 방식으로) | ST_Crosses(g1, g2) |
1 또는 0 |
MBR(Minimum Bounding Rectangle) 관계 함수
더 빠르지만 덜 정확한 MBR 기반 함수들도 있습니다
| 함수 | 설명 | 서명 |
|---|---|---|
MBRContains |
A의 MBR이 B의 MBR을 포함하는지 | MBRContains(g1, g2) |
MBRWithin |
A의 MBR이 B의 MBR 내에 있는지 | MBRWithin(g1, g2) |
MBRIntersects |
A와 B의 MBR이 교차하는지 | MBRIntersects(g1, g2) |
MBROverlaps |
A와 B의 MBR이 겹치는지 | MBROverlaps(g1, g2) |
거리 계산 함수
객체 간 거리를 계산하는 함수들입니다.
| 함수 | 설명 | 서명 |
|---|---|---|
ST_Distance |
두 객체 간의 최단 거리 계산(평면) | ST_Distance(g1, g2) |
ST_Distance_Sphere |
두 객체 간의 구면 거리 계산(미터) | ST_Distance_Sphere(g1, g2) |
ST_DWithin |
두 객체가 지정된 거리 내에 있는지 검사 | ST_DWithin(g1, g2, distance) |
거리 함수 사용 예시
-- 두 지점 간 구면 거리(미터)
SELECT ST_Distance_Sphere(
ST_PointFromText('POINT(127.03 37.50)', 4326),
ST_PointFromText('POINT(127.04 37.51)', 4326)
) AS distance_meters;
-- 반경 500m 내의 인프라 시설 찾기
SELECT name, type
FROM infrastructure
WHERE ST_DWithin(
location,
ST_PointFromText('POINT(127.03 37.50)', 4326),
500
);
공간 데이터 생성 함수
공간 객체를 직접 생성하는 함수들입니다.
| 함수 | 설명 | 서명 |
|---|---|---|
ST_Point |
좌표로 POINT 객체 생성 | ST_Point(x, y) |
ST_LineString |
POINT 배열로 LINESTRING 생성 | ST_LineString(pt_array) |
ST_Polygon |
LINESTRING으로 POLYGON 생성 | ST_Polygon(ls, srid) |
ST_Buffer |
객체 주변에 버퍼 영역 생성 | ST_Buffer(g, distance) |
ST_Union |
두 객체의 합집합 생성 | ST_Union(g1, g2) |
ST_Intersection |
두 객체의 교집합 생성 | ST_Intersection(g1, g2) |
ST_Difference |
첫 객체에서 두번째 객체를 뺀 영역 | ST_Difference(g1, g2) |
ST_ConvexHull |
객체 집합의 볼록 껍질 생성 | ST_ConvexHull(g) |
객체 생성 예시
-- 좌표로 직접 POINT 생성
INSERT INTO locations (name, location)
VALUES ('Office', ST_Point(127.03, 37.50));
-- POLYGON 생성
SET @poly = ST_GeomFromText('POLYGON((0 0, 10 0, 10 10, 0 10, 0 0))');
-- 버퍼 영역 생성 (점 주변 반경 500미터)
SET @circle = ST_Buffer(ST_Point(127.03, 37.50), 500);
좌표계(SRID) 활용
좌표계는 지구 상의 위치를 표현하는 방식을 정의합니다.
주요 SRID
| SRID | 이름 | 설명 | 용도 |
|---|---|---|---|
| 4326 | WGS84 | GPS 좌표계(경도, 위도) | GPS 데이터, 일반적인 지도 |
| 3857 | Web Mercator | 웹 지도 투영 | 구글 맵, 오픈스트리트맵 등 |
| 0 | 지정되지 않음 | 평면 유클리드 좌표계 | 단순 기하학적 연산 |
SRID 변환 함수
| 함수 | 설명 | 서명 |
|---|---|---|
ST_Transform |
한 좌표계에서 다른 좌표계로 변환 | ST_Transform(g, target_srid) |
ST_SRID |
객체의 SRID 값 반환 | ST_SRID(g) |
ST_SetSRID |
객체의 SRID 값 설정 | ST_SetSRID(g, srid) |
SRID 활용 예시
-- WGS84 좌표계로 POINT 생성
SET @point_wgs84 = ST_PointFromText('POINT(127.03 37.50)', 4326);
-- Web Mercator 좌표계로 변환
SET @point_web = ST_Transform(@point_wgs84, 3857);
-- SRID 확인
SELECT ST_SRID(@point_wgs84), ST_SRID(@point_web);
실전 사용 예시
법정동 내 매물 찾기
SELECT p.id, p.address, p.price
FROM properties p, districts d
WHERE d.name = '역삼동'
AND ST_Contains(d.boundary, p.location);
특정 매물 주변 인프라 찾기
SELECT i.name, i.type,
ST_Distance_Sphere(p.location, i.location) AS distance_meters
FROM properties p, infrastructure i
WHERE p.id = 123
AND ST_Distance_Sphere(p.location, i.location) <= 500
ORDER BY distance_meters;
인프라 점수 계산 및 매물 랭킹
SELECT
p.id,
p.address,
p.price,
(
-- 병원 가중치 (2점)
(SELECT COUNT(*) * 2 FROM infrastructure i
WHERE i.type = 'hospital'
AND ST_Distance_Sphere(p.location, i.location) <= 500)
+
-- 버스정류장 가중치 (1점)
(SELECT COUNT(*) FROM infrastructure i
WHERE i.type = 'bus_stop'
AND ST_Distance_Sphere(p.location, i.location) <= 300)
) AS infra_score
FROM
properties p
WHERE
p.location IS NOT NULL
AND EXISTS (
SELECT 1 FROM districts d
WHERE d.name = '역삼동'
AND ST_Contains(d.boundary, p.location)
)
ORDER BY
infra_score DESC,
p.price ASC
LIMIT 10;
성능 최적화 팁
- 공간 인덱스를 항상 사용하세요
- 적절한 거리 제한을 설정하세요
- 복잡한 계산은 중간 결과를 임시 테이블에 저장하세요
- 대용량 데이터에는 페이지네이션(LIMIT)을 적용하세요
- 쿼리 실행 계획(EXPLAIN)을 확인하며 최적화하세요