情報システム基礎II(スクーリング後半)

情報システム基礎IIのスクーリング(後半)に参加しました。

後半はほとんどの時間を制作に費やしていたため、あまり書くことはないのですが、作品を作る上で気づいた点や、作品自体についてかんたんにまとめておきます。

SQLite

GUIでの操作

SQLiteには公式ではCUIコマンドライン)での操作しかありませんが、SQLiteに対応したGUI環境があります。

sqlitebrowser.org

新しいテーブルを作ったり、テーブルの中を参照するのはGUIのほうが見やすいかもしれません。SQLも実行できるので、SQLの検証も可能です。

f:id:ArtificialArts:20190923110824p:plain

カラム名の表示

SELECT文でデータを取り出した際、標準では取り出したデータしか表示されませんが、ヘッダをONにすることで、カラム名を表示することができます。

.headers on

f:id:ArtificialArts:20190923095732p:plain

CSVの取り込み

SQLiteでは標準の機能でCSVデータの取り込みを行えますが、取り込む上で注意する点があります。

  • 区切り文字

SQLiteでは立ち上げたとき、区切り文字が|になっています。この状態でカンマ区切りのCSVを読み込むと正常に取り込めません。

現在の区切り文字の設定は.showで確認することができます。colseparator: "|"と書かれているところが現在の区切り文字です。

f:id:ArtificialArts:20190911220914p:plain

.separatorのあとに区切り文字を指定することで区切り文字を切り替えられます。カンマ区切りの場合は.separator ,です。

f:id:ArtificialArts:20190911221057p:plain

この設定後にカンマ区切りのCSVを読み込むと正常に読み込めます。

特にWindows環境では気をつける必要があるようなのですが、SQLiteで取り込めるCSV文字コードUTF-8である必要があります。もし文字化けしてしまう場合は、メモ帳でCSVを開き、名前を付けて保存でUTF-8を選んで保存することで正常に読み込めるようになります。

f:id:ArtificialArts:20190911221600p:plain

  • ファイルパスの指定

データベースファイルを開くときは問題ありませんが、取り込むCSVを指定するとき(SQLiteでパスを指定するとき)、ファイルをドラッグしてパスを表示すると、ディレクトリの区切りはバックスラッシュ(環境によって¥)になります。(Windows環境)

C:\xxx\xxx\xxx\xxx.csv

この内容で実行するとファイルが開けないエラーが発生します。

Error: cannot open "ファイル名"

SQLiteでは、ディレクトリの区切りはバックスラッシュではなくスラッシュにする必要があります。

C:/xxx/xxx/xxx/xxx.csv

こうすることで正常に開くことができます。

SQLの検証

SQLを書いて実行する際に、うまくいかないときに気にする点があります。

  • 記号が自動変換されている

テキストエディットでSQLを下書きしてから実行したところ、どうしてもエラーが取れない事がありました。(Macでの実行時)よく見ると、文字列を括っているシングルクォーテーションやダブルクォーテーションがいつもと違う形になっていました。スマート引用符・スマートダッシュという機能が原因で、文法が正しかったとしてもエラーが発生します。

discussionsjapan.apple.com

環境設定でキーボードの中にある、スマート引用符とスマートダッシュのチェックを外すことで解除できます。

また、アプリケーションによってはアプリケーション側の設定にもあるようなのでそちらも外す必要があります。(テキストエディットには設定があります)

そのため、SQLを別のところで下書きしてからコピー・貼り付けで実行しようとする際には注意が必要です。

SQLのメモ

COUNTの条件指定

ある条件に当てはまる件数を取得するとき、SQLは下記のようになります。

SELECT COUNT(カラム名) FROM テーブル名 WHERE カラム名 =条件

1つの条件のみ検索する場合はWHEREで問題ないのですが、条件を変えて件数を取得したい場合、その条件の数だけSQLを実行する必要があります。

カウントを1度に行いたい場合、COUNTの中に条件を書きます。

SELECT count(カラム名 = 条件 OR NULL) FROM テーブル名;

このとき、条件の中にOR NULLを付けないと正しく集計されません。

複数条件を指定したい場合には下記のようにします。

SELECT count(カラム名 = 条件 AND カラム名 = 条件 OR NULL) FROM テーブル名;

例えば対象が数値の場合、下記のように範囲指定ができます。

