2025年9月11日木曜日

OpenFlights.orgの航路データからプロパティ・グラフを作成しSQL/PGQで問い合わせる

OpenFlights.orgより空港、運行会社、航路のデータを取得して、Oracle Database 23aiにロードします。これらのデータよりプロパティ・グラフを作成して、SQL/PGQをによる検索を実行してみます。OpenFlights.orgのデータは2014年より更新されていないとのことなので実用的なアプリケーションは作れませんが、グラフ検索の教材としては十分です。

最初にプロパティ・グラフEBAJ_OPENFLIGHTS_ROUTES_GRAPHを作成します。

Oracle Database 23aiにユーザーを作成し、SQLclで接続します。これからの作業はローカルのコンテナで実行しているOracle Database 23ai Freeで作業を行っています。Oracle Database 23aiであれば、Autonomous Databaseでも同様に作業できます。

OpenFlights % sql wksp_apexdev@localhost/freepdb1



SQLcl: 木 9月 11 13:26:36 2025のリリース25.2 Production


Copyright (c) 1982, 2025, Oracle.  All rights reserved.


パスワード (**********?) ******

接続先:

Oracle Database 23ai Free Release 23.0.0.0.0 - Develop, Learn, and Run for Free

Version 23.9.0.25.07


SQL>


以下のスクリプトを実行して、空港を保存する表EBAJ_OPENFLIGHTS_AIRPORTS、運行会社を保存する表EBAJ_OPENFLIGHTS_AIRLINES、航路を保存する表EBAJ_OPENFLIGHTS_ROUTESを作成します。


SQL> create table ebaj_openflights_airports(

  2      airport_id   number primary key,

  3      airport_name varchar2(80 char) not null,

  4      city         varchar2(40 char),

  5      country      varchar2(40 char),

  6      iata         varchar2(3  char),

  7      icao         varchar2(4  char),

  8      latitude     number,

  9      longitude    number,

 10      altitude     number,

 11      timezone_offset  number,

 12      timezone_dst     varchar2(1  char),

 13      timezone_tz_name varchar2(40 char),

 14      airport_type     varchar2(8  char),

 15      source_of_data   varchar2(40 char)

 16* );


Table EBAJ_OPENFLIGHTS_AIRPORTSは作成されました。


SQL> /*

  2   * 運行会社

  3   * https://raw.githubusercontent.com/jpatokal/openflights/master/data/airlines.dat

  4*  */

SQL> drop table if exists ebaj_openflights_airlines;


Table EBAJ_OPENFLIGHTS_AIRLINESが削除されました。


SQL> create table ebaj_openflights_airlines(

  2      airline_id    number primary key,

  3      airline_name  varchar2(100 char),

  4      airline_alias varchar2(100 char),

  5      iata          varchar2(2 char),

  6      icao          varchar2(3 char),

  7      callsign      varchar2(60 char),

  8      country       varchar2(40 char),

  9      active        varchar2(1)

 10* );


Table EBAJ_OPENFLIGHTS_AIRLINESは作成されました。


SQL> /*

  2   * 航路

  3   * https://raw.githubusercontent.com/jpatokal/openflights/master/data/routes.dat

  4   * 

  5   * データ・ロード後に主キーroute_idを追加する。

  6   * alter table ebaj_openflights_routes add route_id number generated by default as identity primary key;

  7*  */

SQL> drop table if exists ebaj_openflights_routes;


Table EBAJ_OPENFLIGHTS_ROUTESが削除されました。


SQL> create table ebaj_openflights_routes(

  2      airline                varchar2(3 char),

  3      airline_id             number,

  4      source_airport         varchar2(3 char),

  5      source_airport_id      number, 

  6      destination_airport    varchar2(3 char),

  7      destination_airport_id number,

  8      codeshare              varchar2(1),

  9      stops                  number,

 10      equipment              varchar2(40 char)

 11* );


Table EBAJ_OPENFLIGHTS_ROUTESは作成されました。


