[MyBatis] MySQL - POLYGON 타입 데이터 쿼리

 

지도기반 검색을 구현하면서 새로운걸 많이 알게되는데, MySQL에 폴리곤 타입이 있다니!

 

Polygon 과 MultiPolygon에서 사용할 수 있는 기능들은 아래를 참고하면 된다.

https://dev.mysql.com/doc/refman/5.7/en/gis-polygon-property-functions.html

 

일단 내가 하고자 하는건

1. 폴리곤 타입의 구역 정보(zone)를 DB에 저장해두고 그걸 지도에 예쁘게 보여주는 것
2. 특정 좌표가 속하는 zone을 찾는 것
   
   • 예를들어 시/도의 폴리곤들을 zone에 저장해두었다면, "잠실어딘가"의 좌표로 질의해서 "서울특별시"라는 zone 정보를 찾는 것
3. zone에 해당하는 장소 데이터(place)를 검색하는 것
   
   • 장소데이터는 이미 가지고 있는 정보이고, 좌표를 포함한다.
   • 예를들면, "서울특별시"라는 zone에 있는 병원타입의 place들을 찾는 것

 

인데, 쿼리들은 검색하면 아-주 잘 나오는데 매퍼는 잘 안나와서 이리저리 그냥 조합..함

 

DB 테이블은 아래와 같이 구성된다.

create table zone
(
    zone_seq bigint auto_increment primary key,
    zone_name varchar(100) not null,
    zone_polygon polygon null
);

create table place
(
    place_seq bigint auto_increment primary key,
    place_type_code varchar(20) not null,
    place_name varchar(200) not null,
    latitude decimal(11,8) not null,
    longitude decimal(11,8) not null
);

 

1. 폴리곤 데이터 insert 하기

폴리곤들은 아래와 같은 쿼리로 insert하는데,

INSERT INTO zone (zone_name, zone_polygon)
VALUES (
           '쏘댕구역',
           POLYGONFROMTEXT('POLYGON((37.3695862 127.1283285,
                                                                   37.3703351 127.1274986,
                                                                   37.3704658 127.1273503,
                                                                   37.3678142 127.1198614,
                                                                   37.3675945 127.11959,
                                                                   37.3673733 127.1193852,
                                                                   37.3697091 127.1294679,
                                                                   37.3696397 127.1293714,
                                                                   37.3693546 127.1290282,
                                                                   37.369277 127.128936,
                                                                   37.3691434 127.1288203,
                                                                   37.3691417 127.128819,
                                                                   37.3695862 127.1283285
           ))')
);

매퍼에는

<insert id="insertZone" parameterType="com.ssodang.place.model.Zone">
           INSERT INTO zone
           (
                      zone_name
                      , zone_polygon
           )
           VALUE
           (
                      #{zoneName}
                     , POLYGONFROMTEXT(
                      <foreach collection="zonePolygon" item="polygon" open="'POLYGON((" close="))'" separator=",">
                                 ${polygon.latitude} ${polygon.longitude}
                      </foreach>
                     )
           )
</insert>

요렇게 쓰면 된다.

 

zonePolygon은 자바에 있는 Point를 쓰면 되어서 List<Point> 이렇게 쓰면 되고, 그 경우 polygon.x polygon.y 로 쓰면 된다.

나는 latitude longitude 명시하는게 안헷갈려서 따로 자료구조를 만들었다.

 

근데 코드블럭 옛날에는 그냥 인텔리제이 복붙하면 예쁘게 나왔던거같은데?ㅜㅜ..???

 

2. 특정 좌표로 해당되는 polygon 데이터 select 하기

그럼 이제 특정 좌표로 해당되는 polygon row를 찾자.

(내 경우는 사용자가 자기 위치에 해당하는 zone을 조회하는거였음)

쿼리는

SELECT zone_seq, zone_name, ASTEXT(zone_polygon) AS zone_polygon_str
FROM zone
WHERE MBRContains(zone_polygon, GeomFromText('Point(37.439126 127.164805)'));

매퍼는

<select id="selectZone" resultMap="zone">
           SELECT zone_seq, zone_name, ASTEXT(zone_polygon) AS zone_polygon_str
           FROM zone
           WHERE MBRContains(zone_polygon, GeomFromText(<trim prefix="'Point(" suffix=")'">${latitude} ${longitude}</trim>))
</select>

zone_polygon_str로 받은 건 위에 언급한 것처럼 따로 자료구조를 만들어 써서 그걸로 변환하기 위해.

 

++ 수정

검색결과 폴리곤안에 좌표가 안들어가는 케이스를 종종 발견했다.

정확도가 떨어지는 느낌인가 했는데, 검색하다보니 

https://stackoverflow.com/questions/39582184/mysql-function-mbrcontains-is-not-accurate

Mysql function MBRContains is not accurate

그렇다고 합니다!

MBRContains로 셀렉한 결과에 원하는 결과가 없던건 아닌데, 좌표가 속하는 zone과 속하지 않는 몇몇 zone들이 같이 셀렉됨

그래서 ST_CONTAINS로 바꿔보니 이제 좌표가 정확히 폴리곤 안에 속하는 데이터들로 잘 셀렉해준다!

          SELECT zone_seq, zone_name, ASTEXT(zone_polygon) AS zone_polygon_str
          FROM zone
          WHERE ST_CONTAINS(zone_polygon, GeomFromText(<trim prefix="'Point(" suffix=")'">${latitude} ${longitude}</trim>))

