[PostGRES] 다른 소스의 공간 데이터 병합을 위한 text similarity 기반 전처리

Task:

POI 중에서 음식점들을 골라서 뽑아내고 싶은데, 별도의 용도 지정이 없다.

그래서 음식점 인허가 데이터랑 비교해서 음식점인 포인트들은 제거하고자 한다.

빨간색: NGII 에서 취득한 POI 데이터 / 파란색: 지방인허가데이터취득한 음식점 인허가 데이터. 고디바 삼청점과 고디바삼청점이 건방지게 서로 다른 척을 하고 있다.

서로 다른 소스에서 취득한 공간 데이터의 경우,

좌표도 미세하게 다르고, 이름도 미세하게 다르다.

 

위와 같은 상황에서 겹치는 포인트를 하나하나 체크하면서 제거하는 것은 매우 번거로운 일.

SQL 의 buffer join 과 text similarity 는 위와 같은 문제를 쉽게 해결할 수 있게 도와준다.

 

 

SHP to Database

1. 데이터베이스 탭 -> DB 관리자 (Ctrl + Shift + D 단축키 로도 이동 가능)

2. 기존에 연동시켜둔 PostGIS 로 연결하여 원하는 Schema 선택

3. 레이어/파일 불러오기로 이동

 

4. 입력 탭에 이동하고자 하는 shp 파일을 선택

5. 스키마 및 테이블 선택 (Tip. 테이블 명을 모두 영문 소문자로 입력하면 pgAdmin 에서 테이블 명에 일일히 큰따옴표를 지정해야 하는 불편함을 줄일 수 있음)

6. 기본 키, 도형 열, 좌표계 및 인코딩을 선택. (인코딩은 UTF-8을 추천, SRID 는 5179를 추천)

 

7. pgAdmin 에서 입력한 데이터베이스를 확인

 

Buffer Join

POI 데이터 근방 25m 이내에 있는 음식점들 중에서 이름이 비슷한 데이터들을 뽑아내고자 함.

POI 근처에 찍힌 인허가 데이터 점들 중에서 이름이 비슷한 녀석이 있으면 POI 데이터에서 제외하기 위해서.

 

1. Buffer Join Query

Subquery 부터 차근차근 살펴보자면, 

SELECT id AS buffer_id,
		ST_Buffer(geom, 25) AS buffer_geom,
        geom,
        poi_nm
FROM
	"Temp"."POI"

POI 테이블에서 각 id 를 buffer_id로 하여 25m 짜리 버퍼 geometry 컬럼을 추가하고, geometry 와 poi 이름 칼럼까지 추가.

 

SELECT
    POI.buffer_id AS buffer_id,
    POI.poi_nm, POI.geom, rest.title
FROM
    "Temp"."bukchon_rest" rest
JOIN
    (
        SELECT
            id AS buffer_id,
            ST_Buffer(geom, 25) AS buffer_geom, 
			geom, poi_nm
        FROM
            "Temp"."POI"
    ) POI
ON
    ST_Within(rest.geom, POI.buffer_geom)

그리고 POI 기준 25m 버퍼로 형성된 geometry 컬럼에 포함되는 인허가 데이터의 이름을 LEFT JOIN.

 

그 결과는 

LEFT JOIN 의 폭력적인 ROW number. 하지만 우리는 미미미하우스 같은 녀석들을 잡아내기 위해 LEFT JOIN 의 힘을 빌렸다. 이제보니 25m도 너무 컸을 수도.

하지만 공간데이터는 소스에 따라서 좌표도, 위치도, 주소도 천차만별이므로 믿지 말자.

POI 근방의 인허가 데이터들의 이름을 모아두었으니, 이제 이름이 비슷한 row 들을 뽑아내야 한다.

 

Text Similarity

Text similarity 는 pg_trgm 이라는 PostGRES extension 을 기반으로 측정된다.

similarity('안녕하세요', '안녕하신가') 함수는 두 텍스트 사이의 유사도를 0~1 사이의 숫자로 산출한다.

