FOSS4G Advent Calendar 2015 二個目の記事での，2回目です。
1回目はこちら -> 初めてのCartoDB その1: shp と csv を読み込む

CartoDB と PostGIS

CartoDB は PostgreSQLというデータベース管理システムを基礎につくられています。
その PostgreSQL の空間情報拡張機能が PostGIS で，大きな空間情報に対して頑健な処理ができる点は DB ならではです。
また，SQL による再利用可能な処理の記述も魅力的です。

PostGIS の壁

しかし，PostGIS のインストールは OS によっては少し大変で，本来サーバの役割をするものなのでデータもその端末に縛られがちです。
また，描画機能がないため，QGIS 等の他のツールが必要になります。

CartoDB で始めよう

CartoDB であれば，webサービスを使えばインストールに悩む必要はなく，いろんな端末からアクセス可能です。
また，CSS で管理できる描画はとても洗練されています。
なので，CartoDB は PostGIS の API としてもおすすめなのです。

前回のあらすじ

埼玉県の行政区域の shp といきものログのスズメ報告の csv を CartoDB に読み込みました。
また，テーブル結合の機能を使い，市区町村名で属性結合しました。
その際，行政区域データが区名だけなのに対し，スズメデータは市＋区名で表現されているため，一部がうまく結合されず別の行になってしまいました。
そこで「その2」では，PostGISの機能を駆使して，データを思い通りに結合して，データの視覚化を行ってみます。CartoDBを使えば，誰でもデータを視覚化できる側面と，PostGISの機能を駆使した高度なデータ解析と視覚化もできることがわかっていただければ嬉しいです。

SQL によるテーブル結合

行政区域データを選択し，サイドバーの [SQL] ボタンを開くと，

SELECT * FROM n03_15_11_150101

と入力されています。
* はすべての列を表すので，この SQL は「行政区域データからすべての列を選択しなさい」という意味になります。
この文章を編集しながら，目的のデータを抽出していきましょう。

まずは，前回と同じ結果を再現します。

SELECT * FROM n03_15_11_150101 FULL JOIN ikilog_suzume_saitama_utf8 ON n03_004 = county

これは，「n03_004 と county が一致するものは行政区域データとスズメデータを結合し，すべての列を選択しなさい」という意味です¹。
Apply query をクリックし，実行してみると…

エラーになりました（笑）
cartodb_id という列が両方にあって，特定できないのが原因です。
では，必要な列の名前を列記して，抽出することにします。

1列目の cartodb_id は CartoDB がつけたシリアルNo です。
2列目の the_geom には，Polygon の位置と形（ジオメトリ）が格納されています。
この列を指定することで，空間情報を選択することができます。
このとき，隠された the_geom_webmercator という列も一緒に指定してください²。
位置情報を持たないスズメデータは the_geom 列が null になっているので，選択する必要はありません。
両方に同じ名前がある場合は，テーブル名.列名で指定が可能です。長いときはエイリアスを使いましょう。

SELECT
    t1.the_geom, t1.the_geom_webmercator, n03_003, n03_004, 
    registrantID, year, month, day, eventRemarks, stateProvince, 
    county, individualCount
FROM n03_15_11_150101 t1 FULL JOIN ikilog_suzume_saitama_utf8 t2
ON n03_004 = county

今度は実行でき，前回のテーブル結合と同じ結果が得られました。
しかし，まだ市＋区のデータが結合していないので，SQL の編集を続けます。

スズメデータの county と同じにするため，行政区域データの n03_003 と n03_004 を || で連結させます。
また，n03_003 が null だと，結合後も null になってしまうため，coalesce で，null のときは '' に置換します。
今回は市区町村での比較をしたいので，INNER JOIN を使い county が空白な県レベルのデータ等，一致しないデータは除外しましょう。

SELECT
    t1.the_geom, t1.the_geom_webmercator, n03_003, n03_004, 
    registrantID, year, month, day, eventRemarks, stateProvince, 
    county, individualCount
FROM n03_15_11_150101 t1 INNER JOIN ikilog_suzume_saitama_utf8 t2
ON coalesce(n03_003, '') || n03_004 = county

今度はうまくできました。
ところどころ，スズメ報告のない市区町村もありますが，全体的な傾向として人口の多そうな地域での報告数が多い結果となりました。

中央の create dataset from query という文字をクリックすると，この結果を新しいデータとして保存できます。

SQL による集計

先ほどは，市区町村ごとのスズメの報告数を比較しましたが，このスズメデータには個体数の情報（individualcount）も記録されています。
今度は，この個体数を合計して比較したいと思います。

SELECT
    t1.the_geom, t1.the_geom_webmercator,  county, 
    sum(individualCount) as sum_of_individualcount
FROM n03_15_11_150101 t1 INNER JOIN ikilog_suzume_saitama_utf8 t2
ON coalesce(n03_003, '') || n03_004 = county
GROUP BY t1.the_geom, t1.the_geom_webmercator,  county

この値を，コロプレスやバブルに反映させて，可視化してみましょう。

さいたま市緑区と川口市における，スズメの報告個体数の合計が 438個体，405個体と県の中で多い結果になりました。
この2地域の調査努力量が多い可能性も考えられますが，もしかすると本当にスズメが多いのかもしれません。

おわりに

今回ご紹介したように，データの整形や集計を SQL で記述しておくことで，今後データが更新されたときも再度実行するだけでよく，二度手間を防ぐことができます（＝再利用可能）。
また，処理の途中の中間ファイルがあふれて，整理が大変になることもありません。

CartoDB によって，GIS に詳しくない人も含めた多くの人が本来悩むべきでないこと³から抜け出し，それぞれの課題の本質だけに集中できるようになる日は近いです♪

参考

地図DB：CartoDBに関するもっと詳細な情報を掲載しています。

JOIN には種類があって，この FULL JOIN は一致しないものは一方の列が空の状態で出力されます。一方，INNER JOIN とすると，一致するものだけが出力されます。また，LEFT JOIN は Excelの vlookup のように，左の表を基準に結合し，右の表に一致するものがなくても左の表はすべて出力されます。 ↩
CartoDB では，いろいろな座標系のデータを webメルカトルに変換して揃え，これを MAP VIEW で表示しているためです。 ↩
座標系をわかってもらえない，どれが最終結果かわからなくなった，プロジェクトファイルの共有どうしよう等 ↩

初めてのCartoDB その2: PostGIS を操る