【Supabase + PostGIS】サーバーレスでも位置情報を扱いたい

はじめに

こんにちは！
「愛犬との毎日を楽しく便利にするアプリ オトとりっぷ」でエンジニアしています、足立です！

最近アプリ内で位置情報を扱うための諸々を整備しているのですが、サーバーレス構成での位置情報とデータベースに関する情報が少なく苦労しました。
AWS がメイン環境で、VPN レスのサーバーレス構成という Amazon Aurora Serverless も利用できない環境でいかに位置情報を扱うか？の備忘録を残すととともに、同じような疑問を抱えている方の一助になればと思います。

位置情報とデータベース

GeoJson

そもそも、位置情報とはどのような形をしているのでしょうか？
大きく分けて 3 つの形があります。

• ポイント座標（Point）：1 次元の点情報
• ライン座標（LineString）：2 次元の線情報
• ポリゴン座標（Polygon）：3 次元の面情報

これらの情報を必要に応じて使い分けるわけです。
例えば、現在地点から目的地までの直線距離の情報が欲しい場合はポイント座標の情報が、現在地点から目的地までの道程の情報が欲しい場合はライン座標が、目的地の所在地の広さの情報が欲しい場合はポリゴン座標が、必要になるわけですね。

そして次元の異なる情報を扱うためのデータ構造として、GeoJson という規格があります。

https://datatracker.ietf.org/doc/html/rfc7946

Point

{
  "type": "Feature",
  "geometry": {
    "type": "Point",
    "coordinates": [102.0, 0.5]
  }
}

LineString と Polygon

[
  {
    "type": "Feature",
    "geometry": {
      "type": "LineString",
      "coordinates": [
        [102.0, 0.0],
        [103.0, 1.0],
        [104.0, 0.0],
        [105.0, 1.0]
      ]
    }
  },
  {
    "type": "Feature",
    "geometry": {
      "type": "Polygon",
      "coordinates": [
        [
          [100.0, 0.0],
          [101.0, 0.0],
          [101.0, 1.0],
          [100.0, 1.0],
          [100.0, 0.0]
        ]
      ]
    }
  }
]

データベースには、これら 1 次元〜3 次元の情報を上手く格納できなければならないわけです。

位置情報の検索性

データベースに格納するということは、何かしらの条件でデータを抽出し利用したいからです。
例えば近くのお店を検索するなどですね。その場合、近くのお店というのをどのように検索すべきなのでしょうか？
いくつか手法は考えられるのですが、「SQL の機能を利用する方法」「Elasticsearch のような検索エンジンを利用する方法」「GeoHash を利用する方法」の 3 つを整理してみます。

---

まず考えられるのが、SQL を利用する方法です。データベースに直接問い合わせる形なので、一番シンプルな方法だといえます。
SQL の機能を利用する場合、PostgreSQL には PostGIS という拡張機能が、MySQL には標準で Geometry 型が存在します。
その機能の違いについては、以下の記事が非常に参考になります。

https://qiita.com/miyauchi/items/327cef3f8a61a00956bb

---

次に検索エンジンを利用する方法です。
例えば Amazon OpenSearch Service を利用して位置情報を扱うことができます。
詳細な利用方法については、以下の記事が非常に参考になります。

https://qiita.com/dayjournal/items/eb7c2804541929f71c38

とはいえあくまで検索エンジンなので、こちらを利用する場合であってもデータベースは別に用意する必要がありそうです。
なので検索するという目的はこちらで対応可能かもしれませんが、それ以外のデータベースに関する機能をどうするか？を別途考える必要がありそうです。

---

最後に GeoHash を利用する方法です。
そもそも GeoHash とは何ぞや？については、以下の記事が非常に参考になります。

https://qiita.com/d-nakajima/items/da0e3d597bc15b3277bf

本文から引用すると、

一言で言うと、世界地図を緯度経度で 2 分割していき、北（東)を 1,南(西)0 を当てた二進数を 32 進数化したものです。

だそうです。緯度経度情報を文字列にエンコードしたものってことですね。
これの面白いところは、エンコードされた文字列を利用して検索可能な点です。詳細は先ほどの記事にありますのでそちらをご覧いただくのがいいのですが、要するに似たような場所は似たような文字列になるからです。
難点は、あくまでエンコードされてしまった情報なので精度の問題を抱える点です。データ量が多すぎて逆に精度を落としたい場合であれば別ですが、そうでなければ少し使い方を工夫する必要がありそうです。

マネージドなデータベースサービス の選択

AWS で VPN レスのサーバーレス環境を志向した場合、データベースの選択肢はマネージドな DB サービスになります。VPN レスなので Amazon Aurora Serverless も利用できません。

例えば Amazon DynamoDB は、VPN レスのマネージドな NoSQL データベースです。しかし DynamoDB は位置情報を扱うための方法が豊富ではなく、前述の GeoHash を用いた手法などで戦わなければなりません。

そこで、AWS 内部のサービスではなく外部のマネージドなデータベースサービスと連携して構築を目指します。マネージドな DB サービスとして、有名なものに以下のようなものがあります。

• MySQL 系
  • TiDB
• PostgreSQL 系
  • Supabase
  • Neon

この中から選択していくわけですが、まず TiDB は Geometry 型が対応していません。（参考）
ですので、使いたくてもまだちょっと難しそうです。

次に Neon ですが、日本リージョンがありません。（参考）
リージョンにそこまでこだわりが深いわけではありませんが、一方で日本リージョンがないということは日本へのサポートを心配してしまいます。困った時に日本語で対応できないのは地味に辛いです。

というわけで、今回は Supabase を利用してみます。

Supabase の PostGIS 拡張機能

導入方法

公式ドキュメントがあります。

https://supabase.com/docs/guides/database/extensions/postgis?queryGroups=language&language=data

こちらを参考に Table 作成からデータ入力まで全て SQL で済ませていきます。

まずは、PostGIS 拡張機能を許可します。

-- Example: enable the "postgis" extension
create extension postgis with schema "extensions";

次に、Table を作成します。

create table if not exists public.restaurants (
	id int generated by default as identity primary key,
	name text not null,
	location geography(POINT) not null
);

インデックスも貼っておきます。

create index restaurants_geo_index
  on public.restaurants
  using GIST (location);

次に、Table に Item を追加します。

insert into public.restaurants
  (name, location)
values
  ('Supa Burger', st_point(-73.946823, 40.807416)),
  ('Supa Pizza', st_point(-73.94581, 40.807475)),
  ('Supa Taco', st_point(-73.945826, 40.80629));

注意点として、st_point は経度、緯度の順番です。

https://postgis.net/docs/ja/ST_Point.html

無事に Table に Item が追加されましたね。

location データは、0101000020E6100000A4DFBE0E9C91614044FAEDEBC0494240のような形で入力されています。クライアントサイドから location データを取得すると、そのまま上記の文字列が渡されるだけで、残念ながら Point 型に自動で変換してくれません。

そこで、位置情報を取得できるように Database Functions なるものを作成します。
こちらは、近いお店を取得する Database Functionsを作成するための SQL です。

create or replace function nearby_restaurants(lat float, long float)
returns table (id public.restaurants.id%TYPE, name public.restaurants.name%TYPE, lat float, long float, dist_meters float)
language sql
as $$
  select id, name, st_y(location::geometry) as lat, st_x(location::geometry) as long, st_distance(location, st_point(long, lat)::geography) as dist_meters
  from public.restaurants
  order by location <-> st_point(long, lat)::geography;
$$;

---

以上、重要な点として、

• PostGIS 機能は拡張機能を別途許可することで利用可能になる
• location データは0101000020E6100000A4DFBE0E9C91614044FAEDEBC0494240みたいなエンコードされた文字列データの状態で保存される
• デコードされたデータの取得には Database Functions を利用して SQL を書く必要がある

です。データを取り回すのに、Database Functions っていうのが必要なんですね。

Database Functions

Database Functions をクライアントサイドから利用するには、 RPC (Remote Procedure Call)で呼び出す必要があります。
RPC の呼び出し方は簡単で、例えば JavaScript クライアントからだと、以下のようになります。

RPC

const { data, error } = await supabase.rpc('nearby_restaurants', {
  lat: 40.807313,
  long: -73.946713,
})

簡単ですね。

注意点

クライアントライブラリでは自由な SQL を記述できるわけではないので、位置情報取得の全てに Database Functions を書く必要があるということですね。
なので、取得自由度の高い SQL を書いてクライアントサイドでデータ整形するのか、それとも全ての取得パターンの Database Functions を書くのか、運用上のお悩みポイントが生まれそうです。

最後に

ここまで読んでいただきありがとうございました。
私は、位置情報および PostgreSQL に関しては完全な初心者でしたが、Supabase を利用して非常に簡単に位置情報データベースを導入することができました。

もし犬専用の音楽アプリに興味を持っていただけたら、ぜひダウンロードしてみてください！

https://www.oto-trip.com/

参考

https://speakerdeck.com/dayjournal/awsdehazimeruwei-zhi-qing-bao-apurikesiyon

https://zenn.dev/dshukertjr/scraps/cacc30ecbf371c