More than 1 year has passed since last update.

IBM

Db2地理空間分析: 緯度・経度情報をインポート

Last updated at 2022-11-01Posted at 2022-10-07

Db2地理空間分析シリーズ

当記事は「5. 緯度・経度情報をインポート」です。

前提

「3. JGD2011座標系の設定」が実施済みとします。
未実施の場合は、実施の上、進んでください。

実行環境

当記事ではjupyter notebookでpythonを使用して、SQLでインポートしています。Db2 Jupyter Notebook Extensionsを使用しているため、jupyter notebookでの実行が前提となっています。
このnotebookの内容はgithubで公開しています：
https://github.com/kyokonishito/Db2_Spatial_Analysis/blob/main/notebooks/Data_Setup.ipynb
の　「5. シェアサイクルポート情報のインポート」部分です。

1. 緯度・経度情報データ: シェアサイクルポート情報について

ここでは公共交通オープンデータセンターの以下の2つのデータを1つのテーブルSHAREBIKE_STATIONにインポートします。これらの2つのstation_informationは、シェアサイクルポートの経度・緯度の情報を持っています。

参考: Qiita 公共交通オープンデータセンターにあるドコモ・バイクシェアのstation_information情報をCSVファイルにする

2. SHAREBIKE_STATIONテーブルの作成

以下のDDLで作成します。GEO_POINTに経度・緯度情報をST_Pointデータタイプで入れます。

DROP TABLE "SHAREBIKE_STATION";
CREATE TABLE "SHAREBIKE_STATION"  (
    "STATION_ID" VARCHAR(10 OCTETS) NOT NULL, 
    "REGION_ID" VARCHAR(10 OCTETS) , 
    "CAPACITY" INT , 
    "NAME" VARCHAR(120 OCTETS) ,
    "ADDRESS"   VARCHAR(300 OCTETS) ,
    "URL"  VARCHAR(300 OCTETS) ,
    "COMPANY"   VARCHAR(60 OCTETS) ,    
    "GEO_POINT" ST_Point,
    PRIMARY KEY (STATION_ID)) ;

3. バイクシェア関連情報 station_informationの取得

pythonでの取得方法を説明します。
参考: Qiita 公共交通オープンデータセンターにあるドコモ・バイクシェアのstation_information情報をCSVファイルにする

3.1 ドコモ・バイクシェアバイクシェア関連情報 station_informationの取得

公共交通オープンデータセンターから

ドコモ・バイクシェアバイクシェア関連情報(GBFS形式)のバイクシェア関連情報 station_information
をダウンロードし、pandasのDataFrameに入れます。

## ドコモ・バイクシェア バイクシェア関連情報 station_information　緯度・経度情報を取得
import requests
import json
import pandas as pd

#ドコモ・バイクシェア バイクシェア関連情報  station_information
req_url = 'https://api-public.odpt.org/api/v4/gbfs/docomo-cycle-tokyo/station_information.json'

# URLにアクセス
request = requests.get(req_url)

# 必要な部分のみ抜き出し
data_json = json.loads(request.text)['data']['stations']

# pandasのDataFrameに入れる
domomo_df = pd.DataFrame(data=data_json)
domomo_df

3.2 OpenStreet（ハローサイクリング）バイクシェア関連情報 station_informationの取得

公共交通オープンデータセンターから

OpenStreet（ハローサイクリング）バイクシェア関連情報(GBFS形式)のバイクシェア関連情報(GBFS形式 / station_information)
をダウンロードし、pandasのDataFrameに入れます。

#OpenStreet（ハローサイクリング） バイクシェア関連情報  station_information　緯度・経度情報を取得
req_url = 'https://api-public.odpt.org/api/v4/gbfs/hellocycling/station_information.json'

# URLにアクセス
request = requests.get(req_url)

# 必要な部分のみ抜き出し
data_json = json.loads(request.text)['data']['stations']

# pandasのDataFrameに入れる
hello_df = pd.DataFrame(data=data_json)
hello_df

4. バイクシェア関連情報 station_informationのSHAREBIKE_STATIONへのインポート

ST_Point(<緯度>, <経度>, <空間参照系 ID >) でST_Pointデータタイプに変換して、INSERTします。
ここでは「3. JGD2011座標系の設定」で設定したJGD2011の空間参照系を使います。srs_idは2011に設定したとしています。

ドコモ・バイクシェア情報をINSERTするSQLの一例はこんな感じになります:

