Oracle Spatial Studio で駅ごとの年齢層の人口を集計してみた

# はじめに
本記事では<a href="https://qiita.com/wahagon/items/d8007bef2934536cf0b3
" target="_blank" rel="noopener noreferrer">こちらの記事</a>の続きとして、東京都の境界データと国勢調査の年齢階層データ、先ほどロードした全国の駅データを使って、Spatial Studioで分析をしてみます。

# 1. 境界データ/小地域（東京都）のダウンロードとロード
<a href="https://www.e-stat.go.jp/gis/statmap-search?page=1&type=2&aggregateUnitForBoundary=A&toukeiCode=00200521&toukeiYear=2015&serveyId=A002005212015&prefCode=13&coordsys=1&format=shape&datum=2000" target="_blank" rel="noopener noreferrer">こちら</a>から東京都の境界データのShapefileをダウンロードします。 
<a href="https://www.e-stat.go.jp/terms-of-use" target="_blank" rel="noopener noreferrer">利用規約</a>を読んでzipファイルを展開します。

  > (補足)
  > Shapefile（シェープファイル）とは、Esri社が提唱する、GISデータのフォーマットの1つです。
  > https://www.esrij.com/gis-guide/esri-dataformat/shapefile/ 
  > 図形情報と属性情報を持った地図データファイルが集まったファイルで、例えばこの後使う「市区町村の形状データ」では、市町村の形を示す情報や、その市町村の名前などが格納されています。*.shp, *.dbf など、決まった拡張子を持つ複数のファイルからなります。

![tokyo_shp.jpg](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1930162/704728b3-95f3-db51-b41f-de723ef49103.jpeg)
出典：<a href="https://www.e-stat.go.jp/" target="_blank" rel="noopener noreferrer">政府統計の総合窓口(e-Stat)</a>

以下の4つのファイルが展開されます。
+ h27ka13.dbf
+ h27ka13.prj
+ h27ka13.shp
+ h27ka13.shx

# 2. 国勢調査の年齢階層データのダウンロード
<a href="https://github.com/r-deguchi/age_group_tokyo" target="_blank" rel="noopener noreferrer">こちら</a>から東京都の年齢階層データ（age_group_tokyo.csv）をダウンロードします。合わせてテーブル定義書も確認します。  
※ 政府統計の総合窓口(e-Stat)『平成２７年国勢調査 人口等基本集計 年齢（５歳階級）、男女別人口、総年齢及び平均年齢（外国人－特掲）－町丁・字等』のデータを一部加工して作成しています。

# 3. 各データのSpatial Studioへのロード
先ほどダウンロードしたシェープファイルと年齢階層データをSpatial Studioへロードし、データセットを作成します。

1. Spatial Studioへログインし、[Create Dataset]→[From file upload]を選択し、4つのファイルを全てインポートします。
![import_shp.jpg](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1930162/897aa96b-ce4f-502f-cd87-3a7eb977661b.jpeg)

1. 以下の画像のように設定し、**Submit**します。
![tokyo_boundary.jpg](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1930162/cd192081-0af9-6922-7186-96fd0be3c023.jpeg)
    + **Upload to connection**：SPATIAL_STUDIOを選択
    + **Table name**：TOKYO_BOUNDARYに変更
    + **Dataset name**：TOKYO_BOUNDARYに変更
    + **Create Spatial Index**：ON（デフォルト）に設定、空間索引が作成されます。

1. AREA列を主キー列に設定します。
![tokyo_boundary_pk.jpg](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1930162/bf01ed8a-b9ad-f47e-ebd3-db18b634f758.jpeg)

1. age_group_tokyo.csvをインポートします。Issuesが2つありますが、ここはこのままで先に進みます。
![age_group_tokyo_issues.jpg](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1930162/309c7913-bd15-7885-ed8b-9d838dc4f8bb.jpeg)

# 4. Database Actionsで2つの表をjoinする
Spatial StudioでロードしたTOKYO_BOUNDARY表とAGE_GROUP_TOKYO表を、共通のKEY_CODE列でjoinします。
現在の設定では、studio_repoユーザーでDatabase Actionsにサインインできないため、ADMINユーザーでRESTの有効化をする必要があります。

1. ADMINユーザーでDatabase Actionsにサインインします。

1. [データベース・ユーザー]をクリックします。
![database_actions_user.jpg](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1930162/7467f9ed-ef9d-6393-a12d-97dd21372756.jpeg)

