41から始めました

文字通り41歳から始めたブログです。DB(MySQL)を使ってお仕事してるので、DB周りの話を中心に最近始めたこととかをTwitterのノリで書いています。なお、本サイトにおいて示されている見解は私個人の見解であり、所属団体や組織を代表するものではありません。

SPACIAL関数で遊ぶ前の準備でハマったアレコレ

MySQL

この日記は RDBMS GIS アドベントカレンダー2018の3日目向けに書きました。

はじめに

アドベントカレンダーが作られてて、お話を聞いて面白そうだと思って少し触った経緯もあり、

あらためて最新のMySQL8.0.13で触りなおしてみた際、ハマったことについて書こうと思う。

空間データ型

  • GEOMETRY
  • POINT
  • LINESTRING
  • POLYGON
  • MULTIPOINT
  • MULTILINESTRING
  • MULTIPOLYGON
  • GEOMETRYCOLLECTION

と８つあるらしい。

https://dev.mysql.com/doc/refman/8.0/en/spatial-type-overview.html

坂井さんの点・線・面、ひっくるめて幾何ってイメージでなんとなく理解。

https://www.slideshare.net/sakaik/mysql-gis-clubmysql-4/13

まあ、基本としてPOINT(経度と緯度の組み合わせ)でテーブルを作ることにしよう。

データは国土交通省・位置参照情報を使う

手っ取り早く緯度と経度を含んだ情報がCSV(またはTSV)化してて、使っても問題無いデータはないかしら？と思っていたところ、国土交通省・位置参照情報というものがありました。

http://w3land.mlit.go.jp/cgi-bin/isj/dls/_choose_method.cgi

データ形式は街区レベルと大字・町丁目レベルの2種類あり、いずれもCSVで即使えるんですが、注意点として数字のコードはASCII、文字のコードはSHIFT-JISコードになってます。

http://w3land.mlit.go.jp/isj/data.html

LinuxでLOAD DATA LOCAL INFILE　やってて

「そんなカラムはねーぜ！」ってエラーはかれるので、なんでや？と小一時間悩んでた自分のようにならないようにしてください(苦笑)

自分はとりあえず大字・町丁目レベルのデータを使うことにしました。

さあ、テーブルを作ろう

任意のスキーマ上に以下のようなテーブルを作りました。

CREATE TABLE towns (
  towns_id        BIGINT(20)  NOT NULL AUTO_INCREMENT COMMENT '大字・町丁目レベルID',
  pref_code       VARCHAR(2)  NOT NULL DEFAULT ''  COMMENT 'JIS都道府県コード',
  pref_name       VARCHAR(4)  NOT NULL DEFAULT ''  COMMENT '都道府県名',
  city_code       VARCHAR(5)  NOT NULL DEFAULT ''  COMMENT 'JIS市区町村コード',
  city_name       VARCHAR(20) NOT NULL DEFAULT ''  COMMENT '市区町村名',
  town_code       VARCHAR(12) NOT NULL DEFAULT ''  COMMENT '大字・町丁目コード（JIS市区町村コード＋独自7桁）',
  town_name       VARCHAR(20) NOT NULL DEFAULT ''  COMMENT '大字・町丁目名 （町丁目の数字は漢数字）',
  full_address    VARCHAR(44) NOT NULL DEFAULT ''  COMMENT '大字・町丁目レベル住所名(フル)',
  lat             DOUBLE      NOT NULL DEFAULT 0.0 COMMENT '十進経緯度（少数第6位まで、半角）',
  lon             DOUBLE      NOT NULL DEFAULT 0.0 COMMENT '十進経緯度（少数第6位まで、半角）',
  lonlat_point point NOT NULL /*!80003 SRID 4326 */  COMMENT '緯度・経度を表すポイント(NOT NULL)',
  org_res_code    VARCHAR(1)  NOT NULL DEFAULT '0' COMMENT '原典資料コード 大字・町丁目位置参照情報作成における原典資料を表すコード 1：自治体資料　2：街区レベル位置参照　3：1/25000地形図　0：その他資料',
  aza_cho_code    VARCHAR(1)  NOT NULL DEFAULT '0' COMMENT '大字・字・丁目区分コード 1：大字　2：字　3：丁目　0：不明',
  PRIMARY KEY (towns_id),
  SPATIAL KEY si_towns_1 (lonlat_point)
) ENGINE InnoDB DEFAULT CHARSET utf8mb4 ROW_FORMAT=DYNAMIC COMMENT='大字・町丁目レベル位置参照情報';