SQL> 


OpenFlights.orgよりairports.dat、airlines.dat、routes.datをダウンロードします。
curl -OL https://raw.githubusercontent.com/jpatokal/openflights/master/data/airports.dat
curl -OL https://raw.githubusercontent.com/jpatokal/openflights/master/data/airlines.dat
curl -OL https://raw.githubusercontent.com/jpatokal/openflights/master/data/routes.dat

OpenFlights % curl -OL https://raw.githubusercontent.com/jpatokal/openflights/master/data/airports.dat

  % Total    % Received % Xferd  Average Speed   Time    Time     Time  Current

                                 Dload  Upload   Total   Spent    Left  Speed

100 1100k  100 1100k    0     0  2811k      0 --:--:-- --:--:-- --:--:-- 2808k

OpenFlights % curl -OL https://raw.githubusercontent.com/jpatokal/openflights/master/data/airlines.dat

  % Total    % Received % Xferd  Average Speed   Time    Time     Time  Current

                                 Dload  Upload   Total   Spent    Left  Speed

100  387k  100  387k    0     0  4200k      0 --:--:-- --:--:-- --:--:-- 4167k

OpenFlights % curl -OL https://raw.githubusercontent.com/jpatokal/openflights/master/data/routes.dat

  % Total    % Received % Xferd  Average Speed   Time    Time     Time  Current

                                 Dload  Upload   Total   Spent    Left  Speed

100 2321k  100 2321k    0     0  6802k      0 --:--:-- --:--:-- --:--:-- 6807k

OpenFlights % 


これらの.datファイルにはnullを示す\Nとエスケープ文字の\\が含まれています。CSVとしてのアップロード時に問題が発生するため、sedを使ってこれらの文字をファイル中から除きます。

以下の内容を記載したファイルをnorm.shとして作成します。
#!/bin/sh
sed -e 's/\\\\//g' $1.dat > t
sed -e 's/\\N//g'  t > $1.csv
rm t
このスクリプトを実行して、datファイルから\Nと\\を除いたcsvファイルを作成します。

sh norm.sh airports
sh norm.sh airlines
sh norm.sh routes
ls *.csv

OpenFlights % sh norm.sh airports

OpenFlights % sh norm.sh airlines

OpenFlights % sh norm.sh routes  

OpenFlights % ls *.csv

airlines.csv airports.csv routes.csv

OpenFlights % 


作成したCSVファイルをSQLclのloadコマンドを呼び出して、それぞれの表に保存します。

SQLclでアップロード先のスキーマに接続します。

OpenFlights % sql wksp_apexdev@localhost/freepdb1



SQLcl: 木 9月 11 13:40:44 2025のリリース25.2 Production


Copyright (c) 1982, 2025, Oracle.  All rights reserved.


パスワード (**********?) ******

接続先:

Oracle Database 23ai Free Release 23.0.0.0.0 - Develop, Learn, and Run for Free

Version 23.9.0.25.07


SQL> 


CSVファイルにはヘッダーが無く、データ行から始まるのでcolumn_namesをoffにします。

set loadformat column_names off

SQL> set loadformat column_names off

SQL> 


airports.csvを表EBAJ_OPENFLIGHTS_AIRPORTSにロードします。

load ebaj_openflights_airports ./airports.csv

SQL> load ebaj_openflights_airports ./airports.csv


csv

column_names off

delimiter ,

enclosures ""

double off

encoding UTF8

row_limit off

row_terminator default

skip_rows 0

skip_after_names


データを表にロードします WKSP_APEXDEV.EBAJ_OPENFLIGHTS_AIRPORTS


#INFO 処理された行数: 7,698

#INFO エラーのある行数: 0

#INFO 最後にコミットされたバッチで処理された最後の行: 7,698

成功: エラーなしで処理されました

SQL> 


airlines.csvを表EBAJ_OPENFLIGHTS_AIRLINESにロードします。

