FlutterでSupabaseのPostgreSQL with PostGISを試してみた

Flutter で Supabase の PostgreSQL with PostGIS を 試してみた 第 29 回 PostgreSQL アンカンファレンス@オンライン 2021/12/21 まつひさ（hmatsu47）

自己紹介 松久裕保（@hmatsu47） ● https://qiita.com/hmatsu47 ● 現在のステータス： ○ 名古屋で Web インフラのお守り係をしています ○ しばらく RDBMS 関連の勉強会では登壇していませんでした ■ これ↓がラスト（5/31・JAWS-UG 名古屋） RDS_Aurora パフォーマンスインサイトのデータを Athena と QuickSight で見る ● 「MySQL 8.0 の薄い本」更新終了のお知らせ 2

• https://qiita.com/hmatsu47
• https://speakerdeck.com/hmatsu47/rds-aurorapahuomansuinsaitofalsedetawoathenatoquicksightdejian-ru
• https://note.com/hmatsu47/n/n3ad586c31dce

最近、情勢変化の兆しが ● ついに出た ○ Amazon Aurora MySQL 3 with MySQL 8.0 compatibility is now generally available ○ ● Amazon Aurora MySQL 3 (MySQL 8.0 互換) がリリースされました。 ウォームアップ開始 ○ Aurora MySQL バージョン 1（5.6 互換）→バージョン 3（8.0 互換）間の dumpInstance() & loadDump() を試す 3

• https://aws.amazon.com/jp/blogs/database/amazon-aurora-mysql-3-with-mysql-8-0-compatibility-is-now-generally-available/
• https://dev.classmethod.jp/articles/amazon-aurora-mysql-3-ga/
• https://qiita.com/hmatsu47/items/9db2ad8e8f41e44a54b7

それはさておき、本日のネタ ● Flutter で地図アプリを作ってみた ○ 地図は Mapbox ■ ● 今回の主題ではないので詳細は省略 その中で Supabase の PostgreSQL を試してみた ○ PostGIS も使ってみた ■ 本当に「ちょっと使ってみた」程度 4

Flutter とは？ ● Google 製の UI フレームワーク ○ 使う言語は Dart ○ 当初はクロスプラットフォームモバイルアプリ開発用 ○ その後、Web や Windows / macOS / Linux も対象に ○ 最近バージョン 2.8 が出た ■ 今年の 3 月に 2.0 が出たばかり 5

Flutter を試そうと思ったきっかけ ● 以前 Qiita でバズったこのサイト ○ https://korette.jp/ 6

• https://korette.jp/

Flutter を試そうと思ったきっかけ ● 以前 Qiita でバズったこのサイト ○ https://korette.jp/ ○ サポーターズの一員として大量にクイズ投稿 ○ その後、コロナ禍で観光地の状況が一変 ○ コロナが落ち着いた隙をみながら問題メンテナンスの旅へ ○ 旅のお供として、情報収集・整理のためのアプリが欲しい ○ 作ることにした 7

• https://korette.jp/

作っているアプリ（maptool） ● https://github.com/hmatsu47/maptool ● 実装済みの主な機能 ○ 訪問（予定）地へのピン立て（登録） ○ 登録ピンと関連づけて写真撮影 ○ 登録ピンの検索 ○ 地図スタイル切り替え ○ 文化財などの近隣スポット検索 8

• https://github.com/hmatsu47/maptool

作っているアプリ（maptool） ● https://github.com/hmatsu47/maptool ● 実装済みの主な機能 ○ 訪問（予定）地へのピン立て（登録） ○ 登録ピンと関連づけて写真撮影 ○ 登録ピンの検索 ○ 地図スタイル切り替え ○ 文化財などの近隣スポット検索←ここで PostGIS を利用 9

• https://github.com/hmatsu47/maptool

作っているアプリ（maptool） ● 訪問（予定）地へのピン立て（登録） 注：画面は少し古めのバージョン です（以降同じ） 10

作っているアプリ（maptool） ● 登録ピンと関連づけて写真撮影 11

作っているアプリ（maptool） ● 登録ピンの検索 12

作っているアプリ（maptool） ● 地図スタイル切り替え 13

作っているアプリ（maptool） ● 文化財などの近隣スポット検索（Supabase / PostGIS） 14

Supabase とは？ 15

Supabase とは？ ● BaaS（Backend as a Service）の一つ ○ Firebase Alternative ● サービスは 4 つ ○ Database ○ Authentication ○ Storage ○ Functions（Coming soon） 16

Supabase とは？ ● BaaS（Backend as a Service）の一つ ○ Firebase Alternative ● サービスは 4 つ ○ Database ← PostgreSQL が使われている ○ Authentication ○ Storage ○ Functions（Coming soon） 17

Flutter から Supabase の PostgreSQL を使う ● Supabase を設定 ○ SQL editor でテーブル作成 ○ SQL editor でデータ登録 ● Flutter からクエリビルダを使ってアクセス ○ 内部的に PostgREST を使用 18

テーブル①（近隣スポットのカテゴリ） CREATE TABLE category ( id int GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY, categoryname text NOT NULL ); 19

