天文データセンター SQL講習会[初級編] 資料

山内＠天文データセンター

2013年2月 第2版

本講習会の内容

目次

注意

講義

イントロ: なぜSQLなのか?

背景

研究者・サービスの開発者が直面する問題

上記問題に対して研究者ができる事

天体カタログDBを使う上で最低限の知識

天文データに使われるデータベースシステムって?

SQL文でできる事

RDBMSのインデックスとは --- RDBMSは万能ではない

ビュー(View)

実習

SQLの修得のコツ

下記の実習の後，SQLについて「一から」レクチャーしますが， 実は，類推力がある人ならこれだけでも ある程度は使えるようになります．

SQL修得において重要なポイントは， 「GUIによる検索結果ページに表示されるSQL文を使いまわす」 事です．

◎実習の前の準備作業◎

この講習では，シェルは bash を使い， 作業ディレクトリとして /adc/data/ユーザ名/ を使います． 次のようにして，作業ディレクトリを作成し， 今回の講習に関するファイルはそこに置くようにしてください． 

$ mkdir /adc/data/ユーザ名
$ cd /adc/data/ユーザ名

◎実習◎

いきなりですが，SQL文で 「あかり」カタログ の Radial Search を試してみましょう． 「そんな無謀な!」と思った貴方，心配ありません． コピペするだけですから．

  1. AKARI Catalogue Archive Server (AKARI-CAS) の Radial Search で検索を実行しましょう． 検索条件はデフォルトでOKなので， 迷わず「Submit」をクリックします．
  2. 次に， SQL Search ページを開いて， SQL入力欄を消去(clearボタンをクリック)した後に， Radial Searchの結果ページにある SQL文をコピペしましょう．
  3. Radial Searchの場合と，SQL Searchの場合とで， 同じ結果が得られる事を確認しましょう．
  4. SQL Searchページにおいて 「LIMIT 10」の「10」の部分を変更して， 結果ページに表示される件数が変わるのを確認しましょう．
  5. すぐにできてしまった方は，検索半径も変えてみましょう．

SQLの最初の一歩

SQLでの検索・演算は，必ず「SELECT」から始めます． SQLでは，文字列を除き，大文字小文字は区別されません (RDBMSによっては， テーブル名やカラム名で大文字小文字を区別する事もあります)．

では，最も簡単な SELECT文 を試してみましょう．

◎実習◎

AKARI-CASの SQL Search ページを使います． 本日の実習では，しばらくこのページを使うので， 別ウィンドゥで常に開いておくと便利です．

下記では，SQL文とその結果を示していますので， 手で入力して結果を確認してください．

テーブルにアクセスする時の基本

基本形

テーブルにアクセスする時のSQL文の基本形は下記のとおりです． 

-- この一行はコメント文です
SELECT カラム名1,カラム名2,…
FROM テーブル名
WHERE 検索条件
LIMIT 表示する件数

「-- 」から始まる部分はコメント文で， RDBMSはこの部分を解釈しません．

◎実習◎

AKARI-CASの SQL Search ページで試しながらみていきましょう．
(以下の例文中のテーブルとカラムの説明: IrcObjAll)

ワイルドカード

SELECT の後に「*」を指定すると， 「すべてのカラム」と解釈されます．
どのようなカラムがあるかを把握する時にも使えます． 

SELECT *
FROM IrcObjAll
LIMIT 10

カラムの別名

SELECT の後に「本来のカラム名 AS 別名」と書けば， 表のカラム名を変更できます． 

SELECT objid, objname AS tentaimei, ra, dec
FROM IrcObjAll
LIMIT 10

テーブルの別名

FROM句の 本来のテーブル名の後に任意の名前を書けば， 表の別名を定義できます． 

SELECT o.objid, o.ra, o.dec     -- 別名を定義すると，このように使う事が多い
FROM IrcObjAll o                -- IrcObjAll の別名として「o」を定義
LIMIT 10

SELECTの後では「テーブル名.カラム名」と 書いていますが，これが厳密な書き方です．

件数を知る

SELECT の後に「count(*)」を使うか， 

SELECT count(*)
FROM IrcObjAll

次のようにプライマリキーで行を数えます． 

SELECT count(objid)
FROM IrcObjAll

count()の場合も， インデックスが存在するカラムを指定した方が高速です．

ただし，テーブルが巨大な場合は実行に非常に時間がかかる事があるので， 注意してください．

あるカラムの最小値・最大値を知る

SELECT の後に「min(column1)」「max(column1)」 を使います． 

SELECT min(flux_09), max(flux_09)
FROM IrcObjAll

