[Oracle Cloud] Autonomous Databaseでジオコーディングしてみた (2023/04/06)

共有インフラストラクチャ上のAutonomous Database （ADB-S）でジオコーディング関数が利用可能になったので試してみた。

# はじめに
住所情報を地理座標(緯度と経度)に変換する操作はジオコーディングと呼ばれます。
ADB-S以外のOracle Databaseでは、ジオコーディング参照データセットをデータベースに格納することで、データベース内ジオコーディングを実行できますが、ADB-Sでは参照データセットの準備なしに直接変換するPL/SQLパッケージ/関数が含まれるようになりました。

# 権限の付与(grant)・取り消し(revoke)

ADMINユーザで 以下のプロシージャを実行することで、ジオコーディング関数を利用する ユーザに対して grant / revoke します。

- SDO_GCDR.ELOC_GRANT_ACCESS
- SDO_GCDR.ELOC_REVOKE_ACCESS

SCOTTユーザに権限付与の例

```
SQL> EXEC SDO_GCDR.ELOC_GRANT_ACCESS('SCOTT');

PL/SQL procedure successfully completed.
```

SCOTTユーザから権限を取り消しの例

```
SQL> EXEC SDO_GCDR.ELOC_REVOKE_ACCESS('SCOTT');

PL/SQL procedure successfully completed.
```

# SDO_GCDR.ELOC_GEOCODE関数
ELOC_GEOCODE関数 は、(フォワード) ジオコーディングとリバースジオコーディングの両方に使用できるオーバーロードされた関数です。
引数に応じて以下の３つのアクションを実行し、出力をJSOMフォーマットで返します。

- フォーマットされた (別々の列に格納された) 住所をジオコーディング
- フォーマットされていない (単一の文字列フィールド内の完全な) 住所をジオコーディング
- 緯度/経度を指定した逆ジオコーディング


# SDO_GCDR.ELOC_GEOCODE_AS_GEOM関数
ELOC_GEOCODE_AS_GEOM関数 は、(フォワード) ジオコーディングに使用するオーバーロードされた関数です。
引数に応じて以下の２つのアクションを実行し、出力をSDO_GEOMETRY オブジェクトとして返します。

- フォーマットされた (別々の列に格納された) 住所をジオコーディング
- フォーマットされていない (単一の文字列フィールド内の完全な) 住所をジオコーディング


# ジオコーディングしてみた
## （構造化された住所の）サンプルデータ ( address_table )


| street | city | region | postal_code | country |
|:-----------|:-----------|:----------|:-----------|:-----------|
| Bötzower Str. 24 | Berlin | BE | 10407 | DE |
| Bd Carl-Vogt 67 | Genève |  | 1205 |  |
| 100 N Renfrew St | Vancouver | BC | V5K 4W3 | CA |
| 2 Chome-5-8 Kitaaoyama | Tokyo |  | 107-0061 | JP |
| 123 Beacon St | Boston | MA | 02116 | US |

## フォーマットされた住所をジオコーディング (ELOC_GEOCODE関数)

