Flutter から Supabase の PostgreSQL with PostGIS を使ってみる

この記事は PostgreSQL Advent Calendar 2021 1 日目のエントリです。

今年の秋頃から、個人的に使うアプリを Flutter で作り始めました。

• hmatsu47 / maptool（GitHub）

その経緯については軽くこちらで触れています。

関連記事はこちらです。

• Flutter で Mapbox の地図を現在位置を中心にして表示してみる
• Flutter で Mapbox の地図を操作してみる
• Flutter + Mapbox で Symbol をタップして id を取得・表示する
• Flutter + Mapbox でピン（Symbol）の情報を DB（SQLite）に保存する
• Flutter で Image Picker（0.8.2 以降）を使って撮った写真をファイル保存する
• 小ネタ／Flutter で AWS S3 へのアクセスに minio を使う
• Flutter で Image Picker を使って連続して写真を撮る

これらの記事の続きで（スライド中にもありますが） Supabase の Database（PostgreSQL）にテーブルを作成して PostgREST 経由で PostGIS の機能を呼び出す機会がありましたので、ここに記事として残しておきます。

参考記事：

• Supabaseのデータベースを使うときに役立つ情報（kabochapo さん）

Supabase の PostgreSQL で PostGIS を有効にする

Database → Extensions で、POSTGIS を ON にします。

テーブルを作成する

geometry 型は Supabase の管理画面にある Table editor では作成・登録できませんので、SQL editor を使って作成・登録します（あわせて INDEX も作成・登録）。

テーブル定義

CREATE TABLE category (
  id int GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
  category_name text NOT NULL
);

CREATE TABLE spot_opendata (
  id bigint GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
  category_id int REFERENCES category (id) NOT NULL,
  title text NOT NULL,
  describe text NOT NULL,
  location geometry(point, 4326) NOT NULL,
  prefecture text NOT NULL,
  municipality text NOT NULL,
  pref_muni text GENERATED ALWAYS AS (prefecture || municipality) STORED,
  created_at timestamp with time zone DEFAULT timezone('utc'::text, now()) NOT NULL,
  updated_at timestamp with time zone DEFAULT timezone('utc'::text, now()) NOT NULL
);
CREATE INDEX spot_location_idx ON spot_opendata USING GIST (location);
CREATE INDEX spot_pref_idx ON spot_opendata (prefecture);
CREATE INDEX spot_muni_idx ON spot_opendata (municipality);
CREATE INDEX spot_pref_muni_idx ON spot_opendata (pref_muni);

※テスト的にデータも登録しておきます。

• サンプルデータ
  • このサンプルデータは、以下の著作物を改変して利用しています。
    • 愛知県文化財マップ（ナビ愛知）、愛知県、クリエイティブ・コモンズ・ライセンス 表示２.１日本
    • https://www.pref.aichi.jp/soshiki/joho/0000069385.html

ストアドファンクションを作成する

通常であれば普通に SQL を書けばいいのですが、Flutter から PostgREST 経由でアクセスする場合は、前掲の参考記事にあるようにそのまま SQL を実行することができません（多分）。

• Supabaseのデータベースを使うときに役立つ情報（kabochapo さん）

というわけで、記事を参考にしてストアドファンクションを（SQL editor で）作成・登録します。

ストアドファンクション

CREATE OR REPLACE 
 FUNCTION get_spots(point_latitude double precision, point_longitude double precision, dist_limit int)
RETURNS TABLE (
  distance double precision,
  category_name text,
  title text,
  describe text,
  latitude double precision,
  longitude double precision,
  prefecture text,
  municipality text
) AS $$
BEGIN
  RETURN QUERY
  SELECT ((ST_POINT(point_longitude, point_latitude)::geography <-> spot_opendata.location::geography) / 1000) AS distance,
    category.category_name,
    spot_opendata.title,
    spot_opendata.describe,
    ST_Y(spot_opendata.location),
    ST_X(spot_opendata.location),
    spot_opendata.prefecture,
    spot_opendata.municipality
  FROM spot_opendata
  INNER JOIN category ON spot_opendata.category_id = category.id
  WHERE
    (ST_POINT(point_longitude, point_latitude)::geography <-> spot_opendata.location::geography) <= dist_limit
  ORDER BY distance;