1. studio_repoユーザーにRESTの有効化をします。
![database_actions_rest.jpg](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1930162/07b6bcd1-2a79-a052-f7ff-c112eed20265.jpeg)

1. 一度サインアウトし、studio_repoユーザーでサインインし直します。

1. 『開発』の[SQL]をクリックします。

1. Spatial Studioでロードした3つの表AGE_GROUP_TOKYO、STATION、TOKYO_BOUNDARYがあることを確認します。
![studio_repo_tables.jpg](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1930162/34722417-aa76-5798-9473-545bf0e77dd3.jpeg)

1. 以下のコマンドでAGE_GROUP_TOKYO表とTOKYO_BOUNDARY表をjoinし、geo_age_tokyo表を作成します。
    ```sql
    create table geo_age_tokyo as select a.PREF, a.CITY, a.S_AREA, a.PREF_NAME, a.CITY_NAME, a.S_NAME, a.KIGO_E, a.HCODE, a.AREA, a.PERIMETER, a.H27KAXX_, a.H27KAXX_ID, a.KEN, a.KEN_NAME, a.SITYO_NAME, a.GST_NAME, a.CSS_NAME, a.KIHON1, a.DUMMY1, a.KIHON2, a.KEYCODE1, a.KEYCODE2, a.AREA_MAX_F, a.KIGO_D, a.N_KEN, a.N_CITY, a.KIGO_I, a.MOJI, a.KBSUM, a.JINKO, a.SETAI, a.X_CODE, a.Y_CODE, a.KCODE1, a.GEOM, b.*
    from TOKYO_BOUNDARY a, AGE_GROUP_TOKYO b 
    where a.key_code = b.key_code;
    ```

1. 再度Spatial Studioに戻り、作成したgeo_age_tokyo表でデータセットを作成します。[Create Dataset]→[Table/view]からGEO_AGE_TOKYOを選択します。また、[Validate geometries]のトグルスイッチをONにします。
![geo_age_tokyo_import.jpg](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1930162/215efc8c-5f30-8015-f535-3591ba6c7a7e.jpeg)

1. 主キー列をAREA列に設定し、[Create Spatial Metadata and Index]をクリックします。
![create_lat_lon_index.jpg](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1930162/1346b888-def8-7575-bb64-04e6a91fbc73.jpeg)

設定が成功するとGEO_AGE_TOKYOのアイコンがピンマークに変わります。

# 5. 地理情報を用いた分析
作成したgeo_age_tokyo表と駅データを用いて、駅周辺の特定の年齢層の人口を集計してみます。
以下は、東京都を駅周辺の650個の小地域に分け、駅ごとに0~14歳、15~64歳、65歳以上の人口を表示するSQL文です。
このSQL文をDatabase Actionsで実行します。
```sql
select sum(a.T000849017), sum(a.T000849018), sum(a.T000849019), b.station_name
from geo_age_tokyo a, STATION b
where sdo_anyinteract (a.geom, sdo_geometry(2001, 8307, sdo_point_type(b.lon, b.lat, NULL), NULL, NULL)) = 'TRUE'
group by b.station_name;
```
+ sdo_anyinteract：表内のジオメトリに、特定のジオメトリとの位相関係があるかどうかを確認する、つまり東京都の小地域の空間データと駅の座標位置になんらかの位相関係があればTRUEを返します。
+ sdo_geometry：Oracleのデータベース上に点やポリゴンを収容するオブジェクト型です。引数の2001は二次元の点を表し、8307はWGS84経度/緯度座標系に関連付けられています。詳しくは<a href="https://docs.oracle.com/database/121/SPATL/sdo_geometry-object-type.htm#SPATL489" target="_blank" rel="noopener noreferrer">こちら</a>をご参照ください。
+ sdo_point_type：点ジオメトリの座標を格納するオブジェクト型です。

10秒ほど待つと、以下のような結果が返されます。東京都の各駅周辺の地域の年齢階層別の人口が表示されています。
![75results.jpg](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1930162/38284c95-ab57-fbf3-7691-63fa8a6577a9.jpeg)

