サーバーレスで建物の緯度経度を求める

# はじめに
今年10/23から3日間、[Plateauアカデミー2024札幌](https://plateau-academy2024-sapporo.peatix.com/)というイベントに参加しました。

- Plateauアカデミー参加までは『Plateau = ビルの3Dモデル』と思い込んでいました。北海道の片田舎に住む私には縁遠い世界だなと思っていました
- しかし、参加してみて『Plateau = 色々なGISデータを整理したデータセット（に3Dモデルも含まれている）』ということがわかりました
- [Plateauアカデミー](https://aigid.jp/?page_id=2630)は素晴らしい試みでした。私のように『GISワカラナイ』という人はぜひご参加を:santa_tone2:

# 概要
GISでは沢山ファイルを使います。面倒くさくなってDBとかに入れようとすると「PostGIS」などのサーバーを用意したり何かと大変です。そこで、サーバーレスな環境（AWS Lambda）を使って、GeoParquetに格納されたLOD0の建物データを取得し、建物の代表的なポイント（重心というらしい）の緯度経度を求めてみようと思います。
あとは、地図にプロットするなり、可視化に利用したりしたら良いのです。

:::note warn
『PostGISはダメ、DuckDBは良い』ということではありません。
PostGIS🐘とDuckDB🦆では使える[関数の数](https://duckdb.org/docs/extensions/spatial/functions)など機能も違います。
またDBの進化はすさまじく、ブラウザで動く（WASM）PostgreSQLとして[PGlite](https://pglite.dev/)も登場し、[Software Design 2024年11月号](https://gihyo.jp/magazine/SD/archive/2024/202411)でも紹介されています。
:::

- DBを立てないで、3D都市モデルの情報をクエリする
- コンピューティング用（SQL実行する側）も使うときだけ動かす
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/30994/5394995b-4d3a-589e-3fef-43a6eeadbe4f.png)

## 使う技術要素
- AWS Lambda(バージニア北部) ランタイムは`Python 3.12.0`
- DuckDB `1.1.2`

### Lambdaの設定
- メモリ：`256MB`
- タイムアウト：5分
※メモリ量を増やせばもっと早くなると思いますが課金に注意。

## 準備＆用意するもの
### ネットに置かれたGeoParquet
今回は、Plateauで使わる[FME Form](https://www.mlit.go.jp/plateau/learning/tpc04-1/)のライセンスを取り扱うPacific Spatial Solutions株式会社が、[Flateau](https://github.com/pacificspatial/flateau)(GeoParquet形式に変換したデータセット)を公開してくれているのでお借りします。
※自分でAWS S3にGeoParquetをアップし、プライベートアクセス（VPCE経由など）しても良いです。
※S3のほうは私が試したときは404(Not Found)でした。S3 URIで指定してもOKです。
- [札幌市の建物（LOD0）のGeoParquet](https://source.coop/pacificspatial/flateau/parquet/01100_sapporo-shi_2020_building_lod0.parquet)
`https://source.coop/pacificspatial/flateau/parquet/01100_sapporo-shi_2020_building_lod0.parquet`


### Lambda関数にLayerを登録
<details><summary>Layerを登録</summary>
- Pandasを登録

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/30994/99059a7f-ae24-d431-bd06-e4c8cc6e8768.png)

- DuckDBを登録
自分で用意したzipか、[Keith's Layers](https://github.com/keithrozario/Klayers/tree/master/deployments)などから入手したzipをアップロードする。「レイヤーソース」には「ARNを指定」を選びます
</details>

## 実施前の備忘

- Lambdaには無料枠がありますが、実行時間 & 割り当てたメモリ量 & リクエスト数で費用が決まります
- 自分のS3にアクセス場合、ソースの中にCredential（認証情報など）を書いているものがありますが、Lambda関数にS3バケットにアクセスできるロールを付与するのが良いです（Lambda 関数 > 設定 > アクセス権限 > 実行ロール > ロール名　から）

# コードと実行結果

```python
import json
import time
import duckdb
import pandas as pd
import os

def lambda_handler(event, context):
    # GeoParquetのuri
    PATH_GEOPARQUET = "https://data.source.coop/pacificspatial/flateau/parquet/01100_sapporo-shi_2020_building_lod0.parquet"

    try:
        # メモリ不足回避のため「:memory:」ではなく、一時ファイルを利用
        conn = duckdb.connect(database='/tmp/geoparquet_fromS3.db', read_only=False)
        conn.execute(f"SET extension_directory='/tmp';")
        # 必要な拡張機能のインストールとロード
        conn.execute("INSTALL httpfs;LOAD httpfs;")
        conn.execute("INSTALL spatial;LOAD spatial;")
        # 認証が必要なS3バケットの場合、認証情報をロードする
        #conn.execute("CALL load_aws_credentials();")

        # クエリ実行：各建物の緯度経度（geometryの重心から）を取得
        df_ret = conn.execute(f"SELECT ST_X(ST_Centroid(geom)) AS longitude, ST_Y(ST_Centroid(geom)) AS latitude FROM '{PATH_GEOPARQUET}' limit 100").df()

        # Lambdaのレスポンスとして返却
        return {
            'statusCode': 200,
            'body': json.dumps(df_ret.to_dict(orient='records'))
        }

    except Exception as e:
        # エラーが発生した場合
        print(f"Error occurred: {str(e)}")  # CloudWatchにエラーメッセージを記録
        return {
            'statusCode': 500,
            'body': str(e)
        }
```

## 実行結果
何回かやってみて、20秒前後でした。
「遅い！」とか「OLTPで使えない...」と思った人は、パフォーマンスチューニングしましょう。
- 札幌市のデータは`170.2 MiB`
- 実行時間：20～25秒
- メモリ：256MBのうち、254MBを利用

<details><summary>レスポンスの例</summary>

```json
{
  "statusCode": 200,
  "body": "[{\"longitude\": 141.2981069136741, \"latitude\": 42.89900331312547}, {\"longitude\": 141.3284126806404, \"latitude\": 42.89885287271848}, {\"longitude\": 141.3283660115009, \"latitude\": 42.898952875009265}, {\"longitude\": 141.3298711253525, \"latitude\": 42.89816709767689}, {\"longitude\": 141.33305769282344, \"latitude\": 42.89612172015246}, {\"longitude\": 141.32989503830785, \"latitude\": 42.8981282453123}, {\"longitude\": 141.32845843973962, \"latitude\": 42.89892668422186}, {\"longitude\": 141.35234581288586, \"latitude\": 42.89923749130498}, {\"longitude\": 141.35415789499618, \"latitude\": 42.899045963377624}, {\"longitude\": 141.2979242168052, \"latitude\": 42.90464386033472}, {\"longitude\": 141.2998652354492, \"latitude\": 42.90552241387512}, {\"longitude\": 141.29970395093653, \"latitude\": 42.90579647943938}, {\"longitude\": 141.29933725984884, \"latitude\": 42.905700472088625}, {\"longitude\": 141.29947265590607, \"latitude\": 42.90509305863892}, {\"longitude\": 141.2998134686353, \"latitude\": 42.905689212576874}, {\"longitude\": 141.29947840114147, \"latitude\": 42.90541757888197}, {\"longitude\": 141.29984631101854, \"latitude\": 42.905738964297896}, {\"longitude\": 141.298059309896, \"latitude\": 42.90468244675313}, {\"longitude\": 141.2995372155015, \"latitude\": 42.9049067672811}, {\"longitude\": 141.29970419612417, \"latitude\": 42.90560426922681}, {\"longitude\": 141.29967405192357, \"latitude\": 42.90533883422913}, {\"longitude\": 141.29780890882788, \"latitude\": 42.90462215090949}, {\"longitude\": 141.30234803983745, \"latitude\": 42.90571039936401}, {\"longitude\": 141.3028703805852, \"latitude\": 42.90503280189168}, {\"longitude\": 141.30179058710056, \"latitude\": 42.90576748894852}, {\"longitude\": 141.3011136872061, \"latitude\": 42.905233940717295}, {\"longitude\": 141.3011318512981, \"latitude\": 42.905635870705275}, {\"longitude\": 141.30562243248272, \"latitude\": 42.907807169238716}, {\"longitude\": 141.30294693377752, \"latitude\": 42.90527464418826}, {\"longitude\": 141.30096060541177, \"latitude\": 42.90569067139922}, {\"longitude\": 141.30175332488867, \"latitude\": 42.90564507147962}, {\"longitude\": 141.30270882235638, \"latitude\": 42.90541770341385}, {\"longitude\": 141.30080432264083, \"latitude\": 42.90703000151144}, {\"longitude\": 141.30554625114345, \"latitude\": 42.90819400625086}, {\"longitude\": 141.3010235826848, \"latitude\": 42.90572995255447}, {\"longitude\": 141.3013239570847, \"latitude\": 42.90537072043273}, {\"longitude\": 141.30124777215488, \"latitude\": 42.905299779945544}, {\"longitude\": 141.30060769260834, \"latitude\": 42.90677271285229}, {\"longitude\": 141.30523330767613, \"latitude\": 42.90783857283568}, {\"longitude\": 141.30525652105436, \"latitude\": 42.90790232166247}, {\"longitude\": 141.3208778173646, \"latitude\": 42.90831741563376}, {\"longitude\": 141.32064590525178, \"latitude\": 42.90816317798288}, {\"longitude\": 141.3203029041579, \"latitude\": 42.90830003925709}, {\"longitude\": 141.32068821429056, \"latitude\": 42.90833106072266}, {\"longitude\": 141.33080324889423, \"latitude\": 42.90459623972993}, {\"longitude\": 141.33652675894118, \"latitude\": 42.90763044559664}, {\"longitude\": 141.33185760510636, \"latitude\": 42.905353395361644}, {\"longitude\": 141.33583847421986, \"latitude\": 42.90462634784521}, {\"longitude\": 141.33538632533654, \"latitude\": 42.90453298177851}, {\"longitude\": 141.3285007157425, \"latitude\": 42.90274226819321}, {\"longitude\": 141.3311314204516, \"latitude\": 42.90769704486608}, {\"longitude\": 141.3286389041352, \"latitude\": 42.903004115054046}, {\"longitude\": 141.33655663875916, \"latitude\": 42.907566429666545}, {\"longitude\": 141.33634254167936, \"latitude\": 42.907852231375706}, {\"longitude\": 141.33389208644806, \"latitude\": 42.90493947274411}, {\"longitude\": 141.32827785530841, \"latitude\": 42.90377019921631}, {\"longitude\": 141.32830301555325, \"latitude\": 42.90365476445861}, {\"longitude\": 141.33519851416366, \"latitude\": 42.90513437222915}, {\"longitude\": 141.33650425756656, \"latitude\": 42.90755998004077}, {\"longitude\": 141.33193859951106, \"latitude\": 42.90594399522143}, {\"longitude\": 141.33038589191773, \"latitude\": 42.90441680513824}, {\"longitude\": 141.3355961455976, \"latitude\": 42.90488876988415}, {\"longitude\": 141.33368327095332, \"latitude\": 42.90491342789189}, {\"longitude\": 141.32832572135865, \"latitude\": 42.9031206411437}, {\"longitude\": 141.33074604075804, \"latitude\": 42.90595940336555}, {\"longitude\": 141.33235526068452, \"latitude\": 42.905094514919156}, {\"longitude\": 141.33422458632486, \"latitude\": 42.90480468288163}, {\"longitude\": 141.32820548937366, \"latitude\": 42.90375631334452}, {\"longitude\": 141.33216273934605, \"latitude\": 42.90530138214457}, {\"longitude\": 141.33438134744165, \"latitude\": 42.904744967919484}, {\"longitude\": 141.3282531422307, \"latitude\": 42.90387474341559}, {\"longitude\": 141.33543448908577, \"latitude\": 42.90484641604461}, {\"longitude\": 141.336845163416, \"latitude\": 42.90768283761475}, {\"longitude\": 141.3425666367413, \"latitude\": 42.90511982903593}, {\"longitude\": 141.3423453201913, \"latitude\": 42.90483877871448}, {\"longitude\": 141.34472910206154, \"latitude\": 42.908116411725}, {\"longitude\": 141.33934559246438, \"latitude\": 42.90424797508398}, {\"longitude\": 141.3387532848731, \"latitude\": 42.90389475073593}, {\"longitude\": 141.34008856355564, \"latitude\": 42.90643855326183}, {\"longitude\": 141.34285751495807, \"latitude\": 42.90391904635038}, {\"longitude\": 141.34214787401433, \"latitude\": 42.90488676614692}, {\"longitude\": 141.34981206604328, \"latitude\": 42.90799052520812}, {\"longitude\": 141.3389441171827, \"latitude\": 42.90557775382784}, {\"longitude\": 141.3428168877417, \"latitude\": 42.90395883033573}, {\"longitude\": 141.3390660476893, \"latitude\": 42.9036453420945}, {\"longitude\": 141.33874910016988, \"latitude\": 42.90381086487707}, {\"longitude\": 141.34491755907897, \"latitude\": 42.90819727578652}, {\"longitude\": 141.34280324682013, \"latitude\": 42.90377003074618}, {\"longitude\": 141.33921066859475, \"latitude\": 42.904981085370224}, {\"longitude\": 141.33925305043252, \"latitude\": 42.904294749138394}, {\"longitude\": 141.34265681489663, \"latitude\": 42.903853431975904}, {\"longitude\": 141.33912238249212, \"latitude\": 42.90446617420037}, {\"longitude\": 141.33876999441944, \"latitude\": 42.90399332722818}, {\"longitude\": 141.33898491979832, \"latitude\": 42.90570539336014}, {\"longitude\": 141.3429709022292, \"latitude\": 42.905433979641174}, {\"longitude\": 141.3386839965921, \"latitude\": 42.90447244187414}, {\"longitude\": 141.34992824167313, \"latitude\": 42.90806154689269}, {\"longitude\": 141.34836296250072, \"latitude\": 42.9072483918286}, {\"longitude\": 141.3392151233358, \"latitude\": 42.90419733359719}, {\"longitude\": 141.33915798837305, \"latitude\": 42.90402915160588}]"
}
```
</details>

# 感想
DuckDBの使い勝手がよく、デスクトップアプリの[DBeaver](https://dbeaver.io/)やVS Codeでも[プラグイン](https://marketplace.visualstudio.com/items?itemName=Evidence.sqltools-duckdb-driver)を入れるとすぐに利用できました。
また、DuckDB`1.1.0`にGeoParquetのミニマム仕様が取り込まれたようです。
https://github.com/duckdb/duckdb_spatial/issues/27#issuecomment-2163327032

すばらしいですね。