Address to Insight: Oracle AI Database 26aiでのライブ空間ヒートマップの構築 (2026/08/25)

https://medium.com/@vbalebai/from-address-to-insight-build-a-live-spatial-heat-map-in-oracle-ai-database-26ai-62cccdaec602

投稿者：vijay balebail

SQLでリレーショナルアドレスをジオコーディングし、リスクが集中している場所を特定し、ORDSを通じてリアルタイムのヒートマップを公開します。外部のGISスタック、APIキー、別途ライセンスは不要です。

主なポイント

通常の住所列が、1つのSQL文で空間クエリ可能になります。Oracle AI Databaseは、外部のジオコーディングAPIやキー管理を必要とせずに、テキスト形式の住所をSDO_GEOMETRY地点に解決します。
登録済みでインデックス付きのジオメトリ列を使用すると、SQL は近接性に関する質問に直接答えることができます。「この地点から 5 km 以内にある場所はどれか」といった質問は、テーブルをスキャンする代わりに R ツリーインデックスに対して実行されます。
1つのビューにリレーショナルフィールド、座標、正規化されたヒートスコアをまとめて格納できるため、ブラウザはデータベース処理を自ら行う代わりに、地図表示に適した小さな結果を受け取ることができます。
ORDSは、数行のPL/SQLコードでそのビューをJSONエンドポイントに変換し、Leafletを使用した単一の静的HTMLページがそれをライブヒートマップとマーカーレイヤーとしてレンダリングします。これはOCIオブジェクトストレージ上で無料でホストされます。

ほとんどのビジネスシステムには既に位置情報データが保存されています。ただ、住所欄（番地、市区町村、都道府県、郵便番号）に閉じ込められているだけです。請求書を郵送するにはそれで十分ですが、次のような質問には答えられません。

違反件数が最も多い場所は、互いに5キロメートル以内の範囲に集中している。

このチュートリアルでは、その疑問に答えます。従来のレストラン検査テーブルをSQLから抜け出さずに空間データセットに変換し、その結果をLeafletのライブヒートマップとして表示します。空間クエリを一度も書いたことがない方でも、このパターンは理解しやすいでしょう。関連属性と位置情報は同じデータベースの同じ行に保持されるため、ブラウザは本来処理しなければならない生の行ではなく、プロット準備が整った小さな結果を取得するだけで済みます。

完全な実行可能なSQLスクリプトと完成したデモは、以下でご覧いただけます。

GitHub（SQL + HTMLソース）： github.com/vijaybalebail/ADB_spacial
ライブデモ（OCIオブジェクトストレージ上でホスト）： chicago_heatmap9.html

データについて：このデータセットに含まれるレストラン名（「ザ・カッパー・スキレット」「スモークハウスNo.9」など）はすべて架空のもので、違反件数も捏造されたものです。これは単に地図を視覚的に面白くするためだけに用いられています。ここに掲載されているのは、実在する事業所や保健所の実際の検査記録ではありません。

私たちが構築するもの

レストランの住所と違反件数をリレーショナルテーブルに格納する。
住所を空間座標に変換します。外部API呼び出しは不要です。
空間メタデータを登録し、近接クエリ用のインデックスを作成します。
関係フィールドに加えて座標と正規化された熱スコアを表示するビューを作成します。
ORDSを使用して、そのビューをREST対応にします。
Leafletページからデータを取得し、ヒートマップとしてレンダリングします。

データベースは、記録システムおよび計算層として常に機能します。ウェブページは単なる表示層であり、生データにアクセスしたり、空間計算を自ら実行したりすることはありません。

実行する前に：1つの助成金、1回実行

この例では、Oracle AI Databaseで利用可能なELOCジオコーディング関数を使用します。作業スキーマがこれらの関数を呼び出す前に、ADMINユーザーがアクセス権限を付与する必要があります。

-- サンプル テーブルを所有するスキーマに対して、管理者として一度実行します: 
EXEC SDO_GCDR.ELOC_GRANT_ACCESS( 'SDO' );

これは、Autonomous AI Database の公式なライセンス付与手順です。一度実行すれば、指定されたスキーマからジオコーダーを呼び出すことができます。

