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

この記事は **[PostgreSQL Advent Calendar 2021](https://qiita.com/advent-calendar/2021/postgresql)** 1 日目のエントリです。

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

 - **[hmatsu47 / maptool](https://github.com/hmatsu47/maptool)（GitHub）**

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

<script async class="speakerdeck-embed" data-id="55203e0fda9a4e408e8955d54779d6a2" data-ratio="1.77777777777778" src="//speakerdeck.com/assets/embed.js"></script>

関連記事はこちらです。

 - **[Flutter で Mapbox の地図を現在位置を中心にして表示してみる](https://qiita.com/hmatsu47/items/b98ef4c1a87cc0ec415d)**
 - **[Flutter で Mapbox の地図を操作してみる](https://zenn.dev/hmatsu47/articles/846c3186f5b4fe)**
 - **[Flutter + Mapbox で Symbol をタップして id を取得・表示する](https://zenn.dev/hmatsu47/articles/9102fb79a99a98)**
 - **[Flutter + Mapbox でピン（Symbol）の情報を DB（SQLite）に保存する](https://zenn.dev/hmatsu47/articles/e81bf3c2bf00f8)**
 - **[Flutter で Image Picker（0.8.2 以降）を使って撮った写真をファイル保存する](https://qiita.com/hmatsu47/items/e4f7e310e88376d54009)**
 - **[小ネタ／Flutter で AWS S3 へのアクセスに minio を使う](https://qiita.com/hmatsu47/items/86a9c028bb5b3beeebdd)**
 - **[Flutter で Image Picker を使って連続して写真を撮る](https://qiita.com/drafts/c3f9cafb499aedaca1f1/edit)**

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

**参考記事：**

 - **[Supabaseのデータベースを使うときに役立つ情報](https://qiita.com/kabochapo/items/26b1bb753116a6904664)（kabochapo さん）**

# Supabase の PostgreSQL で PostGIS を有効にする

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

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/123899/b22b2730-d194-d3b4-abd0-105efbe61434.png)

# テーブルを作成する

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

```sql:テーブル定義
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://github.com/hmatsu47/maptool/tree/main/sampleData/supabase)**
     - このサンプルデータは、以下の著作物を改変して利用しています。 
       - 愛知県文化財マップ（ナビ愛知）、愛知県、クリエイティブ・コモンズ・ライセンス 表示２.１日本
       - https://www.pref.aichi.jp/soshiki/joho/0000069385.html

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

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

 - **[Supabaseのデータベースを使うときに役立つ情報](https://qiita.com/kabochapo/items/26b1bb753116a6904664)（kabochapo さん）**

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

```sql:ストアドファンクション
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 からストアドファンクションを呼び出す

こちらも[先の記事](https://qiita.com/kabochapo/items/26b1bb753116a6904664)を参考に、クエリビルダではなく RPC 経由で呼び出します。

```yaml:pubspec.yaml（関連部分）
  mapbox_gl: ^0.14.0
  supabase: ^0.2.9
```

※`mapbox_gl`の`LatLng`型を利用。

```dart: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);
}
```

```dart: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()`が不要になりました。

```dart:旧コード（部分）
  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;
```

```dart:新コード（同）
  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 を使って計算した距離を受け取っている部分。

```dart: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] 日付の抜けをカレンダーテーブルで埋める](https://qiita.com/kwatch/items/f45cf83e42bb934b0e1e)**