QGIS – 列の一括計算～フィールド計算機

2016-11-06 / tau / コメントする

概要

たとえば都道府県レイヤの属性データが次のようだとする。

	都道府県名	市区町村コード
1	北海道	01101
2	青森県	02201
3	岩手県	03201
・・・	・・・	・・・

このデータは、実際に国土数値情報の行政界データを都道府県名で融合させた例で、元の市区町村名などのフィールドは削除している。

ここで都道府県コードを各都道府県に与えたいが、データとしては市区町村コードの形で残っているので、この先頭2文字に”000″を付けた5桁のコードを新たな都道府県コードのフィールドとして生成したい。

そこでこの操作を、フィールド操作のフィールド計算機で行う。

手順

属性テーブルの操作の手順で、フィールド操作のペインを表示させる
編集ボタンを押して編集モードに入る
フィールド計算機ボタンを押して計算機のダイアログを表示させる
新しいフィールドを作るか、既存フィールドを更新するかを選択
- 新しいフィールドを作る場合は、フィールド名やタイプ、フィールド長を入力
- 今回は新しいフィールド”N03_000″に5桁固定の半角数字のテキストで定義
エディタを使ってフィールド計算の内容を記述
- 今回は市区町村コードのフィールド”N03_007″の左から2文字を取り出し、それに”000″を付け足す
- 文字列の左から取り出す関数はleft()
- 文字列の結合は||ボタンで入力
- 文字列はダブルクォート(“)で囲むとエラーになり、シングルクォートで通る(‘)
OKボタンを押す(この時点でまだフィールド計算はされない)
編集ボタンを押して編集モードを解除
- このときに「変更を保存するか」尋ねられるので、保存すれば計算が実行される

以下はフィールド計算機の入力の様子。

qgis-field-calculator-dialog

実行結果は以下のようになる。

	都道府県名	市区町村コード	N03_000
1	北海道	01101	01000
2	青森県	02201	02000
3	岩手県	03201	03000
・・・	・・・	・・・	・・・

QGIS – 簡素化

2016-11-06 / tau / 1件のコメント

概要

簡素化(Simplify)は、ポリゴン境界の点を間引く。簡素化によって、データ容量は削減、形状は荒くなり、ポリゴン間に隙間・重なりが生じてくる。

たとえば九十九里浜のデータをオリジナルと簡素化後で比較すると以下のようになるが、相当数のポイントが削除されていることがわかる。

qgis-simplification-original

qgis-simplification-simple

手順

メニューの「ベクタ」→「ジオメトリツール」→「ジオメトリの簡素化」でダイアログが開く。

qgis-simplify-dialog1

“入力レイヤ”で簡素化したいレイヤを選択
“許容範囲”の数値を指定(後段で説明)
“Simplified”は簡素化後の出力先／Shapeファイルの場合はshpファイル名で指定する
“アルゴリズム実行後に・・・”は実行終了後にレイヤを表示させたいときにチェック
Runボタンで実行

融合(Dissolve)処理に比べて処理は早く、2～3分程度。

許容範囲の値と容量・精度

許容範囲の数値は簡素化の程度に相当し、数値が大きいほど多くの点が間引かれる。許容範囲が大きすぎると、ポリゴン境界が接しなくなる。

許容範囲	shpファイルサイズ	境界切れ
(元ファイル)	220,131KB	なし
0.000001	176,010KB	なし
0.00001	104,925KB	なし
0.00005	47,158KB	僅か
0.0001	33,165KB	僅か
0.001	13,830KB	発生

たとえば許容範囲0.001の場合、以下のように市区町村間に隙間ができる。

qgis-simplification-disorder

許容範囲をより小さくすると境界線のずれは見た目ではわからないが、よく探すと細かいところでずれている。たとえば以下は京都府木津川市内の笠置町の飛地で、許容値0.00005の場合。

qgis-simplification-disorder2

同じ場所で許容値0.0001にした場合。

qgis-simplification-disorder3

このレベルになるとズレの規模が数十メートル程度なので、全国のデータを扱うときにはこの程度の簡素化をすることで、かなりの容量と処理時間の低減に結び付く。

簡素化の効果

簡素化によってポイントの数が減ることから、shpファイルの容量の削減、処理速度の向上に繋がる。

たとえば国土数値情報の行政界データについて、オリジナルのデータと許容値0.0001で簡素化したデータを比較すると、ファイル容量で220,131KB→33,165KBと190MB、85%の減少。

また時間のかかる融合(Dissolve)処理についても、オリジナルデータで何時間もかかっていたのが、簡素化後では6分程度で終了するという大きな効果。

したがって、全国の市町村データなどを扱うような場合には簡素化データ、特定の県・市町村の場合にはオリジナルデータといったデータの使い分けが重要。

MySQL – テーブル名のエイリアス

2016-11-04 / tau / コメントする

エイリアスの縛り

以下のようなテーブルを考える。

mysql> SELECT * FROM test;
+-------+------+--------+
| name  | age  | nation |
+-------+------+--------+
| John  |   35 | US     |
| Alex  |   40 | NZ     |
| Luice |   29 | US     |
| Alice |   23 | UK     |
| Sala  |   25 | JP     |
+-------+------+--------+
5 rows in set (0.00 sec)

mysql>

mysql> SELECT * FROM test;

+-------+------+--------+

| name | age | nation |

+-------+------+--------+

| John | 35 | US |

| Alex | 40 | NZ |

| Luice | 29 | US |

| Alice | 23 | UK |

| Sala | 25 | JP |

+-------+------+--------+

5 rows in set (0.00 sec)

mysql>

ここでnation=’US’のレコードのみ抽出するのに、WHERE句のフィールド名に敢えてテーブル名を付ける。

