国土数値情報の緯度・経度情報をQuickSightで取り扱えるフォーマットに変換してみた

はじめに

データアナリティクス事業本部の藤川です。
QuickSightで地理空間情報を簡単に取り扱えますが、地理空間情報には様々なフォーマットがあり、実はとても厄介です。
地理空間情報をQuickSightで取り扱えるフォーマットに変換する方法をご紹介したいと思います。

QuickSightが扱える緯度と経度の座標の有効範囲

QuickSightは特定の範囲内の緯度と経度をサポートしています。単位は「度」です。
※参考：緯度と経度に関する問題

平面角を表すものですが、緯度、経度は「秒」で表現される場合もあります。 こちらの記事でご紹介した国土数値情報は、緯度、経度を「秒」で表しています。 国土数値情報の緯度、経度は「秒」、QuickSightの緯度、経度は「度」ですので、「秒」から「度」に変換が必要です。

1度 = 60分 = 3600秒ですので、次式のような変換が必要です。

1秒 = 1/3600度

やってみた

国土数値情報のデータをQuickSightで使用できるようにしてみたいと思います。 ここでは、計算フィールドを使って、QuickSight内で変換してみます。

サンプルデータを用意

1. サンプルデータを用意します。こちらの記事でご紹介したデータを流用します。
2. QuickSightを開きます。
3. データセットを開きます。
4. 「地価公示データ」というデータセットをクリックします。
5. データセットの編集ボタンをクリックします。
6. 「地価公示データ」(カスタムSQL)の右側にある矢印をクリックし、SQLクエリを編集をクリックします。
7. カスタムSQLを次のように変更します。緯度、経度の2行を追加しただけです。

   select
       "都道府県名",
       "駅距離",
       "緯度",
       "経度",
       "ｈ２７価格" as "2015年",
       "ｈ２８価格" as "2016年",
       "ｈ２９価格" as "2017年",
       "ｈ３０価格" as "2018年",
       "ｈ３１価格" as "2019年",
       "ｒ２価格" as "2020年"
   from
       "hello_athena".landprice a left join "hello_athena".prefcode b
       on substr(a."所在地コード", 1, 2) = b."都道府県コード"
   where
       "用途" = '000'
   

8. 適用ボタンをクリックします。

9. 閉じるボタンをクリックします。

計算フィールドを追加

この時点では、緯度、経度は「10進数」というデータ型です。まだ、ロケーション(地理空間コード)として認識されていません。
latitude、longitudeという名称の計算フィールドを追加して、変換できるようにします。

1. 計算フィールドを追加ボタンをクリックします。
2. 名前を追加にlatitudeと入力し、その下のフィールドに次の通り入力します。

   {緯度}/3600
   

3. 保存ボタンをクリックします。

4. 再度、計算フィールドを追加ボタンをクリックします。

5. 名前を追加にlongitudeと入力し、その下のフィールドに次の通り入力します。

   {経度}/3600
   

6. 保存ボタンをクリックします。
7. 画面左側のフィールド一覧で、latitudeの3点リーダーをクリックし、座標に追加をクリックします。
8. 新しい地理空間の座標を作成を選択し、追加ボタンをクリックします。
9. 座標に名前を付けるにGeocode(任意の名前で構いません)と入力し、緯度で使用するフィールドにはlatitudeを、経度で使用するフィールドにはlongitudeを選択します。
10. 座標の作成ボタンをクリックします。
11. 画面右側の保存ボタンをクリックします。
    ※筆者の環境では、SPICEを選択するとエラーが出てしまいましたので、直接クエリに変更しました。

分析に地理空間情報を表示

1. QuickSightを開きます。
2. 「分析」を開きます。
3. こちらの記事でご紹介した「地価公示データ analysis」を流用します。
4. ビジュアルタイプで地図上のポイント(Geospatial Charts (Maps))をクリックします。
5. フィールドウェルに次のようにセットします。
6. データはGeospatialが5,000件、Colorが31件までに制限されるため、都道府県でフィルタを掛ける等絞り込むようにしてください。

他の地理空間情報フォーマットに対応

公式サイトには、緯度、経度をDMS(時分秒)形式からQuickSightが取り扱える「度」に変換する方法が掲載されています。
※参考：緯度と経度に関する問題

度、分、秒 (DMS) 形式の座標を使用する(度分秒→度)

「文字列」型の緯度、経度というカラムにDMS形式の緯度、経度が入力されている場合、そのままお使いいただけます。

1. 計算フィールドlatitudeを追加します。

   ifelse(
       split({緯度}, "''", 2) = "N",
       parseInt(split(split({緯度}, "°", 1), "'", 1) ) +
           (parseDecimal(split(split({緯度}, "°", 2), "'", 1) ) /60) +
           (parseDecimal(split(split({緯度}, "°", 2), "'", 2) ) /3600),
       (parseInt(split(split({緯度}, "°", 1), "'", 1) ) +
           (parseDecimal(split(split({緯度}, "°", 2), "'", 1) ) /60) +
           (parseDecimal(split(split({緯度}, "°", 2), "'", 2) ) /3600)) * -1
   )
   

2. 計算フィールドlongitudeを追加します。

   ifelse(
       split({経度}, "''", 2) = "E",
       parseInt(split(split({経度}, "°", 1), "'", 1) ) +
           (parseDecimal(split(split({経度}, "°", 2), "'", 1) ) /60) +
           (parseDecimal(split(split({経度}, "°", 2), "'", 2) ) /3600),
       (parseInt(split(split({経度}, "°", 1), "'", 1) ) +
           (parseDecimal(split(split({経度}, "°", 2), "'", 1) ) /60) +
           (parseDecimal(split(split({経度}, "°", 2), "'", 2) ) /3600)) * -1
   )
   

3. 次のようなサンプルデータがあるとします。

   緯度	経度	場所
   51°30'26.4636''N	0°7'39.9288''W	サンプル
   34°38'35.3''N	134°59'50''E	明石市

4. この場合、次のように変換されます。

   緯度	経度	場所	latitude	longitude
   51°30'26.4636''N	0°7'39.9288''W	サンプル	51.5074	-0.1278
   34°38'35.3''N	134°59'50''E	明石市	34.6431	134.9972

さいごに

QuickSight以外でも緯度、経度情報を使用する場合は、前処理する段階でフォーマットを変換しておくのが便利でしょう。 今回は、QuickSightの計算フィールドを使用し、QuickSight側でデータ加工してみました。アドホックな分析で、国土数値情報を使用したい場合には重宝すると思います。 ご利用シーンに合わせて是非使い分けてみてください。