Go to Qiita Advent Calendar 2024 Top
LoginSignup
2
0

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?

More than 1 year has passed since last update.

@nishikyon(Kyoko Nishito)inIBM

Db2地理空間分析: 空間処理変数を使ってみる

Last updated at Posted at 2022-11-01

Db2地理空間分析シリーズ

当記事は「7. 空間処理変数を使ってみる」です。

  1. Db2の地理空間分析機能
  2. 地理空間分析機能を有効にする
  3. JGD2011座標系の設定
  4. SHAPEファイルのインポート
  5. 緯度・経度情報をインポート
  6. DBeaverでDb2の地理空間情報を表示してみる
  7. 空間処理変数を使ってみる

1. 空間処理関数

空間処理関数を使用して、形状間の距離やある形状が別の形状に含まれるかどうかの判別などが可能です。 これをSQLのWHERE句として使用することができます。

こちらページに一覧があります:
https://www.ibm.com/docs/ja/db2/11.5?topic=analytics-spatial-functions

例として2つの関数を紹介します。

ST_Distance関数　　

使い方詳細： https://www.ibm.com/docs/ja/db2/11.5?topic=sf-st-distance

ST_Distance 関数は、2 つの形状およびオプションとして単位を入力パラメーターとして取り、1番目の 形状内の任意のポイントと 2番目の形状内の任意のポイントとの間の最短距離を戻します。

つまり、ある地点からxkm以内の場所のリストを出す、というようなSQLに使用できます。

JR東京駅(緯度35.6809591, 経度139.7673068)から距離1km以内のLOCATION(ST_Point属性の列)