mysql> SELECT name, nation FROM test WHERE test.nation='US';
+-------+--------+
| name  | nation |
+-------+--------+
| John  | US     |
| Luice | US     |
+-------+--------+
2 rows in set (0.00 sec)

mysql>

mysql> SELECT name, nation FROM test WHERE test.nation='US';

+-------+--------+

| name | nation |

+-------+--------+

| John | US |

| Luice | US |

+-------+--------+

2 rows in set (0.00 sec)

mysql>

ここでFROM句でテーブルのエイリアスを指定し、WHERE句とSELECT句ではフルネームのテーブル名を使うとエラーが発生。

mysql> SELECT name, nation FROM test t WHERE test.nation='US';
ERROR 1054 (42S22): Unknown column 'test.nation' in 'where clause'
mysql>

mysql> SELECT name, nation FROM test t WHERE test.nation='US';

ERROR 1054 (42S22): Unknown column 'test.nation' in 'where clause'

mysql>

そこでWHERE句のテーブル名をエイリアスに変えると、正常に動作。

mysql> SELECT name, nation FROM test t WHERE t.nation='US';
+-------+--------+
| name  | nation |
+-------+--------+
| John  | US     |
| Luice | US     |
+-------+--------+
2 rows in set (0.00 sec)

mysql>

mysql> SELECT name, nation FROM test t WHERE t.nation='US';

+-------+--------+

| name | nation |

+-------+--------+

| John | US |

| Luice | US |

+-------+--------+

2 rows in set (0.00 sec)

mysql>

さらにSELECT句のフィールド名もフルネームのテーブル名を付けるとエラーになって・・・

mysql> SELECT test.name, test.nation FROM test t WHERE t.nation='US';
ERROR 1054 (42S22): Unknown column 'test.name' in 'field list'
mysql>

mysql> SELECT test.name, test.nation FROM test t WHERE t.nation='US';

ERROR 1054 (42S22): Unknown column 'test.name' in 'field list'

mysql>

ここもエイリアスにすると正常に動作する。

mysql> SELECT t.name, t.nation FROM test t WHERE t.nation='US';
+-------+--------+
| name  | nation |
+-------+--------+
| John  | US     |
| Luice | US     |
+-------+--------+
2 rows in set (0.00 sec)

mysql>

mysql> SELECT t.name, t.nation FROM test t WHERE t.nation='US';

+-------+--------+

| name | nation |

+-------+--------+

| John | US |

| Luice | US |

+-------+--------+

2 rows in set (0.00 sec)

mysql>

これらから、以下のことが分かる。

FROM句でテーブル名に別名を付けると、元のテーブル名はオーバーライドされて使えなくなる
FROM句で付けたエイリアスは、WHERE句、SELECT句に波及する。

この意味で、エイリアス、別名という呼び方よりは、「テーブル名のオーバーライド」くらいがいいのではないかと思った。

INNER JOINでのエイリアスの使い方

次のような小売側・問屋側の2つのテーブルを準備。

mysql> select * from shop;
+--------------+-----------+
| product      | num_order |
+--------------+-----------+
| きゅうり     |        20 |
| リンゴ       |        35 |
| 大根         |        20 |
| バナナ       |        30 |
| みかん       |        50 |
| 玉ねぎ       |        25 |
+--------------+-----------+
6 rows in set (0.11 sec)

mysql> select * from wholesale;
+-----------+-------+
| product   | price |
+-----------+-------+
| リンゴ    |   150 |
| バナナ    |   300 |
| みかん    |   200 |
| ぶどう    |   500 |
| いちご    |   250 |
+-----------+-------+
5 rows in set (0.00 sec)

mysql>

mysql> select * from shop;

+--------------+-----------+

| product | num_order |

+--------------+-----------+

| きゅうり | 20 |

| リンゴ | 35 |

| 大根 | 20 |

| バナナ | 30 |

| みかん | 50 |

| 玉ねぎ | 25 |

+--------------+-----------+

6 rows in set (0.11 sec)

mysql> select * from wholesale;

+-----------+-------+

| product | price |

+-----------+-------+

| リンゴ | 150 |

| バナナ | 300 |

| みかん | 200 |

| ぶどう | 500 |

| いちご | 250 |

+-----------+-------+

5 rows in set (0.00 sec)

mysql>

この2つのテーブルを結合して、果物の取引のみを抽出する。

mysql> SELECT shop.product, num_order, price
    -> FROM shop, wholesale
    -> WHERE shop.product = wholesale.product;
+-----------+-----------+-------+
| product   | num_order | price |
+-----------+-----------+-------+
| リンゴ    |        35 |   150 |
| バナナ    |        30 |   300 |
| みかん    |        50 |   200 |
+-----------+-----------+-------+
3 rows in set (0.01 sec)

mysql>

mysql> SELECT shop.product, num_order, price

-> FROM shop, wholesale

-> WHERE shop.product = wholesale.product;

+-----------+-----------+-------+

| product | num_order | price |

+-----------+-----------+-------+

| リンゴ | 35 | 150 |

| バナナ | 30 | 300 |

| みかん | 50 | 200 |

+-----------+-----------+-------+

3 rows in set (0.01 sec)

mysql>

まずwholesaleテーブルのエイリアスを定義するのに、WHERE句のテーブル名もエイリアスに変更して

mysql> SELECT shop.product, num_order, price
    -> FROM shop, wholesale w
    -> WHERE shop.product = w.product;
+-----------+-----------+-------+
| product   | num_order | price |
+-----------+-----------+-------+
| リンゴ    |        35 |   150 |
| バナナ    |        30 |   300 |
| みかん    |        50 |   200 |
+-----------+-----------+-------+
3 rows in set (0.00 sec)

mysql>