重要な境界線は、アプリケーションコードがサードパーティのジオコーディングREST APIを呼び出したり、独自のキーを管理したりしないことです。データベース機能は内部的にOracleが管理するジオコーディングサービスを呼び出しますが、本番環境への展開前に、テナントにおける可用性、カバレッジ、および利用規約を確認することをお勧めします（ライセンスについては後述します）。

ステップ1 — 1つの住所を空間的なポイントに変換する

テーブルに属さない単一のアドレスから始めて、返ってくるデータの形状を確認してみましょう。

SELECT SDO_GCDR.ELOC_GEOCODE_AS_GEOM( 
           JSON_OBJECT ( 'address'  VALUE  '1600 Amphitheatre Parkway, Mountain View, CA' ) 
       ).GET_GEOJSON() AS location_geojson 
FROM    dual;

{  "type" :  "Point" ,  "coordinates" :  [  -122.0839 ,  37.42305  ]  }

GET_GEOJSON()はSDO_GEOMETRYオブジェクト自体のメンバ関数です。戻り値に対して直接呼び出すことも、後で保存された列に対して呼び出すこともできます。これは、生の座標ではなくGeoJSONドキュメントが必要な場合に便利です。（SDO_UTIL.TO_GEOJSON()ビュー内で後ほど使用されるは、スタンドアロン関数として同じ変換を実行します。どちらもドキュメント化された最新のAPIです。どちらを使用するかはスタイルの選択です。）

その座標順序は、[longitude, latitude]この記事の残りの部分にとって重要です。

OracleはSDO_POINT_TYPEX、次にYという順序でデータを保存します。つまり、経度、次に緯度です。
GeoJSONでは、経度、次に緯度の順で表記されます。
Leafletでは、マーカーやヒートポイントを作成する際に、緯度、次に経度の順で指定する必要があります。

フロントエンドにデータを送信するすべてのクエリでその順序を明示的に指定すれば、マーカーの配置は常に正しく保たれます。

ステップ2 — リレーショナルデータに空間列を追加する

CREATE TABLE restaurant_inspections ( 
    inspection_id NUMBER GENERATED ALWAYS AS  IDENTITY  PRIMARY KEY , 
    restaurant_name VARCHAR2( 200 )   NOT NULL , 
    address VARCHAR2( 300 )   NOT NULL , 
    city VARCHAR2( 100 )   DEFAULT  'Chicago' , 
    state VARCHAR2( 2 )     DEFAULT  'IL' , 
    zip_code VARCHAR2( 10 ), 
    violation_count NUMBER( 3 )       DEFAULT  0 , 
    inspection_date    DATE            DEFAULT SYSDATE, 
    location MDSYS.SDO_GEOMETRY           -- ジオコーディングされるまでNULL
 );

locationこれはMDSYS.SDO_GEOMETRYOracleのネイティブな空間型です。NULLジオコーディング処理によってデータが入力されるまで、この状態が維持されます。

初期データは20行で構成され、各行には架空のレストラン名が記載されています。21行目となる「Ballpark Bites」は、このチュートリアルの後半でライブ配信にて追加されます。これは、新しいデータが到着するとヒートマップが自動的に更新されることを示すためです。この2つの数字を混同しないように注意してください。以降のカウントはすべて、初期データである20行と、ライブ更新デモ後の21行のいずれかを指しています。

Oracle AI Database 26ai (23ai 以降) は SQL テーブル値コンストラクタをサポートしているため、20 行すべてが 1 つのステートメントとしてロードされます。つまり、1 回の往復で、完全にロードされるかまったくロードされないかの 1 つのアトミックユニットとしてロードされます。