load ebaj_openflights_airlines ./airlines.csv

SQL> load ebaj_openflights_airlines ./airlines.csv


csv

column_names off

delimiter ,

enclosures ""

double off

encoding UTF8

row_limit off

row_terminator default

skip_rows 0

skip_after_names


データを表にロードします WKSP_APEXDEV.EBAJ_OPENFLIGHTS_AIRLINES


#INFO 処理された行数: 6,162

#INFO エラーのある行数: 0

#INFO 最後にコミットされたバッチで処理された最後の行: 6,162

成功: エラーなしで処理されました

SQL> 


routes.csvを表EBAJ_OPENFLIGHTS_ROUTESにロードします。

load ebaj_openflights_routes ./routes.csv

SQL> load ebaj_openflights_routes ./routes.csv


csv

column_names off

delimiter ,

enclosures ""

double off

encoding UTF8

row_limit off

row_terminator default

skip_rows 0

skip_after_names


データを表にロードします WKSP_APEXDEV.EBAJ_OPENFLIGHTS_ROUTES


#INFO 処理された行数: 67,663

#INFO エラーのある行数: 0

#INFO 最後にコミットされたバッチで処理された最後の行: 67,663

成功: エラーなしで処理されました

SQL> 


表EBAJ_OPENFLIGHTS_ROUTESにデータをロードしたので、主キー列ROUTE_IDを追加します。

alter table ebaj_openflights_routes add route_id number generated by default as identity primary key;

SQL> alter table ebaj_openflights_routes add route_id number generated by default as identity primary key;


Table EBAJ_OPENFLIGHTS_ROUTESが変更されました。


SQL> 


以上で頂点表、エッジ表が作成できました。

プロパティ・グラフEBAJ_OPENFLIGHTS_ROUTES_GRAPHを作成します。以下のDDLを実行します。

SQL> create or replace property graph ebaj_openflights_routes_graph

  2  vertex tables (

  3      /* 空港 */

  4      ebaj_openflights_airports key (airport_id)

  5      label airport properties are all columns,

  6      /* 運行会社 */

  7      ebaj_openflights_airlines key (airline_id)

  8      label airline properties are all columns,

  9      /* 航路 */

 10      ebaj_openflights_routes   key (route_id)

 11      label route   properties are all columns

 12  )

 13  edge tables (

 14      /* 航路から出発空港と到着空港を参照する。 */

 15      ebaj_openflights_routes as airport_connections key (route_id)

 16      source key (source_airport_id) references ebaj_openflights_airports (airport_id)

 17      destination key (destination_airport_id) references ebaj_openflights_airports (airport_id)

 18      label connects

 19      properties are all columns,

 20      /* 航路から運行会社を参照する。 */

 21      ebaj_openflights_routes AS airline_operations key (route_id)

 22      source key (airline_id) references ebaj_openflights_airlines (airline_id)

 23      destination key (route_id) references ebaj_openflights_routes (route_id)

 24      LABEL operates

 25      properties are all columns

 26* );


Property GRAPHは作成されました。


SQL> 


プロパティ・グラフが作成できました。

東京からロンドンまでの直行便を検索してみます。
select * from graph_table( ebaj_openflights_routes_graph
match (s is airport) -[e is connects]-> (d is airport)
where s.city = 'Tokyo' and d.city = 'London'
columns (
   vertex_id(s) as s, edge_id(e) as e, vertex_id(d) as d
));
８本の路線がある（あった）ようです。

SQL> select * from graph_table( ebaj_openflights_routes_graph

  2  match (s is airport) -[e is connects]-> (d is airport)

  3  where s.city = 'Tokyo' and d.city = 'London'

  4  columns (

  5     vertex_id(s) as s, edge_id(e) as e, vertex_id(d) as d

  6* ));


S                                                                                                                                                       E                                                                                                                                                D                                                                                                                                                      