몇 개의 character 가 바뀌어야 하는지를 기준으로 수치화되며, 구체적인 사항은 아래 링크를 참고.

PostgreSQL: Documentation: 16: F.35. pg_trgm — support for similarity of text using trigram matching

F.35. pg_trgm — support for similarity of text using trigram matching

우선 pg_trgm extension 은 아래 명령어로 module 을 불러줘야 한다.

CREATE EXTENSION pg_trgm;

한 번만 불러주면 알아서 pg_trgm 을 가져다 쓰므로 반복해서 쓸 필요는 없다.

 

similarity 함수는 두 텍스트를 집어 넣어서 쓸 수도 있고, 두 text column 을 넣어서 쓸 수도 있다.

 

1. 앞서 만들었던 Buffer Join Table 을 With 로 하여 임시 테이블을 만들고

2. Buffer Join 테이블의 poi_nm 과 title 컬럼을 similarity 함수에 집어 넣은 후에

3. similarity 가 높은 순서대로 buffer_id (poi 데이터) 마다 정렬하면

WITH
buffer_match AS
(SELECT
    POI.buffer_id AS buffer_id,
    POI.poi_nm, POI.geom, rest.title
FROM
    "Temp"."bukchon_rest" rest
JOIN
    (
        SELECT
            id AS buffer_id,
            ST_Buffer(geom, 25) AS buffer_geom, geom, poi_nm
        FROM
            "Temp"."POI" 
    ) POI
ON
    ST_Within(rest.geom, POI.buffer_geom)),
	
ranked_data AS (
    SELECT
        buffer_match.*, similarity(poi_nm, title) as distance,
        ROW_NUMBER() OVER (PARTITION BY buffer_id ORDER BY similarity(poi_nm, title) DESC) AS row_num
    FROM
        buffer_match
)
SELECT
    *
FROM
    ranked_data

요런 함수를 바탕으로 아래와 같은 결과가 나온다.

참고로 ROW_NUMBER 는 partition 마다 similarity 의 순번대로 정렬된 데이터의 순위를 매겨주는 역할이다.

이름이 비슷하면 similarity 가 높게 측정된다.

각 partition 에서 1등 먹은 애들은 이름이 가장 비슷한 애들이라고 보면 된다.

 

이제 

a. similarity 가 0보다 크고

b. 해당 row에서 가장 큰 similarity 를 보이는

데이터들만 골라내기 위해

WHERE
    distance > 0 AND row_num = 1

을 끝에 추가해주면,

 

상당히 아름다운 비쥬얼. 

QGIS보다 빠르게, 파이썬보다 손쉽게 유사한 이름을 가진 음식점들을 가려낼 수 있다.

 

남은 일은 기존 POI 테이블과 위의 유사도 테이블을 LEFT JOIN 하여,

음식점에 해당하지 않는 POI들을 가려내는 작업으로 마무리 할 수 있다.

WITH
buffer_match AS
(SELECT
    POI.buffer_id AS buffer_id,
    POI.poi_nm, POI.geom, rest.title
FROM
    "Temp"."bukchon_rest" rest
JOIN
    (
        SELECT
            id AS buffer_id,
            ST_Buffer(geom, 25) AS buffer_geom, geom, poi_nm
        FROM
            "Temp"."POI" 
    ) POI
ON
    ST_Within(rest.geom, POI.buffer_geom)),
	
ranked_data AS (
    SELECT
        buffer_match.*, similarity(poi_nm, title) as similarity,
        ROW_NUMBER() OVER (PARTITION BY buffer_id ORDER BY similarity(poi_nm, title) DESC) AS row_num
    FROM
        buffer_match
)

SELECT
    POI_raw.id, POI_raw.poi_nm, POI_raw.geom, ranked.title
FROM "Temp"."POI" POI_raw
LEFT JOIN (SELECT * FROM ranked_data WHERE similarity > 0 AND row_num = 1) ranked
ON POI_raw.id = ranked.buffer_id
WHERE ranked.title IS NULL;

음식점이 아닌 POI만 남은 모습.