INSERT INTO restaurant_inspections (restaurant_name, address, city, state, zip_code, violation_count) 
VALUES
     ( 'The Copper Skillet' ,         '800 W Randolph St' ,      'Chicago' , 'IL' , '60661' , 9 ), 
    ( 'Smokehouse No. 9' ,           '112 N Green St' ,         'Chicago' , 'IL' , '60607' , 8 ), 
    ( 'Corner Kitchen Co.' ,         '2900 W Belmont Ave' ,     'Chicago' , 'IL' , '60618' , 7 ), 
    ( 'Casa Maiz' ,                  '449 N Clark St' ,         'Chicago' , 'IL' , '60654' , 7 ), 
    ( 'Raven and Thistle'、          '619 W Randolph St'、      'Chicago'、'IL'、'60661'、6 )、
    ( 'Wells Street Provisions'、    '2311 W North Ave'、       'Chicago'、'IL'、'60647'、5 )、
    ( 'Lakeside Pizza Co.'、         '135 E Lake St'、          'Chicago'、'IL'、'60601'、5 )、
    ( 'Sunnyside Cafe'、             '909 W Belmont Ave'、      'Chicago'、'IL'、'60657'、4 )、
    ( 'Golden Goat Kitchen'、        '800 W Randolph St'、      'Chicago'、'IL'、'60661'、4 )、
    ( 'Windy City Hot Dogs Co.'、    '100 W Ontario St'、       'Chicago'、'IL'、'60654'、3 )、
    ( 'Harborview Table'、           '1545 W Diversey Pkwy'、   'Chicago'、'IL'、'60614'、3 ) 、
    ('The Tin Cup Tavern'、         '430 N Michigan Ave'、     'Chicago'、'IL'、'60611'、3 )、
    ( 'Hopyard Brewing Co.'、        '1927 W North Ave'、       'Chicago'、'IL'、'60622'、2 )、
    ( 'Deep Dish Union'、            '439 N Wells St'、         'Chicago'、'IL'、'60654'、2 )、
    ( 'Nightowl Diner'、             '1746 N Wells St'、        'Chicago'、'IL'、'60614'、2 )、
    ( 'The Public House Kitchen'、   '837 W Fulton Market'、    'Chicago'、'IL'、'60607'、2 )、
    ( 'Northside Pizza Parlor'、     '2207 N Clybourn Ave'、    'Chicago'、'IL'、'60614'、1 )、
    ( 'Table for Two Bistro'、       '615 W Randolph St'、      'Chicago'、'IL'、'60661'、1 )、
    ( 'Blue Awning Cafe'、           '2537 N Kedzie Blvd'、     'Chicago'、'IL'、'60647'、1 )、
    ( 'The Tasting Room'、           '1723 N Halsted St'、      'Chicago'、'IL'、'60614'、0 ) ); 
COMMIT ;

これは比較的新しい構文なので、特筆する価値があります。Oracle 23aiより前は、1つのINSERTステートメントで書き込めるのは1行だけだったので、20行をロードするには20個の別々のステートメントが必要でした。

UPDATE restaurant_inspections 
SET     location = SDO_GCDR.ELOC_GEOCODE_AS_GEOM( 
                     address, city, state, zip_code, 'US'
                  ) 
WHERE   location IS  NULL ; 
COMMIT ;

コンソールメッセージがすべての行がジオコーディングされた証拠だと鵜呑みにせず、検証してください。

SELECT  COUNT ( * ) AS total_rows, COUNT (location) AS geocoded_rows 
FROM    restaurant_inspections; 
-- ステップ 6 でのライブ挿入前は 20 / 20、挿入後は 21 / 21

これらの数値が一致しない場合は、インデックスを作成したりデータを公開したりする前に、一致しない行を調査してください。ジオコーディングされていない行は、location IS NOT NULL行をフィルタリングする下流のすべてのクエリから静かに消えてしまいます。

費用についてですが、ここで明確に述べておくべきことは、ジオコーディングは無料の組み込みデータベース機能であり、従量制のアドオンではないということです。Oracleのデータベース内ジオコーダーに関する発表によると、このSDO_GCDRパッケージとHEREから取得した参照データを使用しても、Autonomous Database Serverlessには「追加のライセンスは不要」なので、ADBのサブスクリプションにジオコーディングの料金が別途加算されることはありません。ただし、使用にはHEREのエンドユーザー規約と位置情報コンテンツに適用されるサプライヤー規約が適用されます。

