[PostgreSQL, Spring Data JPA] earthdistance를 이용하여 주어진 위도/경도에서 근처 장소 조회하기(Native Query)

들어가며

프로젝트를 진행하면서, 지도상의 위치(위도/경도)와 범위(range)값을 받아서 범위 안에 속하는 빵집을 검색 API를 구현해야 했습니다. 현재 사용하고 있는 database는 postgreSQL이어서 postgreSQL의 거리 계산 관련하여 구글링하였습니다.

그 결과, postgreSQL에서 공식적으로 지원하는 earthdistance 모듈을 설치하여 geolocation을 다뤄보기로 했습니다.

• earthdistance는 지구가 타원형이 아닌 완벽한 구의 형태라고 가정하고 사용하는 모듈이기 때문에, 정밀한 거리 계산이 필요하다면 thirdparty에서 제공하는 postGIS를 쓰는 것도 좋은 방법일 것 같습니다.
  • 단, postGIS는 postgreSQL이 공식적으로 지원하지는 않습니다.

earthdistance라는 모듈은 postgreSQL의 모듈이라 표준 SQL이 지원하는 기능이 아닙니다. 이에 따라, 이번 포스팅에서는 native query를 사용하여 프로젝트에 적용하는 것을 알아보도록 하겠습니다.

earthdistance 개념 잡기

earthdistance 모듈은 PostgreSQL에서 공식적으로 제공하는 모듈입니다. 이 모듈은 하기 2가지 방법을 통해 geolocation을 다룹니다. 각 방법에 따른 사용법은 하단에서 설명하도록 하겠습니다.

• Point-Based
• Cube-Based

earthdistance 설치하기

earthdistance 모듈은 create extension 커맨드를 통해 설치할 수 있습니다. postgreSQL 버전에 따라 설치법이 다를 수는 있지만, 필자가 사용하는 postgreSQL 12.8에서는 database의 query console을 활용해 커맨드를 입력하여 설치하였습니다.

datagrip을 통해 earthdistance 모듈 설치 쿼리 날림

create extension if not exists cube;
create extension if not exists earthdistance;

• earthdistance는 cube extension에 따라 달라지기 때문에 point나 cube 둘 중 어떤 것을 사용하여 geolocation data를 다루는지와 상관없이 cube와 earthdistance를 모두 설치해야 합니다.

earthdistance 사용법 (1) - Point-Based Earth Distance

https://www.postgresql.org/docs/12/earthdistance.html

point를 통해 earthdistance를 사용하면 위 사진과 같이 <@> Operator만을 제공합니다. <@> 연산자는 point(longitude, latitude) 형태의 서로 다른 2개의 points 간의 거리 계산을 진행할 수 있습니다.

사용 예시

Point-Based earthdistance 사용 예시

select name, (
    point(b.longitude, b.latitude)<@>point(other_b.longitude, other_b.latitude)
           ) * 1609.344 as distance
from bakeries b,
     lateral (
         select bakery_id, latitude, longitude from bakeries where name = '호랑이'
         ) as other_b
where b.bakery_id <> other_b.bakery_id
order by distance;

• 이때, statute miles를 meters로 변환하여 distance를 계산

Point-Based Earth Distance 장단점

장점	단점
1. PostgreSQL이 공식적으로 지원하는 방법 2. 표준 PostgreSQL의 컬럼 사용 가능 (point 또는 2개의 double형 컬럼을 point로 cast하여 사용) 3. 낮은 복잡성(하나의 function만 제공)	1. earthdistance의 경우 지구를 완전한 구 형태로 가정 2. 계산시 단위를 변경할 수 없음(위 예시처럼 직접 전환 필요)

earthdistance 사용법 (2) - Cube-Based Earth Distance

https://www.postgresql.org/docs/12/earthdistance.html

Cube-Based earthdistance는 Point-Based보다 유연합니다. 가장 큰 차이점은 Cube 방식을 사용하면 earth() 함수를 사용자가 override할 수 있다는 점입니다.

• Point-Based에서는 계산시 단위를 변경할 수 없었는데, Cube-Based에서는 override를 활용해 단위 변경이 가능합니다.
  • Point-Based에서는 반경(radius)밖에 사용할 수 없어 따로 변환을 했지만, Cube-Based에서는 meter를 바로 사용할 수 있습니다.
• earthdistance에서 Cube-Based 방식을 사용하여 거리 계산을 수행할 때에는 earth_distance() 함수를 사용합니다.
  • 이 함수는 2개의 earth type 좌표(latitude, longitude, distance from the center of the earth)를 받아 계산합니다.
  • latitude, logitude 좌표를 earth type 좌표로 변환하기 위해 ll_to_earth() 함수를 사용할 수 있습니다.

Cube-Based earthdistance 사용 예시

