距離計算をより短時間で。Supabase Database Functionsのおかげで実現できた！

概要

現在、Flutterのアプリケーション作成を行っています。

アプリケーションの機能の中で、現在地との距離を表示する箇所がありました。
以下が実際のアプリケーションのスクリーンショットです。赤枠で囲った箇所です。

距離を表示するための実装方法やデータベースのテーブル構成を考える中で、
Database Functionsについても学ぶ機会となりましたので、まとめてみました。

利用技術

• 言語：Dart（Flutter）
• Database：Supabase

処理の流れ

やりたかったことを説明していきます。

1. 位置情報取得

まずは自分の現在地、位置情報（緯度・経度）を取得する必要があります。
Google Mapとの連携する場合は、Google CloudのAPIを呼び出す必要がありますが、
現在はそこまでのステップには至っていないので、まずは位置情報取得だけ行います。

位置情報の取得には、以下のFlutterパッケージを使いました。

また皆さんも一度はご覧になったことがあるかと思いますが、
アプリからデバイスの位置情報へのアクセスをしてもいいですか？と聞かれたことがあるかと思います。

このパッケージを利用してアプリケーションに組み込むと、ポップアップが表示されるようになります。

権限確認と現在地取得は以下実装で完結します。

  // 位置情報の権限チェック
  LocationPermission permission = await Geolocator.checkPermission();
  if (permission == LocationPermission.denied) {
    permission = await Geolocator.requestPermission();
    if (permission == LocationPermission.denied) {
      state = state.copyWith(isLoading: false, error: '位置情報の権限が拒否されました。');
      return;
    }
  }

  // 位置情報の取得
  final position = await Geolocator.getCurrentPosition(desiredAccuracy: LocationAccuracy.high);

2. 各店舗の位置情報取得

各店舗の情報（ここでは緯度と経度）をデータベースのテーブルに用意してあります。
条件ついて、
緯度経度で範囲指定、といったことができそうですが、今全件取得とします。

List<StoreMobileOrder> _stores = [];

// データ取得
final SupabaseClient supabase = Supabase.instance.client;
final response = await supabase.from('stores').select();

// 画面表示のため、取得結果をStoreMobileOrderモデルに設定
final List<dynamic> data = response as List<dynamic>;
_stores = data.map((json) => StoreMobileOrder.fromJson(json)).toList();

3. 現在地との距離計算

1で取得した現在地と、2で取得した各店舗の位置から、距離を算出します。
距離算出において、ループする点やソート処理がでてきたタイミングで、
別の方法があると思い始めましたが、進みました。

for (final store in _stores) {
   final distanceInMeters = Geolocator.distanceBetween(
     _currentPosition!.latitude,
     _currentPosition!.longitude,
     store.latitude,
     store.longitude,
   );

   // メートルからキロメートルに変換して小数点第1位までを表示
   store.distance = (distanceInMeters / 1000).toStringAsFixed(1);
 }
 // 距離順にソート
 _stores.sort((a, b) => double.parse(a.distance).compareTo(double.parse(b.distance)));

 // 先頭10件に絞る
 _stores = _stores.take(10).toList();

4. 画面に表示

3の結果を表示する処理を追加します。
実際はもっと長いですが、関係する部分に絞ると以下の通りです。

store.formattedDistanceKm,

SliverList(
    delegate: SliverChildBuilderDelegate((context, index) {
      final store = _stores[index];
      return Column(
        mainAxisSize: MainAxisSize.min,
        children: [
          Container(
            child: Padding(
              padding: const EdgeInsets.symmetric(horizontal: 15, vertical: 10),
              child: Column(
                crossAxisAlignment: CrossAxisAlignment.start,
                children: [
                  _RowSelectButtonAndDistance(
                    store: store,
                    distance: store.formattedDistanceKm,
                    onSelected: () {
                      // Providerに選択した店舗を設定
                      ref.read(selectedStoreProvider.notifier).state = store;
                      Navigator.of(context).pushNamed('/togo-inside');
                    },
                  ),
                ],
              ),
            ),
          ),
        ],
      );
    }, childCount: _stores.length),
  ),

※ _RowSelectButtonAndDistance
「選択する」というボタンと距離の表示に関するWidgetです。

動作確認

想像通りでしたが、時間がかかります。

1. 現在地との距離計算

ループ処理内で算出する処理をしているので時間がかかります。
店舗のデータは10件しかないのに、5～10秒はかかっていました。

このくらい時間がかかると、ローディングアニメーションではなく、プログレスバーを使う方が妥当な領域です。

データが100件になったら、アプリを閉じたくなるほどの時間がかかってしまいます。

距離算出のための方法を考える必要がありました。

方法を調べる中で、
Database Functionsが良さそうと分かりました。

rpc(Remote Procedure Calls)を用いたデータ取得

冒頭に記載した通り、SupabaseにDatabase Functionsという機能があります。
存在は知っていましたが、これまで触ったことがありませんでした。

以下はDart関連のページです。

簡単なものから作成

まずは構造理解のために、以下関数を作成してみました。
実際は以下のような関数は作成せず、テーブルから直接SELECTしますが、実験です。

CREATE OR REPLACE FUNCTION get_all_stores()
RETURNS SETOF stores
LANGUAGE sql
AS $$
  SELECT * FROM stores ORDER BY store_number;
$$;

コード上で呼び出すときは以下のように書きます。

final response = await supabase.rpc('get_all_stores', params: {});

作成した関数

呼び出すために必要な情報は以下の3つです。

