この記事は PostgreSQL Advent Calendar 2021 1 日目のエントリです。
今年の秋頃から、個人的に使うアプリを Flutter で作り始めました。
- hmatsu47 / maptool（GitHub）
その経緯については軽くこちらで触れています。
関連記事はこちらです。
- Flutter で Mapbox の地図を現在位置を中心にして表示してみる
- Flutter で Mapbox の地図を操作してみる
- Flutter + Mapbox で Symbol をタップして id を取得・表示する
- Flutter + Mapbox でピン（Symbol）の情報を DB（SQLite）に保存する
- Flutter で Image Picker（0.8.2 以降）を使って撮った写真をファイル保存する
- 小ネタ／Flutter で AWS S3 へのアクセスに minio を使う
- Flutter で Image Picker を使って連続して写真を撮る
これらの記事の続きで（スライド中にもありますが） Supabase の Database（PostgreSQL）にテーブルを作成して PostgREST 経由で PostGIS の機能を呼び出す機会がありましたので、ここに記事として残しておきます。
参考記事：
- Supabaseのデータベースを使うときに役立つ情報（kabochapo さん）
Supabase の PostgreSQL で PostGIS を有効にする
Database → Extensions で、POSTGIS を ON にします。
テーブルを作成する
geometry 型は Supabase の管理画面にある Table editor では作成・登録できませんので、SQL editor を使って作成・登録します（あわせて INDEX も作成・登録）。
テーブル定義
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://www.pref.aichi.jp/soshiki/joho/0000069385.html
- このサンプルデータは、以下の著作物を改変して利用しています。
ストアドファンクションを作成する
通常であれば普通に SQL を書けばいいのですが、Flutter から PostgREST 経由でアクセスする場合は、前掲の参考記事にあるようにそのまま SQL を実行することができません（多分）。
- Supabaseのデータベースを使うときに役立つ情報（kabochapo さん）
というわけで、記事を参考にしてストアドファンクションを（SQL editor で）作成・登録します。
ストアドファンクション
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 からストアドファンクションを呼び出す
こちらも先の記事を参考に、クエリビルダではなく RPC 経由で呼び出します。
pubspec.yaml（関連部分）
mapbox_gl: ^0.14.0
supabase: ^0.2.9
※mapbox_glのLatLng型を利用。
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);
}
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()が不要になりました。
旧コード（部分）
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<dynamic> items =
await client.rpc('get_spots', params: {
'point_latitude': latLng.latitude,
'point_longitude': latLng.longitude,
'dist_limit': distLimit
});
※ item['distance'] as num が PostGIS を使って計算した距離を受け取っている部分。
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 さんです。