BeelinkのミニPCを買って動かしてみた話

ひとつ前のブログで、このマシンで Ubuntu 24.04 を稼働させた話を書きました。
sakaik.hateblo.jp

このエントリでは、このマシンそのものについての紹介と、自分の設定用メモを書いておきたいと思います。

有機

 昨年の5月の連休の時に、ふと買ってしまった(そして今年の連休になるまで開梱していなかった)ものが1台。そして、今年の連休(昨日)にふと見て安かったのでつい買ってしまったのが1台の計2台あります。

1台目 Beelink EQ12 W11: N100(3,4GB)、メモリ16GB、SSD(NVMe) 500GB。LANx2, HDMIx2, USB 3+C1、Wi-fi
2台目 Beelink mini S12: N100(3,4GB)、メモリ16GB、SSD(NVMe) 500GB。LANx1, HDMIx2, USBx4、Wi-fi

1台目:
現在見ると 41,800円ですが、昨年は 3,000円の値引き後で 35,800円での購入でした。

2台目:
公称 29,800円のところ -15%割引のタイムセールで 25,530円でした。

どちらもスペックは似たような感じですが、値段に差があるので、たぶん EQシリーズのほうが少し上位なのでしょう(N100のバージョンとかあまりよく知らない)。EQ 12 のほうは、有線LANが2ポートあるのと、USBのひとつが Type-C になっているところが見かけ上の違いです。

NVMe SSDを換装

 ちょうど部屋に 使っていない2TBのNVMe SSDが転がっていたので、換装することにしました。

1台目(EQ12)を換装しようとフタを開けたら、ちょっとだけ面倒な感じだったので、そっとフタを閉じました(笑)。
EQ12のフタをあけたところの雰囲気は、以下の水野さんの記事で見ることができます。
gihyo.jp

一方の2台目(Mini S12) は非常にシンプルでした。画像で紹介します。なお、どういうタイミングで内部構造が変わるのかは私も知りませんので、あくまでもこのタイミングで買った私のはこういう構造だったという参考程度に。

まず
裏側の4カ所にあるネジを外して裏蓋を取ります。ベロがついていて引っ張りやすくなっているという、この心遣いがニクい。



フタをあけたところ。フラットケーブルでつながっているので、勢いよくフタをひっぱりすぎないように注意。



フタのほうは、2.5インチSSD(HDD)を設置できるようになっています。SATAと電源の端子が、黒い金属カバーの下に隠れていました。金属カバーは2本のネジ(写真中の「A」と「B」の箇所)で留まっています。今回は M.2 SSDを持っていたので換装することにしましたが、普通に 2.5インチSATAを買えば簡単に増設できそうで、良いですね。標準の500GB+増設2TBとかにすれば、かなり色々なことができるようになりそうです。


本体側にはすぐに SSDのスロットが見えるので、換装ラクラクです。そういえばこの形のメモリ(SODIMMでしたっけ?)を見るのもえらく久々のような気がします。
写真は換装後。


フタをしめたら完了。


ちなみにサイズは、少しだけ1台目(EQ)のほうが大きいです。ついでに裏側の端子の状態も紹介。どちらもHDMIが2つ(ただし今回サーバ用途なので接続は確認していません)。EQのほうはLANポートが2つあるのが特長です。あと Type-CのUSBも。


設定など

ここから先は特に、基本的に自分用の記録です。安売りされればなんかそのうちもう一台くらい買いそうな気がするので、その時のために(笑)。
作業は基本的に root にて行っています。
私は Ubuntuは minimum でインストールしているので、色々基本ソフトもインストールされていなかったりします。

sudo su - 
OS (Ubuntu 24.04) のインストール

ひとつ前の記事を参照。 現時点で直接 24.04をインストールできなかったので、22.04インストール後に24.04にupgradeしています。
https://sakaik.hateblo.jp/entry/20240505/ubuntu2404_on_miniPC

upgrade時のサービス起動設定の変更

upgrade処理中にサービスを再起動する際にいちいち尋ねてくるのが鬱陶しいので、勝手に再起動してくれるように設定しておきます。

echo "\$nrconf{restart} = 'a'" >> /etc/needrestart/conf.d/00local.conf
タイムゾーン設定

脳内で9時間を足すのが週間になってしまっているけど、健全ではないので最初にJSTに設定しておこう。脳への負担も少なくなる(はず)です。

timedatectl set-timezone Asia/Tokyo
何はなくとも最新化
apt update
apt upgrade
基本ソフトのインストール

だいたい、私が最初に使いたくなるのはこれくらい。

apt install vim iputils-ping network-manager alsa-utils cron -y
ネットワークの設定

一旦、有線でネットワークを設定しました。最終的には wi-fiにしたいので、ここをすっ飛ばしてもOK。

cd /etc/netplan/
cp 00-installer-config.yaml 10-netconfig.yaml
mv 00-installer-config.yaml 00-installer-config.old
chmod 700 10-netconfig.yaml
vi 10-netconfig.yaml (有線で運用する場合)
---------------------------------------------------
# This is the network config written by 'subiquity'
network:
  ethernets:
    enp1s0:
      dhcp4: no
      optional: true
      addresses: [192.168.0.241/24]
      # gateway4: 192.168.0.1
      routes:
        - to: 0.0.0.0/0
          via: 192.168.0.1
      nameservers:
        addresses: [8.8.8.8] 
    enp2s0:
      optional: true
  version: 2
----------------------------------------------------

→ gateway4指定はdeprecatedなのでroutesを使う

反映。

netplan apply
ホスト名の設定