これは、他の主要なクラウドとは大きく異なる点です。他のクラウドでは、ジオコーディングはデータベース機能ではなく、AWS [1]、Google Cloud [2]、Azure [3] など、アプリケーションがネットワーク経由で呼び出す別個のサービスであり、別途課金されます。Oracle AI Database では、これは SQL 関数です。

ステップ3 — 空間メタデータを登録し、インデックスを構築する

Oracle Spatialでジオメトリ列を索引付けする前に、Oracleの空間カタログであるUSER_SDO_GEOM_METADATAに登録されている列が必要です。これは、過去をスキムするためのボイラープレートではありません。Rツリー索引では、正しい境界ボックスを構築する前に、座標系、各ディメンションの有効な範囲および許容範囲の値が必要です。このステップをスキップし、CREATE INDEXが失敗します。

INSERT INTO user_sdo_geom_metadata (table_name, column_name, diminfo, srid) 
VALUES ( 
    'RESTAURANT_INSPECTIONS' , 'LOCATION' , 
    MDSYS.SDO_DIM_ARRAY( 
        MDSYS.SDO_DIM_ELEMENT( 'LONGITUDE' , -88.5 , -87.2 , 0.00001 ), 
        MDSYS.SDO_DIM_ELEMENT( 'LATITUDE' ,    41.4 ,   42.2 , 0.00001 ) 
    ), 
    4326
 ); 
COMMIT ;


restaurant_inspections(location) にCREATE INDEX restaurant_location_idx ON restaurant_inspections(location) 
INDEXTYPE IS MDSYS.SPATIAL_INDEX_V2 
PARAMETERS ('layer_gtype=POINT GEODETIC=TRUE');

GEODETIC=TRUEは、平面ではなく球面で測定された経度/緯度の座標に対して、大きい円距離計算を使用するようにインデックスに指示します。索引でVALIDがレポートされると、近接検索では全表スキャンのかわりにVALIDが使用されます。

ステップ4 — 座標を正しく取り出す

SELECT r.restaurant_name, vx AS longitude, vy AS latitude 
FROM    restaurant_inspections r, 
       TABLE(SDO_UTIL.GETVERTICES(r.location)) v 
WHERE   r.location IS  NOT NULL;

SDO_UTIL.GETVERTICES()は、ジオメトリの頂点をコレクションとして返します。TABLE(...)は、そのコレクションを結合できるリレーショナル行に変換します。点ジオメトリの場合、1行につき1つの頂点です。v.xは経度、v.yは緯度です。線とポリゴンの場合、ジオメトリごとに複数の行になります。これは、ここで何が起こっているかを記述するより正確な方法です。これは、TABLE()コレクション式による相関結合で、概念的にはそのキーワードを持つデータベースではLATERALと似ていますが、Oracle独自の古いメカニズムであり、LATERALはまったく使用しません。

他の2つの抽出方法では、同一の座標が返され、次の点について知る価値があります。完全なGeoJSONドキュメントが必要な場合はGET_GEOJSON() (ステップ1)、バウンディング・ボックス・ロジックのみが必要な場合はSDO_GEOM.SDO_MIN_MBR_ORDINATE()。GETVERTICESは、点、線およびポリゴンのジオメトリ間で均一であるため、このプロジェクト全体で使用されるものです。

ステップ5 — 1つのビュー：関連フィールド、座標、ヒートスコアをまとめて表示

CREATE  OR REPLACE VIEW restaurant_geojson_v AS 
SELECT
     r.inspection_id, 
    r.restaurant_name, 
    r.address ||  ', '  || r.city ||  ', '  || r.state ||  ' '  || r.zip_code AS full_address, 
    r.violation_count, 
    TO_CHAR(r.inspection_date, 'YYYY-MM-DD' )                AS inspection_date, 
    vx                                                      AS longitude, 
    vy                                                      AS latitude, 
    ROUND(r.violation_count / 
          NULLIF ( MAX (r.violation_count) OVER (), 0 ), 4 )      AS heat_intensity 
FROM   restaurant_inspections r, 
      TABLE (SDO_UTIL.GETVERTICES(r.location)) v 
