Slide 1
Slide 1 text
Flutter で
Supabase の PostgreSQL with PostGIS を
試してみた
第 29 回 PostgreSQL アンカンファレンス@オンライン
2021/12/21
まつひさ（hmatsu47）
Slide 2
Slide 2 text
自己紹介
松久裕保（@hmatsu47）
● https://qiita.com/hmatsu47
● 現在のステータス：
○ 名古屋で Web インフラのお守り係をしています
○ しばらく RDBMS 関連の勉強会では登壇していませんでした
■ これ↓がラスト（5/31・JAWS-UG 名古屋）
RDS_Aurora パフォーマンスインサイトのデータを Athena と QuickSight で見る
● 「MySQL 8.0 の薄い本」更新終了のお知らせ
2
Slide 3
Slide 3 text
最近、情勢変化の兆しが
● ついに出た
○ 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
Slide 4
Slide 4 text
それはさておき、本日のネタ
● Flutter で地図アプリを作ってみた
○ 地図は Mapbox
■ 今回の主題ではないので詳細は省略
● その中で Supabase の PostgreSQL を試してみた
○ PostGIS も使ってみた
■ 本当に「ちょっと使ってみた」程度
4
Slide 5
Slide 5 text
Flutter とは？
● Google 製の UI フレームワーク
○ 使う言語は Dart
○ 当初はクロスプラットフォームモバイルアプリ開発用
○ その後、Web や Windows / macOS / Linux も対象に
○ 最近バージョン 2.8 が出た
■ 今年の 3 月に 2.0 が出たばかり
5
Slide 6
Slide 6 text
Flutter を試そうと思ったきっかけ
● 以前 Qiita でバズったこのサイト
○ https://korette.jp/
6
Slide 7
Slide 7 text
Flutter を試そうと思ったきっかけ
● 以前 Qiita でバズったこのサイト
○ https://korette.jp/
○ サポーターズの一員として大量にクイズ投稿
○ その後、コロナ禍で観光地の状況が一変
○ コロナが落ち着いた隙をみながら問題メンテナンスの旅へ
○ 旅のお供として、情報収集・整理のためのアプリが欲しい
○ 作ることにした
7
Slide 8
Slide 8 text
作っているアプリ（maptool）
● https://github.com/hmatsu47/maptool
● 実装済みの主な機能
○ 訪問（予定）地へのピン立て（登録）
○ 登録ピンと関連づけて写真撮影
○ 登録ピンの検索
○ 地図スタイル切り替え
○ 文化財などの近隣スポット検索
8
Slide 9
Slide 9 text
作っているアプリ（maptool）
● https://github.com/hmatsu47/maptool
● 実装済みの主な機能
○ 訪問（予定）地へのピン立て（登録）
○ 登録ピンと関連づけて写真撮影
○ 登録ピンの検索
○ 地図スタイル切り替え
○ 文化財などの近隣スポット検索←ここで PostGIS を利用
9
Slide 10
Slide 10 text
作っているアプリ（maptool）
● 訪問（予定）地へのピン立て（登録）
10
注：画面は少し古めのバージョン
です（以降同じ）
Slide 11
Slide 11 text
作っているアプリ（maptool）
● 登録ピンと関連づけて写真撮影
11
Slide 12
Slide 12 text
作っているアプリ（maptool）
12
● 登録ピンの検索
Slide 13
Slide 13 text
作っているアプリ（maptool）
● 地図スタイル切り替え
13
Slide 14
Slide 14 text
作っているアプリ（maptool）
14
● 文化財などの近隣スポット検索（Supabase / PostGIS）
Slide 15
Slide 15 text
Supabase とは？
15
Slide 16
Slide 16 text
Supabase とは？
● BaaS（Backend as a Service）の一つ
○ Firebase Alternative
● サービスは 4 つ
○ Database
○ Authentication
○ Storage
○ Functions（Coming soon）
16
Slide 17
Slide 17 text
Supabase とは？
● BaaS（Backend as a Service）の一つ
○ Firebase Alternative
● サービスは 4 つ
○ Database ← PostgreSQL が使われている
○ Authentication
○ Storage
○ Functions（Coming soon）
17
Slide 18
Slide 18 text
Flutter から Supabase の PostgreSQL を使う
● Supabase を設定
○ SQL editor でテーブル作成
○ SQL editor でデータ登録
● Flutter からクエリビルダを使ってアクセス
○ 内部的に PostgREST を使用
18
Slide 19
Slide 19 text
テーブル①（近隣スポットのカテゴリ）
CREATE TABLE category (
id int GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
categoryname text NOT NULL
);
19
Slide 20
Slide 20 text
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> searchSpotCategory(SupabaseClient client) async {
final PostgrestResponse selectResponse = await client
.from('category')
.select()
.order('id', ascending: true)
.execute();
final List items = selectResponse.data;
final List 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
Slide 21
Slide 21 text
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> searchSpotCategory(SupabaseClient client) async {
final PostgrestResponse selectResponse = await client
.from('category')
.select()
.order('id', ascending: true)
.execute();
final List items = selectResponse.data;
final List resultList = [];
for (dynamic item in items) {
final SpotCategory category =
SpotCategory(item['id'] as int, item['category_name'] as String);
resultList.add(category);
}
return resultList;
}
21
←クエリビルダで select()
Slide 22
Slide 22 text
Flutter から Supabase の PostGIS を使う
● Flutter から Supabase の PostgreSQL with PostGIS を使ってみる
● Supabase を設定（Database）
○ Extensions で PostGIS を有効化
○ SQL editor でテーブル作成
○ SQL editor でデータ登録
○ SQL editor でストアドファンクション作成
● Flutter から RPC でストアドファンクションを呼び出す
22
Slide 23
Slide 23 text
Supabase で PostGIS を有効化
23
Slide 24
Slide 24 text
テーブル②（注：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
Slide 25
Slide 25 text
ストアドファンクション（範囲内のスポットを検索）
25
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
Slide 26
Slide 26 text
ストアドファンクション（範囲内のスポットを検索）
26
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;
Slide 27
Slide 27 text
Flutter コード②（RPC でファンクション呼び出し）
Future> 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 items = selectResponse.data;
final List 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
Slide 28
Slide 28 text
Flutter コード②（RPC でファンクション呼び出し）
Future> 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 items = selectResponse.data;
final List 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
←RPC で get_spots() ファンクションを呼び出す
Slide 29
Slide 29 text
その他
● ユーザ別のデータを保存する場合は RLS を使う
○ RLS : 行レベルセキュリティ
○ 今回は不使用
■ 自分一人で使うので
○ 通常は Recommended
29
Slide 30
Slide 30 text
その他
● 無料プランでは 1 週間利用がないと DB が停止
30
Slide 31
Slide 31 text
まとめ
● Flutter から Supabase の PostgreSQL を使う
○ 単純クエリはクエリビルダを使って問い合わせ
● Flutter から Supabase の PostGIS を使う
○ PostGIS を有効化
○ クエリビルダで関数が使えないのでストアドファンクションを
RPC で呼び出す
31
Slide 32
Slide 32 text
参考情報
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