今回はインストール時に設定したものでOKだけど、インストール時には適当に入れてしまったので変えたいときなど。

--確認
hostname
--設定
nmcli general hostname <<ホスト名>>
wi-fiの設定

ip link や nmcli device status などで確認してwlo1などの無線LANのインタフェースが認識されていることを確認。

vi /etc/netplan/50-wificonfig.yaml
----------------------------------------------------
network:
  version: 2
  wifis:
    wlo1:
      dhcp4: false
      access-points:
        "myhomelan01 ":
          password: "p@sSw0Rd"
      addresses: [192.168.0.211/24]
      routes:
        - to: default
          via: 192.168.0.1
      nameservers:
        addresses: [8.8.8.8]
----------------------------------------------------
netplan apply 

をしても反映されなかったので、

nmcli device wifi connect myhomelan01 password p@sSw0Rd

で手動接続した。ただし、先ほど作成した /etc/netplan/50-wificonfig.yaml と、この処理で自動で作成された設定ファイルがコンフリクトしているせいなのか、再起動後にwi-fiに自動で接続されない状態になってしまった。自動作成されたファイルを削除(拡張子をリネーム)することで正常化したが、これで良かったのかどうかは確証がない。
(そういえばサーバで wi-fiの接続するなんて、今回初めてかもしれない。一度ちゃんと整理しておかないとですね)



以上で、「電源を接続してボタンを押すだけで(sshで入って)遊べるサーバ」ができあがりました。

それラズパイでいいんじゃね?

 実はこういう環境が欲しくてラズパイをいくつか買ったのですが、今回ほどサクサクと作業できなかった(気分が盛り上がらなかった?)という経緯があります。
何が違うのかなと考えたのですが:

  • Beelinkのマシンは、フルサイズのHDMIがそのまま刺さる。ラズパイは変換アダプタが必要で、毎度「どこに行ったかな」と探す手間が鬱陶しかった(整理しておけという話w)
  • Beelinkのマシンは、ACアダプタ付属。ラズパイは自分が持っているUSBケーブルをそのまま使えるのがメリットである一方、適切なケーブル類を自分で用意する必要があるのが一手間面倒。
  • 物理電源スイッチの有無。通電したら起動しちゃうという(ある意味メリットなのですが)のが、今まで自分が使ってきた「コンピュータ」と異なって、なんだか他人行儀というか、違う文化の世界に来てしまった気分で気が休まらない(笑)。


特に電源ケーブルの有無は大きくて、安心してそのまま繋いで使い始められるのか、ケーブル類の選定で一手間あるのか(そして「とりあえず今起動したいだけ」ならそのへんのを使うのだけど、ずっと運用する場合はそのケーブル類が占有されるので、結局最初から専用の電源ケーブルがあったほうが良い)。
ラズパイ専用として色々売られているので、適当なものを買って専用に使えば良いのですが、こんどは「単なるUSB給電だよね? コンセントの挿し口を1台でひとつずつ塞がないで、こう、5台分くらいまとめてケーブル出せるのないの?」などと思ってしまい(いや、そういう風にできるのは分かるんですけど、どの組み合わせがいいかを考えるのが面倒)、やはり「電源ケーブル付属ですぐ使える」ことが私にとっては大きかったのだなぁと感じた次第。

あとは、IPあどれすどうする問題をその都度適当にやっていたのが、今回、家の中でのサーバが使うIPアドレスルールを決めたので、今後ちょっとはラズパイでもやりやすくなってきたかもしれません。
→追記: 電源ボタンについて、↓こちらの記事で「ラズパイ5には待望の電源ボタン」と紹介されていました。自動起動は今まで通りとのことで、私のほしい電源ボタンは「点けるときはボタンを押す、落とすときは shutdownコマンドを実行する(ボタンは押さない)」なので、ちょっと思惑とは異なりました。しかし、あれだ。これなのかぁ(手元のラズパイ5のお尻部分の小さなボタンを見つめながら)。
pc.watch.impress.co.jp

Beelinkの小型PCにUbuntu 24.04 Server

購入してあったものの1年近く放置していた、Beelinkの N100 PCをようやくこの連休に開封しました。Ubuntu 24.04 LTS がリリースされたところだったので最新のサーバ替わりとしてちょうど良いかなとも思い。
最近サーバの設定とか、あまりやっていないし、まぁ色々今回固有のハマり事象もあったので、記録として書いておこうと思います。
必ずしも正確なことではなく、「結果オーライ」「想像」もたくさん含まれていますので、参考にされる方がいたらその辺お含み置きください。

ハードウェアスペック

 Beelink EQ12 W11 というミニPCです。 N100(3,4GB)、メモリ16GB、SSD(NVMe) 500GB。LANx2, HDMIx2, USB 3+C1、Wi-fi

最初の壁:Ubuntu 24.04 インストールできず

 
Ubuntu 24.04 Server の .isoをUSBに焼いて、EQ12に挿して起動。言語やキーボードを順調に設定して、ミラーサーバの確認をしている画面で Done を押した瞬間、こんなダイアログが。

"Sorry, the installer has encountered an internal error."

いわゆる500エラーですな(違います)。
何度やっても確実に再現する中、この画面に来る直前に起動USBメモリを抜いておくと突破できることを発見。
しかし、数画面続行すると突然 internal error のダイアログが出てくるので、本質的には何の解決にもなっていない模様。

ログを眺めてみたところ、こんなエラーが目についたけど、関係あるのかないのかすら判断つかず。

一応ログを "Send to Canonical" して、インストールはあきらめました。

Ubuntu 22.04 を入れることに