select name, earth_distance(
    ll_to_earth(b.latitude, b.longitude),
    ll_to_earth(other_b.latitude, other_b.longitude)
    ) as distance
from bakeries b,
lateral (
    select bakery_id, latitude, longitude from bakeries where name = '호랑이'
) as other_b
where b.bakery_id <> other_b.bakery_id
order by distance;

Cube-Based Earth Distance 장단점

장점	단점
1. PostgreSQL이 공식적으로 지원하는 방법 2. 표준 PostgreSQL의 컬럼 사용 가능 3. 극단적인 케이스 없음	1. earthdistance의 경우 지구를 완전한 구 형태로 가정

earthdistance 실제로 사용해보기

earthdistance 쿼리 작성

필자의 경우, 주어진 latitude, longitude를 기준으로 반경 range(m) 내 bakeries를 반환해주는 쿼리를 작성했습니다.

을지로4가 우체국을 기준으로 반경 1km 내 bakery 찾기(bakeries DB 내 데이터 기준)

select b.bakery_id, b.name, b.address, b.latitude, b.longitude
from bakeries b
where earth_distance(ll_to_earth(37.56621061, 126.995157), ll_to_earth(b.latitude, b.longitude)) < 1000;

• 쿼리 내용을 보면, 주어진 위도, 경도(을지로4가 우체국)에서 1km(1000m) 이내의 bakeries를 반환해주었음을 확인할 수 있습니다.

Native Query를 통해 프로젝트에 적용하기

이제 SpringBoot 프로젝트에 직접 적용해보도록 하겠습니다.

• 위에서 설명했듯이 좌표 관련하여서는 보통 표준 SQL이 지원하는 기능이 아니어서 native query를 이용하여 적용해보도록 하겠습니다.

먼저, 아래와 같이 JpaRepository을 extends한 repository 인터페이스를 생성하여 native query를 작성합니다.

public interface BakeriesRepository extends JpaRepository<Bakeries, Long> {

    @Query(value = "SELECT b.bakery_id FROM bakeries b where earth_distance(" +
                    "ll_to_earth(b.latitude, b.longitude)," +
                    "ll_to_earth(:latitude, :longitude)) < :range",
            nativeQuery = true)
    List<Long> findByEarthDistance(@Param("latitude") Double latitude, @Param("longitude") Double longitude, @Param("range") Long range);
}

• 위에서 직접 PostgreSQL에서 날린 것처럼 작성하면 되며, 필자의 경우 postgreSQL에서 날렸던 것과 다르게 bakeryId만 받기 위해 select하는 부분이 위와 상이합니다.

생성한 findByEarthDistance 메소드를 호출하는 Service를 구성합니다.

@Service
@RequiredArgsConstructor
@Slf4j
public class BakeriesService {

    private final BakeriesRepository bakeriesRepository;

    @Transactional(readOnly = true)
    public List<BakeryListResponse> getBakeryList(Double latitude, Double longitude, Long range) {
        // 위도(latitude)/경도(longitude)/반경(range) 통해서 해당 반경에 있는 bakery 검색
        List<Long> bakeryIdList = bakeriesRepository.findByEarthDistance(latitude, longitude, range);
        for(Long bakeryId: bakeryIdList) {
            log.info("bakeryId: " + bakeryId);
        }
        ...
        return bakeryListResponseList;
}

실제 API 호출을 통해 테스트를 해야 하므로 native query 메소드를 실행하는 서비스 메소드를 API에서 호출할 수 있도록 controller를 구성합니다.

@Slf4j
@RestController
@RequestMapping("/bakery")
@RequiredArgsConstructor
public class BakeriesController {

    private final BakeriesService bakeriesService;

    @ApiOperation(value = "빵집 리스트", notes = "빵집 리스트 조회")
    @GetMapping
    public List<BakeryListResponse> getBakeryList(
            @ApiParam(value="위도", required = true) @RequestParam Double latitude,
            @ApiParam(value="경도", required = true) @RequestParam Double longitude,
            @ApiParam(value="반지름(m)", required = true) @RequestParam Long range){
        bakeriesService.getBakeryList(latitude, longitude, range);
        return null;
}

API 테스트는 하기와 같이 Postman을 이용하여 실행하였으며, controller 단에서 return해준 값이 따로 없어서 Postman에서는 따로 넘어온 값 없이 200 OK가 찍힌 것을 확인할 수 있습니다.

Postman을 통한 테스트 실행 및 결과

위와 같이 IntelliJ 로그를 보면, 어떤 쿼리가 실행되었는지 볼 수 있고 기존에 쿼리를 실행해서 나왔던 결과와 비교했을 때 동일한 bakeryId값들이 나온 것을 확인할 수 있습니다.

참고 자료

• PostGIS와 earthdistance 비교글
• JPA, Postgres earthdistance를 이용한 사용자 근처 가맹점 조회 API
• postgreSQL document(earthdistance)