8.1K Views
April 16, 22
スライド概要
第 29 回 PostgreSQL アンカンファレンス@オンライン 2021/12/21
Qiita や Zenn でいろいろ書いてます。 https://qiita.com/hmatsu47 https://zenn.dev/hmatsu47 MySQL 8.0 の薄い本 : https://github.com/hmatsu47/mysql80_no_usui_hon Aurora MySQL v1 → v3 移行計画 : https://zenn.dev/hmatsu47/books/aurora-mysql3-plan-book
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
最近、情勢変化の兆しが ● ついに出た ○ 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
それはさておき、本日のネタ ● 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
Flutter を試そうと思ったきっかけ ● 以前 Qiita でバズったこのサイト ○ https://korette.jp/ ○ サポーターズの一員として大量にクイズ投稿 ○ その後、コロナ禍で観光地の状況が一変 ○ コロナが落ち着いた隙をみながら問題メンテナンスの旅へ ○ 旅のお供として、情報収集・整理のためのアプリが欲しい ○ 作ることにした 7
作っているアプリ(maptool) ● https://github.com/hmatsu47/maptool ● 実装済みの主な機能 ○ 訪問(予定)地へのピン立て(登録) ○ 登録ピンと関連づけて写真撮影 ○ 登録ピンの検索 ○ 地図スタイル切り替え ○ 文化財などの近隣スポット検索 8
作っているアプリ(maptool) ● https://github.com/hmatsu47/maptool ● 実装済みの主な機能 ○ 訪問(予定)地へのピン立て(登録) ○ 登録ピンと関連づけて写真撮影 ○ 登録ピンの検索 ○ 地図スタイル切り替え ○ 文化財などの近隣スポット検索←ここで PostGIS を利用 9
作っているアプリ(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
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