SELECT count(カラム名 > 25 AND カラム名 <= 50 OR NULL) FROM テーブル名;

こうすれば、条件を変えた集計を行いたい場合にcount関数ごとに条件を指定すれば一度に取得することができます。

SELECT count(カラム名 = 条件 OR NULL), count(カラム名 > 25 AND カラム名 <= 50 OR NULL) FROM テーブル名;
  • SQLiteで使用できる関数

SQLiteでは、標準でいくつかの関数が使用できます。

  • 集計関数(Aggregate Functions)
  • 日付・時刻関数(Date And Time Functions)
  • コア関数(Core Functions)
  • Window Functions
  • JSON Functions

www.sqlite.org

ただ、計算に関しては加減乗除と余りの数の算出(%)くらいしかありません。

作品の説明

今回行ったことは、ジャンルごとの店舗間の距離の可視化です。

街中を歩いていると、居酒屋は隣同士で並んでいたり、同じビルに何店舗も入っていたりしますが、和食や中華料理の店舗は近くにあるイメージは少ないかと思います。

その印象が正しいのかを確かめるために店舗間の距離を計算し、可視化しました。

距離の計算

店舗の座標は緯度と経度のデータがありますが、単純に2点間の距離を計算すると、直線で繋いだ距離となります。今回は短い距離なのでそれでも良いのですが、地球は球体なので、少し誤差が出てしまいますし、距離が長くなるほど誤差も大きくなっていきます。

そこで、色々と調べてプログラムで実現可能だったヒュベニの公式という方法を使って距離を計算しました。

下記JavaScriptのプログラムを参考に、Processing用に書き換えています。

tech-blog.s-yoshiki.com

float hubeny(float latA, float lonA, float latB, float lonB)
{
    //degree to radian
    float lat1 = radians(latA);
    float lng1 = radians(lonA);
    float lat2 = radians(latB);
    float lng2 = radians(lonB);

    // 緯度差
    float latDiff = lat1 - lat2;
    // 経度差算
    float lngDiff = lng1 - lng2;
    // 平均緯度
    float latAvg = (lat1 + lat2) / 2.0;
    // 赤道半径
    float a = 6378137.0;
    // 極半径
    float b = 6356752.314140356;
    // 第一離心率^2
    float e2 = 0.00669438002301188;
    // 赤道上の子午線曲率半径
    float a1e2 = 6335439.32708317;

    float sinLat = sin(latAvg);
    float W2 = 1.0 - e2 * (sinLat * sinLat);

    // 子午線曲率半径M
    float M = a1e2 / (sqrt(W2) * W2);
    // 卯酉線曲率半径
    float N = a / sqrt(W2);

    float t1 = M * latDiff;
    float t2 = N * cos(latAvg) * lngDiff;
    return sqrt((t1 * t1) + (t2 * t2));
}

後で気づいたのですが、極半径bが使われていないので、何かしら間違いがある可能性があります。

ただ、地理院の距離計算サイトでの計算結果と比べると、誤差は1m以下でしたので問題ないと判断しました。

測量計算(距離と方位角の計算)

作品の外観

1画面で特定の駅、特定のジャンルを表しています。

f:id:ArtificialArts:20190923101143p:plain

横軸が店舗間の距離を表していて、左に行くほど、店舗間距離が短くなり、右に行くほど長くなります。

縦軸は、該当の店舗間距離のお店の割合です。

画像のスケールでは1点が5mなので、一番左は店舗間の距離が5m以下のお店の割合、一番右は2km以上離れているお店の割合です。

f:id:ArtificialArts:20190923101148p:plain

すべての駅やすべてのジャンルといった表示もできます。

また、上下左右で駅やジャンルを切り替え、Shiftを押しながら上下左右でスケールを切り替えるので、今どの設定になっているか分かりづらいため、下記画像のように切り替えたときだけ場所を表示しています。

f:id:ArtificialArts:20190923101151p:plainf:id:ArtificialArts:20190923101155p:plain


データの表現方法は人それぞれ異なり、講評で作品を見ていて楽しかったです。今回はいませんでしたが、3Dによる表現を行う人も過去にはいたようです。また、APIを使って他のサイトからデータを取り出している人もいました。

難しい方法を使えば良いということではなく、データに合った表現方法を見つけるというのは難しいですが、楽しみながらアイディアを考えました。