そんなわけで別途、Ubuntu 22.04 Server の .isoをUSBメモリに焼いて、インストール。こちらは問題なくインストール完了。
ただし、Wi-fiのチップが 22.04のカーネルでは(?)対応してないようで、Wi-fiバイス認識せず有線LANにて。
カーネルを上げれば・・・という話も目にしたので上げてみたのですが、慣れない作業でもあり結局適切なドライバにたどり着けず、そちらは諦め。
とりあえず一旦、有線LANで利用できる「My Ubuntuサーバ」ができあがりました。めでたしめでたし

やっぱり 24.04にしてみたい

アップグレードならできるかも、ということで do-release-upgradeを。

# do-release-upgrade -c
Checking for a new Ubuntu release
There is no development version of an LTS available.
To upgrade to the latest non-LTS development release 
set Prompt=normal in /etc/update-manager/release-upgrades.

しかし、まだ来ていなかった。 
https://discourse.ubuntu.com/t/noble-numbat-release-notes/39890 によると、少し経って安定が確認できてからになるらしい。

/etc/update-manager/release-upgrades で、対象をltsではなくnormalにしたところ

# do-release-upgrade -c
Checking for a new Ubuntu release
New release '23.10' available.
Run 'do-release-upgrade' to upgrade to it.

と、非LTSは見つかったけど、24.04にはならない。

余談:22.04では使えないwi-fiが、24.04では使える

 このマシン、22.04ではwi-fiが使えないっぽいです。ということを、インストーラの中でも確認できたので、画像で紹介。
24.04 のインストーラでは、ネットワーク接続設定の画面に wlo1 (wi-fiのインタフェース)が登場していることがわかります。

22.04 → 24.04への救世主登場

まぁしばらく 22.04 でいいかと思い始めたとき、@tmtms さんがこんなリンクを教えてくれました。

tech.buty4649.net

リリース一覧の設定ファイル(アップグレードの確認時に参照されるもの)に最新 nobleの設定を加えたファイルを用意してくだしました。ぶていさん( @buty4649 )ありがとうございます!


書いてあるとおりに /etc/update-manager/meta-release での参照先を変更するよう編集し、do-release-upgrade。 なんか、すぐには出てこなかったのですが、ごちゃごちゃやっているうちに 24.04が出てきて無事アップグレードできました! wi-fiバイスも認識してくれて(この後こまごまと設定をするのですが)、最終的には 電源1本つないで立ち上げるだけで稼働するおうちサーバができあがりました。快適。

もう一台では24.04を見つけてくれず

こんな作業をしているさなか、Amazonさんを開いたら、似たようなスペックのものが 2.5万円程度で買えるのを見てしまいました。気づいたらぽちっと(笑)。
こちらBeelink mini S12 というミニPCで、スペックは N100(3,4GB)、メモリ16GB、SSD(NVMe) 500GB。LANx1, HDMIx2, USBx4、Wi-fi

こちらのマシンにも同様に Ubuntu 22.04 インストール→meta-releaseを書き換えて24.04へのupgradeを試みたのですが、do-release-upgradeしても一向に24.04を見つけてくれない(アップグレード対象はないよ、と出力される)。業を煮やして、ぶていさんが作ってくれた設定ファイル(LTSのほう)の jammyとnobleの部分を手元のファイル( /upgrade_list.txt とか)に書いて、 meta-releaseの ltsの行を file:///upgrade_list.txt を指し示すよう書きかえて、無事認識してもらえました。 キャッシュ的なものが効いちゃってるんですかね。。

そんなわけで

非常に快適な遊びサーバが2台、おうちの中にできあがりました。
24.04のタイミングで作業に取りかかったのが幸いだったなと思うのが、24.04でこのマシンの wi-fiに対応してくれていたこと。 22.04の時に触っていたら有線での運用になっていたので、快適さ半減だったことでしょう。
改めて、Twitter(X)で色々教えてくれたとみたさん、meta-release作成して方法を公開してくれたぶていさん、ありがとうございました!!

マシンの事とか、その後の設定のこととか書こうと思っていたのだけど、長くなったので本エントリは 「Ubuntu 24.04 をインストールできた!」という話題で一旦まとめておきたいと思います。マシンや設定については別の記事を書こう。

Re:mysqlでuserテーブルにダミーデータを10万件ほど入れる方法メモ

たまたま目に入った記事で、「mysqlでuserテーブルにダミーデータを10万件ほど入れる方法メモ」というものを拝読しました。
zenn.dev

そちらでは、ストアド・プロシジャを作成して10万回のループでINSERTを実施する方法を採っていましたが、折角の機会なのでお伝えしたいテクニックがあり、これを書きしたためる次第。

プロシジャってあまり使いたくない

 「実現できない」より「実現できる」ほうが100万倍エラいので、プロシジャを使った解法にたどり着いたことは立派なのですが、「実現できる」から「もっと良く実現できる」に進むと更に楽しい世界が待っています。ということで「もっと良く」を紹介します。

私はこの手の処理にあまりプロシジャ使用を選択したくないのは:

  • 使い捨ての処理なのに、プロシジャというオブジェクトをサーバ上に生成したり、消し忘れてゴミオブジェクトとして残ったりするのがイヤ
  • そもそもプロシジャ作るのが面倒(普段あまり作らないので)。デリミタ一時的に変えるとかからして、無駄な作業をしている感がキライ
  • ぐるぐる系の処理はヤだ(INSERTを 10万回もやるなんて全然DB的発想ではない)


といった理由があります。実際、このプロシジャ版を手元で動かしたところ、4分近くかかりました。