```
SQL> select sdo_gcdr.eloc_geocode(street, city, region, postal_code, country, 'RELAX_POSTAL_CODE') from address_table; 

SDO_GCDR.ELOC_GEOCODE(STREET,CITY,REGION,POSTAL_CODE,COUNTRY,'RELAX_POSTAL_CODE')
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
[{"id":"0","matchCount":"1","matches":[{"sequence":"0","x":13.43335,"y":52.53107,"houseNumber":"24","street":"Botzowstra?e","settlement":"Prenzlauer Berg","municipality":"Berlin","region":"BERLIN","po
stalCode":"10407","country":"DE","language":"GER","name":"","edgeId":53499872,"percent":0.82,"side":"R","matchCode":4,"matchVector":"??010121010??200?"}]}]

[{"id":"0","matchCount":"1","matches":[{"sequence":"0","x":6.13693,"y":46.19739,"houseNumber":"67","street":"Boulevard Carl-Vogt","settlement":"Geneve","municipality":"Geneve","region":"GENEVE","posta
lCode":"1205","country":"CH","language":"GER","name":"","edgeId":56045106,"percent":0.4,"side":"R","matchCode":1,"matchVector":"??010101010??400?"}]}]

[{"id":"0","matchCount":"1","matches":[{"sequence":"0","x":-123.04406,"y":49.28577,"houseNumber":"100","street":"N Renfrew St","settlement":"Vancouver","municipality":"Vancouver","region":"BC","postal
Code":"V5K","country":"CA","language":"ENG","name":"","edgeId":811555206,"percent":0.13,"side":"R","matchCode":1,"matchVector":"??010001010??000?"}]}]

[{"id":"0","matchCount":"1","matches":[{"sequence":"0","x":139.7333,"y":35.65,"houseNumber":"","street":"","settlement":"","municipality":"null","region":"TOKYO TO","postalCode":"107-0061","country":"
JP","language":"JPN","name":"","edgeId":-1,"percent":0.0,"side":" ","matchCode":4,"matchVector":"???10101110??400?"}]}]

[{"id":"0","matchCount":"1","matches":[{"sequence":"0","x":-71.07355166666666,"y":42.355174166666664,"houseNumber":"123","street":"Beacon St","settlement":"Boston","municipality":"Suffolk","region":"M
A","postalCode":"02116","country":"US","language":"ENG","name":"","edgeId":946710796,"percent":0.08333333333333333,"side":"R","matchCode":1,"matchVector":"???10101010??000?"}]}]

```

## フォーマットされた住所をジオコーディング (ELOC_GEOCODE_AS_GEOM関数)

```
SQL> select sdo_gcdr.eloc_geocode_as_geom(street, city, region, postal_code, country, 'RELAX_POSTAL_CODE') GEO_CODE from address_table;


SDO_GCDR.ELOC_GEOCODE_AS_GEOM(STREET,CITY,REGION,POSTAL_CODE,COUNTRY,'RELAX_POSTAL_CODE')(SDO_GTYPE, SDO_SRID, SDO_POINT(X, Y, Z), SDO_ELEM_INFO, SDO_ORDINATES)
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SDO_GEOMETRY(2001, 4326, SDO_POINT_TYPE(13.43335, 52.53107, NULL), NULL, NULL)
SDO_GEOMETRY(2001, 4326, SDO_POINT_TYPE(6.13693, 46.19739, NULL), NULL, NULL)
SDO_GEOMETRY(2001, 4326, SDO_POINT_TYPE(-123.04406, 49.28577, NULL), NULL, NULL)
SDO_GEOMETRY(2001, 4326, SDO_POINT_TYPE(139.7333, 35.65, NULL), NULL, NULL)
SDO_GEOMETRY(2001, 4326, SDO_POINT_TYPE(-71.073552, 42.3551742, NULL), NULL, NULL)
```


## フォーマットされていない (単一の文字列フィールド内の完全な) 住所をジオコーディング (ELOC_GEOCODE関数)

オラクル青山センターの住所のジオコーディング
```
SQL> select sdo_gcdr.eloc_geocode('2 Chome-5-8 Kitaaoyama,Tokyo,,107-0061,JP') from dual;

SDO_GCDR.ELOC_GEOCODE('2CHOME-5-8KITAAOYAMA,TOKYO,,107-0061,JP')
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
[{"id":"0","matchCount":"1","matches":[{"sequence":"0","x":139.80885,"y":35.68408,"houseNumber":"","street":"","settlement":"Tokyo","municipality":"Tokyo","region":"Japan","postalCode":"null","country
":"JP","language":"DAN","name":"","edgeId":720645277,"percent":0.0,"side":"L","matchCode":4,"matchVector":"???31111110??201?"}]}]
```