カラムにインデックスが作成されていれば， テーブルが巨大な場合でも瞬時に求まります．

先頭の n 行のみ表示

データベース製品によって異なります．

◎課題1◎

続いて，AKARI-CASの SQL Search ページを使います．

  1. テーブル「IrcObjAll」のカラム 「objID」， 「objName」， 「ra」， 「dec」， 「flux_09」， 「flux_18」 について，最初の20件について表示してください．
  2. 今度は，「ra」「dec」 のカラム名を 「lon」「lat」に変更したテーブルを 表示してください．

いよいよ検索

基本形の復習

検索を実行する時は， SELECT … FROM … WHEREの後に条件 を書きます． 

SELECT カラム名1,カラム名2,…
FROM テーブル名
WHERE 検索条件

検索条件を書く時は，次のような演算子・関数を使う事ができます．
(以下の例文中のテーブルとカラムの説明: IrcObjAll)

◎課題2◎

続いて，AKARI-CASの SQL Search ページを使います．

  1. テーブル「IrcObjAll」について， カラム「flux_09」の値が17.5から18.0であり， かつ カラム「fQual_09」， 「fQual_18」の値が 3 のものが何件あるかを調べてください．
  2. 今度は同じ条件で，すべてを取得してください．

遅くならない「WHERE」句の書き方の掟

RDBMSというものは， 1つのSQL文について「ただちに結果が返ってくる」 ように利用するものです． もし，検索に時間がかかりすぎる場合は， 質の悪いWHERE句を書いている可能性が高いのです． 検索に時間がかかりすぎると，利用者本人がイライラするだけではなく， 不必要にサーバに負担をかける事にもなります．

注意しなければならないのは， 「WHERE」句の式は，一般的なプログラミング言語と同じ感覚で 書いてはいけない 事です． ここでは，検索で遅くならないための「基本」を身につけましょう．

◎実習◎

続いて，AKARI-CASの SQL Search ページを使います．

  1. 「あかり」カタログの天体のフラックスでは，AB等級ではなく Jy を採用しています． AKARI-CASには，fJy2ABmag()， fABmag2Jy() という関数で，AB等級⇔Jy 相互の変換が可能です． 次のSQL文を実行して，Jy と等級との両方が表示される事を確認してください． 
    SELECT objid, 
       flux_09, fjy2abmag(flux_09) AS mag_09,
       flux_18, fjy2abmag(flux_18) AS mag_18
FROM ircobjall
WHERE fQual_09 = 3 AND fQual_18 = 3
LIMIT 10
  2. となると，AB等級で検索したくなる人も出てくるかもしれません．
    次の例は，flux_09 が 13等より暗いものを索こうとしていますが， マズいSQL文なのです． 実行してもすぐには結果が返ってこない事を確認してください． 
    SELECT objid, 
       flux_09, fjy2abmag(flux_09) AS mag_09
FROM ircobjall
WHERE 13.0 < fjy2abmag(flux_09)
  3. 正しい書き方は，次のとおりです．
    実行すると，ただちに結果が返ってくる事を確認してください． 
    SELECT objid, 
       flux_09, fjy2abmag(flux_09) AS mag_09
FROM ircobjall
WHERE flux_09 < fabmag2jy(13.0)

なぜ最初のWHERE句の書き方がマズいのでしょう? 「ピン」ときましたか?

最初のものがマズい理由を考えるにあたって， 天体カタログDBを使う上で最低限の知識 で述べたインデックスの仕組み，すなわち 「RDBMSでの高速検索のためには， 表のカラム・データに対してインデックスが使われる必要がある」 という事を思い出してください．

最初の「WHERE 13.0 < fjy2abmag(flux_09)」 では，RDBMSは「fjy2abmag(flux_09)に対して検索」と 解釈します． しかし，インデックスが作成されているのは， あくまでflux_09の値そのものですから， fjy2abmag(flux_09)のように演算をかまされると， インデックスは使えないのです．インデックスが使用不可となれば， RDBMSは結局，カラムflux_09の全部の行に対して fjy2abmag()関数の計算を行なって， 順に条件を評価するハメになり，遅くなるわけです．

したがって，遅くならない「WHERE」句の書き方の掟は， 「WHERE句でカラム・データに対して演算をしない事」 だと言えます． この掟は，今日の講習では最重要な知識ですから， 絶対に忘れないようにしたいものです．

ここで述べた基本を守りつつ， 難しい条件での検索をいかにパフォーマンスを落とさずに行えるかが， SQLというパズルにおいて最も重要な局面なのです．

