ドラクエウォークのおみやげを集めるのに最寄り駅を調べたい

■■■Oracle Cloudのウェビナーシリーズは→こちら■■■

突然ですが、スマホで遊べるドラクエウォークには、「おみやげ」を集めるミッションがありますよね。

• おみやげ は各都道府県に4つずつある
• あるおみやげをゲットするには、各おみやげに紐付いた ランドマーク へ行く必要がある
  • ランドマークに到着すると、おみやげをゲットするための特別なクエストが解放され、そのクエストを攻略するとおみやげがもらえる

ランドマークの緯度経度データから、ランドマークへの最寄り駅を調べるのに、Oracle Autonomous Databaseを使ってやってみました。なお、最寄り駅は直線距離で算出することにします。ついでに、ランドマークの緯度経度から、市区町村の名前を求めたいと思います。

地理空間情報の扱いについては、よろしければこちらもご覧いただけると嬉しいです。

準備

やろうとしていることは、緯度経度のデータと、国土地理院が公開する Shapefile（駅の情報や、行政区域の情報）を使って、Oracle Autonomous Database上で簡単な地理空間情報検索を行うことです。

準備が長くなってしまったので、とりあえずSKIPして「実行」に行くことをおすすめします。

データを準備（通常表）

おみやげ表（dqw_omiyage表、188行）の作成

https://game8.jp/dqwalk/291056
の情報を参考にさせていただきました。

DQW_OMIYAGE表は、

• tname : 都道府県名
• pname : ランドマーク名
• omiyage : おみやげ
• latitude : 緯度
• longitude : 経度

※47都道府県x4つ=188行のテーブル。ID列を振ったが特に使用しない。

沖縄県のランドマーク「伊良部大橋」は、海の上ではなく、伊良部島側に少しずらした場所の緯度経度にしました。

CSVはここに。(公開に問題がある際は削除します)

データを準備（シェープファイル）

※今回利用したシェープファイルは、出典：「国土地理院「数値地図（国土基本情報）」」の「国土数値情報　行政区域データ」「国土数値情報　鉄道データ」です。

Shapefile（シェープファイル）とは、Esri社が提唱する、GISデータのフォーマットの1つです。
https://www.esrij.com/gis-guide/esri-dataformat/shapefile/

図形情報と属性情報を持った地図データファイルが集まったファイルで、例えばこの後使う「市区町村の形状データ」では、市町村の形を示す情報や、その市町村の名前などが格納されています。*.shp, *.dbf など、決まった拡張子を持つ複数のファイルからなります。

全国の駅データを準備

http://nlftp.mlit.go.jp/ksj/gml/datalist/KsjTmplt-N02-v2_2.html
から、利用規約を読んでShapefileをダウンロードし、zip展開。

• N02-18_GML.zip, 8.06MB (平成30年)

市区町村の形状データを準備

http://nlftp.mlit.go.jp/ksj/gml/datalist/KsjTmplt-N03.html
から、利用規約を読んでShapefileをダウンロードし、zip展開。

• N03-190101_GML.zip, 396.68MB (平成31年)

環境を準備

Oracle Autonomous Database

Oracle Autonomous Database は、Oracle Cloud のフルマネージドなDatabaseです。

Oracle Cloudにログインし、Autonomous Databaseを作成します。ATPでもADWでもどちらでもOKです。作成が完了したら、以下実施します。

• ユーザを作成する
  • 参考：Oracle Cloud Infrastructure の Autonomous Databaseで使える SQL Developer Web (ブラウザベース) の最後の方「追記」のところに書いています
• Autonomous Databaseへの接続に必要なウォレットファイルをダウンロードする
  • 参考：クライアント資格証明(ウォレット)のダウンロード

Oracle Spatial Studio

今回は、ShapefileをAutonomous Databaseにロードするために利用します。

から、ダウンロード、インストール。

Spatial Studio is available as a Java application for trial, development, and testing purposes at no cost under an Oracle Technology Network License Agreement.

→ OTNライセンスであり、トライアル、開発、テスト目的で無料で利用できます。（本記事投稿時点の情報）

Spatial Studioは、JavaのWebアプリで、EARファイルを展開する方法と、Webアプリとしてlocalhostで起動して実行する方法の2種類がありますが、今回は後者を利用しました。手元のWindows PC上で実行しています。

