Upgrade to Pro — share decks privately, control downloads, hide ads and more …

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

B1dca90d4b3ffd2ccd918774e1ba170d?s=47 hmatsu47
PRO
December 21, 2021

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

第 29 回 PostgreSQL アンカンファレンス@オンライン 2021/12/21

B1dca90d4b3ffd2ccd918774e1ba170d?s=128

hmatsu47
PRO

December 21, 2021
Tweet

More Decks by hmatsu47

Other Decks in Technology

Transcript

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

    29 回 PostgreSQL アンカンファレンス@オンライン  2021/12/21 まつひさ(hmatsu47)
  2. 自己紹介 松久裕保(@hmatsu47) • https://qiita.com/hmatsu47 • 現在のステータス: ◦ 名古屋で Web インフラのお守り係をしています

    ◦ しばらく RDBMS 関連の勉強会では登壇していませんでした ▪ これ↓がラスト(5/31・JAWS-UG 名古屋) RDS_Aurora パフォーマンスインサイトのデータを Athena と QuickSight で見る • 「MySQL 8.0 の薄い本」更新終了のお知らせ 2
  3. 最近、情勢変化の兆しが • ついに出た ◦ 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
  4. それはさておき、本日のネタ • Flutter で地図アプリを作ってみた ◦ 地図は Mapbox ▪ 今回の主題ではないので詳細は省略 •

    その中で Supabase の PostgreSQL を試してみた ◦ PostGIS も使ってみた ▪ 本当に「ちょっと使ってみた」程度 4
  5. Flutter とは? • Google 製の UI フレームワーク ◦ 使う言語は Dart

    ◦ 当初はクロスプラットフォームモバイルアプリ開発用 ◦ その後、Web や Windows / macOS / Linux も対象に ◦ 最近バージョン 2.8 が出た ▪ 今年の 3 月に 2.0 が出たばかり 5
  6. Flutter を試そうと思ったきっかけ • 以前 Qiita でバズったこのサイト ◦ https://korette.jp/ 6

  7. Flutter を試そうと思ったきっかけ • 以前 Qiita でバズったこのサイト ◦ https://korette.jp/ ◦ サポーターズの一員として大量にクイズ投稿

    ◦ その後、コロナ禍で観光地の状況が一変 ◦ コロナが落ち着いた隙をみながら問題メンテナンスの旅へ ◦ 旅のお供として、情報収集・整理のためのアプリが欲しい ◦ 作ることにした 7
  8. 作っているアプリ(maptool) • https://github.com/hmatsu47/maptool • 実装済みの主な機能 ◦ 訪問(予定)地へのピン立て(登録) ◦ 登録ピンと関連づけて写真撮影 ◦

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

    登録ピンの検索 ◦ 地図スタイル切り替え ◦ 文化財などの近隣スポット検索←ここで PostGIS を利用 9
  10. 作っているアプリ(maptool) • 訪問(予定)地へのピン立て(登録) 10 注:画面は少し古めのバージョン です(以降同じ)

  11. 作っているアプリ(maptool) • 登録ピンと関連づけて写真撮影 11

  12. 作っているアプリ(maptool) 12 • 登録ピンの検索

  13. 作っているアプリ(maptool) • 地図スタイル切り替え 13

  14. 作っているアプリ(maptool) 14 • 文化財などの近隣スポット検索(Supabase / PostGIS)

  15. Supabase とは? 15

  16. Supabase とは? • BaaS(Backend as a Service)の一つ ◦ Firebase Alternative

    • サービスは 4 つ ◦ Database ◦ Authentication ◦ Storage ◦ Functions(Coming soon) 16
  17. Supabase とは? • BaaS(Backend as a Service)の一つ ◦ Firebase Alternative

    • サービスは 4 つ ◦ Database ← PostgreSQL が使われている ◦ Authentication ◦ Storage ◦ Functions(Coming soon) 17
  18. Flutter から Supabase の PostgreSQL を使う • Supabase を設定 ◦

    SQL editor でテーブル作成 ◦ SQL editor でデータ登録 • Flutter からクエリビルダを使ってアクセス ◦ 内部的に PostgREST を使用 18
  19. テーブル①(近隣スポットのカテゴリ) CREATE TABLE category ( id int GENERATED BY DEFAULT

    AS IDENTITY PRIMARY KEY, categoryname text NOT NULL ); 19
  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; } 20
  21. 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; } 21 ←クエリビルダで select()
  22. Flutter から Supabase の PostGIS を使う • Flutter から Supabase

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

  24. テーブル②(注: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
  25. ストアドファンクション(範囲内のスポットを検索) 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
  26. ストアドファンクション(範囲内のスポットを検索) 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;
  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, '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
  28. 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; } 28 ←RPC で get_spots() ファンクションを呼び出す
  29. その他 • ユーザ別のデータを保存する場合は RLS を使う ◦ RLS : 行レベルセキュリティ ◦

    今回は不使用 ▪ 自分一人で使うので ◦ 通常は Recommended 29
  30. その他 • 無料プランでは 1 週間利用がないと DB が停止 30

  31. まとめ • Flutter から Supabase の PostgreSQL を使う ◦ 単純クエリはクエリビルダを使って問い合わせ

    • Flutter から Supabase の PostGIS を使う ◦ PostGIS を有効化 ◦ クエリビルダで関数が使えないのでストアドファンクションを RPC で呼び出す 31
  32. 参考情報 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