続いて以下のSQLで15歳未満・15~64歳・65歳以上の人口の総計と65歳以上の人口の割合を駅ごとに表示してみます。
駅データと人口データの集計の仕方が若干異なっており、rate_over_64列の計算で0除算が発生してしまうため、回避のための条件を追加しています。
```sql
select
  sum(a.T000849017) AS num_under_15,
  sum(a.T000849018) AS num_15_to_64,
  sum(a.T000849019) AS num_over_64,
  sum(a.T000849019) / NULLIF((sum(a.T000849017) + sum(a.T000849018) + sum(a.T000849019)) ,0) AS rate_over_64,
  b.station_name
from geo_age_tokyo a, STATION b
where sdo_anyinteract (a.geom, sdo_geometry(2001, 8307, sdo_point_type(b.lon, b.lat, NULL), NULL, NULL)) = 'TRUE'
group by b.station_name having sum(a.T000849019) / NULLIF((sum(a.T000849017) + sum(a.T000849018) + sum(a.T000849019)) ,0) is not null
ORDER BY rate_over_64 DESC;
```

実行すると以下のような結果が返されます。

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1930162/d1651e0a-65d4-dc21-ddb6-c225d6039406.png)

最後にこのデータを地図上で図示してみたいと思います。
上記のSQL文にcreate table...asを追加したtmp表を作成し、tmp表と駅データの表を駅名でjoinしてage_over_64_tokyo表を作成します。
```sql
create table tmp
as select
  sum(a.T000849017) AS num_under_15,
  sum(a.T000849018) AS num_15_to_64,
  sum(a.T000849019) AS num_over_64,
  sum(a.T000849019) / NULLIF((sum(a.T000849017) + sum(a.T000849018) + sum(a.T000849019)) ,0) AS rate_over_64,
  b.station_name
from geo_age_tokyo a, STATION b
where sdo_anyinteract (a.geom, sdo_geometry(2001, 8307, sdo_point_type(b.lon, b.lat, NULL), NULL, NULL)) = 'TRUE'
group by b.station_name having sum(a.T000849019) / NULLIF((sum(a.T000849017) + sum(a.T000849018) + sum(a.T000849019)) ,0) is not null
ORDER BY rate_over_64;
```

```sql
create table age_over_64_tokyo as
select a.rate_over_64, b.*
from tmp a, STATION b
where a.station_name=b.station_name;
```

Spatial Studio上でage_over_64_tokyo表のデータセットを作成し、プロジェクトを作成します。
データセットを地図上にドラッグアンドドロップすると、以下のようになります。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1930162/a58a4466-833a-b5a9-c2cf-802d297661e1.png)

65歳以上の割合によって色を分けてみます。
Layers ListのAGE_OVER_64_TOKYOの右のボタンをクリックし、Settingsをクリックします。
![age_over_64_tokyo_config.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1930162/121ea4e4-e9db-a200-d9d1-c2100bd745fd.png)

Layerの設定画面になるので、BasicのColorをデフォルトのSingle ColorからBased on dataに変更します。
![age_over_64_tokyo_layersetting1.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1930162/b3dd5984-8af2-8b8c-1f01-6207e5a9d5ec.png)

列を選択できるのでRATE_OVER_64列を選択し、基準となる値を0から0.6まで0.1刻みで設定します。
設定できたらSet paletteから色分けを再度選択し直すことで以下のような画面になります。
![age_over_64_tokyo_layersetting2.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1930162/c1db8bab-ef17-e335-babd-378919eaba17.png)

これで東京都の駅周辺の地域を65歳以上の人口の割合ごとに色分けして表示することができました。


# おわりに
Oracle Spatial Studioを使って、地理情報を含むデータの可視化やその空間分析ができることをご確認いただきました。  
また、ここでは扱いませんでしたが、面積計算を織り込んだ分析や道路/経路面を考慮した対象商圏の分析などもOracle Spatial の機能を利用することで可能になっています。  
さらにAutonomous Databaseで使用することで、大量のデータに対しても高速に処理を行うことができます。
Oracle Spatial StudioはAutonomous Databaseに標準で含まれておりますので、Autonomous Databaseを使用する際は合わせてご検討ください。

# 参考資料
+ <a href="https://docs.oracle.com/cd/E83857_01/paas/autonomous-database/adbsa/spatial-autonomous-database.html#GUID-2090A775-E049-4695-B371-E583313A5F8C" target="_blank" rel="noopener noreferrer">Autonomous DatabaseでのOracle Spatialの使用</a>
+ Oracle Database 『開発者ガイド』<a href="https://docs.oracle.com/cd/E96517_01/spatl/spatial-operators-reference.html#GUID-85422854-5133-4F1D-BF0E-228CA6EDAF87" target="_blank" rel="noopener noreferrer">空間演算子</a>