-- 2011は自分で設定したJGD2011のSRS ID(参考　https://qiita.com/nishikyon/items/fc97391f02b4e9e4bb9c)
WHERE ST_Distance(
ST_Point (139.7673068, 35.6809591, 2011), LOCATION, 'KILOMETER') <= 1

ST_Within関数　　

使い方詳細： https://www.ibm.com/docs/ja/db2/11.5?topic=sf-st-within

ST_Within 関数は、ある形状が完全に別の形状内にあるかどうかを判別するのに使います。

つまり、市区町村境界などの囲まれた形状の中にある、場所のリストを出す、というようなSQLに使用できます。

中央区内のLOCATION(LOCATIONはST_Point属性の列、中央区の形状はSHAPEにあるとする)

WHERE ST_Within (LOCATION, SHAPE)

↑ちょっとイメージ湧かないかもしれないので、次のセクションで今までロードしたはずのデータでのSQL実行例を紹介します。

2. SQLと視覚化のサンプル

前提: データの準備

以下でDb2のテーブルに入れたものを使用します:

2.1 JR東京駅(緯度35.6809591, 経度139.7673068)から距離1km以内ののシェアサイクルポートの情報

ST_Distance関数

-- 2011は自分で設定したJGD2011のSRS ID(参考　https://qiita.com/nishikyon/items/fc97391f02b4e9e4bb9c)
ST_Distance(ST_Point (139.7673068, 35.6809591, 2011), ST.GEO_POINT, 'KILOMETER') <=1

で1km以内の条件となる

SQL:

-- 2011は自分で設定したJGD2011のSRS ID(参考　https://qiita.com/nishikyon/items/fc97391f02b4e9e4bb9c)
SELECT ST.NAME, ST.CAPACITY, 
       ST_X(ST.GEO_POINT) LON, ST_Y(ST.GEO_POINT) LAT
FROM SHAREBIKE_STATION ST 
WHERE ST_Distance(ST_Point (139.7673068, 35.6809591,  2011), ST.GEO_POINT, 'KILOMETER') <=1

結果： 21件
image.png

ではこの結果をJupyter notebook + python + folium + Db2 Jupyter Notebook Extensionsを使って視覚化してみます。

Jupyter notebookのコード

# Db2 Jupyter Notebook Extensions のロードと実行
import os
path = "./db2.ipynb"
if not os.path.exists(path):
    !wget https://raw.githubusercontent.com/IBM/db2-jupyter/master/db2.ipynb
else:
    print("db2.ipynb is existing.")
%run db2.ipynb

# Db2接続情報のセット
# 下記にDb2接続情報をセットしてください
user = "xxxx" # db2ユーザーid
password = "xxxx" #db2パスワードを入れる
databese = "xxxx" # db2 Db名
host =  "xxxx" # db2 ホスト名
port =  9999 #Db2ポート番号

# Db2 Jupyter Notebook Extensionsを使用してDb2に接続（SSL接続）
%sql CONNECT TO {databese} USER {user} USING {password} HOST {host} port {port} SSL TRUE

# JR東京駅(緯度35.6809591, 経度139.7673068)から距離1km以内ののシェアサイクルポート
# 2011は自分で設定したJGD2011のSRS ID(参考　https://qiita.com/nishikyon/items/fc97391f02b4e9e4bb9c)
df =%sql \
SELECT ST.NAME, ST.CAPACITY, \
       ST_X(ST.GEO_POINT) LON, ST_Y(ST.GEO_POINT) LAT \
FROM SHAREBIKE_STATION ST  \
WHERE ST_Distance(ST_Point (139.7673068, 35.6809591,  2011), ST.GEO_POINT, 'KILOMETER') <=1
df 

import folium
# 地図生成
folium_map = folium.Map(location=[35.6809591, 139.7673068], zoom_start=14, height = 500)

# マーカープロット　
for i, row in df.iterrows():
    folium.CircleMarker(
        location=[row['LAT'], row['LON']],
        tooltip=row['NAME'],
        radius=5,
        color='green',
        fill_color='green'
    ).add_to(folium_map)

#東京駅から1km 円と東京駅地点プロット
folium.Circle(
        [35.6809591, 139.7673068], #東京駅
        radius=1000,
    ).add_to(folium_map)
folium.Marker(
        location=[35.6809591, 139.7673068],
        tooltip='東京駅'
    ).add_to(folium_map)

folium_map

こんな表示ができます！
image.png

DBeaverで表示

6. DBeaverでDb2の地理空間情報を表示してみるで解説したDBeaverで表示するSQLは以下になります:

-- 2011は自分で設定したJGD2011のSRS ID(参考　https://qiita.com/nishikyon/items/fc97391f02b4e9e4bb9c)
SELECT ST.NAME, ST.CAPACITY, 
        CAST( ST_AsText(ST.GEO_POINT)AS VARCHAR(50) ) AS LOC
FROM SHAREBIKE_STATION ST 
WHERE ST_Distance(ST_Point (139.7673068, 35.6809591, 2011), ST.GEO_POINT, 'KILOMETER') <=1

結果が表示されたら4.2 結果パネルに表示された空間情報列のformatを変更を実施後、 5. Spatialビューの表示を行なってください。

以下のように表示されます（1km円は表示されません、点のみ）：
image.png

2.2 千代田区と中央区と港区のシェアサイクルポートの情報

ST_Within関数

よくばり（？）にも、3つのエリア情報(Polygon)が必要なので、まずは3つのエリア千代田区と中央区と港区をSELECTするSQLは以下です：

SELECT GEO
FROM CITY_SHAPE 
WHERE N03_004 IN ('中央区', '千代田区', '港区') # N03_004には市区町村名が入っている

この結果をST_Within関数で使います。最終的なSQL全体は以下のようになります:

with AREA AS (
    SELECT GEO
    FROM CITY_SHAPE 
    WHERE N03_004 IN ('中央区', '千代田区', '港区')
)
SELECT ST.NAME, ST.CAPACITY, ST_X(ST.GEO_POINT) LON, ST_Y(ST.GEO_POINT) LAT
FROM SHAREBIKE_STATION ST, AREA
WHERE ST_Within(ST.GEO_POINT, AREA.GEO) 
--- ↑ここで'中央区', '千代田区', '港区'のPolygonに内包されたシェアサイクルポートのPoint(経度、緯度)という条件にしている

結果：　331件
image.png

ではこの結果をJupyter notebook + python + folium + Db2 Jupyter Notebook Extensionsを使って視覚化してみます。

Jupyter notebookのコード

# Db2 Jupyter Notebook Extensions のロードと実行
import os
path = "./db2.ipynb"
if not os.path.exists(path):
    !wget https://raw.githubusercontent.com/IBM/db2-jupyter/master/db2.ipynb
else:
    print("db2.ipynb is existing.")
%run db2.ipynb

# Db2接続情報のセット
# 下記にDb2接続情報をセットしてください
user = "xxxx" # db2ユーザーid
password = "xxxx" #db2パスワードを入れる
databese = "xxxx" # db2 Db名
host =  "xxxx" # db2 ホスト名
port =  9999 #Db2ポート番号

# Db2 Jupyter Notebook Extensionsを使用してDb2に接続（SSL接続）
%sql CONNECT TO {databese} USER {user} USING {password} HOST {host} port {port} SSL TRUE

# 千代田区と中央区と港区のシェアサイクルポートの情報
df = %sql \
    with AREA AS ( \
    SELECT GEO \
    FROM CITY_SHAPE  \
    WHERE N03_004 IN ('中央区', '千代田区', '港区')\
    )\
    SELECT ST.NAME, ST.CAPACITY, ST_X(ST.GEO_POINT) LON, ST_Y(ST.GEO_POINT) LAT \
    FROM SHAREBIKE_STATION ST, AREA \
    WHERE ST_Within(ST.GEO_POINT, AREA.GEO)
display(df)

# '中央区', '千代田区', '港区'の境界線情報(Polygon)取得
df_area =%sql \
    SELECT OBJECTID ,N03_001 ,N03_002 ,N03_003, N03_004, N03_007,\
    CAST(ST_AsText(GEO) AS VARCHAR(30000)) LOC  \
    FROM CITY_SHAPE WHERE N03_004 IN ('中央区', '千代田区', '港区')

#取得情報の表示
display(df_area)

import folium
import geopandas as gpd
import 
# 地図生成
folium_map = folium.Map(location=[35.6636391,139.7606268], zoom_start=12, height = 800)

#境界線の描画 geopandas　GeoDataFrameを使用
gdf = gpd.GeoDataFrame(df_area, geometry = gpd.GeoSeries.from_wkt(df_area.LOC.astype('str')))　
geo_j = gdf['geometry'].to_json()
folium.GeoJson(geo_j, style_function=lambda x: {'fillColor': 'orange'}).add_to(folium_map)

#シェアサイクルポートマーカープロット＋地図表示

for i, row in df.iterrows():
    folium.CircleMarker(
        location=[row['LAT'], row['LON']],
        tooltip=row['NAME'],
        radius=5,
        color='green',
        fill_color='green'
    ).add_to(folium_map)

folium_map

こんな表示ができます！
image.png

DBeaverで表示

6. DBeaverでDb2の地理空間情報を表示してみるで解説したDBeaverで表示するSQLは以下になります:

with AREA AS (
    SELECT N03_004, GEO
    FROM  CITY_SHAPE 
    WHERE N03_004 IN ('中央区', '千代田区', '港区')
)
SELECT ST.NAME, ST.CAPACITY, CAST( ST_AsText(ST.GEO_POINT)AS VARCHAR(50) ) AS LOC, 
AREA.N03_004, CAST( ST_AsText(AREA.GEO)AS VARCHAR(30000) )AS AREA
FROM  SHAREBIKE_STATION ST, AREA
WHERE ST_Within(ST.GEO_POINT, AREA.GEO) ;

結果が表示されたら4.2 結果パネルに表示された空間情報列のformatを変更を実施後、 5. Spatialビューの表示を行なってください。

以下のように表示されます（AREAは'中央区', '千代田区', '港区'3レコード分のみ選択）：
image.png

3. まとめ

いかがでしたか？　シリーズ7本目にしてやっとDb2地理空間分析としてSQLの結果を地図にマッピングして眺められた感があると思います(!)

Db2での地理空間分析の最大の利点は、Db2にデータを持っている場合、いつも使っているテーブルのデータと位置情報を組み合わせた分析がDb2にデータを置いたままできることです。

ぜひみなさんのDb2データに空間情報を付加して分析してみてください。

なお、Db2地理空間分析シリーズ

の内容はIBM Database Dojoとして
Db2でシェアサイクルポートの地理情報分析をやってみよう!
というハンズオンを実施しています。
ハンズオンで使用したJupyter Notebookを公開していますので、ぜひ参考にしてみてください。

以上です。

2
0
0

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
Sign upLogin
2
0

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?