Flutter コード①（接続とクエリビルダでの呼び出し）
import 'package:mapbox_gl/mapbox_gl.dart';
import 'package:supabase/supabase.dart';
import 'class_definition.dart';
SupabaseClient getSupabaseClient(String supabaseUrl, String supabaseKey) {
return SupabaseClient(supabaseUrl, supabaseKey);
}
Future<List<SpotCategory>> searchSpotCategory(SupabaseClient client) async {
final PostgrestResponse selectResponse = await client
.from('category')
.select()
.order('id', ascending: true)
.execute();
final List<dynamic> items = selectResponse.data;
final List<SpotCategory> resultList = [];
for (dynamic item in items) {
final SpotCategory category =
SpotCategory(item['id'] as int, item['category_name'] as String);
resultList.add(category);
}
return resultList;
}

20

Flutter コード①（接続とクエリビルダでの呼び出し）
import 'package:mapbox_gl/mapbox_gl.dart';
import 'package:supabase/supabase.dart';
import 'class_definition.dart';
SupabaseClient getSupabaseClient(String supabaseUrl, String supabaseKey) {
return SupabaseClient(supabaseUrl, supabaseKey);
}
Future<List<SpotCategory>> searchSpotCategory(SupabaseClient client) async {
final PostgrestResponse selectResponse = await client
.from('category')
.select()
←クエリビルダで
.order('id', ascending: true)
.execute();
final List<dynamic> items = selectResponse.data;
final List<SpotCategory> resultList = [];
for (dynamic item in items) {
final SpotCategory category =
SpotCategory(item['id'] as int, item['category_name'] as String);
resultList.add(category);
}
return resultList;
}

select()

21

Flutter から Supabase の PostGIS を使う ● Flutter から Supabase の PostgreSQL with PostGIS を使ってみる ● Supabase を設定（Database） ○ Extensions で PostGIS を有効化 ○ SQL editor でテーブル作成 ○ SQL editor でデータ登録 ○ SQL editor でストアドファンクション作成 ● Flutter から RPC でストアドファンクションを呼び出す 22

• https://qiita.com/hmatsu47/items/c3f9cafb499aedaca1f1

Supabase で PostGIS を有効化 23

テーブル②（注：INDEX を定義していますが未使用です） 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); 24

ストアドファンクション（範囲内のスポットを検索） CREATE OR REPLACE FUNCTION get_spots(point_latitude double precision, point_longitude double precision, dist_limit int, category_id_number 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 25

ストアドファンクション（範囲内のスポットを検索）
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
AND
(CASE WHEN category_id_number = -1 THEN true ELSE category.id = category_id_number END)
ORDER BY distance;
END;
$$ LANGUAGE plpgsql;

26

Flutter コード②（RPC でファンクション呼び出し）
Future<List<SpotData>> searchNearSpot(SupabaseClient client, LatLng latLng,
int distLimit, int? categoryId) async {
final PostgrestResponse selectResponse =
await client.rpc('get_spots', params: {
'point_latitude': latLng.latitude,
'point_longitude': latLng.longitude,
'dist_limit': distLimit,
'category_id_number': (categoryId ?? -1)
}).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;
}

27

Flutter コード②（RPC でファンクション呼び出し）
Future<List<SpotData>> searchNearSpot(SupabaseClient client, LatLng latLng,
int distLimit, int? categoryId) async {
final PostgrestResponse selectResponse =
await client.rpc('get_spots', params: {
'point_latitude': latLng.latitude,
'point_longitude': latLng.longitude,
←RPC で get_spots()
'dist_limit': distLimit,
'category_id_number': (categoryId ?? -1)
}).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;
}

ファンクションを呼び出す

28

その他 ● ユーザ別のデータを保存する場合は RLS を使う ○ RLS : 行レベルセキュリティ ○ 今回は不使用 ■ 自分一人で使うので ○ 通常は Recommended 29

その他 ● 無料プランでは 1 週間利用がないと DB が停止 30

まとめ ● Flutter から Supabase の PostgreSQL を使う ○ 単純クエリはクエリビルダを使って問い合わせ ● Flutter から Supabase の PostGIS を使う ○ PostGIS を有効化 ○ クエリビルダで関数が使えないのでストアドファンクションを RPC で呼び出す 31

参考情報 ● 関連ブログ記事 ○ https://qiita.com/hmatsu47/items/b98ef4c1a87cc0ec415d ○ https://zenn.dev/hmatsu47/articles/846c3186f5b4fe ○ https://zenn.dev/hmatsu47/articles/9102fb79a99a98 ○ https://zenn.dev/hmatsu47/articles/e81bf3c2bf00f8 ○ https://qiita.com/hmatsu47/items/e4f7e310e88376d54009 ○ https://qiita.com/hmatsu47/items/86a9c028bb5b3beeebdd ○ https://qiita.com/hmatsu47/items/53ea68769c4fc2d76450 ○ https://qiita.com/hmatsu47/items/c3f9cafb499aedaca1f1 32

• https://qiita.com/hmatsu47/items/b98ef4c1a87cc0ec415d
• https://zenn.dev/hmatsu47/articles/846c3186f5b4fe
• https://zenn.dev/hmatsu47/articles/9102fb79a99a98
• https://zenn.dev/hmatsu47/articles/e81bf3c2bf00f8
• https://qiita.com/hmatsu47/items/e4f7e310e88376d54009
• https://qiita.com/hmatsu47/items/86a9c028bb5b3beeebdd
• https://qiita.com/hmatsu47/items/53ea68769c4fc2d76450
• https://qiita.com/hmatsu47/items/c3f9cafb499aedaca1f1