mysql> SELECT shop.product, num_order, price

-> FROM shop, wholesale w

-> WHERE shop.product = w.product;

+-----------+-----------+-------+

| product | num_order | price |

+-----------+-----------+-------+

| リンゴ | 35 | 150 |

| バナナ | 30 | 300 |

| みかん | 50 | 200 |

+-----------+-----------+-------+

3 rows in set (0.00 sec)

mysql>

またshopテーブルのエイリアスを定義するなら、SELECT句で使われているテーブル名まで全て変更しなければならない。

mysql> SELECT s.product, num_order, price
    -> FROM shop s, wholesale w
    -> WHERE s.product = w.product;
+-----------+-----------+-------+
| product   | num_order | price |
+-----------+-----------+-------+
| リンゴ    |        35 |   150 |
| バナナ    |        30 |   300 |
| みかん    |        50 |   200 |
+-----------+-----------+-------+
3 rows in set (0.00 sec)

mysql>

mysql> SELECT s.product, num_order, price

-> FROM shop s, wholesale w

-> WHERE s.product = w.product;

+-----------+-----------+-------+

| product | num_order | price |

+-----------+-----------+-------+

| リンゴ | 35 | 150 |

| バナナ | 30 | 300 |

| みかん | 50 | 200 |

+-----------+-----------+-------+

3 rows in set (0.00 sec)

mysql>

MySQL – 主なコマンド

2016-11-04 / tau / コメントする

データベース操作

データベース一覧の表示: SHOW DATABASES;
データベースへの接続: USE [database]

テーブル操作

テーブル全体の操作

テーブル一覧の表示: SHOW TABLES;
テーブルの作成: CREATE TABLE [table]([column] [type], [column] [type], ...);
テーブルの複製: CREATE TABLE [dest_DB].[new_table] SELECT * FROM [source_DB].[source_table]
テーブル名の変更: ALTER TABLE [table] RENAME TO [new_table_name];
テーブルの削除: DROP TABLE [table];
テーブル構造の表示: DESCRIBE [table]　または　DESC [table];

カラム操作

カラムの追加: ALTER TABLE [table] ADD [field] [type];
カラムの名称と型の変更: ALTER TABLE [table] CHANGE [field] [new_field_name] [type];

カラムの型のみ変更: ALTER TABLE [table] MODIFY [field] [type];
カラムの削除: ALTER TABLE [table] DROP COLUMN [field];

レコード操作

全レコード・全フィールドの表示: SELECT * FROM [table];
指定したフィールドの表示: SELECT [field], [field], ... FROM [table];
昇順／降順表示: SELECT [fields] FROM [table] ORDER BY [field] ASC/DESC;
レコードの追加: INSERT INTO [table] VALUES([field], [field], ...);
レコードの削除(全削除に注意): DELETE FROM [table] WHERE [condition]
全レコードの削除: DELETE FROM [テーブル]
レコードの変更(全変更に注意): UPDATE [table] SET [field]=[value] WHERE [condition]

テーブルの結合

内部結合／等結合～Inner Join

INNER JOIN句: SELECT [fields]
FROM [table_1] INNER JOIN [table_2]
ON ([table_1].)[key_1] = ([table_2].)[key_2]
WHERE句: SELECT [field], [field], ...
FROM [table_1], [table_2]
WHERE ([table_1].)[キー1] = ([table_2].)[key_2]

抽出カラムの選択

結合後のカラムの限定: SELECT ..., [table].* [table].[field], ... from [table], [table], ...

条件式

値がNULL: [field] IS NULL

MySQL – レコードの検索

2016-11-04 / tau / コメントする

フィールド選択

全レコードの全フィールド

SELECT * FROM [テーブル];

1	SELECT * FROM [テーブル];

mysql> select * from test;
+-------+------+--------+
| name  | age  | nation |
+-------+------+--------+
| John  |   35 | US     |
| Alex  |   40 | NZ     |
| Luice |   29 | US     |
| Alice |   23 | UK     |
| Sala  |   25 | JP     |
+-------+------+--------+
3 rows in set (0.00 sec)

mysql>

mysql> select * from test;

+-------+------+--------+

| name | age | nation |

+-------+------+--------+

| John | 35 | US |

| Alex | 40 | NZ |

| Luice | 29 | US |

| Alice | 23 | UK |

| Sala | 25 | JP |

+-------+------+--------+

3 rows in set (0.00 sec)

mysql>

指定したフィールド

SELECT [フィールド](, [フィールド], ...) FROM [テーブル];

1	SELECT [フィールド](, [フィールド], ...) FROM [テーブル];

mysql> select nation, name, nation from test;
+--------+-------+--------+
| nation | name  | nation |
+--------+-------+--------+
| US     | John  | US     |
| NZ     | Alex  | NZ     |
| US     | Luice | US     |
| UK     | Alice | UK     |
| JP     | Sala  | JP     |
+--------+-------+--------+
3 rows in set (0.00 sec)

mysql>

mysql> select nation, name, nation from test;

+--------+-------+--------+

| nation | name | nation |

+--------+-------+--------+

| US | John | US |

| NZ | Alex | NZ |

| US | Luice | US |

| UK | Alice | UK |

| JP | Sala | JP |

+--------+-------+--------+

3 rows in set (0.00 sec)

mysql>

レコードの条件検索

SELECT [フィールド選択] FROM [テーブル] WHERE [検索条件];

1	SELECT [フィールド選択] FROM [テーブル] WHERE [検索条件];

文字列検索

たとえば国名で検索する場合。

mysql> select * from test where nation='US';
+-------+------+--------+
| name  | age  | nation |
+-------+------+--------+
| John  |   35 | US     |
| Luice |   29 | US     |
+-------+------+--------+
2 rows in set (0.00 sec)