WHERE r.location IS  NOT NULL ;

MAX(r.violation_count) OVER ()分析ウィンドウ関数は、静かで重要な作業を行っています。つまり、すべての問合せで結果セット全体の最大値を再計算するため、行が追加または削除されても、heat_intensityは0.0から1.0の間で正しく正規化されたままです。フロント・エンドではJavaScript数学は必要ありません。

ステップ6 — 空間に関する真の質問をする

索引が整えば、この記事の冒頭で提起した質問に答えることができます。

違反件数が最も多い場所は、互いに5キロメートル以内の範囲に集中している。

WITH reference_point AS ( 
    SELECT SDO_GCDR.ELOC_GEOCODE_AS_GEOM( 
               '800 W Randolph St' , 'Chicago' , 'IL' , '60661' , 'US'
            ) AS geom 
    FROM dual 
) 
SELECT r.restaurant_name, r.violation_count, 
       ROUND(SDO_GEOM.SDO_DISTANCE(r.location, p.geom, 0.005 , 'unit=meter' )) AS dist_m 
FROM    restaurant_inspections r 
CROSS  JOIN reference_point p 
WHERE   SDO_WITHIN_DISTANCE(r.location, p.geom, 'distance=2000 unit=meter' ) =  'TRUE' 
ORDER   BY dist_m;

この単一の結果には、両方の種類の情報が混在します。dist_mは空間、violation_countはリレーショナルです。これは、住所を別のGISワークフローにエクスポートするのではなく、同じデータ・モデルに場所を保持するという実際の利点です。

SDO_WITHIN_DISTANCEには、ジオメトリ列(ステップ3で構築)の空間索引と、特にAutonomous AI Database Serverlessのインスタンスで有効になっているOracle JVMという2つの前提条件があります。これを自分のテナンシ内の2つの別々のAutonomous Databasesに対してチェックしましたが、Javaは両方ですでに有効になっています。つまり、Autonomous Database ServerlessのOracle独自のドキュメントでは、デフォルトで保証されるものではなく、手動の1回かぎりのステップ(DBMS_CLOUD_ADMIN.ENABLE_FEATURE('JAVAVM')、インスタンスの再起動)として有効にすることが説明されています。クエリで何かが間違っていると仮定する前に、10秒のチェックに値します。

SELECT  value  FROM v$option WHERE  parameter  =  'Java' ;

それでは、21行目をリアルタイムで追加し、ヒートマップの更新状況を確認してみましょう。

INSERT INTO restaurant_inspections 
    (restaurant_name, address, city, state, zip_code, violation_count, location) 
VALUES ( 
    'Ballpark Bites' , '1060 W Addison St' , 'Chicago' , 'IL' , '60613' , 12 , 
    SDO_GCDR.ELOC_GEOCODE_AS_GEOM( '1060 W Addison St' , 'Chicago' , 'IL' , '60613' , 'US' ) 
); 
COMMIT ;

違反回数が12回と、これまでの最大値である9回を上回ったため、この1回の挿入は単にマップ上に新しいポイントを追加するだけでなく、heat_intensityビューのウィンドウ関数がクエリごとに再実行されるため、21行すべてに対して再計算が行われます。

[ここに、リアルタイムで更新されるヒートマップのスクリーンショットまたはGIF画像を挿入できます]

ステップ7 — ORDSを使用してビューを公開する

BEGIN
   ORDS.ENABLE_OBJECT( 
    p_enabled         = >  TRUE , 
    p_object          = >  'RESTAURANT_GEOJSON_V' , 
    p_object_type     = >  'VIEW' , 
    p_object_alias    = >  'violations' , 
    p_auto_rest_auth = >  FALSE         -- 本番環境ではOAuth2をTRUEに設定
  ); 
  COMMIT ; 
END ; 
/

ORDS.ENABLE_OBJECT()— Auto-REST — は、わずか 5 行の PL/SQL でビューをライブ JSON エンドポイント ( /ords/sdo/violations/) に変換します。代替案として、手動で定義した ORDS モジュール ( DEFINE_MODULE+ DEFINE_TEMPLATE+ DEFINE_HANDLER) を使用すると、よりきめ細かな制御が可能になりますが、同じ結果を得るには約 4 倍のコードが必要になります。

