手軽にデータベースを楽しもう、という企画です＾＾。
とってもクリーンなデータでした。これならダウンロードして自前のDBに入れるのは簡単です。
準備
０．Windows用の MySQL をダウンロードしてインストールします。
使うのは主に MySQL Query Browser です。
解説サイトが山ほどあるので割愛
１．DBを作ります
コマンドボックスにコマンド入力して；（セミコロン）打ったら、右にあるExecuteボタンで実行。
create database `TychoDB`;
２．デフォルトのDBを TychoDB にセットします
use `TychoDB`;
３．Table を作ります。
※ヒッパルコス星表のデータ型に合わせて、pmRA, pmDEのデータタイプを float(7,1) から float(8,2) へ変更
Tycho2 ダウンロードに入っていた ReadMe ファイルの中身をよくみてそのままテーブルを作りました。
以上全部コピペで動くはずです。
４．Load 用のファイルを用意します
tyc2.dat.00 などのファイルは、空白や｜（バーティカルバー）や位置などで区切られているので整形する必要があります。
また空白カラムなども存在するので、その部分を \N で置き換えます。
オリジナル： ３５カラムが色んな区切り方で存在
整形後
スクリプト言語で250万行処理するので1時間ほどかかりました。
できあがったファイル tyc2.txt は約 484MB 程度。
参考：整形用プログラム mkLoadfile.tcl
５．DBへデータを流し込みます
とってもクリーンなデータでした。これならダウンロードして自前のDBに入れるのは簡単です。
準備
０．Windows用の MySQL をダウンロードしてインストールします。
使うのは主に MySQL Query Browser です。
解説サイトが山ほどあるので割愛
１．DBを作ります
コマンドボックスにコマンド入力して；（セミコロン）打ったら、右にあるExecuteボタンで実行。
create database `TychoDB`;
２．デフォルトのDBを TychoDB にセットします
use `TychoDB`;
３．Table を作ります。
※ヒッパルコス星表のデータ型に合わせて、pmRA, pmDEのデータタイプを float(7,1) から float(8,2) へ変更
create table `tycho` (
`id` mediumint(8) unsigned NOT NULL auto_increment,
`TYC1` smallint(4) unsigned NOT NULL comment '[1,9537]+= TYC1 from TYC or GSC',
`TYC2` smallint(5) unsigned NOT NULL comment '[1,12121] TYC2 from TYC or GSC',
`TYC3` smallint(1) unsigned NOT NULL comment '[1,3] TYC3 from TYC',
`pflag` char(1) default NULL comment '[ PX] mean position flag',
`RAmdeg` float(12,8) default NULL comment '[]? Mean Right Asc, ICRS, epoch=J2000',
`DEmdeg` float(12,8) default NULL comment '[]? Mean Decl, ICRS, at epoch=J2000',
`pmRA` float(8,2) default NULL comment '? Proper motion in RA*cos(dec)',
`pmDE` float(8,2) default NULL comment '? Proper motion in Dec',
`e_RAmdeg` smallint(3) default NULL comment '[3,183]? s.e. RA*cos(dec),at mean epoch',
`e_DEmdeg` smallint(3) default NULL comment '[1,184]? s.e. of Dec at mean epoch',
`e_pmRA` float(4,1) default NULL comment '[0.2,11.5]? s.e. prop mot in RA*cos(dec)',
`e_pmDE` float(4,1) default NULL comment '[0.2,10.3]? s.e. of proper motion in Dec',
`EpRAm` float(7,2) default NULL comment '[1915.95,1992.53]? mean epoch of RA',
`EpDEm` float(7,2) default NULL comment '[1911.94,1992.01]? mean epoch of Dec',
`Num` smallint(2) default NULL comment '[2,36]? Number of positions used',
`q_RAmdeg` float(3,1) default NULL comment '[0.0,9.9]? Goodness of fit for mean RA',
`q_DEmdeg` float(3,1) default NULL comment '[0.0,9.9]? Goodness of fit for mean Dec',
`q_pmRA` float(3,1) default NULL comment '[0.0,9.9]? Goodness of fit for pmRA',
`q_pmDE` float(3,1) default NULL comment '[0.0,9.9]? Goodness of fit for pmDE',
`BTmag` float(6,3) default NULL comment '[2.183,16.581]? Tycho-2 BT magnitude',
`e_BTmag` float(5,3) default NULL comment '[0.014,1.977]? s.e. of BT',
`VTmag` float(6,3) default NULL comment '[1.905,15.193]? Tycho-2 VT magnitude',
`e_VTmag` float(5,3) default NULL comment '[0.009,1.468]? s.e. of VT',
`prox` smallint(3) default NULL comment '[3,999] proximity indicator',
`TYC` char(1) default NULL comment '[T] Tycho-1 star',
`HIP` mediumint(6) unsigned default NULL comment '[1,120404]? Hipparcos number',
`CCDM` char(3) default NULL comment 'CCDM component identifier for HIP stars',
`RAdeg` float(12,8) default NULL comment 'Observed Tycho-2 Right Ascension, ICRS',
`DEdeg` float(12,8) default NULL comment 'Observed Tycho-2 Declination, ICRS',
`EpRA-1990` float(4,2) default NULL comment '[0.81,2.13] epoch-1990 of RAdeg',
`EpDE-1990` float(4,2) default NULL comment '[0.72,2.36] epoch-1990 of DEdeg',
`e_RAdeg` float(5,1) default NULL comment 's.e.RA*cos(dec), of observed Tycho-2 RA',
`e_DEdeg` float(5,1) default NULL comment 's.e. of observed Tycho-2 Dec',
`posflg` char(1) default NULL comment '[ DP] type of Tycho-2 solution',
`corr` float(4,1) default NULL comment '[-1,1] correlation (RAdeg,DEdeg)',
PRIMARY KEY (`id`)
) comment='Tycho-2';
Tycho2 ダウンロードに入っていた ReadMe ファイルの中身をよくみてそのままテーブルを作りました。
以上全部コピペで動くはずです。
４．Load 用のファイルを用意します
tyc2.dat.00 などのファイルは、空白や｜（バーティカルバー）や位置などで区切られているので整形する必要があります。
また空白カラムなども存在するので、その部分を \N で置き換えます。
オリジナル： ３５カラムが色んな区切り方で存在
0001 00039 1| | 2.37141849| 2.30400355| 13.5| -13.5| 12| 13| 1.3| 1.2|1990.18|・・・・・
0001 00041 1|X| | | | | | | | | |・・・・・
10,1,39,1,\N,2.37141849,2.30400355,13.5,-13.5,12,13,1.3,1.2,1990.18,・・・・・
11,1,41,1,X,\N,\N,\N,\N,\N,\N,\N,\N,\N,・・・・・
スクリプト言語で250万行処理するので1時間ほどかかりました。
できあがったファイル tyc2.txt は約 484MB 程度。
参考：整形用プログラム mkLoadfile.tcl
#!/usr/bin/wish
set id 0
proc fout { f fdo } {
global id
set fdi [open $f r]
while {[gets $fdi line] != -1} {
incr id
set b [split $line |]
set t [split [lindex $b 0] " "]
scan [lindex $t 0] "%04d" t1
scan [lindex $t 1] "%05d" t2
scan [lindex $t 2] "%d" t3
set a {}
for {set i 1} {$i < 32} {incr i} {
if {$i == 23} {
set e1 [string trim [string range [lindex $b $i] 0 5]]
if {$e1 == {}} {
lappend a \\N
} else {
lappend a $e1
}
set e [string trim [string range [lindex $b $i] 6 end]]
} else {
set e [string trim [lindex $b $i]]
}
if {$e == {}} {
lappend a \\N
} else {
lappend a $e
}
}
set s [join [list $id $t1 $t2 $t3 [join $a ,]] ,]
puts $fdo $s
}
close $fdi
}
set fdo [open tyc2.txt w]
for {set i 0} {$i < 20} {incr i} {
fout [format "tyc2.dat.%02d" $i] $fdo
}
close $fdo
exit
５．DBへデータを流し込みます
load data local infile "d:\\Tycho2Origin\\tyc2.txt"
replace into table tycho fields terminated by ','
lines terminated by '\r\n';
これは2分程度で終わります。2539913 rows affected by the last command, と出ます。
これからどう使っていくか、パフォーマンス要件は何もまだ考えていないので、インデックスは張っていません。よく使う検索カラムにインデックスをつけるとめちゃくちゃ速くなります。
ただ、ここまで準備すれば、あとは自由にデータ加工して取り出せます。
例：
select * from tycho where HIP='429';
→ ヒッパルコス番号４２９のデータを検索
select * from tycho where abs(pmRA) > '100.0' order by pmRA desc limit 10;
→ 赤経方向の固有運動が１00ミリ秒角以上のものを降順で10件表示
※これ、バーナード星が出るかと思ったんですが、2番目に速いカプタイン星が出ました。
バーナード星は pmDE の絶対値が大きいため、この条件では出ませんでした。
赤色矮星カプタイン星はハビタブルゾーンにスーパーアースが今年発見されていて年齢115億歳だそうです。超文明が存在するかも！
今日はここまで。
補完用データも整形が必要なので今後実施予定。
テーブルの定義間違いなどのときは、drop table tycho; として一旦全部削除したほうが早いです。エクセルでも同様のことができると思いますが、なんといっても250万件くらいのデータならサクサク扱えるところが魅力です。
これは2分程度で終わります。2539913 rows affected by the last command, と出ます。
これからどう使っていくか、パフォーマンス要件は何もまだ考えていないので、インデックスは張っていません。よく使う検索カラムにインデックスをつけるとめちゃくちゃ速くなります。
ただ、ここまで準備すれば、あとは自由にデータ加工して取り出せます。
例：
select * from tycho where HIP='429';
→ ヒッパルコス番号４２９のデータを検索
select * from tycho where abs(pmRA) > '100.0' order by pmRA desc limit 10;
→ 赤経方向の固有運動が１00ミリ秒角以上のものを降順で10件表示
※これ、バーナード星が出るかと思ったんですが、2番目に速いカプタイン星が出ました。
バーナード星は pmDE の絶対値が大きいため、この条件では出ませんでした。
赤色矮星カプタイン星はハビタブルゾーンにスーパーアースが今年発見されていて年齢115億歳だそうです。超文明が存在するかも！
今日はここまで。
補完用データも整形が必要なので今後実施予定。
テーブルの定義間違いなどのときは、drop table tycho; として一旦全部削除したほうが早いです。エクセルでも同様のことができると思いますが、なんといっても250万件くらいのデータならサクサク扱えるところが魅力です。