mysql>

mysql> select * from test where nation='US';

+-------+------+--------+

| name | age | nation |

+-------+------+--------+

| John | 35 | US |

| Luice | 29 | US |

+-------+------+--------+

2 rows in set (0.00 sec)

mysql>

LIKEによるあいまい検索

LIKE指定で任意の文字・文字列を含む検索ができる。

WHERE [フィールド] LIKE [検索文字列]

1	WHERE [フィールド] LIKE [検索文字列]

任意の一文字

アンダースコア('_')は任意の一文字に相当。

mysql> select * from test where nation like 'U_';
+-------+------+--------+
| name  | age  | nation |
+-------+------+--------+
| John  |   35 | US     |
| Luice |   29 | US     |
| Alice |   23 | UK     |
+-------+------+--------+
3 rows in set (0.00 sec)

mysql> select * from test where nation like 'U_';

+-------+------+--------+

| name | age | nation |

+-------+------+--------+

| John | 35 | US |

| Luice | 29 | US |

| Alice | 23 | UK |

+-------+------+--------+

3 rows in set (0.00 sec)

任意長の文字列

任意長(0文字以上)の文字列に対応するのは'%'。

mysql> select * from test where name like 'A%';
+-------+------+--------+
| name  | age  | nation |
+-------+------+--------+
| Alex  |   40 | NZ     |
| Alice |   23 | UK     |
+-------+------+--------+
2 rows in set (0.00 sec)

mysql>

mysql> select * from test where name like 'A%';

+-------+------+--------+

| name | age | nation |

+-------+------+--------+

| Alex | 40 | NZ |

| Alice | 23 | UK |

+-------+------+--------+

2 rows in set (0.00 sec)

mysql>

数値検索

年齢の範囲で検索する場合。

mysql> select * from test where age>30;
+------+------+--------+
| name | age  | nation |
+------+------+--------+
| John |   35 | US     |
| Alex |   40 | NZ     |
+------+------+--------+
2 rows in set (0.00 sec)

mysql>

mysql> select * from test where age>30;

+------+------+--------+

| name | age | nation |

+------+------+--------+

| John | 35 | US |

| Alex | 40 | NZ |

+------+------+--------+

2 rows in set (0.00 sec)

mysql>

上限と下限で検索する場合、ANDで複数条件を結ぶ。

mysql> select * from test where age>=25 and age <= 35;
+-------+------+--------+
| name  | age  | nation |
+-------+------+--------+
| John  |   35 | US     |
| Luice |   29 | US     |
| Sala  |   25 | JP     |
+-------+------+--------+
3 rows in set (0.00 sec)

mysql>

mysql> select * from test where age>=25 and age <= 35;

+-------+------+--------+

| name | age | nation |

+-------+------+--------+

| John | 35 | US |

| Luice | 29 | US |

| Sala | 25 | JP |

+-------+------+--------+

3 rows in set (0.00 sec)

mysql>

同じ検索条件をBETWEENで指定できる。

mysql> select * from test where age between 25 and 35;
+-------+------+--------+
| name  | age  | nation |
+-------+------+--------+
| John  |   35 | US     |
| Luice |   29 | US     |
| Sala  |   25 | JP     |
+-------+------+--------+
3 rows in set (0.00 sec)

mysql> select * from test where age between 25 and 35;

+-------+------+--------+

| name | age | nation |

+-------+------+--------+

| John | 35 | US |

| Luice | 29 | US |

| Sala | 25 | JP |

+-------+------+--------+

3 rows in set (0.00 sec)

対象列挙

IN(...)でカンマで区切った値の何れかと一致するレコードが抽出される。

mysql> select * from test where nation in('UK', 'NZ');
+-------+------+--------+
| name  | age  | nation |
+-------+------+--------+
| Alex  |   40 | NZ     |
| Alice |   23 | UK     |
+-------+------+--------+
2 rows in set (0.00 sec)

mysql>

mysql> select * from test where nation in('UK', 'NZ');

+-------+------+--------+

| name | age | nation |

+-------+------+--------+

| Alex | 40 | NZ |

| Alice | 23 | UK |

+-------+------+--------+

2 rows in set (0.00 sec)

mysql>

QGIS – 融合(Dissolve)

2016-11-03 / tau / コメントする

概要

融合処理は、同じ属性条件の複数のポリゴン間の境界を取り除き、一つのポリゴンにする。

シングルパートをマルチパートに変換しても、各々のポリゴンは残っており、その境界線は表示されるが、融合することでそれらが一つのポリゴンとなる。

qgis-disolved-japan

手順

メニューの「ベクタ」→「空間演算ツール」→「Dissolve」でダイアログが開く。

qgis-dissolve-dialog

“入力レイヤ”で融合したいレイヤを選択
“Dissolve all …”のチェックは、全てのポリゴンを一つにまとめたいときを除いて外す
“Unique ID fields”は融合の同一判定に使うフィールドを選択して右へ
“融合”は融合後の出力先／Shapeファイルの場合はshpファイル名で指定する
“アルゴリズム実行後に・・・”は実行終了後にレイヤを表示させたいときにチェック

処理時間

QGISによる融合処理には非常に長い時間がかかる。

たとえば国土数値情報の市区町村別行政界データを都道府県でまとめるのに、Intel Core i5、メモリ4GB、Windows10の条件下で6時間ほどかかった。

5時間過ぎるまでほとんどフリーズしたかのような状態になり、最後の20～30分くらいでプログレスバーが表示され(進捗率27%)、そこからは(それまで待たされた時間と比べれば)進みが早い。

隣接する二つの市の融合が終わるまで数分、徳島県と高知県の市町村を両県にまとめるのに15分くらい。離島や港湾地域のややこしい形があると、より実行時間が長くなるものと推測される。

