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

`Db2地理空間分析`シリーズ


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

1. [Db2の地理空間分析機能](https://qiita.com/nishikyon/items/2f8bc4858e2d54bf9b1a)
1. [地理空間分析機能を有効にする](https://qiita.com/nishikyon/items/4cd0430e4ebb7d541a82)
1. [JGD2011座標系の設定](https://qiita.com/nishikyon/items/fc97391f02b4e9e4bb9c)
1. [SHAPEファイルのインポート](https://qiita.com/nishikyon/items/fd6d166fdbf6523930d4)
1. [**緯度・経度情報をインポート**](https://qiita.com/nishikyon/items/cf90796a13fa7d2cf769)
1. [DBeaverでDb2の地理空間情報を表示してみる](https://qiita.com/nishikyon/items/60e23324b1ec1f65deb5)
1. [空間処理変数を使ってみる](https://qiita.com/nishikyon/items/7eeb7ee541e7d4f76a7f)

# 前提
[「3. JGD2011座標系の設定」](https://qiita.com/nishikyon/items/fc97391f02b4e9e4bb9c)が実施済みとします。
未実施の場合は、実施の上、進んでください。

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

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

- [ドコモ・バイクシェア バイクシェア関連情報(GBFS形式)](https://ckan.odpt.org/dataset/c_bikeshare_gbfs-d-bikeshare)の[バイクシェア関連情報 station_information](https://ckan.odpt.org/dataset/c_bikeshare_gbfs-d-bikeshare/resource/e8936a97-c341-416c-a39a-258335580de5)
- [OpenStreet（ハローサイクリング） バイクシェア関連情報(GBFS形式)](https://ckan.odpt.org/dataset/c_bikeshare_gbfs-openstreet)の[バイクシェア関連情報(GBFS形式 / station_information)](https://ckan.odpt.org/dataset/c_bikeshare_gbfs-openstreet/resource/5455c77d-154d-4b22-b0ca-9ea78209ed76)


参考: Qiita [公共交通オープンデータセンターにあるドコモ・バイクシェアのstation_information情報をCSVファイルにする](https://qiita.com/nishikyon/items/bfb328bca59c64228474)

# 2. SHAREBIKE_STATIONテーブルの作成
以下のDDLで作成します。`GEO_POINT`に経度・緯度情報を**ST_Point**データタイプで入れます。
```sql
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ファイルにする](https://qiita.com/nishikyon/items/bfb328bca59c64228474)


## 3.1 ドコモ・バイクシェア バイクシェア関連情報 station_informationの取得
[公共交通オープンデータセンター](https://www.odpt.org/)から<br>
- [ドコモ・バイクシェア バイクシェア関連情報(GBFS形式)](https://ckan.odpt.org/dataset/c_bikeshare_gbfs-d-bikeshare)の[バイクシェア関連情報 station_information](https://ckan.odpt.org/dataset/c_bikeshare_gbfs-d-bikeshare/resource/e8936a97-c341-416c-a39a-258335580de5)
をダウンロードし、pandasのDataFrameに入れます。
```python
## ドコモ・バイクシェア バイクシェア関連情報 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の取得
[公共交通オープンデータセンター](https://www.odpt.org/)から<br>
- [OpenStreet（ハローサイクリング） バイクシェア関連情報(GBFS形式)](https://ckan.odpt.org/dataset/c_bikeshare_gbfs-openstreet)の[バイクシェア関連情報(GBFS形式 / station_information)](https://ckan.odpt.org/dataset/c_bikeshare_gbfs-openstreet/resource/5455c77d-154d-4b22-b0ca-9ea78209ed76)
をダウンロードし、pandasのDataFrameに入れます。

```python
#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座標系の設定」](https://qiita.com/nishikyon/items/fc97391f02b4e9e4bb9c)で設定したJGD2011の空間参照系を使います。srs_idは2011に設定したとしています。

ドコモ・バイクシェア 情報をINSERTするSQLの一例はこんな感じになります:
```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://github.com/IBM/db2-jupyter)を使用しています。
(使い方の参考: https://speakerdeck.com/kyokonishito/db2-and-jupyter-notebooks)
あらかじめ、[こちらのnotebook](https://github.com/kyokonishito/Db2_Spatial_Analysis/blob/main/notebooks/Data_Setup.ipynb)の1.1から1.3の実行が必要です。

## 4.1 ドコモ・バイクシェア バイクシェア関連情報 station_informationのSHAREBIKE_STATIONへのインポート
最初の1つだけ、実行したSQLを出力するようにしています。
```python
# 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を出力するようにしています。
```python
# 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](https://geopandas.org/en/stable/)ライブラリ等を使用して、SHAPEファイルを作成し、[SHAPEファイルのインポート](https://qiita.com/nishikyon/item/fd6d166fdbf6523930d4)の方法でロードする方法もあります(この方がおそらくロード時間が短いです)。

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

以上です。