国会議事堂の住所のジオコーディング
```
SQL> select sdo_gcdr.eloc_geocode('1 Chome-7-1 Nagatacho,Tokyo,,100-0014,JP') from dual;

SDO_GCDR.ELOC_GEOCODE('1CHOME-7-1NAGATACHO,TOKYO,,100-0014,JP')
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
[{"id":"0","matchCount":"1","matches":[{"sequence":"0","x":139.80885,"y":35.68408,"houseNumber":"","street":"","settlement":"Tokyo","municipality":"Tokyo","region":"JAPAN","postalCode":"null","country
":"JP","language":"DAN","name":"","edgeId":720645277,"percent":0.0,"side":"L","matchCode":4,"matchVector":"???13131110??001?"}]}]
```


## 逆ジオコーディング

逆ジオコーディングできた例
```
SQL> select sdo_gcdr.eloc_geocode(13.4129, 52.52115) from dual;

SDO_GCDR.ELOC_GEOCODE(13.4129,52.52115)
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
[{"id":"0","matchCount":"1","matches":[{"sequence":"0","x":13.412898244410691,"y":52.521153112181054,"houseNumber":"","street":"Alexanderplatz","settlement":"Mitte","municipality":"Berlin","region":"B
ERLIN","postalCode":"10178","country":"DE","language":"GER","name":"","edgeId":860397302,"percent":0.559600997510756,"side":"R","matchCode":1,"matchVector":"???11141414??404?"}]}]
```

逆ジオコーディングできない例
```
SQL> select sdo_gcdr.eloc_geocode(139.7333, 35.65) from dual;

SDO_GCDR.ELOC_GEOCODE(139.7333,35.65)
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
[{"id":"0","matchCount":"0","matches":[{"sequence":"0"}]}]
```

# 出力のフォーマット

## JSONフォーマットから緯度/経度の抽出例

```
SQL> with lonlat_from_json as (
  select
    sdo_gcdr.eloc_geocode(street, city, region, postal_code, country) as j
  from
    address_table
)
select
  json_value(j,'$[0].matches[0].x') as lon,
  json_value(j,'$[0].matches[0].y') as lat
from
  lonlat_from_json;
```

|lon|lat|
|:-----------|:-----------|
|13.43335|52.53107|
|6.13693|46.19739|
|-123.04406|49.28577|
|139.7333|35.65|
|-71.07355166666666|42.355174166666664|

## SDO_GEOMETRY オブジェクトから緯度/経度の抽出例

```
SQL> with lonlat_from_sdo_geometry as (
  select
    sdo_gcdr.eloc_geocode_as_geom(street, city, region, postal_code, country) geom
  from
    address_table
)
select
  g.geom.sdo_point.x as lon,
  g.geom.sdo_point.y as lat
from
  lonlat_from_sdo_geometry g;
```

|lon|lat|
|:-----------|:-----------|
|13.43335|52.53107|
|6.13693|46.19739|
|-123.04406|49.28577|
|139.7333|35.65|
|-71.07355166666666|42.355174166666664|