この講習会の初級編ではとりあげませんが， カラム・データに対して演算をしている場合でも インデックスを使う技があります(式に対するインデックス(式インデックス))． fjy2abmag(flux_09)のような場合は式インデックスを作る事ができますが， 関数の引数に検索条件が入ってくる場合はインデックスは作れません．

ひとやすみ: 検索後のソートとランダム抽出

WHERE句の後に「ORDER BY xxx」 を指定すると，昇順，降順またはランダムに並び換えができます． 

SELECT カラム名1,カラム名2,…
FROM テーブル名
WHERE 検索条件
ORDER BY xxx

ランダムに並び換える場合は，RDBMS製品によって異なります．

上記は，すべての行に対して浮動小数点型の乱数を発生させ， その値によって並び換えを行う事を意味します． ここで紹介している 「ORDER BY random()」は， 乱数を発生させる関数 random() を利用した少し特殊な書き方で， 厳密なSQL文は次のようになります． 

SELECT カラム名1,カラム名2,…,random()
FROM テーブル名
WHERE 検索条件
ORDER BY random

このSQLステートメントから明らかなように， この方法は乱数発生に必要となる時間がテーブルの行数に比例するので， 巨大なテーブルに対しては不適切と言えます．

参考: 巨大なテーブルからのランダム抽出については， 巨大テーブルとの付き合い方 --- 4.7億天体の2MASS PSCで実習 をご覧ください．

◎課題3◎

続いて，AKARI-CASの SQL Search ページを使います．

  1. テーブル「IrcObjAll」について， カラム「flux_09」の値がAB等級で12.2等より暗い天体を検索し， フラックスの昇順で並べ変えてください． 取得するカラムは，objidとフラックス(Jy と AB等級) とします．
  2. 今度は同じ条件で検索し，ランダムに100個選択してください．

もっと学びたい人は 「行の並び替え」をどうぞ．

FROM句でのサブクエリ --- 絞り込み検索

次のような構文を使うと，検索した結果テーブルに対して， さらに条件を絞り込んで検索する事ができます． 

SELECT o.*
FROM              -- 次の()の中は表を返すものならどんな SELECT 文でも良い
    (
      SELECT column1, column2, …       -- ここは単に「SELECT *」でも良い
      FROM table_name
      WHERE column1 < 123
    ) o                     -- () を実行後の結果テーブルを「o」と名づける
WHERE o.column2 < 345

()の中に書いたものをサブクエリといい， 検索は，まずサブクエリのSELECT文が実行され， その後に外側のSELECT文が実行されます． ただし，インデックスが使われるのは内側のSELECT文だけである という点に注意してください．

()の中のSELECT文の 結果テーブルに対して 任意の名前をつけ(上記の例では「o」)， それを外側のSELECT文からは 名前つきで使う事がポイントです．

わざわざこんな事をしなくても，1つのSELECT文で WHERE以下でANDを使って ズラズラと条件をつなげれば良い気がしますが，その場合は 検索のアルゴリズムはRDBMSが勝手に決めるので， どのようにインデックスが使われるかは明らかではありません． FROM句でのサブクエリを使う事により， 確実に意図したカラムのインデックスを使って検索をさせる事ができます． また，きちんと整形されたサブクエリのSQL文により， 検索ロジックを明確にする事ができます．

もう1つサブクエリが有効なのは，表の結合 の時です． 表の結合についてはこの後で実習しますが， サブクエリにより，結果テーブルを既存のテーブルに結合できます．

◎課題4◎

続いて，AKARI-CASの SQL Search ページを使います．

  1. テーブル「IrcObjAll」について， カラム「flux_09」の値がAB等級で12.2等より暗いものを 検索して結果を確認し，さらに FROM句でのサブクエリを使って，結果テーブルの nScanC_09(サーベイにおいて天体が検出された回数) の値が 6 以上のもののみ選んでください．
    取得するカラムは，objid，フラックス(Jy と AB等級)， nScanC_09 とします．

WHERE句でのサブクエリ

サブクエリとして重要で奥が深いものに， WHERE句でのサブクエリがあります． IN，EXISTS，ALL 等をWHERE句のサブクエリで使うと， かなり高度な検索ができます． ここでは比較的良く利用する IN，EXISTS を使った サブクエリを取り上げます．

◎実習◎

AKARI-CASには，「あかり」カタログの天体1つ1つについてを， SIMBADとNEDに座標で問い合わせた結果がテーブルに保存されています． そのテーブルの1つが「SimbadIrc」です． ここでは，「あかり」IRCカタログの中にSIMBADのデータが存在するものが 何件あるかを調べてみます．