p_auto_rest_auth => FALSEは、本番のデフォルトではなく概念実証の利便性として扱います。デモ以外の場合は、TRUEにフリップし、「データベース・アクション」→「REST」→「セキュリティ」からOAuth2クライアント資格証明を発行して、クライアントが実際に必要とする列のみを公開します。

ステップ8 — 意図的に選択したLeafletでレンダリングする

Leafletは、軽量でオープンソースのマップ・ライブラリです。特に、概念実証に自由に使用でき、APIキーや使用層がなく、このアプローチが機能するかどうかを検証しているときに管理できます。リーフレット・ヒート・プラグインは、ポイントを[緯度、経度、強度] (ステップ1からのGeoJSONおよびSDO_POINT_TYPE順序の逆)と想定しているため、フェッチ・コードは、順序を明確に信頼するのではなく、明示的にフリップします。

const rows = ( await  fetch ( ORDS_URL ). then ( r => r. json ())). items || []; 
L. heatLayer (rows. map ( row => [row. latitude , row. longitude , row. heat_intensity ]), { 
  radius : 35 , blur : 20 , max : 1.0
 }). addTo (map);

完成したページは、ビルド手順もフレームワークも不要な、単一の自己完結型HTMLファイルです。ホスティングを含む完全なセットアップ手順は、下記のリンク先のSQLスクリプトとREADMEファイルに記載されています。

まとめ

レストランを除けば、そのパターンは次のようになります。テーブルにはビジネス指標と住所が格納され、Oracleは住所をネイティブな空間ポイントにジオコーディングします。OracleはSQLで正規化されたスコアと近接性の結果を計算し、ORDSは小さなJSON形式の投影図を返します。軽量のマップライブラリがその投影図を、人がすぐにクラスターを確認できる形式に変換します。

同じパターンは、店舗と需要、サービスコールと応答時間、配送と遅延、顧客の住所とサービス提供エリアのギャップなど、関係性指標がどこで発生しているかを把握することでより有用になるあらゆる場面に当てはまります。この記事のヒートマップはその一例であり、本質ではありません。

よくある質問

ジオコーディングには、SDO_GCDR.ELOC_GEOCODE_AS_GEOM()ADB サブスクリプションとは別に料金がかかりますか？Oracle の発表に
よると、Autonomous Database Serverless 上でパッケージとその HERE 提供の参照データを使用するために追加の Oracle ライセンスは必要ありません。ただし、使用には HERE のエンドユーザー規約とサプライヤー規約が適用されます。

USER_SDO_GEOM_METADATA空間インデックスを作成する前に登録する必要があるのはなぜですか
？ Rツリーインデックスは、境界ボックスを正しく構築するために、座標系、次元範囲、および許容値を事前に必要とします。CREATE INDEXこの登録がないと処理が失敗します。これは必須の入力であり、オプションのドキュメントではありません。

この結合はTABLE(SDO_UTIL.GETVERTICES(...))LATERAL結合ですか？
文字通りではありません。これはOracleのTABLE()コレクション式で相関結合を実行しています。概念的にはLATERAL、そのキーワードを持つデータベースのものと似ていますが、Oracle独自の古いメカニズムであり、LATERALまったく使用していません。

SDO_WITHIN_DISTANCEデータに明らかなエラーがないのに、クエリが失敗するのはなぜですか
？まず、2つの前提条件を確認してください。ジオメトリ列に空間インデックスが存在すること、そして特にAutonomous AI Database Serverlessの場合は、インスタンスでOracle JVMが有効になっていることです。これらはどちらもこのオペレーターのドキュメントに記載されている要件であり、実装の詳細ではありません。

自分で試してみて

完全なSQLスクリプトとHTMLソース： github.com/vijaybalebail/ADB_spacial（上記で述べた修正spacialが完了するまでリポジトリ名は変更されます）spatial
ライブデモ： chicago_heatmap9.html

このブログを検索

Oracle Cloud Infrastructure Blog(翻訳)