# おわりに
Autonomous Database (ADB-S)で簡単にジオコーディングすることができました。
~~日本語住所、日本国の緯度/経度の対応には追加の検証が必要です。~~

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/108635/c343ef96-893e-26a1-91bf-d3605a2dd947.png)
中国、日本、韓国は [対応してません](https://www.oracle.com/jp/database/technologies/spatial-studio/faq.html)でした。


# 参考情報

- [Using the Geocoding Capabilities](https://docs.oracle.com/en/database/oracle/oracle-database/19/spatl/geocoding-address-data-concepts.html#GUID-24A5F3CE-3E46-4AA0-8196-71A831288259)
- [SDO_GCDR Package (Geocoding)](https://docs.oracle.com/en/database/oracle/oracle-database/19/spatl/SDO_GCDR-reference.html#GUID-74DA21CB-D78A-40C7-88CF-65EE41310349)
- [Now available: Geocoding API in Oracle Autonomous Database (Shared)](https://blogs.oracle.com/database/post/new-in-database-geocoder-for-autonomous-database-shared)
- [Tips for using the Oracle Spatial geocoding engine to convert addresses directly in the Oracle database](https://blogs.oracle.com/oraclespatial/post/tips-for-using-the-oracle-spatial-geocoding-engine-to-convert-addresses-directly-in-the-oracle-database)

##補足
### Database Actionsでの実行について
Autonomous DatabaseインスタンスのDatabase Actions SQLワークシートを使用している場合、SDO_GEOMETRYなどのオブジェクト・タイプが[object Object]として表示されます。

```
SELECT (SDO_GCDR.ELOC_GEOCODE_AS_GEOM('1 Chome-7-1 Nagatacho,Tokyo,,100-0014,JP')) GEOCODE FROM DUAL;
```

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/108635/1d559b92-afde-397d-c4f3-9bce0365fb39.png)

SQLワークシートでSDO_GEOMETRYタイプの結果を表示するには、Get_GeoJSON( )メソッドを呼び出します。

```
SELECT (SDO_GCDR.ELOC_GEOCODE_AS_GEOM('1 Chome-7-1 Nagatacho,Tokyo,,100-0014,JP')).GET_GEOJSON() GEOCODE FROM DUAL;
```

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/108635/6698137f-2040-c7fa-5dd0-9f3229fe6cec.png)


### 日本語住所の例

```
select sdo_gcdr.eloc_geocode('永田町１丁目７−１,東京,,100-0014,JP') GEOCODE from dual;

GEOCODE                                                                                               
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- 
[{"id":"0","matchCount":"1","matches":[{"sequence":"0","x":139.80885,"y":35.68408,"houseNumber":"","street":"","settlement":"Tokyo","municipality":"Tokyo","region":"日本","postalCode":"null","country":"JP","language":"JPN","name":"","edgeId":720645277,"percent":0.0,"side":"L","matchCode":4,"matchVector":"???31111110??001?"}]}]


select sdo_gcdr.eloc_geocode('東京都千代田区永田町１丁目７−１,100-0014,JP') GEOCODE from dual;

GEOCODE                                                     
----------------------------------------------------------- 
[{"id":"0","matchCount":"0","matches":[{"sequence":"0"}]}]
```

### ファンクションパラメータ

```
FUNCTION ELOC_GEOCODE RETURNS VARCHAR2
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 STREET                         VARCHAR2                IN
 CITY                           VARCHAR2                IN
 REGION                         VARCHAR2                IN
 POSTAL_CODE                    VARCHAR2                IN
 CC2                            VARCHAR2                IN
 MATCH_MODE                     VARCHAR2                IN     DEFAULT
FUNCTION ELOC_GEOCODE RETURNS VARCHAR2
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 ADDRESS                        VARCHAR2                IN
FUNCTION ELOC_GEOCODE RETURNS VARCHAR2
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 LONGITUDE                      NUMBER                  IN
 LATITUDE                       NUMBER                  IN
FUNCTION ELOC_GEOCODE_AS_GEOM RETURNS SDO_GEOMETRY
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 STREET                         VARCHAR2                IN
 CITY                           VARCHAR2                IN
 REGION                         VARCHAR2                IN
 POSTAL_CODE                    VARCHAR2                IN
 CC2                            VARCHAR2                IN
 MATCH_MODE                     VARCHAR2                IN     DEFAULT
FUNCTION ELOC_GEOCODE_AS_GEOM RETURNS SDO_GEOMETRY
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 ADDRESS                        VARCHAR2                IN
PROCEDURE ELOC_GRANT_ACCESS
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 USER_NAME                      VARCHAR2                IN
PROCEDURE ELOC_REVOKE_ACCESS
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 USER_NAME                      VARCHAR2                IN

```