その前に，テーブル「SimbadIrc」の内容を ちょっと確認しておきましょう． 次のSELECT文を，AKARI-CASの SQL Search ページから実行してみましょう． 

SELECT *
FROM SimbadIrc
LIMIT 10

テーブル「SimbadIrc」のカラムには， 「oid」「objid」 が含まれていますね． このうち前者はSIMBADデータベースの天体IDで， 後者は「あかり」カタログの天体IDです． objidは表 SimbadIrc において重複する場合があり， 単純にこのテーブルのobjidを数えても 件数を取得できません．

このような場合には， 「WHERE ... IN (...)」または 「WHERE EXISTS (...)」 の構文を使います．それぞれの場合を示します． 

SELECT count(o.objid)
FROM IrcObjAll o
WHERE o.objid IN 
(
 SELECT s.objid
 FROM SimbadIrc s
)

SELECT count(o.objid)
FROM IrcObjAll o
WHERE EXISTS
(
 SELECT *                      -- ここは「SELECT 1」と書く作法もある
 FROM SimbadIrc s
 WHERE o.objid = s.objid
)

「WHERE ... IN (...)」 「WHERE EXISTS (...)」 は「(...)で見つかったものすべてを取り出す」 という事を意味しています． EXISTS句を使ったSQL文では， サブクエリ内の条件「WHERE o.objid = s.objid」が (...)の外側のテーブル「IrcObjAll」を 参照していますが，このような形を「相関サブクエリ」と呼んでいます。

IN か EXISTS かは状況による(RDBMSによっても異なる)ので 判断が難しいものですが， 上記の例ではテーブル SimbadIrc が大きいので， 一般的には EXISTS の方が良いとされます． 使い分けの基本的な判断基準として， 次のような感覚を持っておくと良いかもしれません． 

-- IN を使う場合
SELECT ...
FROM でかいテーブル o
WHERE o.id IN 
(
  小さい結果セット
)

-- EXISTS を使う場合
SELECT ...
FROM 小さいテーブル o
WHERE EXISTS
(
 SELECT *
 FROM でかいテーブル s
 WHERE o.id = s.id
)

参考1: TECHSCORE サブクエリ
参考2: 副問い合わせ式

複数のテーブルを結合する(ナチュラルジョイン)

┌──────────┐　┌────────┐　┌──────────────┐
│　　　　　　　　　　│　│　　　　　　　　│　│　　　　　　　　　　　　　　│
├──────────┤　├────────┤　├──────────────┤
│　　　　　　　　　　│　│　　　　　　　　│　│　　　　　　　　　　　　　　│
│　　　　　　　　　　│　│　　　　　　　　│　│　　　　　　　　　　　　　　│
│　　　　表Ａ　　　　│＋│　　　表Ｂ　　　│＝│　　　　　　表Ｃ　　　　　　│
│　　　　　　　　　　│　│　　　　　　　　│　│　　　　　　　　　　　　　　│
│　　　　　　　　　　│　│　　　　　　　　│　│　　　　　　　　　　　　　　│
│　　　　　　　　　　│　│　　　　　　　　│　│　　　　　　　　　　　　　　│
└──────────┘　└────────┘　└──────────────┘

のように，複数の表を横方向に結合する事を「ジョイン」といい， RDBMSでは複雑な条件による結合でも簡単にできてしまいます．

この「ジョイン」のうち， 表の結合のための条件を完全に満たす行のみ取り出して， 2つのテーブルを結合する事を， 「ナチュラルジョイン」といいます． 別の言い方をすれば， 2つの表で互いに結びつく行のみを取り出して結合する という事になります．

◎実習◎

さきほど，「あかり」カタログの天体についての SIMBADのテーブル SimbadIrc を使いました． これに，物理量のカラムを追加したものが 「SimbadIrcAll」であり， 今度はこれと「あかり」IRCカタログとをナチュラルジョインしてみます．

その前に，テーブル「SimbadIrcAll」の内容を ちょっと確認しておきましょう． 次のSELECT文を，AKARI-CASの SQL Search ページから実行してみましょう． 

SELECT *
FROM SimbadIrcAll
LIMIT 10

SimbadIrc と同様， 「oid」「objid」 が含まれていますね．

つまり，両者に 「あかり」カタログの天体ID(objid)が存在するわけですから， 「両者のobjidが等しい」を条件に 2つのテーブルを 結合できるわけです．

ナチュラルジョインには，二通りの書き方があります． AKARI-CASの SQL Search ページから実行し，同じ結果が得られる事を確認してください．

複数のテーブルを結合する(レフトジョイン)