_______________________________________________________________________________________________________________________________________________________ ________________________________________________________________________________________________________________________________________________ ______________________________________________________________________________________________________________________________________________________ 

{"GRAPH_OWNER":"WKSP_APEXDEV","GRAPH_NAME":"EBAJ_OPENFLIGHTS_ROUTES_GRAPH","ELEM_TABLE":"EBAJ_OPENFLIGHTS_AIRPORTS","KEY_VALUE":{"AIRPORT_ID":2279}}    {"GRAPH_OWNER":"WKSP_APEXDEV","GRAPH_NAME":"EBAJ_OPENFLIGHTS_ROUTES_GRAPH","ELEM_TABLE":"AIRPORT_CONNECTIONS","KEY_VALUE":{"ROUTE_ID":14469}}    {"GRAPH_OWNER":"WKSP_APEXDEV","GRAPH_NAME":"EBAJ_OPENFLIGHTS_ROUTES_GRAPH","ELEM_TABLE":"EBAJ_OPENFLIGHTS_AIRPORTS","KEY_VALUE":{"AIRPORT_ID":507}}    

{"GRAPH_OWNER":"WKSP_APEXDEV","GRAPH_NAME":"EBAJ_OPENFLIGHTS_ROUTES_GRAPH","ELEM_TABLE":"EBAJ_OPENFLIGHTS_AIRPORTS","KEY_VALUE":{"AIRPORT_ID":2359}}    {"GRAPH_OWNER":"WKSP_APEXDEV","GRAPH_NAME":"EBAJ_OPENFLIGHTS_ROUTES_GRAPH","ELEM_TABLE":"AIRPORT_CONNECTIONS","KEY_VALUE":{"ROUTE_ID":15551}}    {"GRAPH_OWNER":"WKSP_APEXDEV","GRAPH_NAME":"EBAJ_OPENFLIGHTS_ROUTES_GRAPH","ELEM_TABLE":"EBAJ_OPENFLIGHTS_AIRPORTS","KEY_VALUE":{"AIRPORT_ID":507}}    

{"GRAPH_OWNER":"WKSP_APEXDEV","GRAPH_NAME":"EBAJ_OPENFLIGHTS_ROUTES_GRAPH","ELEM_TABLE":"EBAJ_OPENFLIGHTS_AIRPORTS","KEY_VALUE":{"AIRPORT_ID":2359}}    {"GRAPH_OWNER":"WKSP_APEXDEV","GRAPH_NAME":"EBAJ_OPENFLIGHTS_ROUTES_GRAPH","ELEM_TABLE":"AIRPORT_CONNECTIONS","KEY_VALUE":{"ROUTE_ID":24296}}    {"GRAPH_OWNER":"WKSP_APEXDEV","GRAPH_NAME":"EBAJ_OPENFLIGHTS_ROUTES_GRAPH","ELEM_TABLE":"EBAJ_OPENFLIGHTS_AIRPORTS","KEY_VALUE":{"AIRPORT_ID":507}}    

{"GRAPH_OWNER":"WKSP_APEXDEV","GRAPH_NAME":"EBAJ_OPENFLIGHTS_ROUTES_GRAPH","ELEM_TABLE":"EBAJ_OPENFLIGHTS_AIRPORTS","KEY_VALUE":{"AIRPORT_ID":2279}}    {"GRAPH_OWNER":"WKSP_APEXDEV","GRAPH_NAME":"EBAJ_OPENFLIGHTS_ROUTES_GRAPH","ELEM_TABLE":"AIRPORT_CONNECTIONS","KEY_VALUE":{"ROUTE_ID":25118}}    {"GRAPH_OWNER":"WKSP_APEXDEV","GRAPH_NAME":"EBAJ_OPENFLIGHTS_ROUTES_GRAPH","ELEM_TABLE":"EBAJ_OPENFLIGHTS_AIRPORTS","KEY_VALUE":{"AIRPORT_ID":507}}    

