Geometry
PostgreSQL에서는 geometry 타입 컬럼과 공간 데이터 계산이 가능한 PostGIS 함수를 제공한다.
CREATE EXTENSION postgis; 위 쿼리를 통해 postgis 확장 모듈을 설치해주어야만 geometry 타입 컬럼이 사용가능하다.
postgis 설치가 완료되면 아래처럼 테이블과 뷰가 생긴다. 삭제하면 postgis를 사용할 수 없으므로 주의하자.
Geometry 타입 컬럼 추가하기
이미 만들어진 테이블에 lat, lng 값이 있다. 그 값을 사용하여 Point 값을 업데이트 할 것이다.
ALTER TABLE 테이블명
ADD COLUMN 추가할컬럼명 GEOMETRY(Point, 4326);좌표계는 4326을 사용한다.
UPDATE 테이블명
SET 추가한컬럼명 = ST_SetSRID(ST_MakePoint(lng, lat), 4326;ST_SetSRID()함수와 ST_MakePoint()함수를 사용해서 값을 넣는다. lng(경도), lat(위도) 순으로 작성하지 않으면 오류가 발생하므로 유의
Geometry 타입 컬럼의 값을 검증하기
SELECT ST_IsValid(컬럼명) from 테이블명;
UPDATE 테이블명 SET 컬럼명 = ST_MakeValid(컬럼명);ST_IsValid() 함수로 값을 검증할 수 있다. 유효한 값이라면 true를 반환한다.
유효하지 않은 값이 있다면, ST_MakeValid() 잘못된 형식을 유효한 형식으로 변환할 수 있다.
업데이트해서 데이터를 잃을 수 있는데 괜찮냐고 묻는다. 확인을 눌러야 진행가능하다.
Geometry 타입 컬럼의 값을 수정하기
lng, lat 컬럼의 값이 잘못되어 있었다고 가정하자.
두 컬럼의 값은 수정하더라도 새로 생성한 geometry 타입 컬럼의 값은 수정되지 않는다.
update store
SET 컬럼명 = ST_SetSRID(ST_MakePoint(lng,lat), 4326)
where id = id값ST_SetSRID()와 ST_MakePoint()함수를 사용해서 값을 다시 변경할 수 있다.
자동으로 Geometry 타입 컬럼의 값을 추가하기
위와 같은 테이블이 만들어진 상태에서 바로 새 행을 추가하려고 하면, 오류가 발생한다.
geometry 타입 컬럼은 null을 허용하지 않기 때문이다.
lng, lat 값만 추가하면 자동으로 geometry 타입 컬럼에도 값이 등록되게끔 하고 싶다면 트리거 함수를 이용하면 된다.
CREATE OR REPLACE FUNCTION 함수명()
RETURNS TRIGGER AS $$
BEGIN
NEW.컬럼명 := ST_SetSRID(ST_MakePoint(NEW.lng, NEW.lat), 4326);
RETURN NEW;
END;
$$ LANGUAGE plpgsql; -- 트리거 함수 생성'$$' 사이에 함수 내용을 작성한다.
LANGUAGE plpsql은 PL/pgSQL(Procedural Language/PostgreSQL)을 사용하겠다는 뜻이다. 이를 사용해서 데이터베이스를 관리 및 조작할 수 있다.
CREATE TRIGGER 트리거명
BEFORE INSERT OR UPDATE ON 테이블명
FOR EACH ROW
EXECUTE FUNCTION 함수명(); -- 위에서 만든 트리거 함수를 호출작성된 함수를 트리거를 지정해서 각 행이 INSERT 또는 UPDATE 될 때, 호출되도록 한다.
두 쿼리를 실행시켜주면 오류 없이 행을 추가할 수 있다.
