共有インフラストラクチャ上の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)で簡単にジオコーディングすることができました。
日本語住所、日本国の緯度/経度の対応には追加の検証が必要です。
中国、日本、韓国は 対応してませんでした。
参考情報
- Using the Geocoding Capabilities
- SDO_GCDR Package (Geocoding)
- Now available: Geocoding API in Oracle Autonomous Database (Shared)
- 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;
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;
日本語住所の例
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