🐘

Python上でPostGIS検索結果をGeoJSONにて出力

2022/09/04に公開

PostgreSQL

Python

postgis

tech

PythonからPostgreSQLへのアクセスにはpsycopg2を用いる。
バージョン3であるpsycopg3も登場しているが、筆者の環境はApple M1(ARM)のため非対応とのこと（令和4年8月現在）。

実行環境

Apple M1(macOS Monterery)
Python 3.9.13
PostgreSQL 13.6
psycopg2 2.9.3

psycopg2をインポート、PostgreSQLの接続設定を記述。

import psycopg2
dsn = "dbname=XXX host=XXX port=XXX user=XXX password=XXX"

for文を使った取り出し

SQL文を定義する。出力されるgeomはgeometry型のため、ST_AsGeoJSONメソッドでJSON形式に変換するよう定義する。

sql_str ="SELECT ST_AsGeoJSON(geom)::json FROM XXX"

psycopg2を通じてPostgreSQLに接続し、SQL文を実行する。
実行結果はfor文を使って取り出し、"features"の配列内に順次追加する。

with psycopg2.connect(dsn) as conn:
    with conn.cursor() as cur:
        cur.execute(sql_str)
        data = cur.fetchall()
	
    geojson = {"type":"FeatureCollection","features":[]}
    
    for li in data:
        feature = {
            "type":"Feature",
            "geometry":{
                "type":"Polygon",
                "coordinates":li[0]['coordinates']
                }
            }
        geojson['features'].append(feature)

    with open('output.json','w') as f:
        f.write(json.dumps(geojson))

{"type": "FeatureCollection","features": [
{"type": "Feature",
    "geometry": {
        "type": "Polygon",
	"coordinates": [[[140.0, 40.0],...]]]
	}
    },
...]}

SQL文のみで一発出力

先のコードは、予め作成したgeojson辞書にfor文で抜き出した辞書オブジェクトを繰り返し追加という処理をPythonにて行ったが、SQL文にて一発でGeoJSON出力することが可能。

SELECT row_to_json(fc)
FROM (
    SELECT
        'FeatureCollection' As type,
        json_agg(feature) AS features
    FROM (
        SELECT
            'Feature' AS type,
            ST_AsGeoJSON(geom)::json AS geometry
        FROM XXX
    ) AS feature
) AS fc

ポイントは、row_to_json、json_aggの2つの関数。
row_to_json関数は、各行をまとめてJSONオブジェクト化する。

SELECT row_to_json(t) FROM (SELECT 'val1' AS key1, 'val2' AS key2) AS t
# -> {"key1":"val1","key2":"val2"}

json_agg関数は入力値を結合してJSON配列を作成。

SELECT
    json_agg(feature)
FROM (
    SELECT
        ST_AsGeoJSON(geom) AS geometry
    FROM XXX) AS feature
# ->  [{"geometry":"{\"type\":\"Polygon\",\"coordinates\":[...]}"},...]       
# (1 row)

実行環境

for文を使った取り出し

SQL文のみで一発出力

Discussion