テーブル定義とプロシジャおよび実行方法を https://zenn.dev/mesi/scraps/48b6479d21e00d から引用します:

CREATE TABLE `user` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `name` VARCHAR(50),
  `email` VARCHAR(100),
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
DELIMITER $$

CREATE PROCEDURE InsertDummyData()
BEGIN
  DECLARE i INT DEFAULT 1;
  WHILE i <= 100000 DO
    INSERT INTO `user` (`name`, `email`)
    VALUES (CONCAT('User ', i), CONCAT('user', i, '@example.com'));
    SET i = i + 1;
  END WHILE;
END$$

DELIMITER ;
CALL InsertDummyData();

それ、CTEでできるよ!

こういう処理を行いたいときに便利なのが、CTEです。
白状すると、私もこの構文の書き方をすぐに忘れてしまうので、書く前に毎度検索しています(笑)。

set @@cte_max_recursion_depth=100000

INSERT INTO user (name, email) 
WITH RECURSIVE num(n) AS (SELECT 1 UNION ALL SELECT n+1 FROM num WHERE n<100000)
SELECT CONCAT('User ',n), concat('user',n,'pexample.com')  FROM num;

最初の set文は、cteの実行可能段数を緩和するための指定です。

笑っちゃうと思うんですけど(というか盛大に笑っちゃってほしいんですけど)、先ほど4分かかったこの処理が、どれくらいの時間でできると思います?

手元の環境では、 0.6秒程度でした。


私が若手の頃に、1時間半近くかかっていたクエリ(PL/SQL)が、ちょっと考えて処理の仕方を工夫したらヒトケタ分になったこと(その後更に改善して1分以内になったはず)という、工夫次第でダイナミックに効果が出る体験をしたことがきっかけで、DBMSの世界って面白いなと感じたので、これはそれに匹敵するくらいの差と言えるんじゃないかなと思います。

まとめ

ということで、

  • ぐるぐる系よりガッツリ系
  • CTE知ってると便利
  • 構文覚えてさらさら書けると格好良いけど私は覚えてないw

でした。


MySQL 8.4-LTSがやってきた&native_passwordに注意

お待ちかねの「MySQLのはじめての LTS」、MySQL 8.4.0 がリリースされました!

Note: If you are having trouble connecting to the upgraded MySQL 8.4.0 Server, please read the end of this blog.
(Summary of solutions for "unknown variable 'default-authentication-plugin" error, and "mysql_native_password' is not loaded" error)



status表示に、特に「LTS」をあらわすものはないようです。(variablesを確認していて「お。LTSって書いてあるじゃん!」と思ってよく見たら TLS でした。関係ない)

インストール

手元の Ubuntu 20.04の、既に MySQL 8.3.0が稼働している環境を、今回アップグレードしました。
apt update しても降ってこなかったので、リポジトリ側に 8.4-ltsが存在しているのを確認して(そう、この名前には「lts」がついているのです)、

Origin: MySQL
Label: MySQL
Codename: jammy
Architectures: i386 amd64 source
Components: mysql-apt-config mysql-8.0 mysql-8.4-lts mysql-innovation mysql-cluster-8.0 mysql-cluster-8.4-lts mysql-tools mysql-cluster-innovation mysql-tools-old mysql-tools-preview
Description: Apt repository for Oracle MySQL packages
SignWith: B7B3B788A8D3785C

手動で、/etc/apt/sources.list.d/mysql.list を書き換えちゃいました。

deb [signed-by=/usr/share/keyrings/mysql-apt-config.gpg] http://repo.mysql.com/apt/ubuntu/ jammy mysql-8.4-lts

そして、apt update / apt upgrade。

サーバが起動しない

アップグレードは完了したものの、mysqldサーバが起動しません。

2024-04-30T07:13:31.955229Z 0 [ERROR] [MY-000067] [Server] unknown variable 'default-authentication-plugin=mysql_native_password'.
2024-04-30T07:13:31.955815Z 0 [ERROR] [MY-010119] [Server] Aborting

数ある my.cnf 系設定ファイルを手繰っていき、私は /etc/mysql/mysql.conf.d/default-auth-override.cnf に、この設定があるのを見つけました。これをコメントアウト。サーバ再起動(というか起動)。

# This file is automatically generated by MySQL Maintainer Scripts
[mysqld]
default-authentication-plugin = mysql_native_password

クライアントから接続できない

サーバ起動指示はエラーなく完了したものの、こんどは mysql コマンドでの接続でエラーが発生しました。もうやだ。

ubuntu@vmubuntu:~$ mysql -uroot -p
Enter password: 
ERROR 1524 (HY000): Plugin 'mysql_native_password' is not loaded

救世主登場:


いとうさんありがとう!!!!!

ということで、どこの設定ファイルでも良いのだけど、先ほど設定をコメントアウトしたファイルがちょうどいいや、と(ファイル名も default-auth なので意味は間違ってないし)そのファイルに mysql_native_password=ON の記述を追加。mysqld再起動。
再度 mysql小文字5文字クライアントから接続

ubuntu@vmubuntu:~$ mysql -uroot -p
Password:
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 9
Server version: 8.4.0 MySQL Community Server - GPL

Copyright (c) 2000, 2024, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> 

やったー!!

Conclusion

まとめると、

  • サーバが起動せず「unknown variable 'default-authentication-plugin=mysql_native_password'.」のエラーメッセージが表示されていたら、my.cnf系のどこかの設定ファイルに 「default-authentication-plugin = mysql_native_password」の設定があるので、コメントアウトしろ
  • その後クライアントからもつながらないと思うので、さっきコメントアウトしたファイルあたりにでも、「mysql_native_password=ON」の指定を追加しろ