そして、以下のLOAD DATA文でデータ取込を行うことにしました。

LOAD DATA LOCAL INFILE '13_2017.csv'
REPLACE INTO TABLE towns
CHARACTER SET utf8mb4
FIELDS TERMINATED BY ',' ENCLOSED BY '"' ESCAPED BY '\\'
LINES TERMINATED BY '\n' STARTING BY ''
IGNORE 1 LINES
(@pref_code, @pref_name, @city_code, @city_name, @town_code, @town_name, @lat, @lon, @org_res_code, @aza_cho_code)
SET
towns_id = @towns_id,
pref_code = @pref_code,
pref_name = @pref_name,
city_code = @city_code,
city_name = @city_name,
town_code = @town_code,
town_name = @town_name,
full_address = CONCAT(@pref_name, @city_name, @town_name), 
lat = @lat,
lon = @lon,
lonlat_point = ST_GeomFromText(CONCAT('POINT(', @lat, ' ', @lon, ')')),
org_res_code = @org_res_code,
aza_cho_code = @aza_cho_code
;

※(latとlonが逆でした　↑は修正済)

ところが・・・

ERROR 1364 (HY000): Field 'lonlat_point' doesn't have a default value

？？？

このエラー(doesn't have a default value)、sql_modeで厳しい制限かかってるときによくみるやつや。

というわけでsql_mode空っぽにしてみましたが、変わらず・・・。

文言を見る限り直訳ですが「デフォルト値を持ってない」って言ってるんで、NOT NULLとバッティングしてるんやろうなあ、と。

悲しいことにSPATIAL INDEXはNOT NULLのカラムにしか張れないので、

  1. インデックスを外し、
  2. カラムをNULLABLEにしてから
  3. もう一度データ投入すると・・・

Query OK, 5358 rows affected (2.39 sec)　

Records: 5358 Deleted: 0 Skipped: 0 Warnings: 0

見事に入りました…。

その時のテーブルはこんな感じですね

CREATE TABLE towns (
  towns_id        BIGINT(20)  NOT NULL AUTO_INCREMENT COMMENT '大字・町丁目レベルID',
  pref_code       VARCHAR(2)  NOT NULL DEFAULT ''  COMMENT 'JIS都道府県コード',
  pref_name       VARCHAR(4)  NOT NULL DEFAULT ''  COMMENT '都道府県名',
  city_code       VARCHAR(5)  NOT NULL DEFAULT ''  COMMENT 'JIS市区町村コード',
  city_name       VARCHAR(20) NOT NULL DEFAULT ''  COMMENT '市区町村名',
  town_code       VARCHAR(12) NOT NULL DEFAULT ''  COMMENT '大字・町丁目コード（JIS市区町村コード＋独自7桁）',
  town_name       VARCHAR(20) NOT NULL DEFAULT ''  COMMENT '大字・町丁目名 （町丁目の数字は漢数字）',
  full_address    VARCHAR(44) NOT NULL DEFAULT ''  COMMENT '大字・町丁目レベル住所名(フル)',
  lat             DOUBLE      NOT NULL DEFAULT 0.0 COMMENT '十進経緯度（少数第6位まで、半角）',
  lon             DOUBLE      NOT NULL DEFAULT 0.0 COMMENT '十進経緯度（少数第6位まで、半角）',
  lonlat_point    POINT       DEFAULT NULL  /*!80003 SRID 4326 */ COMMENT '緯度・経度を表すポイント',
  org_res_code    VARCHAR(1)  NOT NULL DEFAULT '0' COMMENT '原典資料コード 大字・町丁目位置参照情報作成における原典資料を表すコード 1：自治体資料　2：街区レベル位置参照　3：1/25000地形図　0：その他資料',
  aza_cho_code    VARCHAR(1)  NOT NULL DEFAULT '0' COMMENT '大字・字・丁目区分コード 1：大字　2：字　3：丁目　0：不明',
  PRIMARY KEY (towns_id)
) ENGINE InnoDB DEFAULT CHARSET utf8mb4 ROW_FORMAT=DYNAMIC COMMENT='大字・町丁目レベル位置参照情報';

データ投入後にカラムNot Null化＋インデックス適用

こんなDDLを実行です。

alter table towns modify column lonlat_point POINT NOT NULL SRID 4326 COMMENT '緯度・経度を表すポイント(NOT NULL)';
alter table towns add SPATIAL INDEX si_towns_1(lonlat_point);

show create tableすると、

CREATE TABLE `towns` (
   `towns_id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '大字・町丁目レベルID',
   `pref_code` varchar(2) NOT NULL DEFAULT '' COMMENT 'JIS都道府県コード',
   `pref_name` varchar(4) NOT NULL DEFAULT '' COMMENT '都道府県名',
   `city_code` varchar(5) NOT NULL DEFAULT '' COMMENT 'JIS市区町村コード',
   `city_name` varchar(20) NOT NULL DEFAULT '' COMMENT '市区町村名',
   `town_code` varchar(12) NOT NULL DEFAULT '' COMMENT '大字・町丁目コード（JIS市区町村コード＋独自7桁）',
   `town_name` varchar(20) NOT NULL DEFAULT '' COMMENT '大字・町丁目名 （町丁目の数字は漢数字）',
   `full_address` varchar(44) NOT NULL DEFAULT '' COMMENT '大字・町丁目レベル住所名(フル)',
   `lat` double NOT NULL DEFAULT '0' COMMENT '十進経緯度（少数第6位まで、半角）',
   `lon` double NOT NULL DEFAULT '0' COMMENT '十進経緯度（少数第6位まで、半角）',
   `lonlat_point` point NOT NULL /*!80003 SRID 4326 */  COMMENT '緯度・経度を表すポイント(NOT NULL)',
   `org_res_code` varchar(1) NOT NULL DEFAULT '0' COMMENT '原典資料コード 大字・町丁目位置参照情報作成における原典資料を表すコード 1：自治体資料　2：街区レベル位置参照　3：1/25000地形図　0：その他資料',
   `aza_cho_code` varchar(1) NOT NULL DEFAULT '0' COMMENT '大字・字・丁目区分コード 1：大字　2：字　3：丁目　0：不明',
   PRIMARY KEY (`towns_id`),
   SPATIAL KEY `si_towns_1` (`lonlat_point`)
 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ROW_FORMAT=DYNAMIC COMMENT='大字・町丁目レベル位置参照情報'

当初作ろうとしていたテーブルになりました。

その他

空間データ型のカラムが無いテーブルにデータが入ってる状態ではNOT NULLの空間データ型のカラムを追加できない

こんなテーブルを作って、

CREATE TABLE towns2 (
  towns_id        BIGINT(20)  NOT NULL AUTO_INCREMENT COMMENT '大字・町丁目レベルID',
  pref_code       VARCHAR(2)  NOT NULL DEFAULT ''  COMMENT 'JIS都道府県コード',
  pref_name       VARCHAR(4)  NOT NULL DEFAULT ''  COMMENT '都道府県名',
  city_code       VARCHAR(5)  NOT NULL DEFAULT ''  COMMENT 'JIS市区町村コード',
  city_name       VARCHAR(20) NOT NULL DEFAULT ''  COMMENT '市区町村名',
  town_code       VARCHAR(12) NOT NULL DEFAULT ''  COMMENT '大字・町丁目コード（JIS市区町村コード＋独自7桁）',
  town_name       VARCHAR(20) NOT NULL DEFAULT ''  COMMENT '大字・町丁目名 （町丁目の数字は漢数字）',
  full_address    VARCHAR(44) NOT NULL DEFAULT ''  COMMENT '大字・町丁目レベル住所名(フル)',
  lat             DOUBLE      NOT NULL DEFAULT 0.0 COMMENT '十進経緯度（少数第6位まで、半角）',
  lon             DOUBLE      NOT NULL DEFAULT 0.0 COMMENT '十進経緯度（少数第6位まで、半角）',
  org_res_code    VARCHAR(1)  NOT NULL DEFAULT '0' COMMENT '原典資料コード 大字・町丁目位置参照情報作成における原典資料を表すコード 1：自治体資料　2：街区レベル位置参照　3：1/25000地形図　0：その他資料',
  aza_cho_code    VARCHAR(1)  NOT NULL DEFAULT '0' COMMENT '大字・字・丁目区分コード 1：大字　2：字　3：丁目　0：不明',
  PRIMARY KEY (towns_id)
) ENGINE InnoDB DEFAULT CHARSET utf8mb4 ROW_FORMAT=DYNAMIC COMMENT='大字・町丁目レベル位置参照情報';

さっきと同じデータを入れてみます(lonlatカラムはありませんので、除いてます)

LOAD DATA LOCAL INFILE '13_2017.csv'
REPLACE INTO TABLE towns2
CHARACTER SET utf8mb4
FIELDS TERMINATED BY ',' ENCLOSED BY '"' ESCAPED BY '\\'
LINES TERMINATED BY '\n' STARTING BY ''
IGNORE 1 LINES
(@pref_code, @pref_name, @city_code, @city_name, @town_code, @town_name, @lat, @lon, @org_res_code, @aza_cho_code)
SET
towns_id = @towns_id,
pref_code = @pref_code,
pref_name = @pref_name,
city_code = @city_code,
city_name = @city_name,
town_code = @town_code,
town_name = @town_name,
full_address = CONCAT(@pref_name, @city_name, @town_name), 
lat = @lat,
lon = @lon,
org_res_code = @org_res_code,
aza_cho_code = @aza_cho_code
;
Query OK, 5358 rows affected (2.38 sec)
Records: 5358  Deleted: 0  Skipped: 0  Warnings: 0

とりあえず無事に入りました。

ここで、適当なNOT NULLの空間データ型(ここではGEOMETRY型)のカラムを追加しようとすると怒られます。

mysql> ALTER TABLE towns2 ADD COLUMN `lonlat_point` GEOMETRY NOT NULL;
ERROR 1138 (22004): Invalid use of NULL value

NULLABLEだと怒られません

mysql> ALTER TABLE towns2 ADD COLUMN `lonlat_point` GEOMETRY DEFAULT NULL;
Query OK, 0 rows affected (0.31 sec)

普通のカラムならNOT NULLでもデフォルトがあろうが無かろうがカラム追加できますが、どうやら空間データ型は特殊なようです。

空間データ型のカラムにはデフォルト値は入れられない

文言を読む限り、BLOB, TEXT, GEOMETRY or JSON columnはそういうもんなんですね。

了解です！

mysql> ALTER TABLE towns2 ADD `lonlat_point2` POINT NOT NULL SRID 4326 DEFAULT 0;
ERROR 1101 (42000): BLOB, TEXT, GEOMETRY or JSON column 'lonlat_point2' can't have a default value

以上、MySQL8のSPACIAL関数で遊ぶ前にハマった話でした。

後日談

坂井さんからdefaultの設定の仕方を教えてもらいました。

f:id:next4us-ti:20181227101828p:plain (mentionが違うのはご愛敬)

さっそく作ってみる f:id:next4us-ti:20181227102323p:plain あれ？Workbenchでエラーっぽく見える。 とりあえず実行してエラーを見ようか・・・エラーにならない！？

んー、なんだかよくわからんが、データロードできるか試してみる。

mysql> LOAD DATA LOCAL INFILE '13_2017.csv'
    -> REPLACE INTO TABLE towns3
    -> CHARACTER SET utf8mb4
    -> FIELDS TERMINATED BY ',' ENCLOSED BY '"' ESCAPED BY '\\'
    -> LINES TERMINATED BY '\n' STARTING BY ''
    -> IGNORE 1 LINES
    -> (@pref_code, @pref_name, @city_code, @city_name, @town_code, @town_name, @lat, @lon, @org_res_code, @aza_cho_code)
    -> SET
    -> towns_id = @towns_id,
    -> pref_code = @pref_code,
    -> pref_name = @pref_name,
    -> city_code = @city_code,
    -> city_name = @city_name,
    -> town_code = @town_code,
    -> town_name = @town_name,
    -> full_address = CONCAT(@pref_name, @city_name, @town_name),
    -> lat = @lat,
    -> lon = @lon,
    -> lonlat_point = ST_GeomFromText(CONCAT('POINT(', @lat, ' ', @lon, ')'),4326),
    -> org_res_code = @org_res_code,
    -> aza_cho_code = @aza_cho_code
    -> ;
Query OK, 5358 rows affected (4.26 sec)
Records: 5358  Deleted: 0  Skipped: 0  Warnings: 0

いえーい！無事成功 これでインデックス張った状態でデータ取込できることも確認できた。