[JPA, Query dsl] Distance 쿼리 뻘짓 역사서

기존 : Place Entity 에 latitude, longitude 이 Double 형식으로 위치정보가 저장되어있음.

- > point 형식으로 point ( latitude longitude ) 으로 위치 정보 저장 형식을 변경함

- > DB상에서 구 위에서의 거리를 재는함수를 작성함. 

DELIMITER $$

CREATE
    FUNCTION `u_st_distance_sphere`(`user_point` POINT, `search_point` POINT)
    RETURNS double
BEGIN

    return (6371*acos(cos(radians(ST_X(user_point)))*cos(radians(ST_X(search_point)))*cos(radians(ST_Y(search_point))
        -radians(ST_Y(user_point)))+sin(radians(ST_X(user_point)))*sin(radians(ST_X(search_point)))));
END$$
DELIMITER ;

 

- > spring 상에서 새로 작성한 함수를 포함하는 방언 custom dialect 를 만들어서 적용시켜줌.

this.registerFunction(
        "u_st_distance_sphere",
        new StandardSQLFunction("u_st_distance_sphere", StandardBasicTypes.DOUBLE)

 

- > order by distance 를 수행하기 위해서 query dsl select 문에 Expressions.numberTemplate 으로 함수와 파라미터를 지정하고, .as 으로 컬럼이름을 지정한다.

NumberTemplate<Double> expr = Expressions.numberTemplate(
            Double.class, 
            "u_st_distance_sphere({0}, {1})", 
            userPoint, 
            place.point
        );

!! 문제발생 -> 너가 보낸 데이터를 geometry field 에 넣지 못한다는 에러가 발생함. java 의 Point 와 DB의 Point 형식이 달라서 매칭이 안되는것처럼보임.

 

- > JPA Entity 에서 columnDefinition 을 지정하지않으면 Point 타입은 tinyblob 으로 지정되는데 ( 원래는 point 으로 타입 지정을 했었음 ), DB에 WellKnownText 형식으로 point 를 저장하기로한다. 기존 column 은 drop 함.

update place set point = 
    concat('POINT(', latitude,' ' ,longitude, ')') where id > 0;

 

- > 작성한 u_st_distance_sphere 은 point 타입을 인자로 받기때문에, WKT 을 POINT 형식으로 변환해주어야한다. query dsl Expressions.numberTemplate 에 Template 에서 인자별로 텍스트를 Geometry 형식으로 파싱하는 기본 DB함수 ST_GeomFromText 를 각각 수행해준다.

String pointWKT = String.format("POINT(%s %s)", 
            placeFilterParam.getLatitude(), 
            placeFilterParam.getLongitude());

NumberTemplate<Double> expr = Expressions.numberTemplate(
        Double.class, 
        "u_st_distance_sphere( ST_GeomFromText({0}), ST_GeomFromText({1}) )", 
        pointWKT, 
        place.point);

 

- > 위의 expression 을 사용해서 아래와 같이 쿼리를 수행하면 Double 형식으로 distance 가 잘나온다.

List<PlaceWithDistanceResponse> results = jpaQueryFactory.select(
        new QPlaceWithDistanceResponse(
                place.id, place.kakaoId, place.name, place.rating, place.address, place.latitude, place.longitude, place.viewCount, place.reviewCount, place.pickCount, place.createdAt, place.updatedAt,
                expr.as("distance")
        ))
        .from(place)
        .limit(5)
        .fetch()

 

!! 문제 발생 - > expression 으로 새로생성한 column 은 place 에 없는 column 이라 where 절에서나, order by 절에서 사용이 불가능하다.