ということになります。MySQL 8.4.0、いきなり動かなくて焦りました(笑)。


-

After upgrading MySQL 8.4:

  • If the server does not start on error "unknown variable 'default-authentication-plugin=mysql_native_password'.", you shoud comment out the "default-authentication-plugin = mysql_native_password" setting in my.cnf or in other cnf file.
  • After that, you will not able to connect the server by using mysql command-line client on error "ERROR 1524 (HY000): Plugin 'mysql_native_password' is not loaded", then, you should add "mysql_native_password=ON" to the cnf file.
  • After rebooting mysqld, you can connect the MySQL 8.4.0 LTS! Enjoy it!!

追記

yyamasakiさんからも、この変更に関するリリースノートの記述を補足いただきました。

SQLパズル:離れ島を探せ!

SQLの未解決問題(坂井調べ)。解けたらIQ180! さぁキミはできるかな!?

・・・・とくだらないサイトの煽り文句のような出だしですが、「SQLで書けそうなのにうまく書けない」テーマがあります。ウデに自信がある人も自信がない人も、頭の体操として取り組んでいただき、教えて戴けると、わたしがとっても助かります(笑)。
書けそうなのになぁ・・・なにか私がちょっとしたところで勘違いしちゃっているだけのような気がするんだけどなぁ・・・・

追記:このエントリでは、「面を構成する辺」に注目して書いていますが、実際には(今回の目的である)MOJデータでは辺IDは共有されていないことがわかりました。そのため一番下のサンプルデータでは「面を構成する点」に話を置き換えていますが、本質的には違いはありません。

データ構造に関する説明

基本的な要素

「辺」に囲まれた「面」をあらわすデータです。面にはIDがあり、また、各辺にもそれぞれIDが付けられています。
以下の図は、面1 が、 辺101,102,103,104,105に囲まれて成り立っているものです。

データにするとこんな感じ。

面ID 辺ID
1    101
1    102
1    103
1    104
1    105
面はたくさんある

面は、辺を共有する形で複数(たくさん)存在しています。

先ほどの面1のデータに加えて、こんな感じ。

面ID 辺ID
2    104
2    106
2    107
2    108
4    105
4    110
4    111
4    112
4    109
:

(面3は図の中に辺IDをひとつ書き忘れてしまったので気にしないでください。。面3と面6で共有している線ね)

「島」の存在

さて、上の例(面1~面6までがある状態)のようにひとかたまりになった状態を「島」と呼びます。実はこのデータは、すべてがひとつの「島」になっているわけではなく、多数の「島」に分かれています。以下の図のように。

データは、これまで例示したように淡々と、面IDと辺IDのセットが格納されています。

さて問題です

ここで、出題です! 
本質的には同じ課題のような気がしますが、いくつかの設問にしてみました。すべてSQLで回答してください。

【問1】データはいくつの島から成り立っていますか(いくつの島に分かれていますか)

【問2】任意に「島ID」を決めて、面ID+島IDのリストを返してください。つまり一つの島の中にどの面が含まれているのかを知ることができる結果となります。島IDは構成する面のなかからMAXやMINなどで適当に決めたり、適当に連番にしてみたり等、なんでも構いません。

これはなに

お気づきの方もいるかもしれませんが、この設問、MOJ-XMLデータ(登記所備付地図のデータ)で遊ぼうとしたときにぶち当たってしまった課題でした。できると思っていたのになぁ(まだ言ってるw)。
実際のデータは、この「面」が数億件あります。
今回の「パズル」は課題をシンプル化しましたが、実際には各「面」にはその情報が書かれていた「ファイル」があって、そのファイルごとに「島の数」を求めたいのでした。その辺は、このパズルが解決すれば応用できることでしょう。


ということで、面白おかしく書いていますが、今日1時間くらい考えてこれを解決できなかったことが本気で悔しくてしょうがないので、解決できた方はぜひ披露してください!


サンプルデータ

実際のデータがないと萌えないよ、という人がほとんどだと思うので、用意しました(6月末までの期間限定公開)。5459件のデータです。
データに誤りがありました。というか、このエントリで説明してきた「辺IDが共有されている」は正しくなかったです(同じ辺でも別のIDが振られていた)。
実際に試して、ご指摘くださった皆さんありがとうございました。

ということで、改めてテーマを微修正します。面を構成する辺ではなく、面を構成する「点」に注目することにします。点IDは共有されていることを確認済みです。
(今回のデータでは例えば、F000000007と008で共有点が2つ、F000000006と007で共有点が2つ、などちゃんと重なっています!)


CREATE TABLE と INSERT文ですが、さくっと作成しただけで自分では動作確認していないので、誤りがあったらすいません適当に修正して使ってください(データは間違いないです→データは正しかったのだけどそもそも「辺IDが共有される」という発想自体が間違っていた...)。
surface_idが「面」のID、curve_idが「辺」のIDです。numはその面の中でのその線の順番を表すもので、今回は気にしなくて良いです。

面と点の関係にしたデータダウンロード(データセット2 :益城)ただしMAPPLEビューワの情報と変化している可能性あり
https://www.dropbox.com/scl/fi/9bce437lldnhuhm8o4z53/sql_quiz_moj_data_20240430b.txt?rlkey=6hxui0za4l67iv8iitygqyz35&dl=0

面と点の関係にしたデータダウンロード(データセット3 両国):
https://www.dropbox.com/scl/fi/fm5gl36pu1m356vhai2bx/sql_quiz_moj_data_20240430c.txt?rlkey=j0mi9kepm0lpqc7o4p7g96byn&dl=0