END;
$$ LANGUAGE plpgsql;

Flutter からストアドファンクションを呼び出す

こちらも先の記事を参考に、クエリビルダではなく RPC 経由で呼び出します。

pubspec.yaml（関連部分）

  mapbox_gl: ^0.14.0
  supabase: ^0.2.9

※mapbox_glのLatLng型を利用。

class_definition.dart（関連部分）

import 'package:mapbox_gl/mapbox_gl.dart';

class PrefMuni {
  String prefecture;
  String municipalities;

  PrefMuni(this.prefecture, this.municipalities);

  String getPrefMuni() {
    return prefecture + municipalities;
  }
}

class SpotData {
  num distance;
  String categoryName;
  String title;
  String describe;
  LatLng latLng;
  PrefMuni prefMuni;

  SpotData(this.distance, this.categoryName, this.title, this.describe,
      this.latLng, this.prefMuni);
}

supabase_access.dart

import 'dart:async';
import 'package:mapbox_gl/mapbox_gl.dart';
import 'package:supabase/supabase.dart';
import 'package:maptool/class_definition.dart';

// Supabase Client
SupabaseClient getSupabaseClient(String supabaseUrl, String supabaseKey) {
  return SupabaseClient(supabaseUrl, supabaseKey);
}

Future<List<SpotData>> searchNearSpot(
    SupabaseClient client, LatLng latLng, int distLimit) async {
  final PostgrestResponse selectResponse =
      await client.rpc('get_spots', params: {
    'point_latitude': latLng.latitude,
    'point_longitude': latLng.longitude,
    'dist_limit': distLimit
  }).execute();
  final List<dynamic> items = selectResponse.data;
  final List<SpotData> resultList = [];
  for (dynamic item in items) {
    final SpotData spotData = SpotData(
        item['distance'] as num,
        item['category_name'] as String,
        item['title'] as String,
        item['describe'] as String,
        LatLng((item['latitude'] as num).toDouble(),
            (item['longitude'] as num).toDouble()),
        PrefMuni(item['prefecture'] as String, item['municipality'] as String));
    resultList.add(spotData);
  }
  return resultList;
}

---

2022/11/6 追記：
supabase 1.0.0より、リクエスト時の.execute()が不要になりました。

旧コード（部分）

  final PostgrestResponse selectResponse =
      await client.rpc('get_spots', params: {
    'point_latitude': latLng.latitude,
    'point_longitude': latLng.longitude,
    'dist_limit': distLimit
  }).execute();
  final List<dynamic> items = selectResponse.data;

新コード（同）

  final List<dynamic> items =
      await client.rpc('get_spots', params: {
    'point_latitude': latLng.latitude,
    'point_longitude': latLng.longitude,
    'dist_limit': distLimit
  });

---

※ item['distance'] as num が PostGIS を使って計算した距離を受け取っている部分。

supabase_access.dartを呼び出す側のコード

import 'dart:async';
import 'package:mapbox_gl/mapbox_gl.dart';
import 'package:supabase/supabase.dart';
import 'package:maptool/class_definition.dart';
import 'package:maptool/supabase_access.dart';

  SupabaseClient? _supabaseClient;
  String _supabaseUrl = '【Supabase の URL】';
  String _supabaseKey = '【Supabase の API Key】';
  _supabaseClient = getSupabaseClient(_supabaseUrl, _supabaseKey);

  final LatLng position = 【起点とする緯度経度】;
  final int _distLimit = 【検索範囲の距離(m)】;
  final List<SpotData> spotList =
      await searchNearSpot(_supabaseClient!, position, _distLimit);

※この利用例ではユーザの識別をしていないので Supabase の API Key を単純にそのまま使っていますが、ユーザ別にデータを保存する場合（Supabase の Database（PostgreSQL）では行レベルセキュリティでこれを実現しています）、適切な認証情報を使ってください。

なお、Dart で整数以外を含む数値を扱うときの「お約束」ですが、カラム値を直接double型で受けると　DB から（たまたま）整数値が渡ってきたときに「type 'int' is not a subtype of type 'double'」というエラーが出るため、（一旦）num型で受けます。

---

明日（2 日目）は kwatch さんです。

• [SQL] 日付の抜けをカレンダーテーブルで埋める