INSERT INTO SHAREBIKE_STATION (
station_id, region_id, capacity, name, company, GEO_POINT) 
VALUES (
'D00010001', '1', 8, 
'A1-01.千代田区役所', 'DOCOMO', 
ST_Point(139.753281, 35.693799, 2011))

またここでのpythonからのDbアクセスには、notebookでdb2 SQLを使いやすくするDb2 Jupyter Notebook Extensionsを使用しています。
(使い方の参考: https://speakerdeck.com/kyokonishito/db2-and-jupyter-notebooks)
あらかじめ、こちらのnotebookの1.1から1.3の実行が必要です。

4.1 ドコモ・バイクシェアバイクシェア関連情報 station_informationのSHAREBIKE_STATIONへのインポート

最初の1つだけ、実行したSQLを出力するようにしています。

# SHAREBIKE_STATIONへのインポート(といいつつINSERT)
import datetime
print("{0} Start.".format(datetime.datetime.now()))

srs_id = 2011 # 空間参照系 IDは2011とする
print("srs_id is " +str(srs_id))
%sql AUTOCOMMIT OFF
sql_flag = '-e'
for i, row in domomo_df.iterrows():
    station_id='D' + str(row['station_id'])
    region_id=row['region_id']
    capacity=row['capacity']
    name=row['name']
    company="DOCOMO"
    lon=row['lon']
    lat=row['lat']

    
    %sql {sql_flag} INSERT INTO SHAREBIKE_STATION (station_id, region_id, capacity, name, company, GEO_POINT) VALUES (:station_id, :region_id, :capacity, :name, :company, ST_Point(:lon, :lat, {srs_id}))
    sql_flag = '-q'
    if sqlcode != 0:
        print("Line {0} Error : sqlcode:{1}, sqlstate:{2}, Message: {3}".format(i, sqlcode, sqlstate, sqlerror))
        break;

    if i%100 == 0:
        %sql COMMIT WORK
        print("{0} {1}/{2}: completed.".format(datetime.datetime.now(), i, len(domomo_df)))

%sql COMMIT WORK
%sql AUTOCOMMIT ON
print("{0} End.".format(datetime.datetime.now()))

4.2 OpenStreet（ハローサイクリング）バイクシェア関連情報 station_informationのSHAREBIKE_STATIONへのインポート

最初の1つだけ、実行したSQLを出力するようにしています。

# SHAREBIKE_STATIONへのインポート(といいつつINSERT)
import datetime
print("{0} Start.".format(datetime.datetime.now()))

srs_id = 2011 # 空間参照系 IDは2011とする
%sql AUTOCOMMIT OFF
sql_flag = '-e'
for i, row in hello_df.iterrows():
    station_id='H' + str(row['station_id'])
    capacity=row['vehicle_type_capacity']['num_bikes_limit']
    name=row['name']
    address=row['address']
    url=row['rental_uris']
    company="HELLO"
    lon=row['lon']
    lat=row['lat']

    
    %sql {sql_flag} INSERT INTO SHAREBIKE_STATION (station_id,  capacity, name, address, url, company, GEO_POINT) VALUES (:station_id,  :capacity, :name, :address, :url, :company, ST_Point(:lon, :lat, {srs_id}))
    sql_flag = '-q'
    if sqlcode != 0:
        print("Line {0} Error : sqlcode:{1}, sqlstate:{2}, Message: {3}".format(i, sqlcode, sqlstate, sqlerror))
        break;

    if i%100 == 0:
        if i%1000==0:
            %sql COMMIT WORK
            print("{0} {1}/{2}: commit completed.".format(datetime.datetime.now(), i, len(hello_df)))
        print("{0} {1}/{2}: completed.".format(datetime.datetime.now(), i, len(hello_df)))

%sql COMMIT WORK
%sql AUTOCOMMIT ON
print("{0} End.".format(datetime.datetime.now()))

これでSHAREBIKE_STATIONテーブルに経度・緯度情報が空間情報タイプST_POINT属性で入りました!

別の方法としてはgeopandasライブラリ等を使用して、SHAPEファイルを作成し、SHAPEファイルのインポートの方法でロードする方法もあります(この方がおそらくロード時間が短いです)。

インポートした位置情報の地図での出力は「6. DBeaverで地理空間情報を表示してみる」で説明します。

以上です。

You get articles that match your needs
You can efficiently read back useful information
You can use dark theme

What you can do with signing up