ダウンロードURL: https://www.dropbox.com/scl/fi/zut7jh73lnet1hmkr9uyo/sql_quiz_moj_data_20240430.txt?rlkey=dbxb6v0gwyo6itf5jx5hgdhbm&dl=0

このデータが表す地域は、以下のような島を表しています (MAPPLE法務局地図ビューワより*1)。
ただし、MAPPLEさんのビューワは現時点で 202308データのため、今回の 202404データとID等は異なっている可能性があります。

両国はたぶん変わってない。

なんだこりゃ。SQLの「IS DISTINCT FROM」演算子

発端

ふと、「IS NOT DISTINCT FROM」あるいは「IS DISTINCT FROM」という文字列が含まれるSQL文を目にしました。
SQLの基本的な構文として、

SELECT DISTINCT col1, col2 FROM t1; 

とか書くので、そのDISTINCT と FROM が、、、、とか考えていると混乱します。とりあえず「単にこういう長い名前の記号」と思っておくのがよさそう(笑)。

これはなに

Oracleから入り、その後MySQLをメインとするようになった私の通った道には、こんな構文はなかったわけです。それにしても、SQLiteでさえ(←ひどい言い草)対応しているのにMySQLにないってのは、ちょっと悔しい(笑)。

PostgreSQL 16 で動作を確認

こんなデータを作った。

\pset null (null)
create table t1 (id integer, s varchar(10));

SELECT * FROM t1;
 id |   s    
----+--------
  1 | AA
  2 | CBB
  3 | AA
  4 | CC
  5 | 
  6 | CC
  7 | 
  8 | (null)
  9 | (null)
(9 rows)

単純に結合するとこんな感じ(自分自身との結合を除外)。

db=# SELECT ta.*, tb.* FROM t1 ta, t1 tb WHERE ta.id<>tb.id;
 id |  s  | id |  s  
----+-----+----+-----
  2 | CBB |  1 | AA
  3 | AA  |  1 | AA
  4 | CC  |  1 | AA
 :
  5 |     |  9 | 
  6 | CC  |  9 | 
  7 |     |  9 | 
  8 |     |  9 | 
(72 rows)

値が同じのだけを抽出したい場合は、(今までの私の発想だと)イコールを使う。ここに null のものは現れない。

db=# SELECT ta.*, tb.* FROM t1 ta, t1 tb WHERE ta.s = tb.s AND ta.id<>tb.id;
 id | s  | id | s  
----+----+----+----
  1 | AA |  3 | AA
  4 | CC |  6 | CC
  5 |    |  7 | 
  3 | AA |  1 | AA
  6 | CC |  4 | CC
  7 |    |  5 | 
(6 rows)

IS NOT DISTINCT FROM を使うと、nullも「nullという値だとみなして」一致比較をしてくれる。「nullという値」というとっても気持ち悪いパワーワードですが「みなして」ということで我慢することにします:-)

db=# SELECT ta.*, tb.* FROM t1 ta, t1 tb WHERE ta.s IS NOT DISTINCT FROM tb.s AND ta.id<>tb.id;
 id |   s    | id |   s    
----+--------+----+--------
  3 | AA     |  1 | AA
  1 | AA     |  3 | AA
  6 | CC     |  4 | CC
  7 |        |  5 | 
  4 | CC     |  6 | CC
  5 |        |  7 | 
  9 | (null) |  8 | (null)
  8 | (null) |  9 | (null)
(8 rows)

余談

本題ではないのだけど、今回試したクエリで、行きと帰りの一致データが重複して出ているのが気になりますよね。
今回は「IDが一致しないもの」ということで自分自身との結合を除外しましたが、この条件を「IDが自分よりも小さいもの」とだけ比較するようにすると片道切符になります。

db=# SELECT ta.*, tb.* FROM t1 ta, t1 tb WHERE ta.s IS NOT DISTINCT FROM tb.s AND ta.id<tb.id;
 id |   s    | id |   s    
----+--------+----+--------
  1 | AA     |  3 | AA
  4 | CC     |  6 | CC
  5 |        |  7 | 
  8 | (null) |  9 | (null)
(4 rows)

最初からこちらでやっていたほうが、結果がシンプルになりましたね。


応用例

IS NOT DISTINCT FROM ではなく IS DISTINCT FROM を使うと、不一致のものにマッチさせることができます。
たとえば、値が'AA'のものと、これに一致しないもののIDの対応表を作りたい時に、こんなふうに。
対象としてnullも含まれるところが、新しいところです(べつに新しくないのですが、= とか <> しか知らなかった私には新しい)。

db=# SELECT ta.*, tb.* FROM t1 ta, t1 tb WHERE ta.s='AA' AND ta.s IS DISTINCT FROM tb.s AND ta.id<tb.id;
 id | s  | id |   s    
----+----+----+--------
  1 | AA |  2 | CBB
  1 | AA |  4 | CC
  1 | AA |  5 | 
  1 | AA |  6 | CC
  1 | AA |  7 | 
  1 | AA |  8 | (null)
  1 | AA |  9 | (null)
  3 | AA |  4 | CC
  3 | AA |  5 | 
  3 | AA |  6 | CC
  3 | AA |  7 | 
  3 | AA |  8 | (null)
  3 | AA |  9 | (null)
(13 rows)

まとめ

今回の構文が長いので IS DISTINCT FROM を [コレ] と書くとすると、
A [コレ] B は、AがBと違っているときに成立(true)、
A NOT[コレ] B は、AがBと違ってないとき(笑)、、、つまり同じ時ですね、、に成立。(正確にはNOTの位置は IS NOT の場所になります)

