[Next.js] Supabase PostGIS로 거리순 데이터 정렬하기

 

➜  [Next.js + 네이버 지도 api ] Supabase의 PostGIS로 가까운 순으로 데이터 가져오기

 

supabase에서 제공하는 PostGIS를 사용하면 위치 기반 거리를 계산할 수 있다.

✔ PostGIS
Geo 데이터와 상호 작용할 수 있는 Postgres extension으로
지리적 위치에 따라 데이터를 정렬하고, 특정 지리적 경계 내에서 데이터를 가져올 수 있다.

 

 

Supabase에서 postgis 활성화

 

 

Supabase - Database Extensions 에서 postgis를 활성화해준다.

 

테이블 생성

 

⇣ 공식 문서 예시

create table if not exists public.restaurants (
	id int generated by default as identity primary key,
	name text not null,
	location gis.geography(POINT) not null
);

 

⇣ 이미 있는 테이블에 컬럼만 추가할 경우

ALTER TABLE pools ADD COLUMN location geography(POINT, 4326) NOT NULL;

 

난 이미 테이블을 만들어 둔 상태라 location 컬럼만 추가해 줬다.

 

이때 발생한 오류

➭ ERROR: 23502: column "location" of relation "pools" contains null values

이미 존재하는 레코드에 location 컬럼이 생길 때 기본값으로 NULL이 채워지는데

거기에 'NOT NULL'을 걸어버렸으니 생긴 문제..ㅎㅎ

그래서 규칙을 빼주고 컬럼 추가 후 데이터 넣어주고 다시 규칙을 걸어줬다.

 

location 데이터 추가

 

⇣ 공식 문서 예시

insert into public.restaurants
  (name, location)
values
  ('Supa Burger', gis.st_point(-73.946823, 40.807416)),
  ('Supa Pizza', gis.st_point(-73.94581, 40.807475)),
  ('Supa Taco', gis.st_point(-73.945826, 40.80629));

 

⇣ 기존 레코드 값 변경

UPDATE pools SET location = st_point(longitude, latitude);

 

location의 geography(POINT) 타입은 st_point(경도, 위도) 순이다!

 

Supabase에 함수 작성

 

CREATE OR REPLACE FUNCTION get_nearby_pools(latt FLOAT, long FLOAT)
RETURNS TABLE (
    id UUID,
    name TEXT,
    road_address TEXT,
    jibun_address TEXT,
    dist_meters INT
)
LANGUAGE SQL 
AS $$
    SELECT 
        id, 
        name, 
        road_address,
        jibun_address,
        st_distance(location, st_point(long, latt)::geography) AS dist_meters
    FROM 
        public.pools
    WHERE 
        st_distance(location, st_point(long, latt)::geography) <= 5000
    ORDER BY 
        location <-> st_point(long, latt)::geography;
$$;

 

Supabase SQL Editor에서 위와 같은 함수를 작성한다.

➭ 위도, 경도 값을 매개변수로 받고 반환될 컬럼을 지정

➭ 두 지점 사이의 거리를 미터 단위로 반환

   * st_point(long, latt) 경도, 위도를 POINT 형태로 만들고 geography 타입으로 변환

➭ 5km 이하의 데이터만 가까운 거리순으로 정렬

   * <-> : PostGIS의 KNN(k-최근접) 연산자로 가장 가까운 거리순으로 정렬

 

 

작성한 함수는 Supabase - Database Functions 에서 확인할 수 있다.

 

함수 호출하기

 

⇣ 공식 문서 예시

const { data, error } = await supabase.rpc('nearby_restaurants', {
  lat: 40.807313,
  long: -73.946713,
})

 

⇣ 난 Route API로 Next.js 내에 api를 만들고 있어서 아래와 같이 작성했다.

// app/api/pool/nearby
import { createClient } from '@/utils/supabase/server';
import { NextResponse } from 'next/server';

export async function GET(request: Request) {
  const { searchParams } = new URL(request.url);
  const lat = searchParams.get('lat');
  const lng = searchParams.get('lng');

  const supabase = await createClient();
  const { data: list, error } = await supabase.rpc('get_nearby_pools', {
    latt: Number(lat),
    long: Number(lng),
  });

  if (error) {
    return NextResponse.json({ error: error.message }, { status: 500 });
  }

  return NextResponse.json({ data: list || [] });
}

 

이렇게 해주고 fetch로 호출했다.

 

결과 화면

 

 

그럼 이렇게 가까운 거리순으로 정렬된 데이터를 받아볼 수 있다!!

 

 

 

* 잘못된 내용이 있다면 알려주세요 ^_^

---

참고

공식 문서 PostGIS: Geo queries

Velog-야간 카페 지도 서비스 개발(2)