※以降の画面や手順は、version 19.1.0 のものです。最新版では変更されている可能性があります。

初回起動時は、リポジトリの作成を求められる

Autonomous Databaseを指定してウォレットファイルを指定し、先に作成したDatabaseのスキーマ名（ユーザ名）とパスワード、サービス名、必要に応じてPROXYを指定

データセットを作成していく

Shapefileのロードを求められる

市区町村のShapefileをロードします。N03-19_190101 で始まるファイル群４つ(*.dbf, *.prj, *.shp, *.shx)を、Ctrlキーで複数指定すると下の画面に。

• Spatial SRIDに表示された番号を確認し、必要に応じて修正する¹ ：SRIDとは空間参照系の識別コード（測地系と座標系の組合せ）、ここではSRID=4326で扱っている（SRID=4326は、測地系:WGS84, 座標系:地理座標系を示すらしい)。
• 「Create Spatial Index」がON ：空間索引を作成してくれる

「Submit」を押下し、Shapefileをロードします。

同様の手順で、駅情報のshapefile２つもロードします。

• N02-18_RailroadSectionで始まるファイル群
• N02-18_Station で始まるファイル群

※全部で3回、Shapefileをロードします。

Dataset files size(s) are too big エラーが出たとき

ロードしたいShapefileに対し、デフォルトの設定が小さいためこのエラーが起きている場合は、Spatial Studioを停止し、設定ファイルを編集してから、起動します。設定ファイルsgtech_config.json といくつかのパラメータについてドキュメントに記載があるものの dataset_max_size は特に言及無いですが、とりあえずやってみます。

設定ファイル：~.sgtech/sgtech_config.json ( C:\ユーザー\username\.sgtech\sgtech_config.json )

sgtech_config.json

  "upload" : {
    "_comments" : [
      "Maximum combined size of dataset files allowed in an upload request.",
      "Default is 50 MB."
    ],
    "dataset_max_size" : 50
  },

「50」を適切なサイズに変更し、再起動。

実行

①SHIBUYA109から近い駅TOP5は？

おみやげ「忠犬の像」をゲットするには、ランドマーク「SHIBUYA109」に行く必要があります。
SHIBUYA109の (経度, 緯度) = (139.6987507, 35.6595709) をSQL内に直接指定して実行してみます。
駅情報はShapefileをロードしたので n02_18_station 表としてAutonomous Database上にあります。

※SDO_POINT_TYPE()内で、経度、緯度の順で指定（経度が先、順番注意！）
※「35度41分22秒」のような60進数ではなく、10進数で指定

SELECT s.n02_003, s.n02_004, s.n02_005, sdo_nn_distance(1) dist 
FROM n02_18_station s
WHERE SDO_NN(s.geom, SDO_GEOMETRY(2001,4326,SDO_POINT_TYPE(139.6987507, 35.6595709,null) ,null,null) ,'sdo_num_res=5',1)='TRUE';