既にSQL構文の中で使われている DISTINCT とか FROM という単語をこういう形でまったく違う用途で使うのって、仕様決めた人はセンスないよなと私は思ってしまうのですが、たぶん私など理解不能なレベルで考えぬいた末に、きっとセンスの塊の成果として決まったものだと思います(思いたい)。

軽く調べてみたのですが、 SQL-92には含まれていなくて、SQL-1999で登場した記法のようです。
近年のMySQL開発はどんどん、なるべく標準に準拠するようにとうことを心がけているように見えるので、そのうちこの構文が導入されたりするのかな。新機能好きなので楽しみに待ちたいと思います。導入されたら「知ってる!知ってる!導入前から注目していたんですよ!」と自慢できるように、このエントリを書いておきました(そんなわけじゃないw)。




参照

Twitter(X)で色々おしえてもらいました。ありがとうございました!

Snowflakeのマニュアル、わかりやすい。
https://docs.snowflake.com/ja/sql-reference/functions/is-distinct-from


追記

要するにこういうことなのか。
何か新しい事ができるようになったというよりは、一種のシンタックスシュガーと捉えても良いのかな。
(ここで「絶対に既存のデータと被らない置換文字列」を決める部分で「絶対」を保証できないので、IS DISTINCT FROM の存在意義が出てくるわけですが)

db=# SELECT ta.*, tb.* FROM t1 ta, t1 tb WHERE COALESCE(ta.s,'*+;:')=COALESCE(tb.s,'*+;:') AND ta.id<tb.id;
 id |   s    | id |   s    
----+--------+----+--------
  1 | AA     |  3 | AA
  4 | CC     |  6 | CC
  5 |        |  7 | 
  8 | (null) |  9 | (null)
(4 rows)
db=# SELECT ta.*, tb.* FROM t1 ta, t1 tb WHERE ta.s=tb.s AND ta.id<tb.id;
 id | s  | id | s  
----+----+----+----
  1 | AA |  3 | AA
  4 | CC |  6 | CC
  5 |    |  7 | 
(3 rows)
db=# SELECT ta.*, tb.* FROM t1 ta, t1 tb WHERE ta.s IS NOT DISTINCT FROM tb.s AND ta.id<tb.id;
 id |   s    | id |   s    
----+--------+----+--------
  1 | AA     |  3 | AA
  4 | CC     |  6 | CC
  5 |        |  7 | 
  8 | (null) |  9 | (null)
(4 rows)


ジオイド2024(試行版)が公開された!

2024年3月27日に国土地理院から『「ジオイド2024日本とその周辺」(試行版)』が公開されました。
日本における各地点の「高さ(標高)」を知る上で大きな進化であり、旧来の苦労して実施していた(らしい)基準点から繋げてきた水準測量からの開放を意味するパラダイムシフトであると感じ、大変興奮しています。
www.gsi.go.jp

なお、このエントリは私の「おべんきょう」の結果を整理するとともに、まだこの情報に触れてない人に概要が伝わったらいいなとの試みとして書いているものです。誤りや思い込みを含んでいるかもしれません。正確かつ詳細な情報は上記リンクで公開されている各種情報を参照いただけたらと思います。

前提知識:標高って何?

 あの山は標高何メートル、この場所は標高何メートルと言いますが、意外と正確に知らない人が多いのが、この「標高」というものです。

  • 標高が同じ2地点間では水は流れない
  • 標高が異なる2地点間では、水は標高が高いところから低いところに流れる

当たり前ですね。この当たり前を一旦、頭の中に置いておきましょう。

標高、というと「ゼロメートルはどこ?」という話をする必要があるのですが、これは後でお話するとして、「2地点間の高さの差」を求める方法について簡単に書くと、比較したい2地点に長い定規を立てて持ってもらって、真ん中くらいの地点から平行にそれぞれの定規の目盛りを読めば、2地点間の高さが分かります。これを繰り返していって日本中の標高が決まるのです。
この測量のスタート地点は「日本水準原点」24.3900メートル(2024年3月現在)です。

余談:海抜ゼロメートルはどこ

「海面の高さがゼロメートルです」と言っても、1日の中でも変動するし、月の中でも変化しますよね。日本では「東京湾平均海面」をゼロとすると定められています(法律上は「東京湾平均海面上二十四・三九〇〇メートル」を原点とすると定められていますが、つまり平均海面をゼロメートルとする、という意味です)。明治初期に6年位かけて観測し、決定されました。
隅田川の河口にある霊岸島で測られていたのですが、その後の都市開発で(当時は海だったのに)今は全然海じゃなくなって川の途中みたいになってしまったので、今は神奈川県の油壺で検潮しています。


前提知識: 「平ら」は平らじゃない

地球を回転楕円体としてモデル化しましょう。高さの差の測定は、先ほど「平行にして(2地点の)定規の目盛りを読」む、と書きました。実はこの「平行」というのがクセモノです。回転楕円体表面の曲面を無視して平面として扱える局所的な話として考えてみます。先ほどの「平行」とは、この平面に対して平行なのでしょうか。
いや、当たり前すぎることを質問されて意味がわからないだろうなと思います。平行なんだから平行でしょう、と。平行じゃないのは平行と言わない。・・・ですよね。  ところが違うんです。

