Python(psycopg2) + PostgreSQLでosmを入出力します．

• ファイル構成
• ライブラリのインストール
• OSMの読み込み
• PostgreSQLに格納
• PostgreSQLから出力
• まとめ
• 参考サイト

ファイル構成

project_dir
├── /data
│   ├── /R2_boundary/04_miyagi/04_miyagi.shp
│   ├── /osm/tohoku/gis_osm_roads_free_1.shp
│   └── (省略)
└── merge_bound.ipynb <- 実行用ノートブック

ライブラリのインストール

!pip install geopandas
!pip install shapely
!pip install geoalchemy2

!sudo apt-get update
!sudo apt-get -y install libpq-dev gcc
!pip install psycopg2

OSMの読み込み

geometry(地物情報)はそのままPostgreSQLに格納できないので、WKTに変換します。

import pandas as pd
import geopandas as gpd
import matplotlib.pyplot as plt
from shapely.ops import unary_union

# osmデータのshpファイル読込
osm_gdf = gpd.read_file("./data/osm/tohoku/gis_osm_roads_free_1.shp")

# 0~10行を抽出
gdf1 = osm_gdf[0:10]
# WKTに変換
gdf1['geometry'] = gdf1['geometry'].apply(lambda x: WKTElement(x.wkt, srid=4326))
display(gdf1.head(2))

PostgreSQLに格納

import os
import psycopg2
from sqlalchemy import create_engine
from geoalchemy2 import Geometry, WKTElement

# DBのURL
DATABASE_URL='postgresql://postgre:postgre@workspace-postgres-1:5432/postgres'

# テーブル作成のDB起動
engine = create_engine(DATABASE_URL)
# テーブル作成 if_exists='replace' or 'append'
gdf1.to_sql('osm',con=engine,if_exists='replace',index=None,
               dtype={'geometry': Geometry(geometry_type='LINESTRING', srid= 4326)})

PostgreSQLから出力

そのままだと、下記のようにWKTのままで出力します。

import os
import psycopg2
import pandas as pd

# DATABASE_URL
DATABASE_URL='postgresql://postgre:postgre@workspace-postgres-1:5432/postgres'

# postgresの接続
conn = psycopg2.connect(DATABASE_URL)
conn.autocommit = True # 操作の重複を防ぐ(databaseの操作)呪文

# テーブル名一覧を取得するSQL
query = """SELECT geometry FROM public.osm;"""
df = pd.read_sql(query, con=conn)

# dbとカーソルを閉じる
conn.close()

display(df.head(2))
# 0102000020E6100000270000004308235103A36140829E...
# 0102000020E6100000050000009F4CED56609C614090B1...

酔って、下記のようにWKTからテキストに変換して出力します。

import os
import psycopg2
import pandas as pd

# DATABASE_URL
DATABASE_URL='postgresql://postgre:postgre@workspace-postgres-1:5432/postgres'

# postgresの接続
conn = psycopg2.connect(DATABASE_URL)
# conn.autocommit = True # 操作の重複を防ぐ(databaseの操作)呪文
# cur = conn.cursor()

# テーブル名一覧を取得するSQL
query = """SELECT ST_AsText(geometry) FROM public.osm;"""
df = pd.read_sql(query, con=conn)

# dbとカーソルを閉じる
conn.close()

display(df.head(2))
# LINESTRING(141.0941549 38.8040228,141.0945369 ...
# LINESTRING(140.8867602 38.3035443,140.8866131 ...

まとめ

Python(psycopg2) + PostgreSQLでosmを入出力しました．

参考サイト