SHIBUYA109に近い駅が、5つ表示されました。路線が違うと別の駅としてそれぞれデータが登録されているようです。(半蔵門線の渋谷駅と、田園都市線の渋谷駅は同じ駅なので、両方のエントリがありますがdist列の値は同じです）

WHERE句で指定している SDO_NN() は、ジオメトリ1に作成済みの空間索引を使って、ジオメトリ2 から最も近い ジオメトリ1を求める空間演算子です。

• SDO_NN(ジオメトリ1, ジオメトリ2, パラメータ [,NUMBER])
  • ジオメトリ1 : n02_18_station表のGEOM列 (駅の場所を示すジオメトリ)
  • ジオメトリ2 : 経度緯度(139.6987507, 35.6595709) を、2次元の点(2001), SRID=4326 として、ジオメトリオブジェクトに変換
  • パラメータ：'sdo_num_res=5' は件数指定で、最も近い5件を返す
  • NUMBER ：SDO_NN_DISTANCE 補助演算子(ジオメトリ間の距離)を使うときは同じ数字を指定する

SRID=4326は、Spatial StudioからShapefileロード時に確認できました¹。空間索引も「Create Spatial Index」がONでしたので作成済みです。

• マニュアル
  • Oracle Spatial and Graph 開発者ガイド - 20.9 SDO_NN
  • Oracle Spatial and Graph 開発者ガイド - 2.2 SDO_GEOMETRYオブジェクト型
  • Oracle Spatial and Graph 開発者ガイド - 2.7.5 点

②すべてのランドマークの最寄駅は？

緯度経度情報は、dqw_omiyage表に格納しているので、その情報を使って、全ての最寄り駅を調べるSQLです。
sdo_num_res=1 で、最も近い1つ、つまり最寄り駅を検索（1行返す）。
order by で、駅からの距離が小さい順にソート。

SELECT d.tname, d.pname, d.omiyage, s.n02_003, s.n02_004, s.n02_005, sdo_nn_distance(1) dist 
FROM n02_18_station s, dqw_omiyage d
WHERE SDO_NN(s.geom, SDO_GEOMETRY(2001,4326,SDO_POINT_TYPE(d.longitude, d.latitude ,null),null,null),'sdo_num_res=1',1)='TRUE' 
ORDER BY dist;

いちばんゲットしやすいおみやげは石川県の「名園の灯篭」で、北陸新幹線の金沢駅から2.1mの場所でクエスト解放できることがわかりました。ランドマーク自体が駅のものが上位にきているので一瞬分かりづらいですが、pname列=ランドマーク名、n002_005列=検索された駅名 です。

※ゲットしやすいの定義は色々あるかと思いますが、、ここでは、「駅から近い＝ゲットしやすい」と考えることにします。

③最寄駅からの距離が遠いランドマークTOP10は？

上のSQLと同じ要領で書いています。

SELECT * FROM (
SELECT d.oid, d.tname, d.pname, d.omiyage, s.n02_003, s.n02_004, s.n02_005, sdo_nn_distance(1) dist 
FROM n02_18_station s, dqw_omiyage d
WHERE SDO_NN(s.geom, SDO_GEOMETRY(2001,4326,SDO_POINT_TYPE(d.longitude, d.latitude ,null) ,null,null) ,
'sdo_num_res=1',1)='TRUE' order by dist desc
)
WHERE rownum <= 10;

国内最南端の駅は沖縄県ゆいれーるの赤嶺駅ですが、「シーサーの置物」をゲットするためにはそこからさらに289km先に行く必要がありそうです。

④愛媛県にあるランドマークの最寄駅は？

上の結果でひとつ気になる箇所が。9番目、愛媛県の「じゃこてん」をゲットするのに、「佐田岬」の最寄駅が、日豊線？九州旅客鉄道（＝JR九州）？？

佐田岬 is どこ？

愛媛県の最西端にある岬の先端、たしかに九州側に大きくせり出している場所にありました。

そんなわけで、愛媛のランドマークに絞って最寄駅を検索してみます。
②のSQLに愛媛縛りを追加 ( and tname ='愛媛' ) 。

SELECT d.tname, d.pname, d.omiyage, s.n02_003, s.n02_004, s.n02_005, sdo_nn_distance(1) dist 
FROM n02_18_station s, dqw_omiyage d
WHERE SDO_NN(s.GEOM, SDO_GEOMETRY(2001,4326,SDO_POINT_TYPE(d.longitude, d.latitude ,null),null,null),'sdo_num_res=1',1)='TRUE' 
and tname ='愛媛' 
ORDER BY dist;

愛媛県のおみやげは、なかなか難儀なようです。「タオル」をゲットするための「大山祇神社」の最寄り駅は、呉線の忠海駅＠広島県。

最も近くにある駅（直線距離）、という観点で検索しているので、このように海をまたいだ計算結果になりました。

⑤青春18きっぷでゲットしやすいおみやげは？（東北編）：その前に

例えば青春18きっぷで青森に行く用事があるので、途中ゲットしやすいおみやげがあるかどうか調べたくなったとします。

④の要領で、これまでと同じように書いてみたのですが、（ and s.n02_004='東日本旅客鉄道' で、鉄道会社をJR東日本に絞っている）

SELECT d.tname, d.pname, d.omiyage, s.n02_003, s.n02_004, s.n02_005, sdo_nn_distance(1) dist 
FROM n02_18_station s, dqw_omiyage d
WHERE SDO_NN(s.geom, SDO_GEOMETRY(2001,4326,SDO_POINT_TYPE(d.longitude, d.latitude ,null) ,null,null) ,'sdo_num_res=1',1)='TRUE'
and s.n02_004='東日本旅客鉄道' 
and d.tname in ('青森','秋田','岩手','山形','宮城','福島') 
ORDER BY d.tname;

県名でORDER BYしていますが、思ってたのと違う...。結果を見ると、先に最も近い駅で絞り込み、その駅が東日本旅客鉄道のときだけ、結果表示されているように見えます。各都道府県で4つずつおみやげがあるはずなのに、宮城や岩手など、3つしか表示されていない県が。

ここでマニュアルをちゃんと読んでみると、こういうとき(=使いたい空間索引がある表の、別の列もWHERE句で条件として指定したいとき)は sdo_num_res=XX ではなく sdo_batch_size=XX を指定するべきのようです。

-- マニュアル掲載のサンプルSQL
-- RESTAURANTS表に様々なタイプのレストランが含まれ、ホテルに最も近い2軒のイタリアン・レストラン(ただし、2マイル以内)を検索

SELECT r.name FROM restaurants r WHERE 
   SDO_NN(r.geometry, :my_hotel,
      'sdo_batch_size=10 distance=2 unit=mile') = 'TRUE' 
   AND r.cuisine = 'Italian' AND ROWNUM <=2; 

• sdo_batch_size= で、空間索引から一度に取ってくる（フェッチ）件数を指定
  • 先のsdo_num_res=XX は、最も近いXX件、の意だったが、sdo_batch_size= はあくまでも一度にフェッチする件数を指定する
  • なのでマニュアルの例のように、一緒に他の条件(distance=2 unit=mile' 2マイル以内)も指定しないと延々にフェッチしそう
• sdo_batch_size= にいくつを指定していいかわからないときは =0 を指定可能（よきに計らうよ）。ただしパフォーマンスは直接数字を指定したほうが良いかも。

ということで、まずはシンプルにDQW_OMIYAGE表から1行固定（ランドマークを1つ指定）してやってみます。

ランドマークには、上のSQLでHitしていない、d.pname = '伊達政宗騎馬像' を指定し、distance=1000, 2000, 3000 と変えて実行してみました。

SELECT d.tname, d.pname, d.omiyage, s.n02_003, s.n02_004, s.n02_005, sdo_nn_distance(1) dist 
FROM n02_18_station s, dqw_omiyage d
WHERE SDO_NN(s.geom, SDO_GEOMETRY(2001,4326,SDO_POINT_TYPE(d.longitude, d.latitude ,null),null,null),'sdo_batch_size=0 distance=3000',1)='TRUE'
and d.pname = '伊達政宗騎馬像' 
ORDER BY dist;

distance=1000で2件、2000で8件、3000で19件Hitしました。（結果は纏めて貼ってます↓）

distance=3000での検索結果より、「伊達政宗騎馬像」から最も近い東日本旅客鉄道の駅は、仙石線 あおば通駅で、2028mの距離があります。

上のSQLに、and s.n02_004='東日本旅客鉄道' を追加して、欲しい結果が得られます。

-- 「伊達政宗騎馬像」から3km以内にある、東日本旅客鉄道の駅
SELECT d.tname, d.pname, d.omiyage, s.n02_003, s.n02_004, s.n02_005, sdo_nn_distance(1) dist 
FROM n02_18_station s, dqw_omiyage d
WHERE SDO_NN(s.geom, SDO_GEOMETRY(2001,4326,SDO_POINT_TYPE(d.longitude, d.latitude,null),null,null),'sdo_batch_size=0 distance=3000',1)='TRUE'
and d.pname = '伊達政宗騎馬像' 
and s.n02_004='東日本旅客鉄道' 
ORDER BY dist;

⑤青春18きっぷでゲットしやすいおみやげは？（東北編）：実行

問いを、「東北にあるランドマークで、JR東日本の駅から程よい距離（ここでは2km以内とする）にあるものを検索する」と解釈します。

• 先のSQLを、東北にある全てのランドマークで実行
• distance=2000 を指定
• 都道府県名でORDER BY

SELECT d.tname, d.pname, d.omiyage, s.n02_003, s.n02_004, s.n02_005, sdo_nn_distance(1) dist 
FROM n02_18_station s, dqw_omiyage d
WHERE SDO_NN(s.geom, SDO_GEOMETRY(2001,4326,SDO_POINT_TYPE(d.longitude, d.latitude,null),null,null),'sdo_batch_size=0 distance=2000',1)='TRUE'
and d.tname in ('青森','秋田','岩手','山形','宮城','福島') 
and s.n02_004='東日本旅客鉄道' 
ORDER BY d.tname;

ほぼ完成ですが、各ランドマークにつき2km以内なら複数の駅が表示されているので、最も近い駅1件に絞って表示します。

SELECT * FROM (
	SELECT tname, pname, omiyage, n02_003, n02_004, n02_005, dist, rank() over ( partition by omiyage order by dist) as rank
	FROM (
		SELECT d.tname, d.pname, d.omiyage, s.n02_003, s.n02_004, s.n02_005, sdo_nn_distance(1) dist 
      	FROM n02_18_station s, dqw_omiyage d
		WHERE SDO_NN(s.geom, SDO_GEOMETRY(2001,4326,SDO_POINT_TYPE(d.longitude, d.latitude ,null) ,null,null) ,'sdo_batch_size=0 distance=2000',1)='TRUE'
      	and d.tname in ('青森','秋田','岩手','山形','宮城','福島') 
		and s.n02_004='東日本旅客鉄道' 
	) a
) b
WHERE rank=1
ORDER BY tname;

マニュアルには、2表JOIN時、Nested Loop で、かつ、Nested Loopの内部表が空間索引を持つ表になるようにするべきでそのようにヒントを書くべし、ということも書かれていました。ので、うまく動かないときは詳細を確認しようと思います。

⑥ランドマークのある市町村名を検索する(逆geocoding)

dqw_omiyage表 には緯度経度と、都道府県名はありますが、市町村名は分かりません。
緯度経度から市町村名を求めることにします。(逆geocoding)
Oracle Databaseにもgeocoding系の機能はありますが、残念ながら日本の住所表記には対応していないので、市町村のShapefileをロードしたN03_19_190101表 を使って検索します。

-- ランドマークの緯度経度から市区町村を検索
SELECT d.pname, d.latitude, d.longitude, n.n03_001, n.n03_003, n.n03_004
FROM dqw_omiyage d,  N03_19_190101 n
WHERE SDO_ANYINTERACT(n.geom, SDO_GEOMETRY(2001, 4326, SDO_POINT_TYPE(d.longitude, d.latitude, NULL), NULL, NULL)) = 'TRUE';

SDO_ANYINTERACT() は、ジオメトリ同士がなんらかの形で接合するかを確認する空間演算子です。

• Oracle Spatial and Graph Spatial Features 空間データで遊ぶ (p.15)

• Oracle Spatial and Graph 開発者ガイド - 20.1 SDO_ANYINTERACT

まとめ

緯度経度のデータと、国土地理院が公開する Shapefileを使って、Oracle Autonomous Database上で簡単な地理空間情報検索を行いました。

使用した空間演算子は、

• SDO_NN()：最も近くにあるジオメトリを検索する
  • sdo_num_res= で最も近い いくつを検索するかを指定(=1で最も近い1つが求まる)
  • WHERE句で空間索引を持つ表の他の列の条件を指定したい(例：JR東日本に絞りたい)ときは、sdo_batch_size= でフェッチサイズと 何らか条件（例えば distance= で距離を指定）を指定する
• SDO_ANYINTERACT()：ジオメトリがなんらかの形で接合するかを確認する

参考

• Oracle Spatial and Graph 開発者ガイド
• Oracle Spatial and Graph Spatial Features 空間データで遊ぶ

1. SRIDの自動判定がうまくいかない場合もあるのでその際はShapefile内の *.prjを見て手動で上書きする必要があります For some uploaded shapefiles, Spatial Studio may fail to automatically match the best or even correct SRID. In such cases you may need to examine the .prj file that came with your shapefile and consult Oracle Spatial's Spatial Reference systems to figure out the best SRID to use for your shapefile geometries, then enter it manually in the upload screen. https://www.oracle.com/database/technologies/spatial-studio/oracle-spatial-studio-downloads.html ↩ ↩²