地球というのは、場所によって重い物質や軽い物質が不均一に存在しています。そして、重い物質がある場所はより強い重力となります。私たちが「平行」だと判断する根拠は何かを考えみましょう。そう、重力ですよね。自分の右側のほうの地下に赤色物質(あるいは超小型のブラックホールのようなもの)があるとすると、そちら側に引っ張られるので、立っていても右斜め下のほうが「真下」と感じるはずです。重りを付けた紐を手に持っても右下のほうにひっぱられます。
といいつつ実際には、感覚的には「真下(と自分が感じる方向)にちゃんと重りの糸が垂れ下がってる」というだけなのでまったく違和感は感じません。単に、回転楕円対面とは平行ではない、というだけのことです。私たちは地球表面に居て、ただ重力のベクトルが向く方向を「下」だと思っているだけなのです。

前提知識:同じ標高面は回転楕円体面に対してでこぼこしている

そんなわけで、地球内部のあちこちに小さな赤色物質があると想像すると、「同じ標高を結んだ線」は回転楕円対面のように滑らかにはならずに、でこぼこしたものになります。このでこぼこの形を知るための数値が「重力」です。 実際には「右斜め下に重力が強い部分がある」という測定をするわけではなく、たくさんの「その場所」の重力を測定します。地図上にプロットすれば「自分より右側の方が重力強い場所なんだな」と分かりますよね。
日本では先ほど紹介した東京湾の平均海面を標高ゼロメートルとして、これと同じ高さを結んだ面を「ジオイド面」と呼んでいます。いわば「標高ゼロメートル面」です。回転楕円対面に対して結構デコボコしています。
「標高」というのは、回転楕円対面からの高さではなく、このジオイド面からの高さなのです。

これまでの重力の測定

あまり詳しくないので、今回の公開資料からの受け売りです。
重力を正確に測定するためには、今までは陸上の場合はその場所に行って実際に測定するしかありませんでした(海上は船で)。この正確な測定は「短波長な測定」と言うそうです。衛星でもある程度は測定できるのですが「長波長の測定」と言って、精度(有効桁数)が高くないとのこと。
どうしても山の中では観測ポイントは少なくなりますし、何度も測定に行けないのでかなり古いデータもあるのが現状でした。

今回のすごいデータ!

国土地理院では、航空機を用いて重力を測定しました。2019年から4年間に亘って、飛行距離約14万km、総飛行時間1300時間以上の測定を行ったそうです。測定の線数600本近く。これで日本周辺の重力を正確に知ることができました。
www.gsi.go.jp

測定は2023年の5月に終わったのですが、情報の整理や様々な資料を整備していたのでしょう。このたび2024年3月に公開されたのが、この測定結果を使って求めた「ジオイド面」のデータなのです。いやはや、ついに来た。網羅的に測定された重力データ(を元にしたジオイド面のデータ)。これは「高さの伊能図」と言って良いくらいの前進ですよ。すごい。しかも伊能図と違って国家機密でもなく、誰でもアクセスできるデータです。

ここ大事なので、今回の国土地理院「補足解説」資料から引用します。

今回公開されたジオイド面のデータ

GSIGEO2024beta.isg というファイルとして、北緯15度から50度、東経120度から160度の範囲のジオイド高の値が公開されています。東西は0度1分30秒ごと、南北は0度1分ごとの値です。まずは雰囲気から。

ヘッダ28行に続いて、北緯50度のジオイド高が西から東への順に並んでいます。1.5分ごとなので1行あたり約1600のデータがあります。
これが北から南へ順に各行1.0分おきの値として、2100行ちょっと続きます。大きなマトリックスデータですね。なお、EPSG:6668=JGD2011地理座標系です。
(日本国外に相当する部分の値はどこから得ているのだろうと疑問を持ちました。ご存じの方、教えてください)

任意の地点のジオイド高の求め方

緯度1分経度1.5分ごとのポイントにおけるジオイド高のデータは公開されましたが、自分の知りたいポイントがぴったり公開された座標に当てはまることは稀です。 自分の知りたいポイントのジオイド高を得るには、バイリニア補間を行います。2次元の線形補間です。
まず自分の知りたい点の近傍4点を特定し、タテ方向の線形補間、ヨコ方向の線形補間を行います。(計算式はそれなりにややこしいですが、直感的にはそういうことです)

具体的な計算式を、公開資料「ジオイド2024説明書」から引用します。


これらの計算を行うことができるWindows用とLinux用のプログラムも公開されています。 geoidcalc_win64.exe とgeoidcalc_linux_x86_64 です。
入力ファイルに緯度と経度のセットを何行でも書いておいてプログラムに喰わせると、その右側にジオイド高を追加して出力してくれるというものです。今のところ私は大量の地点のジオイド高を求めたいシーンがないのですが、必要になったときに便利に活用させてもらえそうです。(自動処理を考えるとファイルへの出力ではなく標準出力に出せるようにしてもらいたいところですが、まぁこれくらいなら使う側の工夫でもなんとか)

まとめ

こんな感じで、「かなり正確な」ジオイド高データが公開されると、衛星測位で得た楕円体高から即座にその地点の「かなり正確な」標高が計算できるようになります。測量業務的には、全国に1000カ所以上ある電子基準点を標高の既知点として使えるようになることが大きそうです。今まで東京にある水準原点からの高さを測量していたので、遠くに行けば行くほど誤差が蓄積する(と言っても一桁センチメートル内ですが)のが、重力(ジオイド高)+GNSSによる楕円体高 での算出になることで、水準原点からの距離に関係なく標高を計算できるようになったのは、大きな変化だと感じました。
改めて今回の「試行版」の公開は、「高さに関する伊能図」並の革命だと思います。

最後に現時点の電子基準点の所在地を、地理院地図より引用。全国これだけの点が高さの既知点になる!