ナチュラルジョインでは，表の結合のための条件を完全に満たす行のみを 選んでいましたが， 「レフトジョイン」の場合は，条件を完全に満たさない場合も， 左側のテーブルについてはすべてを出力します．

結合条件に合わず，結びつけるべき行が右側のテーブルに存在しない場合， セルはすべて「null」で埋められます．

構文はナチュラルジョインの2つめの書き方と同じで， 「LEFT JOIN 結合するテーブル ON 条件」とします．

◎課題5◎

  1. ナチュラルジョインでの「2つめの書き方」を参考に， 「あかり」IRCカタログとSIMBADのデータとを，レフトジョインし，最初の 30件を取得してください． SIMBAD側にAKARI天体のデータが無い場合，「null」で埋まっている事を 確認しましょう．

講義

SQL の入力を受けつけている検索システムを発見したら…

実習

ひとやすみ: AKARI-CASが持つ，SIMBADとNEDデータのもう1つの使い方

AKARI-CASの Match-up AKARI catalogues with Cached SIMBAD/NED catalogs を使えば， 「M %」(メシエ天体の場合)， 「NGC %」(NGC天体の場合) と入力するだけで，SIMBADあるいはNEDのデータと「あかり」カタログとを 瞬時に結合する事が可能です．

◎実習◎

みなさんが知っているカタログに「あかり」天体が存在するか， 確認してください．

◎自由課題(宿題)◎

AKARI-CASのSIMBADのデータを保存したテーブル 「SimbadCache」 「SimbadIdent」 「SimbadFis」 「SimbadIrc」 が，どのような関係を持っているかを調べてください． SQL Schema: Tables にテーブルの解説があります．

GROUP BY で表の集計をする

天体カタログには， 天体種別・スペクトル型のような「天体の分類情報」が含まれている事が あります． このような時，どの種別あるいはどの型の天体がいくつあるかを 調べたくなるものです．

このような場合もSQLなら「GROUP BY」と「count()」 を使えば簡単に結果を得られます．

◎実習◎

さきほどのSIMBADのテーブル「SimbadIrcAll」には， type， specType， morphType というカラムがありました(データは文字列)． このうち，typeはSIMBADデータベースにおける 天体種別， specTypeはスペクトル型， morphTypeは形態型を示しています．

それでは，「あかり」IRCカタログには どんなSIMBAD登録天体がどれくらいいるのかを， GROUP BYを使って簡単に調べてみましょう． 

SELECT type, count(type)
FROM SimbadIrcAll
WHERE orderDistance = 1
GROUP BY type
ORDER BY count

orderDistanceは， 1つのSIMBAD天体に対し，複数の「あかり」天体がヒットしている場合， 距離の近い順に，1,2,3,.. の順にセットされているものです． 従って，SIMBAD天体と「あかり」天体とを"一対一"の関係にするには， orderDistanceが 1 のものに 限る必要があるわけです．

ではさらに，specType， orderDistance， についても集計してみましょう． 

SELECT type, count(type) as cnt_t,
       specType, count(specType) as cnt_s,
       orderDistance, count(orderDistance) as cnt_o
FROM SimbadIrcAll
GROUP BY type, specType, orderDistance
ORDER By type
LIMIT 2000

type→specType→orderDistance のように複数のカラムを階層的に集計する事もできます．

2つ前の例文では，orderDistanceが1だけのものを集計するのに， WHERE句を使いましたが， 実はこのような場合は，次のようにHAVING句を使うのが正解です． 

SELECT type, count(type) as cnt_t,
       specType, count(specType) as cnt_s,
       orderDistance
FROM SimbadIrcAll
GROUP BY type, specType, orderDistance
HAVING orderDistance = 1
ORDER By type
LIMIT 2000

HAVING句は，GROUP BYの後に書き， グループ化した後からある条件に合うグループだけを選択(不要なグループを除去) するために使います． これにより，SQLでは非常に柔軟な集計が可能になっているわけです．

参考: テーブル式

◎課題6◎

  1. 最後のSQL文を流用して， specType が NULL のものを 集計結果から除去してください．
  2. 「あかり」天体カタログには，空をスキャンした回数を示すカラムがあります． 「あかり」IRCカタログ「IrcObjAll」の カラム「nScanP_09」 を使って，空のスキャン回数の分布を 「GROUP BY」と「count()」とを使って 簡単に調べてください． nScanP_09で並び換えるとその分布が直感的に把握できます．
  3. 「あかり」IRCカタログのポジションエラー(カラム「posErrMj」)について， サンプリング間隔が 0.1 のヒストグラムのためのデータを作成してください． floor()関数を使うと良いでしょう．

◎発展学習◎