属性データ

融合の際にユニークフィールドとして都道府県名を指定すると、同じ都道府県名の市区町村の属性データが複数存在するが、融合後のポリゴンの属性としては一つしか残らない。

融合前のユニークキーが同じデータの先頭行のデータが融合後の属性データとなるが、ほとんどの場合はこれは意味を持たないため、別にデータをセットする必要がある。

簡素化の効果

簡素化(Simplify)の効果は大きく、全国レベルでポリゴン境界の齟齬が殆ど目立たない程度の簡素化で、大幅に(数時間→数分)処理時間が短縮される。

コンソールでのMySQLの文字化け

2016-11-01 / tau / コメントする

症状

コンソールからテーブルに、日本語を含んだレコードを追加すると文字化けしてしまう。

たとえば以下のようなデータを持つテーブルがあるとする。

+--------+------+
| name   | age  |
+--------+------+
| John   |   38 |
| Alex   |   40 |
| 山田   |   50 |
+--------+------+

+--------+------+

| name | age |

+--------+------+

| John | 38 |

| Alex | 40 |

| 山田 | 50 |

+--------+------+

これをそのままSELECTコマンドで表示させると、以下のように文字化け。

mysql> select * from test;
+------+------+
| name | age  |
+------+------+
| John |   38 |
| Alex |   40 |
|  |   50 |
+------+------+
3 rows in set (0.00 sec)

mysql>

mysql> select * from test;

+------+------+

| name | age |

+------+------+

| John | 38 |

| Alex | 40 |

| | 50 |

+------+------+

3 rows in set (0.00 sec)

mysql>

WHERE句で無理矢理元の文字列’山田’で探そうとすると、以下のようなエラーが出る。

ERROR 1267 (HY000): Illegal mix of collations (utf8_general_ci,IMPLICIT) and (ujis_japanese_ci,COERCIBLE) for operation '='

1	ERROR 1267 (HY000): Illegal mix of collations (utf8_general_ci,IMPLICIT) and (ujis_japanese_ci,COERCIBLE) for operation '='

これはその文字列は存在しているがコードが違うと言われているようだ。

そこでMySQLのcharacter_set群がどのように設定されているかを確認。確認コマンドは以下の形式。

SHOW VARIABLES LIKE 'character_set%';

1	SHOW VARIABLES LIKE 'character_set%';

以下、実行結果。

mysql> show variables like 'character_set%';
+--------------------------+----------------------------------+
| Variable_name            | Value                            |
+--------------------------+----------------------------------+
| character_set_client     | ujis                             |
| character_set_connection | ujis                             |
| character_set_database   | ujis                             |
| character_set_filesystem | binary                           |
| character_set_results    | ujis                             |
| character_set_server     | ujis                             |
| character_set_system     | utf8                             |
| character_sets_dir       | /usr/local/share/mysql/charsets/ |
+--------------------------+----------------------------------+
8 rows in set (0.01 sec)

mysql>

mysql> show variables like 'character_set%';

+--------------------------+----------------------------------+

| Variable_name | Value |

+--------------------------+----------------------------------+

| character_set_client | ujis |

| character_set_connection | ujis |

| character_set_database | ujis |

| character_set_filesystem | binary |

| character_set_results | ujis |

| character_set_server | ujis |

| character_set_system | utf8 |

| character_sets_dir | /usr/local/share/mysql/charsets/ |

+--------------------------+----------------------------------+

8 rows in set (0.01 sec)

mysql>

さくらレンタルサーバでDBを追加する際の文字コード指定はUTF-8としていて、ターミナル側の設定はUTF-8だが、ほとんどがujis = EUC_jpになっている。

対策

MySQLにログインした後、一度だけ以下を実行する。

CHARSET utf8;

1	CHARSET utf8;

以下、実行例。

mysql> charset utf8
Charset changed
mysql> select * from test;
+--------+------+
| name   | age  |
+--------+------+
| John   |   38 |
| Alex   |   40 |
| 山田   |   50 |
+--------+------+
3 rows in set (0.00 sec)

mysql> show variables like 'character_set%';
+--------------------------+----------------------------------+
| Variable_name            | Value                            |
+--------------------------+----------------------------------+
| character_set_client     | utf8                             |
| character_set_connection | utf8                             |
| character_set_database   | utf8                             |
| character_set_filesystem | binary                           |
| character_set_results    | utf8                             |
| character_set_server     | ujis                             |
| character_set_system     | utf8                             |
| character_sets_dir       | /usr/local/share/mysql/charsets/ |
+--------------------------+----------------------------------+
8 rows in set (0.00 sec)

mysql>

mysql> charset utf8

Charset changed

mysql> select * from test;

+--------+------+

| name | age |

+--------+------+

| John | 38 |

| Alex | 40 |

| 山田 | 50 |

+--------+------+

3 rows in set (0.00 sec)

mysql> show variables like 'character_set%';

+--------------------------+----------------------------------+

| Variable_name | Value |

+--------------------------+----------------------------------+

| character_set_client | utf8 |

| character_set_connection | utf8 |

| character_set_database | utf8 |

| character_set_filesystem | binary |

| character_set_results | utf8 |

| character_set_server | ujis |

| character_set_system | utf8 |

| character_sets_dir | /usr/local/share/mysql/charsets/ |

+--------------------------+----------------------------------+

8 rows in set (0.00 sec)

mysql>

適切に表示され、chracter_set変数群も_serverを除いて全てUTF-8になっている。

なお、SET CHARSET utf8での文字コード設定を説明しているサイトが殆どだが、この方法だと文字化けはなくなるが、カラム幅が崩れてしまう。この時のcharacter_setの設定値は上と同じに変更されるが表示のされ方が違う。

対策・改

その後、MySQLに入る時に以下を実行する方法も知った。--default-character-set=utf8mb4を指定する。

% mysql --host=mysql57.taustation.sakura.ne.jp --user=taustation --password --default-character-set=utf8mb4

1	% mysql --host=mysql57.taustation.sakura.ne.jp --user=taustation --password --default-character-set=utf8mb4

この結果文字コードは以下のようになって、文字化けも解消。

mysql> show variables like 'char%';
+--------------------------+----------------------------+
| Variable_name            | Value                      |
+--------------------------+----------------------------+
| character_set_client     | utf8mb4                    |
| character_set_connection | utf8mb4                    |
| character_set_database   | utf8mb4                    |
| character_set_filesystem | binary                     |
| character_set_results    | utf8mb4                    |
| character_set_server     | utf8mb4                    |
| character_set_system     | utf8                       |
| character_sets_dir       | /usr/share/mysql/charsets/ |
+--------------------------+----------------------------+
8 rows in set (0.00 sec)

mysql> show variables like 'char%';

+--------------------------+----------------------------+

| Variable_name | Value |

+--------------------------+----------------------------+

| character_set_client | utf8mb4 |

| character_set_connection | utf8mb4 |

| character_set_database | utf8mb4 |

| character_set_filesystem | binary |

| character_set_results | utf8mb4 |

| character_set_server | utf8mb4 |

| character_set_system | utf8 |

| character_sets_dir | /usr/share/mysql/charsets/ |

+--------------------------+----------------------------+

8 rows in set (0.00 sec)

20210604

MySQL – カラムの追加・変更・削除

2016-10-31 / tau / 2件のコメント

カラムの追加

最後尾に追加

以下のコマンドで最後尾に新しいカラムが追加される。追加されたカラムのフィールド値には初期値が設定される。

ALTER TABLE [テーブル名] ADD [フィールド名] [型];

1	ALTER TABLE [テーブル名] ADD [フィールド名] [型];

以下、実行例。

mysql> select * from test;                                                           
+------+------+
| name | age  |
+------+------+
| John |   32 |
| Alex |   40 |
+------+------+
2 rows in set (0.00 sec)

mysql> describe test;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| name  | varchar(10) | YES  |     | NULL    |       |
| age   | tinyint(4)  | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.01 sec)

mysql> alter table test add occupation varchar(20);                                  
Query OK, 2 rows affected (0.01 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> describe test;
+------------+-------------+------+-----+---------+-------+
| Field      | Type        | Null | Key | Default | Extra |
+------------+-------------+------+-----+---------+-------+
| name       | varchar(10) | YES  |     | NULL    |       |
| age        | tinyint(4)  | YES  |     | NULL    |       |
| occupation | varchar(20) | YES  |     | NULL    |       |
+------------+-------------+------+-----+---------+-------+
3 rows in set (0.01 sec)

mysql> select * from test;                                                           
+------+------+------------+
| name | age  | occupation |
+------+------+------------+
| John |   32 | NULL       |
| Alex |   40 | NULL       |
+------+------+------------+
2 rows in set (0.00 sec)

mysql>

mysql> select * from test;

+------+------+

| name | age |

+------+------+

| John | 32 |

| Alex | 40 |

+------+------+

2 rows in set (0.00 sec)

mysql> describe test;

+-------+-------------+------+-----+---------+-------+

+-------+-------------+------+-----+---------+-------+

| age | tinyint(4) | YES | | NULL | |

+-------+-------------+------+-----+---------+-------+

2 rows in set (0.01 sec)

mysql> alter table test add occupation varchar(20);

Query OK, 2 rows affected (0.01 sec)

Records: 2 Duplicates: 0 Warnings: 0

mysql> describe test;

+------------+-------------+------+-----+---------+-------+

+------------+-------------+------+-----+---------+-------+

| age | tinyint(4) | YES | | NULL | |

+------------+-------------+------+-----+---------+-------+

3 rows in set (0.01 sec)

mysql> select * from test;

+------+------+------------+

| name | age | occupation |

+------+------+------------+

| John | 32 | NULL |

| Alex | 40 | NULL |

+------+------+------------+

2 rows in set (0.00 sec)

mysql>

先頭に追加

FIRSTを指定すると、新たな列が先頭に追加される。

ALTER TABLE [テーブル名] ADD [フィールド名] [型] FIRST;

1	ALTER TABLE [テーブル名] ADD [フィールド名] [型] FIRST;

指定位置への追加

AFTERで指定したカラムの後に新たな列が追加される。

ALTER TABLE [テーブル名] ADD [フィールド名] [型] AFTER [基準カラム名];

1	ALTER TABLE [テーブル名] ADD [フィールド名] [型] AFTER [基準カラム名];

カラムの変更

名称と型の変更

カラムの名前や型の変更ALTERコマンドを使う。

CHANGEでフィールド名と型を同時に変更する。

ALTER TABLE [テーブル名] CHANGE [フィールド名] [新フィールド名] [新しい型];

1	ALTER TABLE [テーブル名] CHANGE [フィールド名] [新フィールド名] [新しい型];

以下、実行例。

mysql> alter table test change name name varchar(20);
Query OK, 1 row affected (0.01 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> describe test;
+------------+-------------+------+-----+---------+-------+
| Field      | Type        | Null | Key | Default | Extra |
+------------+-------------+------+-----+---------+-------+
| name       | varchar(20) | YES  |     | NULL    |       |
| age        | tinyint(4)  | YES  |     | NULL    |       |
| occupation | varchar(20) | YES  |     | NULL    |       |
+------------+-------------+------+-----+---------+-------+
3 rows in set (0.01 sec)

mysql>

mysql> alter table test change name name varchar(20);

Query OK, 1 row affected (0.01 sec)

Records: 1 Duplicates: 0 Warnings: 0

mysql> describe test;

+------------+-------------+------+-----+---------+-------+

+------------+-------------+------+-----+---------+-------+

| age | tinyint(4) | YES | | NULL | |

+------------+-------------+------+-----+---------+-------+

3 rows in set (0.01 sec)

mysql>

型のみの変更

型のみの変更はMODIFY。

ALTER TABLE [テーブル名] MODIFY [フィールド名] [型];

1	ALTER TABLE [テーブル名] MODIFY [フィールド名] [型];

以下、実行例。

mysql> describe test;
+--------+-------------+------+-----+---------+-------+
| Field  | Type        | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| name   | varchar(10) | YES  |     | NULL    |       |
| age    | tinyint(4)  | YES  |     | NULL    |       |
| nation | varchar(10) | YES  |     | NULL    |       |
+--------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

mysql> alter table test modify nation varchar(2);
Query OK, 5 rows affected (0.00 sec)
Records: 5  Duplicates: 0  Warnings: 0

mysql> describe test;
+--------+-------------+------+-----+---------+-------+
| Field  | Type        | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| name   | varchar(10) | YES  |     | NULL    |       |
| age    | tinyint(4)  | YES  |     | NULL    |       |
| nation | varchar(2)  | YES  |     | NULL    |       |
+--------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

mysql>

mysql> describe test;

+--------+-------------+------+-----+---------+-------+

+--------+-------------+------+-----+---------+-------+

| age | tinyint(4) | YES | | NULL | |

+--------+-------------+------+-----+---------+-------+

3 rows in set (0.00 sec)

mysql> alter table test modify nation varchar(2);

Query OK, 5 rows affected (0.00 sec)

Records: 5 Duplicates: 0 Warnings: 0

mysql> describe test;

+--------+-------------+------+-----+---------+-------+

+--------+-------------+------+-----+---------+-------+

| age | tinyint(4) | YES | | NULL | |

+--------+-------------+------+-----+---------+-------+

3 rows in set (0.00 sec)

mysql>

カラムの削除

以下のコマンドで、指定したカラムを削除できる。

ALTER TABLE [テーブル名] DROP COLUMN [フィールド名];

1	ALTER TABLE [テーブル名] DROP COLUMN [フィールド名];

以下、実行例。

mysql> describe test;
+------------+-------------+------+-----+---------+-------+
| Field      | Type        | Null | Key | Default | Extra |
+------------+-------------+------+-----+---------+-------+
| name       | varchar(10) | YES  |     | NULL    |       |
| age        | tinyint(4)  | YES  |     | NULL    |       |
| occupation | varchar(20) | YES  |     | NULL    |       |
+------------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

mysql> alter table test drop column occupation;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> describe test;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| name  | varchar(10) | YES  |     | NULL    |       |
| age   | tinyint(4)  | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

mysql>

mysql> describe test;

+------------+-------------+------+-----+---------+-------+

+------------+-------------+------+-----+---------+-------+

| age | tinyint(4) | YES | | NULL | |

+------------+-------------+------+-----+---------+-------+

3 rows in set (0.00 sec)

mysql> alter table test drop column occupation;

Query OK, 0 rows affected (0.01 sec)

Records: 0 Duplicates: 0 Warnings: 0

mysql> describe test;

+-------+-------------+------+-----+---------+-------+

+-------+-------------+------+-----+---------+-------+

| age | tinyint(4) | YES | | NULL | |

+-------+-------------+------+-----+---------+-------+

2 rows in set (0.00 sec)

mysql>

MySQL – レコードの追加・変更・削除

2016-10-31 / tau / コメントする

レコードの追加

全フィールド追加

INSERT INTO ~ VALUESコマンドで、全フィールドの内容をフィールドの順番通りに追加する。

INSERT INTO [テーブル名] VALUES([フィールド値1], [フィールド値2], ...);

1	INSERT INTO [テーブル名] VALUES([フィールド値1], [フィールド値2], ...);

以下、実行例。

mysql> insert into test values('John', 32);
Query OK, 1 row affected (0.00 sec)

mysql> select * from test;
+------+------+
| name | age  |
+------+------+
| John |   32 |
+------+------+
1 row in set (0.00 sec)

mysql>

mysql> insert into test values('John', 32);

Query OK, 1 row affected (0.00 sec)

mysql> select * from test;

+------+------+

| name | age |

+------+------+

| John | 32 |

+------+------+

1 row in set (0.00 sec)

mysql>

フィールドを指定した追加

フィールドを指定した実行は次の通り。

INSERT INTO [テーブル名] ([フィールド名1], ...) VALUES([フィールド値1], ...);

1	INSERT INTO [テーブル名] ([フィールド名1], ...) VALUES([フィールド値1], ...);

以下、実行例。

mysql> insert into test (name, age) values('Alex', 40);
Query OK, 1 row affected (0.00 sec)

mysql> select * from test;
+------+------+
| name | age  |
+------+------+
| Alex |   40 |
| John |   32 |
+------+------+
2 rows in set (0.00 sec)

mysql>

mysql> insert into test (name, age) values('Alex', 40);

Query OK, 1 row affected (0.00 sec)

mysql> select * from test;

+------+------+

| name | age |

+------+------+

| Alex | 40 |

| John | 32 |

+------+------+

2 rows in set (0.00 sec)

mysql>

フィールドの一部だけを指定すると、指定していないフィールドには初期値が設定される。

mysql> insert into test (name) values('Alice');
Query OK, 1 row affected (0.11 sec)

mysql> select * from test;
+-------+------+
| name  | age  |
+-------+------+
| Alex  |   40 |
| Alice | NULL |
| John  |   32 |
+-------+------+
3 rows in set (0.00 sec)

mysql>

mysql> insert into test (name) values('Alice');

Query OK, 1 row affected (0.11 sec)

mysql> select * from test;

+-------+------+

| name | age |

+-------+------+

| Alex | 40 |

| Alice | NULL |

| John | 32 |

+-------+------+

3 rows in set (0.00 sec)

mysql>

レコードの変更

レコードのフィールド値の変更はUPDATE ~ SET ~ WHEREコマンドを使う。

UPDATE test SET age=28 WHERE name='Alice';

1	UPDATE test SET age=28 WHERE name='Alice';

以下、実行例。

mysql> update test set age=28 where name='Alice';                                    
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from test;
+-------+------+
| name  | age  |
+-------+------+
| Alex  |   40 |
| Alice |   28 |
| John  |   32 |
+-------+------+
3 rows in set (0.00 sec)

mysql>

mysql> update test set age=28 where name='Alice';

Query OK, 1 row affected (0.00 sec)

Rows matched: 1 Changed: 1 Warnings: 0

mysql> select * from test;

+-------+------+

| name | age |

+-------+------+

| Alex | 40 |

| Alice | 28 |

| John | 32 |

+-------+------+

3 rows in set (0.00 sec)

mysql>

UPDATEの注意点

UPDATEコマンドは必ずWHEREで対象となるレコードを指定する。さもないと、全レコードのフィールドが指定した内容で変更されてしまう。

mysql> update test set age=30;                                                       
Query OK, 3 rows affected (0.11 sec)
Rows matched: 3  Changed: 3  Warnings: 0

mysql> select * from test;
+-------+------+
| name  | age  |
+-------+------+
| Alex  |   30 |
| Alice |   30 |
| John  |   30 |
+-------+------+
3 rows in set (0.00 sec)

mysql>

mysql> update test set age=30;

Query OK, 3 rows affected (0.11 sec)

Rows matched: 3 Changed: 3 Warnings: 0

mysql> select * from test;

+-------+------+

| name | age |

+-------+------+

| Alex | 30 |

| Alice | 30 |

| John | 30 |

+-------+------+

3 rows in set (0.00 sec)

mysql>

レコードの削除

レコードの削除は、DELETE FROM ~ WHEREコマンドで行う。

DELETE FROM [テーブル名] WHERE [フィールド名]=[フィールド値];

1	DELETE FROM [テーブル名] WHERE [フィールド名]=[フィールド値];

以下、実行例。

mysql> delete from test where name='John';
Query OK, 1 row affected (0.01 sec)

mysql> select * from test;
+-------+------+
| name  | age  |
+-------+------+
| Alex  |   30 |
| Alice |   30 |
+-------+------+
2 rows in set (0.00 sec)

mysql>

mysql> delete from test where name='John';

Query OK, 1 row affected (0.01 sec)

mysql> select * from test;

+-------+------+

| name | age |

+-------+------+

| Alex | 30 |

| Alice | 30 |

+-------+------+

2 rows in set (0.00 sec)

mysql>

DELETEの注意点

DELETEコマンドでWHEREを省略すると、全データが削除されてしまうので注意。

mysql> delete from test;
Query OK, 2 rows affected (0.00 sec)

mysql> select * from test;                                                     
Empty set (0.00 sec)

mysql>

mysql> delete from test;

Query OK, 2 rows affected (0.00 sec)

mysql> select * from test;

Empty set (0.00 sec)

mysql>

QGIS – マルチポリゴン化

2016-10-29 / tau / コメントする

マルチポリゴンの意義

国土数値情報の行政界データでは、離島や飛び地がある自治体はそれぞれが独立したポリゴンとなっていて、それぞれに同じ自治体名が付けられている。ただし自治体番号の後ろに連番を付けた番号が付けられている。

これら同じ自治体に複数のポリゴンがある場合、自治体オブジェクトとしては一つとして、それが複数のポリゴンをもつようなマルチポリゴンにする方が有効な場合が多い。

たとえば自治体単位での面積を求める場合、一つの自治体を指定すれば全ポリゴンの面積を加えられる。またラベルを表示する場合、マルチポリゴンにしなければ一つひとつの島や飛び地にも自治体名が表示されてしまうが、集約すれば一つの自治体に一つだけラベルが付けられる。

マルチポリゴンのほかにはラインやポイントを集約する場合もあり、それらは総称してマルチパートと呼ばれている。これに対して一つのオブジェクトに一つのポリゴンが対応しているのをシングルポリゴン(シングルパート)と呼ぶ。

QGISでのマルチポリゴン化

QGISでシングルポリゴンをマルチポリゴンに変更するには以下のように操作する。

メニューから「ベクタ」→「ジオメトリツール」→「シングルパートをマルチパートに集約」
集約のキーとなるユニークなフィールドを指定
集約後のshpファイルを保存する場所とファイル名を指定
実行

まずメニューから「シングルパートをマルチパートに集約」を選択。

注意点

処理時間

全国のデータをマルチパート化するとかなり時間がかかる。Core i5のマシンで2時間近くかかった。

マルチパートのデータ

シングルパーツのオブジェクトが面積などのデータを持っていた場合、マルチパートに集約すると、先頭のアイテムのデータが代表となってマルチパートのデータになる。

行政改データの境界未定地

行政界データをマルチポリゴン化する場合、集約キーとなるユニークフィールドに市町村名を用いると、各都道府県の所属未定地が全国で同じ名前となり、ひとまとめになってしまう。これを回避するため、新たにフィールドをつくり、値を都道府県名+市町村名とすれば、所属未定地も都道府県単位で分割される。