{"GRAPH_OWNER":"WKSP_APEXDEV","GRAPH_NAME":"EBAJ_OPENFLIGHTS_ROUTES_GRAPH","ELEM_TABLE":"EBAJ_OPENFLIGHTS_AIRPORTS","KEY_VALUE":{"AIRPORT_ID":2359}}    {"GRAPH_OWNER":"WKSP_APEXDEV","GRAPH_NAME":"EBAJ_OPENFLIGHTS_ROUTES_GRAPH","ELEM_TABLE":"AIRPORT_CONNECTIONS","KEY_VALUE":{"ROUTE_ID":39299}}    {"GRAPH_OWNER":"WKSP_APEXDEV","GRAPH_NAME":"EBAJ_OPENFLIGHTS_ROUTES_GRAPH","ELEM_TABLE":"EBAJ_OPENFLIGHTS_AIRPORTS","KEY_VALUE":{"AIRPORT_ID":507}}    

{"GRAPH_OWNER":"WKSP_APEXDEV","GRAPH_NAME":"EBAJ_OPENFLIGHTS_ROUTES_GRAPH","ELEM_TABLE":"EBAJ_OPENFLIGHTS_AIRPORTS","KEY_VALUE":{"AIRPORT_ID":2279}}    {"GRAPH_OWNER":"WKSP_APEXDEV","GRAPH_NAME":"EBAJ_OPENFLIGHTS_ROUTES_GRAPH","ELEM_TABLE":"AIRPORT_CONNECTIONS","KEY_VALUE":{"ROUTE_ID":40079}}    {"GRAPH_OWNER":"WKSP_APEXDEV","GRAPH_NAME":"EBAJ_OPENFLIGHTS_ROUTES_GRAPH","ELEM_TABLE":"EBAJ_OPENFLIGHTS_AIRPORTS","KEY_VALUE":{"AIRPORT_ID":507}}    

{"GRAPH_OWNER":"WKSP_APEXDEV","GRAPH_NAME":"EBAJ_OPENFLIGHTS_ROUTES_GRAPH","ELEM_TABLE":"EBAJ_OPENFLIGHTS_AIRPORTS","KEY_VALUE":{"AIRPORT_ID":2359}}    {"GRAPH_OWNER":"WKSP_APEXDEV","GRAPH_NAME":"EBAJ_OPENFLIGHTS_ROUTES_GRAPH","ELEM_TABLE":"AIRPORT_CONNECTIONS","KEY_VALUE":{"ROUTE_ID":49804}}    {"GRAPH_OWNER":"WKSP_APEXDEV","GRAPH_NAME":"EBAJ_OPENFLIGHTS_ROUTES_GRAPH","ELEM_TABLE":"EBAJ_OPENFLIGHTS_AIRPORTS","KEY_VALUE":{"AIRPORT_ID":507}}    

{"GRAPH_OWNER":"WKSP_APEXDEV","GRAPH_NAME":"EBAJ_OPENFLIGHTS_ROUTES_GRAPH","ELEM_TABLE":"EBAJ_OPENFLIGHTS_AIRPORTS","KEY_VALUE":{"AIRPORT_ID":2279}}    {"GRAPH_OWNER":"WKSP_APEXDEV","GRAPH_NAME":"EBAJ_OPENFLIGHTS_ROUTES_GRAPH","ELEM_TABLE":"AIRPORT_CONNECTIONS","KEY_VALUE":{"ROUTE_ID":50082}}    {"GRAPH_OWNER":"WKSP_APEXDEV","GRAPH_NAME":"EBAJ_OPENFLIGHTS_ROUTES_GRAPH","ELEM_TABLE":"EBAJ_OPENFLIGHTS_AIRPORTS","KEY_VALUE":{"AIRPORT_ID":507}}    


8行が選択されました。 


SQL>


以前は、マニュアルや参考書やインターネット上の文献を読んでSQL/PGQの構文を勉強しながら、自分でSELECT文を書いて実行する、という勉強の仕方でしたが、今は生成AIに教えてもらえます。