근데 데이터그립에서 직접 쿼리할때도 약간 느리다 싶어 까리했는데, 개발서버에 올려서 돌려보니 쿼리 타임아웃.....

여러번 시도해보고 쿼리플랜도 뜨고 비교해보니, MBRContains 보다 ST_CONTAINS가 훨-씬! 느리다.... 뭐지 왜지...

플랜에는 그냥 둘 다 풀스캔인데, MBRContains는 1초도 안걸리는데 ST_CONTAINS는 2초가 넘게 걸린다.

(zone에 row는 약 8천개)

결국 이렇게 저렇게 해보다가 JOIN을 쓰고 MBRContains를 썼다. (마이바티스 매퍼 아니고 실제 돌려본 쿼리)

          SELECT s.zone_seq, s.zone_name
          FROM zone s
          JOIN (
                   SELECT zone_seq
                           , ST_CONTAINS(zone_polygon, POINT(37.38921525741821, 127.12584136469891)) AS is_contains
                   FROM zone
          ) cs ON s.zone_seq = cs.zone_seq
          WHERE MBRContains(zone_polygon, GeomFromText('Point(37.38921525741821 127.12584136469891)'))
          AND !ISNULL(cs.is_contains) AND cs.is_contains > 0;

MBRContains로 셀렉한 결과에 이미 원하는 결과 row가 존재하므로 그 여러개중에 정확도를 ST_CONTAINS가 높이도록?

근데 이런 이상한 쿼리로 정확하고 빠른 결과를 얻음.

이상해..... 무서워... 아직도 의문이라서 쿼리 검수 올림........ DBA님을 믿습니다..

안개속이야? 명확한게 없어... 계속 감으로 뒤져가며 납땜중... 좌표랑 계속 싸우다간 미칠지도 몰라!!!!! ㅠ_ㅠ

 

++ ST_CONTAINS Data truncation: Invalid GIS data provided to function st_within. 에러

실제로 적용하고 돌려봤는데, 몇건의 데이터에서 위와같은 오류가 발생했다.

검색하면 온통 펑션을 뭘로 바꾸라고 되어있는데, 다른 좌표케이스는 잘 도는데 특정영역만 저러는게 이상해서 계속 구글링구글링링

어디선가 찾았다! 폴리곤 좌표가 올바르지 않은 row가 걸리면 그럴 수 있다고!

근데 폴리곤이 완성되지 않으면, 예를 들어 시작값과 끝값이 다른경우 insert부터 실패나던데.. 이건 또 무슨 케이스인가요 ㅠㅠ

하다가 문득 중복값이 있나 찾아보았다. (무려 엑셀로 ㅋㅋㅋㅋㅋㅋㅋㅋㅋㅋㅋㅋ)

 

있다......... 삑사리 점...

내 경우는 폴리곤에서 저렇게 완전일치하는 점 하나는 지워도 데이터가 문제되지 않는 케이스라

과감히 데이터 insert 시 중복 좌표를 제거하는 필터 로직을 추가했다.

 

잘 된 다 !

 

3. polygon에 속하는 place 데이터 select 하기

마지막 미션은 사실 검색엔진으로 해결하려고 했는데, 폴리곤 데이터가 너무 길어져서 (폴리곤 사이즈 최고 긴게 5만개가 넘음....)

처리가 안된다고 답변받고 부랴부랴 DB 조회로 바꿨는데ㅜ_ㅜ

내가 쓰고있는 MySQL 버전이 5.7.15인데 여기서 몇가지 크리티컬 버그들이 있다고 하는데다 성능도 걱정되고....

다행히도 이게 1년에 두번만 업데이트하는 데이터라서 아예 마이그레이션 돌려서 zone 테이블에 place_seqs 컬럼을 추가해서 저장하기로..

 

          SELECT p.place_seq, p.place_type_code, p.name, p.latitude, p.longitude
          FROM place p
          WHERE ST_CONTAINS(ST_GEOMFROMTEXT( 'POLYGON(( 34.4561244 126.6466466 ,
                                                                                                      34.4568543 126.6465755 ,
                                                                                                      34.3943053 126.6200031 ,
                                                                                                      34.3945487 126.6201445 ,
                                                                                                      34.394587 126.6201612 ,
                                                                                                      34.3947171 126.6202987 ,
                                                                                                      34.3948426 126.6204503 ,
                                                                                                      34.3949127 126.620536 ,
                                                                                                      34.3950711 126.6206766 ,
                                                                                                      34.3952012 126.6208492 ,
                                                                                                      34.4176665 126.6509135 ,
                                                                                                      34.4176655 126.6509726 ,
                                                                                                      34.4176849 126.6510458 ,
                                                                                                      34.4555647 126.6467157 ,
                                                                                                      34.4561244 126.6466466 ))'
                                               ), Point(p.latitude, p.longitude)
          );

이제 뭐 위에꺼랑 똑같은거지만 ㅎ_ㅎ

<select id="selectPlacesByPolygon" resultMap="placeInfo">
SELECT p.place_seq
            , p.place_type_code
            , p.name
            , p.latitude
            , p.longitude
FROM place p
WHERE ST_CONTAINS(
            ST_GEOMFROMTEXT(
            <foreach collection="polygonList" item="polygon" open="'POLYGON((" close="))'" separator=",">
                        ${polygon.latitude} ${polygon.longitude}
            </foreach>
            ),
            Point(p.latitude, p.longitude)
)
</select>

 

 

끝!

 

좌표친구들이랑 친해지기 어려운데 재미는 있넹 ㅋㅋ