OSMから建造物データだけpostgisへ
OSMをgeojsonに変換
$ npm install -g osmtogeojson
$ osmtogeojson file.osm > file.geojson
add
def add(filename):
# 必要なカラムを選択
column_list = ["id", "building", "name", "building:levels", "height", "geometry"]
gdf = geopandas.read_file(filename)
gdf = geopandas.GeoDataFrame(gdf, columns=column_list)
# 建物以外のオブジェクトを除外
gdf = gdf[pandas.isnull(gdf.building) == False]
# DB コネクト
conn = psycopg2.connect(db_params)
d_cur = conn.cursor(cursor_factory=psycopg2.extras.DictCursor)
for index, row in gdf.iterrows():
if "node" in str(row['id']):
continue
print(row['id'], row['name'], row['building:levels'], row['geometry'])
query = """INSERT INTO building (osm_id, name, levels, height, geometry)
VALUES (%s, %s, %s, %s, ST_GeomFromText(%s, %s))
ON CONFLICT ON CONSTRAINT building_pkey
DO UPDATE SET name=%s, levels=%s, height=%s, geometry=ST_GeomFromText(%s, %s)
"""
levels = 4
if row['building:levels'] is not None:
try:
levels = int(row['building:levels'])
except ValueError:
levels = 4
row['height'] = levels * 5
values = (
row['id'],
row['name'],
levels,
row['height'],
str(row['geometry']),
4612,
row['name'],
levels,
row['height'],
str(row['geometry']),
4612
)
d_cur.execute(query, values)
d_cur.close()
conn.commit()
conn.close()