Claude DesktopにSQLclのMCPサーバーを組み込んで、プロパティ・グラフEBAJ_OPENFLIGHTS_ROUTES_GRAPHがあるスキーマに接続します。

以下のように指示します。油断しているとClaudeは頂点表やエッジ表を参照して、CONNECT BYやJOINで繋げて回答します。

「これからはプロパティ・グラフEBAJ_OPENFLIGHTS_ROUTES_GRAPHにSQL/PGQで問い合わせて回答してください。グラフ問合せの勉強をしているので、CONNECT BYやJOINを使ったSELECT文は生成しないでください。」


いつもうまくSQL/PGQを使ったSELECT文が生成されるわけではありませんが、聞きたいことを問い合わせるとSELECT文を作ってくれます。


自分の聞きたかったことと生成AIが作ったSELECT文を見比べることで、SQL/PGQの学習効率が上がります。また、生成AIに指示をすればJOINとCONNECT BYを使ったSELECT文も生成しますが、読み比べるとSQL/PGQの方が可読性が高いことがわかります。生成AIが作ったかどうかに関わらず、JOINとCONNECT BYを駆使したSELECT文の検索条件が意図したものかどうかの判断は難しいですが、それと比較してSQL/PGQではその判断は比較的容易です。

次にVS Codeを使用します。先日、Oracle CorporationよりVS Codeの拡張機能として、Oracle SQL Developer Graph Visualization for VS Codeがリリースされました。オラクルのプロダクト・マネージャーのRamu Murakami Gutierrezさんが、紹介記事「Visualize Graphs in SQL Developer for VS Code」を書いています。


先ほど実行したSELECT文をVS Codeで実行してみます。Graph Visualizationの拡張機能をインストールすると、ツールにVisualize SQL Graphのボタンが追加されます。

SELECT文の検索結果をグラフとして表示するには、VERTEX_ID(s), EDGE_ID(e), VERTEX_ID(d)といった形式で、ソースの頂点のID、エッジのID、宛先の頂点のIDを列として返す必要があります。


設定を開いて、頂点やエッジにつけるラベルや色などを調整できます。


Stylesでは条件ごとに表示を切り替えることができます。


VS Codeに組み込まれたグラフを表示するコンポーネントは、Oracle APEXのGraph Visualization Pluginと同じです。

そのため、VS Codeで確認したSELECT文をAPEXアプリケーションのGraph Visualization Pluginのソースとして活用できます。

プロパティ・グラフEBAJ_OPENFLIGHTS_ROUTES_GRAPHの定義を以下に変更します。APEXのGraph Visualization PluginでLayoutにGeographicalを使用するには、経度と緯度の列が小文字でlongitudeおよびlatitudeである必要があります。


Graph Visualizaiton PluginのソースのSQL問い合せに以下を記述します。出発地と到着地は都市から国に変更しています。また、出発地はページ・アイテムP1_SOURCE_COUNTRY、到着地はP1_DESTINATION_COUNTRYで設定しています。
select * from graph_table( ebaj_openflights_routes_graph
match (s is airport) -[e is connects]-> (d is airport)
where s.country = :P1_SOURCE_COUNTRY and d.country = :P1_DESTINATION_COUNTRY
columns (
   vertex_id(s) as s, edge_id(e) as e, vertex_id(d) as d
));

属性のLayoutでGeographicalを選択します。CaptionのVertex CaptionにIATAを設定します。


日本から中国への直行便は以下のように表示されます。


日本から米国への直行便は以下のように表示されました。太平洋を渡るように表示して欲しいので、少し残念な結果です。


簡単なAPEXアプリケーションですが、エクスポートを以下に置きました。
https://github.com/ujnak/apexapps/blob/master/exports/openflights-pg-airports-and-routes.zip

今回の記事は以上になります。

プロパティ・グラフの活用の参考になれば幸いです。

完
投稿者 時刻:
ラベル: