補足記事（2016年1月2日）: xxdコマンドを使う場合
Contents


実行環境



出力用サンプルデータ: PostGISラスタをPNGに変換したもの

bytea型（バイナリ列データ型）なら何でもいいですが、ちょうど先週PostGISをインストールした時にPNG形式をクエリ上で作成したので、それを使います。psqlで示すと ↓ こんな感じ。
-- 出力結果をデータだけにする
\pset tuples_only on
\pset format unaligned

-- 適当なジオメトリ → ラスタ → PNG
select st_asPNG(
    st_asRaster(
        st_buffer(
            st_point(1, 5), 10
        ), 150, 150, '8BUI'
    )
);

この出力をそのままファイルに保存してもテキストになってしまうので、何か工夫してPNGにします。


いくつか方法はあるけど、今回はCOPY ... FORMAT BINARYの結果をトリム

PostgreSQLのbytea型をテキストでなくバイナリのままファイル出力する方法は、標準で備わってないため若干面倒ですが、次のようにいくつかあります。



StackOverflowで3番目の方法（hex→bin）のプログラムを示した人は、4番目の方法についてCOPY has a binary format with a variable length header, not very easy to "trim". と。ヘッダのサイズが可変なので取り除くのが簡単でないというわけですが、今回試した範囲では常に先頭25バイト分がヘッダだったので、この方法をやってみることにしました。

COPYコマンドにヘッダを出力しないオプション（HEADER FALSE）があるものの、FORMAT BINARYでは無視され常にヘッダ付きになるっぽい。StackOverflow - How to export binary file with psql (without PGCOPY header)?のやり取りを参照。


COPY ... FORMAT BINARYの結果をバイナリエディタで開き、先頭を削る

COPYコマンドのクエリは下のとおり。PostGISで適当なジオメトリ（ある点からの円状のバッファ）を作成 → 縦横それぞれ150ピクセル・8ビットのラスタに変換 →PNGに変換してbytea型データを得る、というもの。
copy (
    -- 適当なジオメトリ → ラスタ → PNG
    select st_asPNG(
        st_asRaster(
            st_buffer(
                st_point(1, 5), 10
            ), 150, 150, '8BUI'
        )
    )
) to 'R:/tmp.bin' (format binary);


出力されたtmp.binをバイナリエディタのStirlingで開き ↓ 先頭のヘッダ部分を確認。普通PNGは89 50から始まるので、先頭25バイト分（画像の黒い所）がヘッダのようです。


もう一つサイズの大きいバイナリを作成し、ヘッダ範囲が異なるか確認。先ほどの「縦横150ピクセル・8ビット」を、今度は縦横1500ピクセル・16ビットにしました。クエリのうちST_AsRaster()の引数を変えます。
select st_asPNG(
    st_asRaster(
        st_buffer(
            st_point(1, 5), 10
        ), 1500, 1500, '16BUI' -- ここだけ変更
    )
)

↓ 両者をStirlingで比較すると、ヘッダ最後あたりの値が違いますが範囲は25バイト分で同じ。この他JPEGでも試しましたがヘッダ範囲が異なるケースは見当たらないので、今回は先頭25バイト固定で削ることに決定。


Stirlingで先頭25バイト分を削って別名で保存すると ↓ちゃんとPNGファイルになりました。下の画像2つ目が、エクスプローラで各ファイルをサムネイル表示したもの。


参考まで、各ファイルをzipにまとめて置いておきます。拡張子binがCOPY ... FORMAT BINARYでの出力、拡張子pngが先頭25バイトを削ったもの。


ddコマンドのWindows版を使い、コンソールでバイナリファイルの先頭を削る

前項のバイナリエディタの作業を、今度はコンソールのコマンド実行に置き換えます。そうすればpsqlや、PostgreSQL 9.3で追加されたCOPY ... TO PROGRAMと連携できて便利。ただ問題は、Windowsのコンソールでバイナリの部分切り出しができるかどうかです。

ウェブ検索するとdebugというコマンドの情報があったものの、自分のWindows 7 64bitではファイル自体が見つからず。一方Linuxでは、下記のとおりddコマンドで簡単にできることが分かりました。

dd if=入力ファイル bs=1 skip=先頭の削るバイト数 of=出力ファイル


次に、Windowsに移植されたUnix/Linux環境からddコマンドを含むものを探すと、最新のGit Portableにありました。下記からGitPortable_2.6.2_Development_Test_1.paf.exeをダウンロードし実行。指定したフォルダ下に全ファイルが展開され、指定フォルダ/App/Git/usr/bin/dd.exeがそのパス。


↓ 先頭25バイト分を削るコマンド実行の様子と結果。当然ながら先ほどバイナリエディタでやったのと同じPNGファイルが得られたので、次項でPostgreSQLのクエリと一体化します。
:: 対象ファイル確認
$ dir *.bin

:: ddコマンドのパスを環境変数に入れる
$ set dd=D:\AppsPortable\GitPortable\2.6.2\App\Git\usr\bin\dd

:: バイナリ先頭のヘッダを削る
$ %dd% if=tmp.bin bs=1 skip=25 of=tmp.bin.dd.png
$ %dd% if=tmp_large.bin bs=1 skip=25 of=tmp_large.bin.dd.png


完成形はクエリ一つ、COPY ... TO PROGRAM 'dd ...' (FORMAT BINARY)

今回必要な2つの手順、「psqlでのヘッダ付バイナリ出力」と「ddでのヘッダ削除」を別々に実行する場合は ↓ このように一時ファイルを経由します。
-- on psql
copy (
    bytea型を一行だけ返すクエリ
) to 一時ファイル (format binary);

-- on console
dd if=一時ファイル bs=1 skip=25 of=出力ファイル


ところでPostgreSQL 9.3で追加されたCOPY ... TO PROGRAMという構文を使えば、COPYの出力をそのまま外部コマンドに渡せるので一時ファイルが不要、かつクエリ一つ ↓ で済みます。
copy (
    bytea型を一行だけ返すクエリ
) to program 'dd bs=1 skip=25 of=出力ファイル' (format binary);


↓ 実際にやってみた様子。ユーザはpostgres、PostgreSQLサーバを起動したWindowsユーザの種別は管理者権限ありです。ユーザ権限とパスの制約がいろいろあると思うので、環境によっては動かないかも。
copy (
    select st_asPNG(
        st_asRaster(
            st_buffer(
                st_point(1, 5), 10
            ), 150, 150, '8BUI'
        )
    )
) to program
'"D:/AppsPortable/GitPortable/2.6.2/App/Git/usr/bin/dd" bs=1 skip=25 of="R:/st_aspng_test.png"'
(format binary);

実際に出力されたPNGは ↓ こちら。といっても先ほどバイナリエディタで手動で作ったのと同じです。


今後の課題: COPY ... FORMAT BINARYのヘッダサイズが変わるケース

ドキュメントのCOPYコマンドの項には、バイナリフォーマットに付くヘッダが固定部分（15バイト）と可変部分から成るとあり、その意味では確かに、前半で紹介したStackOverflowでnot very easy to "trim".と言われている通り。ただ可変部分の決まり方がまだよく分からず、今回試した範囲では常に10バイトで（ヘッダ全体で25バイト）、引き続き調べます。


関連リンクまとめ



来年もよろしくお願いしますm(_)m

今年5月からこちらに移転&衣替えしたものの、相変わらず自分しか使わないような内容が多いなぁ…。個人メモとしては十分役立っている一方、もう少し人々の参考になるような記事も増やしたいところ。いろいろ忙しくて以前のように毎日更新はできませんが、来年もよろしくお願いします。m(_)m