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

# 背景

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

[以前の記事](https://qiita.com/r1wtn/items/8dceb821ad43b4dc10f8)にて、RLSを利用する際にアプリ専用の `app_user` を作ったほうがいいということを書きました。

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


# 結論

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

```sql
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` というライブラリに用意されているジオグラフィ型として定義します
    - ジオメトリよりもジオグラフィ型のほうが使いやすいので、こちらを選んでいます。

```py
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を組み立てます。
こんな感じでかけます。

```py
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 を選択します。

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/494791/194c7e52-2afc-df8a-d2ca-01ddde6d52ac.png)

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


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

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


```py
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` を追加してあげるほうがスマートです。

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

```sql
ALTER USER app_user SET search_path TO public, extensions;
```

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