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

はじめに

今年10/23から3日間、Plateauアカデミー2024札幌というイベントに参加しました。

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

概要

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

『PostGISはダメ、DuckDBは良い』ということではありません。
PostGIS🐘とDuckDB🦆では使える関数の数など機能も違います。
またDBの進化はすさまじく、ブラウザで動く（WASM）PostgreSQLとしてPGliteも登場し、Software Design 2024年11月号でも紹介されています。

• DBを立てないで、3D都市モデルの情報をクエリする
• コンピューティング用（SQL実行する側）も使うときだけ動かす
  

使う技術要素

• AWS Lambda(バージニア北部) ランタイムはPython 3.12.0
• DuckDB 1.1.2

Lambdaの設定

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

準備＆用意するもの

ネットに置かれたGeoParquet

今回は、Plateauで使わるFME Formのライセンスを取り扱うPacific Spatial Solutions株式会社が、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

Lambda関数にLayerを登録

Layerを登録

- Pandasを登録

• DuckDBを登録
  自分で用意したzipか、Keith's Layersなどから入手したzipをアップロードする。「レイヤーソース」には「ARNを指定」を選びます

実施前の備忘

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

コードと実行結果

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を利用

レスポンスの例

{
  "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}]"
}

感想

DuckDBの使い勝手がよく、デスクトップアプリのDBeaverやVS Codeでもプラグインを入れるとすぐに利用できました。
また、DuckDB1.1.0にGeoParquetのミニマム仕様が取り込まれたようです。
https://github.com/duckdb/duckdb_spatial/issues/27#issuecomment-2163327032

すばらしいですね。