次のようにすると，最も多い天体種別は何かを調べる事ができます． 

SELECT type, count(*)
FROM SimbadIrcAll
GROUP BY type, orderDistance
HAVING orderDistance = 1 AND
       count(*) >= ALL (SELECT count(type)
                        FROM SimbadIrcAll
                        GROUP BY type, orderDistance
                        HAVING orderDistance = 1)

「左辺 >= ALL (…)」は， 「(…)の結果すべてに対して，左辺が等しいか大きい場合に真」 を意味します．
参考1: CodeZine HAVING句の力
参考2: 副問い合わせ式

講義

RDBMSにおける Radial Search の仕組みの考え方

天球上のあるポジションからある角度半径内の天体を検索する Radial Search (コーンサーチとも呼ばれる) は，天文屋にとっては最も良く使う検索である． ここでは，RDBMSでどのようにして高速な Radial Search を実現しているかを解説する．

試しに，「あかり」IRCカタログについて，この検索を素直にSQL文に書き下してみる． テーブル中のxyz座標(単位ベクトル; 値の範囲は0.0から1.0)を使うと余弦定理で2点間の角度が簡単に求まり(内積の公式でも良いが，課題のためここでは余弦定理を用いる)， 次のように書ける(テーブル中の天体座標はcx,cy,czである)． 

SELECT *
FROM IrcObjAll
WHERE
 acos( (2.0 - ((cx-位置x)^2 + (cy-位置y)^2 + (cz-位置z)^2) ) / 2.0 )
 <= 角度半径

しかし，このSQL文では検索に非常に時間がかかるのは明白である． 「遅くならない『WHERE句』の書き方の掟」 で示したとおり， カラム・データに対して演算をしてしまっているので， インデックスは使われないからである．

このような場合，RDBMS上での実装の定石は，

  1. 必要な行を取りこぼさないように， インデックスを使ってザックリ(少し多めに)検索をする．
  2. 1.の結果から，厳密な条件に合うものだけを取り出す．

のように，検索を2段階に分ける事である．

当然，天文業界ではすでにいくつかの手法が確立しており， 本講義ではそのうちの2つを紹介する．

天球分割によるRadial Searchの手法

  1. 何らかの手法で天球を非常に細かく分割
  2. 分割したそれぞれの領域にuniqueな「天域ID」を付与


    (図は SDSS SkyServer から引用)
  3. 天体リストのテーブルに「天域ID」のためのカラムを追加し， 値の登録を行ない，インデックスを作成
  4. 検索する時は， 検索領域を取りこぼさないように必要な「天域ID」を計算して求め， テーブル上の「天域ID」でインデックスで検索した後， 検索位置からの角度を求めて厳密な条件を満たすものだけ取り出す．

    (図は SDSS SkyServer から引用)

直交座標系(xyz)を利用したRadial Searchの手法

天球分割の方法に比べ，実装が単純で小さめの検索半径の時に 非常に高速に検索が可能である．

  1. 天体リストのテーブルに，直交座標系の位置(cx,cy,cz;値の範囲は-1.0から1.0)を入れるカラムを追加し，値を登録．
  2. xyzの組み合わせで，インデックスを作成(複合インデックス)．
  3. サーチ半径Rの時，サーチポジションを中心とする 一辺2Rの立方体の中にある天体を，インデックス検索で 取り出し「候補」とする．
  4. 上記「候補」の天体すべてについて，検索位置からの角度を求め， 厳密な条件を満たすものだけ取り出す．

実習

直交座標系を利用した方法で，Radial Searchしてみよう

◎課題7◎