• 現在地の緯度
• 現在地の経度
• 距離制限（仮で15kmに設定。これほど遠い店舗に対してオーダーすることはないはず）

算出した距離に関係するクエリが以下部分です。これはClaudeに聞いてみたコードです。

6371は地球の半径なので、数学物理あたりの計算を活用した算出方法です。

6371 * acos(
        cos(radians(lat)) * cos(radians(s.latitude)) *
        cos(radians(s.longitude) - radians(lng)) +
        sin(radians(lat)) * sin(radians(s.latitude))
      ) AS distance_km

関数の内容は、以下の通りです。

CREATE OR REPLACE FUNCTION get_nearby_stores(
  lat DOUBLE PRECISION, 
  lng DOUBLE PRECISION, 
  radius_km DOUBLE PRECISION
)
RETURNS TABLE (
  id INTEGER,
  store_name VARCHAR(50),
  address VARCHAR(100),
  closing_time TIME,
  opening_time TIME,
  latitude DOUBLE PRECISION,
  longitude DOUBLE PRECISION,
  distance_km DOUBLE PRECISION
)
LANGUAGE sql
AS $$
  WITH stores_with_distance AS (
    SELECT 
      s.id,
      s.store_name,
      s.address,
      s.closing_time,
      s.opening_time,
      s.latitude,
      s.longitude,
      6371 * acos(
        cos(radians(lat)) * cos(radians(s.latitude)) *
        cos(radians(s.longitude) - radians(lng)) +
        sin(radians(lat)) * sin(radians(s.latitude))
      ) AS distance_km
    FROM stores s
    WHERE s.closing_date IS NULL OR s.closing_date > CURRENT_DATE
  )
  SELECT 
    id,
    store_name,
    address,
    closing_time,
    opening_time,
    latitude,
    longitude,
    distance_km
  FROM stores_with_distance
  WHERE distance_km < radius_km
  ORDER BY distance_km
  LIMIT 10;
$$;

実装を再修正

最初はDartで実装していた距離算出に関する実装が簡潔になりました。

また現在地取得のタイミングも修正しました。
対象画面を表示する前に取得し、その結果をProviderに設定しました。

Providerと書きましたが、正確にはriverpodを使った状態管理です。

以下は、Providerを経由して現在地を取得する実装です。

// 現在地の取得
final locationState = ref.read(locationProvider);
if (locationState.position != null) {
// Supabaseから店舗情報を取得
final response = await _supabase.rpc(
  'get_nearby_stores',
  params: {
    'lat': locationState.position?.latitude,
    'lng': locationState.position?.longitude,
    'radius_km': 15.0,
  },
);
final List<dynamic> data = response as List<dynamic>;
_stores = data.map((json) => StoreMobileOrder.fromJson(json)).toList();

利用した結果と感じたこと

結果

実行時のデータは10件ほどでしたが、5～10秒はかかっていました。

データを30件ほどに増やしても、1～2秒ほどに改善されました。
これであれば、ローディングアニメーションのままで問題なさそうです！！（参考記事再掲）

感じたこと

SQLの実装経験がある方であれば、進めやすいと思いました。

また、Supabaseのメソッドを使ってテーブル結合して取得することはできますが、
以下の2点から、テーブル結合が必要となる場合は、Database Functionsを使う方が進めやすいと感じました。（個人的な感想です！）

• 直感的ではない印象（別途記法を理解する必要がある）点
• コード行数が多くなりやすい点（長くなるのであれば、生のSQLの方が見やすいかと）

ちなみに、6つのテーブルを結合したとき以下のように実装します。

今はSELECT項目数が少ないのでそれほど気にならないですが、増えると少し大変になる気がしました。

  final response =
  await supabase
      .from('products')
      .select('''
      product_id,
      product_name,
      description,
      categories (
        category_id,
        category_name
      ),
      product_temperature_types (
        temperature_types (
          temperature_type_id,
          type_name
        )
      ),
      product_sizes (
        price,
        sizes (
          size_id,
          size_name
        )
      )
    ''')
      .eq('product_id', 1)
      .eq('is_active', true)
      .single(); // 単一行の結果を期待

Database Functionsを用いると、以下の通りスッキリします。

final response = await _supabase.rpc(
  'get_nearby_stores',
  params: {
    'lat': locationState.position?.latitude,
    'lng': locationState.position?.longitude,
    'radius_km': 15.0,
  },
);

複数テーブルに跨るデータ追加したいとき
rpcを使えば、複数テーブルにデータを投入する際のトランザクション管理も適切かつシンプルに構築できそうです。

Dart側のコード側で検討するのは最善ではないしょうし、大変です。
(テーブルAに追加して、テーブルBのデータ投入に失敗したら・・・ロールバック・・・)

rpc側でトランザクションを含めてすべて管理してしまえば、そのあたりもサクッとできそうです。

この後の流れ

データ取得タイミングとローカル保存とDatabase Functionsの更なる活用

本記事冒頭に画像を載せたアプリケーションに関して、画面の遷移パターンや機能を見ていると、
毎回データベースから呼び出すのではなく、
端末にデータを保存して、一定時間経過したら再取得といった処理になっているはずです。

またまだやれることは多そうなので、１つずつやっていければと思います。

PostGIS: Geo queries

今回作成した関数では、距離算出を原始的なやり方で行っていました。
Supabaseのドキュメントで以下情報を見つけました。

地理情報に特化したSupabaseの拡張機能の一つで、緯度経度から距離を算出するできる拡張機能のようです。
今回は触れていないのですが、この後の改修内容になりそうなるかもしれないので、取り組んでみたいと思います。

ありがとうございました。