tatsunode
12/26/2017 - 9:57 PM

PostGIS

delete-existence-area
content_copyfile_download
DELETE FROM existence_area
WHERE ap IN
(SELECT id FROM ap
WHERE tag = 'shinsaibashi-SET123');

DELETE FROM ap
WHERE tag = 'shinsaibashi-SET123';
osm.md
content_copyfile_download
  • Rendered
  • Source

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()
sqls.md
content_copyfile_download
  • Rendered
  • Source

ジオメトリカラムの追加

SELECT AddGeometryColumn('tbl2', 'geom', 4612, 'point', 2);

確認・SRIDとか

SELECT * FROM geometry_columns;
 f_table_catalog | f_table_schema | f_table_name | f_geometry_column | coord_dimension | srid | type  
-----------------+----------------+--------------+-------------------+-----------------+------+-------
 db              | public         | tbl          | geom              |               2 | 4612 | POINT
 db              | public         | tbl2         | geom              |               2 | 4612 | POINT