Python(psycopg2) + PostgreSQLでosmを入出力する
/
Python(psycopg2) + PostgreSQLでosmを入出力します.
# ファイル構成
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を入出力しました.