次の手順に従って， 「あかり」IRCカタログについて， 検索座標(ra=188.580537[deg],dec=6.467703[deg])，直交座標では(cx=-0.982513866380272,cy=-0.148249882182357,cz=0.112643130298329) から半径 R= 0.02 ラジアン内の天体を検索してください．

  1. 「WHERE (cx BETWEEN xmin AND xmax) AND (cy BETWEEN ...」 の形を使って， 検索座標を中心とする一辺 2R の立方体内にある天体を IrcObjAllテーブルから検索し， カラム objid, objname, ra, dec, cx, cy, cz のデータを取得してください．
  2. FROM句でのサブクエリを使って，1. の結果から厳密な条件に合うもののみ 取り出してください．

ひとやすみ: SQLで使える関数の種類

SQL文で使える関数は「ストアド関数」と呼ばれ， 関数の返り値は 3 通りあります．

◎実習◎

PostgreSQLでは， generate_series() というテーブルを返すビルトイン関数があります． 返り値がどのようになるかを確認してください． 

SELECT *
FROM generate_series(1, 32)

ストアド関数を使ってらくらく位置検索

SQLの修得のコツ で動かしてもらったRadial Searchは， 実はストアド関数を使ったものでした．

Radial Search や Rectangular Search のように，天文屋が頻繁に使う位置検索については， 検索システムの構築者が高性能なストアド関数を仕込んでくれている事があります． その場合は，必ずそれらを利用します．

ここでは，SDSSの場合と，AKARI-CASの場合とを紹介します．

◎実習◎

位置，半径を変えて， AKARI-CASの SQL Search でいろんなRadial Searchをしてみましょう．

登録されているカタログとカタログとのマッチアップ

データベースに複数の天体カタログが存在する場合， それらは簡単に座標マッチアップが行える可能性があります．

◎課題8◎

AKARI-CAS上で，「あかり」衛星によるFISカタログとIRCカタログとを 座標によりマッチアップする問題です．

  1. ストアド関数fGetNearestObjIDEq()の引数は，順に， の順にセットします． この関数の返り値は，検索範囲内の，最も検索座標に近い天体 のobjidです．
    では，FISのカタログ(FisObjAllテーブル)の右端に， IRC天体のobjidを追加したテーブルを最初の100件を 表示させてください．サーチ半径は 1.0 [arcmin] としてください．
    ☆ヒント: ____ を考えよう． 
    SELECT objid,ra,dec, fGetNearestObjIDEq(_____, ____, ____, 1.0) as objid_irc
FROM FisObjAll
LIMIT 100
  2. FISカタログに， ストアド関数fGetNearestObjIDEq()による マッチアップ結果として，IRCカタログを ナチュラルジョインしてください． サーチ半径は 1.0 [arcmin] としてください． 結果テーブルとして，それぞれの objid，J2000座標， FIS天体からの角度(fDistanceArcMinEq()を使う) を取得してください．
    ☆ヒント: ____ を考えよう． 
    SELECT p.objid, p.ra, p.dec,
       q1.objid, q1.ra, q1.dec,
       fDistanceArcminEq(p.ra,p.dec,q1.ra,q1.dec) as distance
FROM FisObjAll p
JOIN IrcObjAll q1
ON fGetNearestObjIDEq(_____, ____, ____, 1.0) = q1.objID
LIMIT 100
  3. AKARI-CASには IRASカタログも 登録されています． テーブルは「iras」 で，その構造は「あかり」の場合と同様です．
    では，2. の結果に，さらにIRASカタログを同様にFISとマッチアップして ナチュラルジョインし，最初の100件を取得してください．
    ☆ヒント: SQL文のはじめは次のようにします． 
    SELECT p.objid, p.ra, p.dec,
       q1.objid, q1.ra, q1.dec,
       fDistanceArcminEq(p.ra,p.dec,q1.ra,q1.dec) as distance1,
       q2.objid, q2.ra, q2.dec,
       fDistanceArcminEq(p.ra,p.dec,q2.ra,q2.dec) as distance2
FROM ...

ひとやすみ: AKARI-CAS で便利なストアド関数

◎実習◎

AKARI-CAS では，日常的によく使いそうな関数をいくつか用意しています． 下記に例を示しましたので，動かしてみてください．

参考1: AKARI-CASのSQL関数のリファレンス
参考2: SDSS DR8 SkyServerのSQL関数のリファレンス

巨大テーブルとの付き合い方 --- 4.7億天体の2MASS PSCで実習

テーブルの行数が1千万行くらいになると， 全ての行についての検索はインデックスを使わないと かなり厳しくなってきます． また，構築者側の事情として， インデックスの作成に時間がかかり， インデックスのためのデータがディスクを消費するという問題があります． したがって，特に一億を越えるような大規模カタログの場合には， データベース上でインデックスが作成されているカラムは， 「プライマリキー」「座標」「フラックス」程度に限定されていると考えた方が良いでしょう．

小規模カタログではかなり自由にテーブル全体に対して検索・集計が可能でしたが， 大規模カタログを複雑な条件で検索する場合は， 次のような使い方が前提になってきます．

もし，どうしても インデックスが作成されていないカラムを 一番最初の検索条件に含まなければならない場合は， データセンターの人に助けを求めると良いでしょう．

◎実習: 2MASS PSCのランダム抽出◎

巨大テーブルから直接ランダムに抽出したい場合， PostgreSQLの場合は次のようにobjidをランダムに 発生させ，カタログのテーブルから該当する行を取り出す方法があります．

次の例では，AKARI-CASにて，約4.7億行の2MASSカタログ (テーブル「twomass」)から， 約100件(乱数が重複した場合は100件未満)をランダムに取り出します． random()関数が 0.0〜1.0 の範囲の値を返す事と， objidが500000001から連続した数字である事を 利用しています． SQL Search で試してみましょう． 

SELECT o.*
FROM twomass o
WHERE o.objid IN
( 
  SELECT 500000001 + CAST(floor(470992970*random()) AS INTEGER)
  FROM generate_series(1, 100)
)

generate_series()は， 「ひとやすみ: SQLで使える関数の種類」 で実習しました．
CAST(value AS type)は型を変換するために使う構文です． 上の例では，浮動小数点数を整数(4バイト)に変換しています．
なお，CAST()で浮動小数点数を整数に変換する場合， RDBMS製品によって四捨五入か切り捨てかが異なりますので， 上記のように必ずfloor()を使ってから型変換します．

次の例は，上記と同じ事をナチュラルジョインを使って書いたものです． 

SELECT o.*
FROM (
      SELECT 500000001 + CAST(floor(470992970*random()) AS INTEGER) AS rid
      FROM generate_series(1, 100)
      GROUP BY rid                           -- 重複を避けるために使用
     ) s
JOIN twomass o
ON   o.objid = s.rid

JOINを使った場合よりも WHERE ... IN の方が やりたい事を素直にSQL文に表現できており， こういう場面では前者の書き方をお勧めします．

MS SQL Serverを採用している SDSS SkyServerではgenerate_series()が使えません． そのかわり，htmIDと呼ばれる天域IDを使ってランダム抽出する方法が Webページ に紹介されています．

◎課題9◎

AKARI-CASでは，2MASS PSCについても ストアド関数を使ってらくらく位置検索 で示したようにストアド関数で位置検索が可能です．

  1. テーブル「twomass」について， ra=162.991850[deg]，dec=+03.792003[deg]，半径5.0[arcmin] のRadial Searchを fGetNearbyObjCel()を使って行なってください． 結果は，検索位置からの角度が小さいものが先頭になるように並び換え， テーブル「twomass」のカラムはすべて取得してください． なお，fGetNearbyObjCel()の第一引数は， 'twomass'とします．
  2. 1. の結果からさらに，twomassテーブルの カラム dist_opt が null ではないものだけを取り出してください． 確実に座標検索を先に行わせるため， FROM句でのサブクエリを使ってください．
    ☆ヒント

◎課題10◎

今度は，位置検索により2MASS PSCの天体をランダムに抽出してみましょう．

  1. generate_series()， random()， fGetNearestObjIDEq() を使って，サーチ半径 3 [arcmin] のランダムな座標検索により， 約100天体のobjidを求めてください．
    ヒント: Lambert cylindrical equal-area projection． Decの引数は，asin() を使って分布を調整する必要があります． ラジアンから度への変換はdegrees()関数を使います．
  2. 1. で作った SQL 文を利用して，ランダムに100個の twomassテーブルの全カラムを取得してください．
参考: 特定の座標範囲内のランダム抽出もちょっとした細工でできますね．

スクリプトからSQLを活用してみる

◎課題11◎

SDSS DR8で分光観測された天域に存在するすべての「あかり」IRC天体を， 次の手順で検索してみましょう．

SDSSの分光観測では，視野半径1.49[deg]の円形アルミ板プレートのターゲット位置に ファイバーを手で差し込み，これを 望遠鏡に取り付けて天体に向けます． このプレートでどの天域が観測されたかは， SkyServerの「sdssTileAll」というテーブルに格納されています． このテーブルの tile がプライマリキー， raCen，decCenがJ2000でのプレートの中心座標です．

  1. SDSS DR8の SQL Search で，テーブル「sdssTileAll」のプライマリキーと中心座標のみを， csv形式で取得してください．
  2. getakaribylist_utf8.php.gz( UTF8版, EUC-JP版) を ダウンロードして解凍し，エディタで開いてください．
    $ wget http://akebono.mtk.nao.ac.jp/adc2012sql/getakaribylist_utf8.php.gz
    このPHPスクリプトは，引数で指定された csv ファイルをオープンし， それを1行ずつ読み取りながら， AKARI-CASに対して SQL 文を投げ，その結果を表示するものです．
    スクリプトの先頭にある $cmd に，Radial Searchを行なうための 適切なSQL文をセットし， AKARI-CASからSDSS DR8で分光観測された天域にあるIRC天体を すべて取得してください．
    PHPスクリプトは次のように実行します． 
    $ php getakaribylist.php result.csv

参考: 史上最強の手続き言語，PHP のススメ

課題の回答例