Supabaseで PostGISを利用する際の注意点

背景

Supabaseには多くの拡張機能(extensions)が用意されていて、地理情報を扱える PostGIS も用意されています。

以前の記事にて、RLSを利用する際にアプリ専用の app_user を作ったほうがいいということを書きました。

このロールにて、PostGISの地理関数を呼び出そうとしたときにちょっと苦戦したので、備忘録を残しておきます。

結論

search_path に extensions を追加してください。

ALTER USER app_user SET search_path TO public, extensions;

前提

• postgresとは別の app_user ロールを使って データベースにアクセスする
• extensionsで postgis を追加済
• SQLModelで テーブル定義したものを利用
• SQLAlchemyのORMは利用せず、sqlalchemy_core の関数を利用してSQLを組み立てる

SQLModelでのテーブル定義

• sqlmodelには地理関数型は用意されていないので、sa_column を使ってカスタマイズします
• geoalchemy2 というライブラリに用意されているジオグラフィ型として定義します
  • ジオメトリよりもジオグラフィ型のほうが使いやすいので、こちらを選んでいます。

from typing import Any
from geoalchemy2 import Geometry, Geography

class TField(SQLModel, table=True):
    __tablename__ = "t_fields"

    field_id: int | None = Field(default=None, primary_key=True)
    polygon: Any = Field(
        None,
        sa_column=sa.Column(
            Geography(geometry_type="POLYGON", srid=4326, spatial_index=True),
            nullable=True,
        ),
    )

SQLAlchemyでの地理関数の利用

ORMを使わずにsqlを組み立てます。
こんな感じでかけます。

from sqlalchemy import select as sa_select, literal_column, text

def _get_sql():
    columns = [
        TField.field_id,
        literal_column("ST_AREA(t_fields.polygon)").label("area"),
        literal_column("ST_X(ST_Centroid(t_fields.polygon)::GEOMETRY)").label(
            "center_lon"
        ),
        literal_column("ST_Y(ST_Centroid(t_fields.polygon)::GEOMETRY)").label(
            "center_lat"
        ),
        ]    
    stmt = (
        sa_select(*columns)
        .select_from(TField)
        .join(...) # 結合するテーブルがあれば
        .where(...) # フィルタリング条件があれば
    )

    return sql

stmt = _get_sql()

result = session.execute(stmt).all()

注目してほしいのが、literal_column("ST_AREA(t_fields.polygon)") のようにPostGISの地理関数を記述しているところ。もしかしたら func メソッドにあるのかもしれませんが、見つからなかったためにこのように literal_column を使ってそのままSQLを書きます。

起きたエラー

psycopg2を使って postgresにアクセスしていますが、ST_AREAなんて関数はないよ、と怒られます。これは postgres ロールでは起きなかったエラーです。

sqlalchemy.exc.ProgrammingError: (psycopg2.errors.UndefinedFunction) function st_area(extensions.geography) does not exist
LINE 1: ...eld_plans.cultivar_id, m_cultivars.cultivar_name, ST_AREA(t_...
                                                             ^
HINT:  No function matches the given name and argument types. You might need to add explicit type casts.

解決

まず ST_AREA という関数がどこに保存されているか探します。

ダッシュボード > Database > Functions のところで、 schema: extensions を選択します。

すると、st_area という functionsが保存されていることがわかります。

要はextensionsスキーマに存在する関数であることを指定してあげればいいわけです。

さきほどの pythonをちょっと編集します。

from sqlalchemy import select as sa_select, literal_column, text

def _get_sql():
    columns = [
        TField.field_id,
        literal_column("extensions.ST_AREA(t_fields.polygon)").label("area"),
        literal_column("extensions.ST_X(extensions.ST_Centroid(t_fields.polygon)::extensions.GEOMETRY)").label(
            "center_lon"
        ),
        literal_column("extensions.ST_Y(extensions.ST_Centroid(t_fields.polygon)::extensions.GEOMETRY)").label(
            "center_lat"
        ),
        ]    
    stmt = (
        sa_select(*columns)
        .select_from(TField)
        .join(...) # 結合するテーブルがあれば
        .where(...) # フィルタリング条件があれば
    )

    return sql

stmt = _get_sql()

result = session.execute(stmt).all()

postgis由来の関数やタイプすべてに、extensions. というprefixをつけました。これでエラーは解消されます。

とはいえ冗長でやりたくないため、app_userロールの search_path に extensions を追加してあげるほうがスマートです。

というわけで結論になります。

ALTER USER app_user SET search_path TO public, extensions;

おそらく postgis以外のextensionsを利用するときにもこの設定は必要になると思います