[Spring] st_distance_sphere을 사용하여 위치기반 조회 구현

당근의 내 동네 설정같이, 내 위치를 기반으로 반경 2~ 5km의  서비스를 조회하는것을 구현하였다.

SET @location = POINT(127.0752881, 37.4921615);
	
desc products;

SELECT *
FROM products
WHERE ST_Distance_Sphere(@location, POINT(longitude, latitude)) <= 5000

 

클라이언트에서 설정한 동네를 가져와서 Point의 longitude와 latitude를 설정해준다. 

이후 상품에 등록된 longitude 와 latitude를 비교하여 N km 내에 있는 상품들을 조회한다.  

 

public List<?> findAllByDistance(CategoryType target, LocationDTO locationDTO, Pageable pageable, String keyword) throws Exception {
        EntityPath<?> entity = null;

        switch (target) {
            case PRODUCT:
                entity = QProduct.product;
                break;
            case CLUB:
                entity = QClub.club;
                break;
            case POST:
                entity = QPost.post;
                break;
            default:
                throw new Exception("잘못된 엔티티 입력");
        }

        PathBuilder<Object> pathBuilder = new PathBuilder<>(Object.class, entity.getMetadata());

        NumberPath<Double> longitudePath = pathBuilder.getNumber("longitude", Double.class);
        NumberPath<Double> latitudePath = pathBuilder.getNumber("latitude", Double.class);
        StringPath titlePath = pathBuilder.getString("title");

        QImage image = QImage.image;

        JPAQuery<?> jpaQuery = jpaQueryFactory
                .select(entity, image)
                .from(entity)
                .leftJoin(image).on(getJoinCondition(entity, image))
                .where(Expressions.numberTemplate(Double.class,
                        "ST_Distance_Sphere(POINT({0}, {1}), POINT({2}, {3}))",
                        locationDTO.getLongitude(), locationDTO.getLatitude(),
                        longitudePath, latitudePath).loe(locationDTO.getSearchRange() * 1000));

        if (keyword != null) {
            jpaQuery.where(titlePath.containsIgnoreCase(keyword));
        }
        switch (target) {
            case PRODUCT:
                jpaQuery.orderBy(QProduct.product.regDate.desc()); // QProduct.product.regDate.desc() 대신 entity.regDate.desc() 사용


                List<Tuple> filterList = (List<Tuple>) jpaQuery.fetch();

                List<Product> products = filterList.stream()
                        .map(tuple -> tuple.get(0, Product.class)) // 첫 번째 엔터티는 Product
                        .collect(Collectors.toList());
                for (Product product : products) {
                    System.out.println(product.toString());
                }
                return products;
            case CLUB:
                entity = QClub.club;
                break;
            case POST:
                entity = QPost.post;
                break;
            default:
                throw new Exception("잘못된 엔티티 입력");
        }
        return null;
    }

 

 

위에 mysql st_distance_sphere 쿼리를 queryDSL로 작성하였다.