個性的なcsvからデータを取り出した話

コンピュータシステム周辺に関わっているとCSVファイルとのお付き合いは避けて通れないものと言えるでしょう。みなさんはどんなCSVとお付き合いしたことがありますか。
 セパレータが明確でない(というか明確なのだけど例外例外の積み重ねが意外とややこしい)のがcsvとのお付き合いで気を使うところですよね。

 単純に考えれば、コンマが出てきたら次の項目、改行により次のレコードだと思うじゃないですか。ところが、コンマが出てきても次の項目に移らなかったり、1つのレコードが複数行に亘って記述されたりすることもあります。そう、クォーテーションされている場合ですね。

 でも、この日記で紹介しようとしているのは、そんなレベルではない「個性的なCSVファイル」です。実際には「クソCSV」と呼んでいたのですが、日記に書くには品のない言葉なので、ここでは「個性的なCSV」と呼ぶことにします。 

ことの起こり

 先日参加したハッカソン、Geospatial Hackers Japan での開発で、名古屋地区の観光地情報が必要となり、教えてもらいました。ハッカソン参加に関する日記は、以下。

sakaik.hateblo.jp

 このファイルから、名古屋市内の観光地情報を抜き出して使える状態にする、というのが私の役割。
最低限、名称と緯度経度は取得したいが、状況に応じて他の項目も抜き出せるよう、「抜き出し作業」を行うことを目的とせずに、「抜き出すしくみ」を作ることにしました。 ハッカソンという限られた時間内での作業ですので、最高最適な方法であるかよりも、時間内での実現性を優先しての作業となります。

ザ・データの取得

 私を楽しませてくれることになるCSVファイルは、総務省の「公共クラウドシステム」で公開しているものです。このページは

本サイトは、2020年3月末日をもって閉鎖となりますので、ご利用にあたってはご注意願います。

 ということで、とりあえずデータを実際にみてみたいかたは、ダウンロードを急げ!

公共クラウドシステム | API公開サイト
f:id:sakaik:20200329181452p:plain

ザ・データの概要

 CSVファイルです。ざっと雰囲気だけキャプチャ画像で紹介すると、こんな感じ。

f:id:sakaik:20200329181344p:plain

 テキストでも紹介しておきます(膨大なので、間引き加工しています。画像のほうは間引きなし)。

"tourspots[0]","name","name1","written",,,,,,"夏まつり桂川2020"
"tourspots[0]","name","name1","spoken",,,,,,"なつまつりけいせん2020"
"tourspots[0]","genres[0]","L",,,,,,,"イベント"
"tourspots[0]","views[0]","copyright",,,,,,,"有"
"tourspots[0]","views[0]","fid",,,,,,,"base/40/421/40421KANKO2080556001.jpg"
"tourspots[0]","place","coordinates","longitude",,,,,,"130.6788888"
"tourspots[0]","place","coordinates","latitude",,,,,,"33.5791666"
"tourspots[0]","place","postal_code",,,,,,,"820-0696"
"tourspots[0]","place","pref","written",,,,,,"福岡県"
"tourspots[0]","place","city","written",,,,,,"嘉穂郡桂川町"
"tourspots[0]","place","street","written",,,,,,"大字土居424番地8"
"tourspots[0]","place","street","spoken",,,,,,"おおあざとい424ばんち8"
"tourspots[0]","visit","guide","note",,,,,,"8/22(土)【昨年内容】18:00王塚太鼓~
8/23(日)【昨年内容】16:50吹奏楽~17:20戦隊ヒーロー~18:05SUPER・BOWL~ 20:00閉会式"
"tourspots[0]","visit","service","periods[0]","type",,,,,"公開"
"tourspots[0]","visit","service","periods[0]","st_date",,,,,"2020年8月22日"
"tourspots[0]","visit","service","periods[0]","day_of_week",,,,,"土"
"tourspots[0]","visit","service","periods[0]","hours",,,,,"18:00~20:40"
"tourspots[0]","visit","service","periods[0]","note",,,,,"王塚太鼓演奏、盆踊り大会、お化け屋敷"
"tourspots[0]","wifi","established",,,,,,,"無し"
"tourspots[0]","mng","lgcode",,,,,,,"40421"
"tourspots[0]","mng","refbase",,,,,,,"40421KANKO2080556"
"tourspots[0]","mng","refsub",,,,,,,"2080556"
"tourspots[0]","mng","status","update",,,,,,"2019-10-23 13:46:46"
"tourspots[0]","mng","data_source",,,,,,,"福岡県嘉穂郡桂川町"
"tourspots[1]","name","name1","written",,,,,,"土師の獅子舞<土師老松神社>"
"tourspots[1]","name","name1","spoken",,,,,,"はじのししまい<はじおいまつじんじゃ>"
"tourspots[1]","genres[0]","L",,,,,,,"イベント"
"tourspots[1]","genres[0]","M",,,,,,,"祭事"
"tourspots[1]","genres[0]","S",,,,,,,"行事・祭事"
"tourspots[1]","views[0]","name","written",,,,,,"獅子舞(2014春 上土師地区当番)"
"tourspots[1]","views[0]","name","spoken",,,,,,"ししまい(2014はる かみはじちくとうばん)"
"tourspots[1]","views[0]","where",,,,,,,"土師老松神社"
"tourspots[1]","views[0]","copyright",,,,,,,"有"
"tourspots[1]","views[0]","fid",,,,,,,"base/40/421/40421KANKO2059825001.jpg"

 

 tourspots[] ごとに1つの観光地を表しています。ナニコレCSV

とりあえずMySQLに入れる

 いやもう、「こんなCSVがあるよ」と紹介したかっただけなので、続きを書くモチベーションもさほど高くないのだけど、折角なので、苦労話の一端だけでも書いておこうと思います。まずは、とりあえずMySQLに突っ込むことを目標にしました。 bash上で加工する手も考えましたが、「とりあえずRDBMSに突っ込めばなんとかなる」という自信というか、なんとなくSQL文は頭に浮かんでいたからです。

 そのためには、まずこの「個性的なCSV」をプレ処理する必要があります。改行が含まれていたりクォーテーション文字列内にコンマが入っているようなCSVファイルに対しては、Excelが非常に強力です。いきなりCSVファイルをダブルクリックしちゃだめですよ。「テキストファイルの読み込み」機能を使います。
 読み込ませたら、改行が含まれている項目の改行コードを除去して、INSERT文を作ります。この作業はさくっと使い捨てVBAマクロを書いて実施。

Sub MakeIns()
  Dim o As Worksheet
  Set o = ActiveWorkbook.Worksheets(2)
  Set ws = ActiveWorkbook.Worksheets(1)
  For i = 2 To 553444
    s = "INSERT INTO kanko (c1, c2, c3, c4, c5,c6,c10) VALUES ("
    s = s + "'" + ws.Cells(i, 1) + "',"
    s = s + "'" + ws.Cells(i, 2) + "',"
    s = s + "'" + ws.Cells(i, 3) + "',"
    s = s + "'" + ws.Cells(i, 4) + "',"
    s = s + "'" + ws.Cells(i, 5) + "',"
    s = s + "'" + ws.Cells(i, 6) + "',"
    tmpstr = Replace(ws.Cells(i, 10), vbCrLf, "\n")
    s = s + "'" + tmpstr + "');"
    o.Cells(i, 1) = s
  Next
  
  Set o = Nothing
  Set ws = Nothing
End Sub

できあがったSQLの例(冒頭数行):

INSERT INTO kanko (c1, c2, c3, c4, c5,c6,c10) VALUES ('tourspots[0]','name','name1','written','','','夏まつり桂川2020');
INSERT INTO kanko (c1, c2, c3, c4, c5,c6,c10) VALUES ('tourspots[0]','name','name1','spoken','','','なつまつりけいせん2020');
INSERT INTO kanko (c1, c2, c3, c4, c5,c6,c10) VALUES ('tourspots[0]','genres[0]','L','','','','イベント');
INSERT INTO kanko (c1, c2, c3, c4, c5,c6,c10) VALUES ('tourspots[0]','genres[0]','M','','','','祭事');
INSERT INTO kanko (c1, c2, c3, c4, c5,c6,c10) VALUES ('tourspots[0]','genres[0]','S','','','','行事・祭事');
INSERT INTO kanko (c1, c2, c3, c4, c5,c6,c10) VALUES ('tourspots[0]','views[0]','copyright','','','','有');
INSERT INTO kanko (c1, c2, c3, c4, c5,c6,c10) VALUES ('tourspots[0]','views[0]','fid','','','','base/40/421/40421KANKO2080556001.jpg');
INSERT INTO kanko (c1, c2, c3, c4, c5,c6,c10) VALUES ('tourspots[0]','views[1]','name','written','','','夏まつり桂川 盆踊り');
INSERT INTO kanko (c1, c2, c3, c4, c5,c6,c10) VALUES ('tourspots[0]','views[1]','copyright','','','','有');
INSERT INTO kanko (c1, c2, c3, c4, c5,c6,c10) VALUES ('tourspots[0]','views[1]','fid','','','','base/40/421/40421KANKO2080556002.jpg');
INSERT INTO kanko (c1, c2, c3, c4, c5,c6,c10) VALUES ('tourspots[0]','place','coordinates','longitude','','','130.6788888');
INSERT INTO kanko (c1, c2, c3, c4, c5,c6,c10) VALUES ('tourspots[0]','place','coordinates','latitude','','','33.5791666');
INSERT INTO kanko (c1, c2, c3, c4, c5,c6,c10) VALUES ('tourspots[0]','place','postal_code','','','','820-0696');
INSERT INTO kanko (c1, c2, c3, c4, c5,c6,c10) VALUES ('tourspots[0]','place','pref','written','','','福岡県');
INSERT INTO kanko (c1, c2, c3, c4, c5,c6,c10) VALUES ('tourspots[0]','place','pref','spoken','','','ふくおかけん');

 このINSERT文のファイルを UTF-8 で保存し、目視でざーっと眺めて、おかしな気がするところを修正します(1行まるごとが クォーテーションされているものがいくつかあったり、無駄な改行が除去しきれていなかったものがあったりしました)。このファイルは約58万行、スポット数で2万2千件ほどありますので、作業のやり方としては、テキストファイルを高速でスクロールさせながら視界の中に入る「なんかへんだぞ」を発見していく感じになります。少し経験が必要になる作業かもしれませんが、見落としても大概後続の処理でエラーになってくれるだけなので、適当なところでやめて、先に進みます。

 以下のテーブルを作って、作成したINSERT文を流し込みます。エラーが出たら原因を調べてやり直し、の繰り返し。

DROP TABLE IF EXISTS kanko;
CREATE TABLE kanko (
  c1 varchar(40),
  c2 varchar(40),
  c3 varchar(40),
  c4 varchar(40),
  c5 varchar(40),
  c6 varchar(40),
  c10 text);
CREATE INDEX idxc1 ON kanko (c1);

 なお、LOAD DATA を使わないのか、と思った人もいるかもしれませんが、(私にとっては)未知のデータに対しては DMLを使って実施したほうが、問題点の把握と追跡に利があると判断してのことです。ロードツールを使うと、エラーになったときに、何が気に入らなくてエラーにされたのかが見えにくいケースがあり、今回はその泥沼リスクを少しでも低減させる意図もあり、DMLを使いました(今回に限らず、SQL文を作成して作業することが比較的多いですね、私は)。

データの取り出し

 よく考えたら全然 CTE 使う必要がない(サブクエリで十分)だったり、もっとスマートな書き方がありそうだったりするので、我こそはという方はぜひお試しいただきたいところですが、私の考えた「とりあえずデータを取得できるSQL例」はこんな感じになります。

WITH CTE_name AS (SELECT * FROM kanko WHERE c2='name' AND c3='name1' AND c4='written')
    ,CTE_lon  AS (SELECT * FROM kanko WHERE c2='place' AND c3='coordinates' AND c4='longitude')
    ,CTE_lat  AS (SELECT * FROM kanko WHERE c2='place' AND c3='coordinates' AND c4='latitude')
SELECT n.c1, substr(n.c10,1,15) name, lon.c10 longitude, lat.c10 latitude
  FROM CTE_name n
    LEFT OUTER JOIN CTE_lon lon ON (n.c1=lon.c1)
    LEFT OUTER JOIN CTE_lat lat ON (n.c1=lat.c1);

 最低限の項目(番号と名前と緯度経度)だけを取得する例ですが、もっと必要な項目がある場合は適宜、CTE/SELECT句/JOIN を増やせば良いです。

いざデータ活用へ

 冒頭の話を覚えているでしょうか。今回は、この中から名古屋市の観光地情報だけが欲しかったのでした。ということで住所欄に「名古屋市」が含まれているものだけを抽出対象にすれば良いです。SQLラクですね。万歳。
 ・・・・・あれ? ない。。。1件もマッチしない。。。??
 SQLの書き方を間違えたのかと驚くも、もとのテキストファイルをgrepもしてみて、原因判明。


 名古屋市のデータ、入ってない!!


 想像するに、どうも総務省のこのデータ、各自治体に「データ提出依頼」でも出したりして、それに乗っかってくれた自治体の情報だけをそのまま載せたのではないかという気がします。名古屋市は誘いに乗らなかったのではないかと。


 教訓。 データを加工する前に、中に欲しいデータが含まれているかを確認しよう。

Geospatial Hackers Program に参加して優秀な成績を収めた話

 少し前の話になるのですが、Geospatial Hackers Program (GHP)というイベントに参加して来ました。実は、ハッカソンなるものに参加するのは初めてのことで、勝手も分からずに緊張していたのですが、素敵な仲間たちと出会い、心地よく参加させていただくことができました。

Geospatial Hackers Program とは

 公式サイトによると「多方面で注目を集める高需要スキル 「G空間技術」 をイチから学び、エンジニアの方ももそうでない方も、地域課題の解決や新規ビジネス創出に活かせる力を手にいれる2日間の集中プログラムです。 」とのことで、アウトプットそのものよりも、体験を通じて「学び」のきっかっけとすることを主たる目的としているようです。
 総務省の主催で、各2日間の開催。2日間で、学び(セミナーセッション)、アイデア出し、チーム作り、開発までを行います。今年(2019年度)は全国4カ所での開催計画。東海地区(名古屋)、北陸地区(富山・魚津)、関東(東京)、沖縄(沖縄)。残念ながら新型コロナウィルスCOVID-19感染拡大の影響で、東京開場での開催が中止となってしまいましたので、都合3カ所での開催となりました。各地の代表1チームを決め、「全国大会(Demo Day)」で発表ができます。
 私は今回、一番早いスケジュールだった(まだ猛威が「きざし」程度だった頃)名古屋に参加しました。

ghp.connpass.com

東海地区(名古屋)に参加

 なぜ千葉県に住む私が(東京でも予定されていたのに)名古屋に参加したのかというと、「遠出をしたほうが、2日目もちゃんと参加できるから」でした。ホテルにお泊まりしていれば、(それしかやることがないから)夜も作業ができるし、朝もちゃんと起きて行くことができる。東京会場などで、一旦家に帰ると2日目にまた行くのが面倒になっちゃいそうだな~との思いからでした。行き帰りの新幹線の中で他の開発作業が捗ったり、東京会場での開催が中止になってしまったりして、今回の「名古屋行き」は結果として良い判断だったようです。 

GHPの2日間(1日目)

f:id:sakaik:20200201095444j:plain
土曜日10:30にスタート。基本的には午前は自由参加の自習タイムで、初学者向けのお話を聞いたり、同じテーブルに座っている方々とおしゃべりしたり、ちょっと色々ためしているうちにお昼になりました。あ、そうそう。当日イキナリではなくて、事前にいくつかの技術要素についての説明資料(動画などもあり)が公開されているので、不安な人はそういったもので予習も可能です。
 お昼は各自で。下のコンビニの、豊富な種類のお弁当にしていた人も多かったようです。私も。
f:id:sakaik:20200201121244j:plain

 午後になり、本格スタート。技術要素、事例、研究などの発表を聴きながら、どんなことができるかなー、あんなことやってみたいかもー、とノーミソが刺激されます。15:00頃から、アイデアソン・タイム。同じテーブルに座っていた人たちと会話をしながらアイデアを膨らませていき、その後、各自ひとりひとりでアイデアを1枚の紙に書き出します。 このアイデア出しのしくみも、しっかりと工夫されていたのが興味深かったです(一人で考える→グループ内でお話する→またひとりで考える、、を何度か繰り返す)。
 紙に書き出したら紙はテーブルの上に置いたままで、一旦みんなで席を立ち、他の方のアイデアの鑑賞タイム。「いいな!」と思ったものに星印を付けていきます。星印が多かったアイデア(上位8つくらいでしたっけ?)は前に呼び出され、みんなの前で軽くアイデアの紹介発表。その後、自分が参加したいアイデアの所にそれぞれが集まって、チーム結成がほぼ16時(これとは別に予めチームを組んでから参加している人たちもいます)。そこから、21時頃まで各チームで作業し、1日目終了。
 呑みにいこーぜー、って言っていたのに、ちょっと目を離したすきに(会場を出る前の時点で既に)はぐれてしまって、一人寂しくついたてに挟まれながらラーメン。
f:id:sakaik:20200201221719j:plain

 

GHPの2日間(2日目)

 ホテルからの出陣なので、駅ナカでパンを買って参加。名古屋らしいものを発見して、ひとりテンションアップ(笑)。
f:id:sakaik:20200202101016j:plain
 午前午後と、チームで開発継続。お昼は昨日と同じコンビニ弁当ですが、名古屋スイッチが入った私は名古屋的なお弁当に(笑)。
f:id:sakaik:20200326181044j:plain
 16時ごろ各チームまとめに入りはじめ、17時から成果発表会。名古屋は14チームの発表があり、それぞれのコダワリと工夫を、それぞれ短い持ち時間の中でたっぷりと聞かせていただきました。みんな発表うまいなぁ。
 参加者による投票により、3位、2位、1位が発表されて、締めのお話を聞いた後、19時閉会。名古屋らしい晩ごはんを食べて、20:44ののぞみ号で帰りました。
f:id:sakaik:20200202195141j:plain

我がチーム

 アイデア出しの中で、あろうことか私のアイデアも、星をたくさんもらってしまいました。このアイデアに興味を持ってくれた人4人でチームを結成。(正確に言うと、アイデア出しの時に同じテーブルに居た方と表裏一体の案となったため、一緒にやりましょうということになり、アイデアのタイトルには偶々私のほうのを採用しただけ、とも言えます)
 星をたくさんいただいたアイデアの内容は、これ。出張などで出かけた際に、空港や新幹線に行く最寄りの駅であと少し時間ができたとき、どこかもう少し見て回れたら面白いのになぁ、と思うのに「乗り遅れ恐怖症」の私は、その場からなかなか離れられない。どこまで行けるか調べているうちにその時間を使い切ってしまうこともしばしば。そんなときに「あと1時間くらいあるんだけど」と入れたら、見に行ける名所がぱっとわかったらいいのにな、との願いです。 一応ハッカソンのアイデアらしく「そうすれば、地域に対する思い出が増えてイメージアップするし、経済効果も少しあるかもよ」と付け足したのは、我ながら、うまくこじつけたと思います(笑)。プラス思考の勝利。
f:id:sakaik:20200201175559j:plain


 チームは、フロント(地図に表示したり検索機能を作ったり)をやる人、サーバ側(データを蓄積してJSON等で返す)をやる人、データを作る人がきれいに揃っていて、非常にバランスの良いチームでした。私の役割は当然データ、、、だと思うでしょう? それが、何もやってないんだ(笑)。一応自己弁護すると、トライしてコケたほうのデータをいじっていたのですが、まぁ役に立ってないのは自信ある(自信を持つところではない)。あ、表示するための画像(色のついた丸)を何種類か作ったな。Python使って画像生成させるやつ。
 仕事では一人であれこれやってしまうことが多いので、自分が何もしないのにモノが形になっていくのを横で見ているのは、ちょっと新鮮で楽しかったです。
 
 結果は、1位獲得。プレゼンも説得力があったし、「魅せる」ために必要な機能を、実際に動く形で提供できたのが良かったですね。初期アイデアも、みなさんに高評価をもらった2つのアイデアを合体させたものなので、考えてみたら、そりゃパワフルだったよな、と。
写真はチームメンバーではなく、講評をくださっているメンターのみなさまです。
f:id:sakaik:20200202185529j:plain

 我が「帰る前」チームのほかでは、スキマウォークの案を進めれたチームが印象的でした。「最短で」というサービスは多くあれど、敢えて無駄な時間を使う(健康のために歩く)という発想は、とても私好みだったので、自分のアイデアが選ばれていなければ参加していたかも、でした。将棋で言えば、強いソフトは開発されているけど「ちょうどよく弱くする」技術というのは結構難しいようで(北陸先端大(JAIST)の先生が研究されていたと思いますが、すごく興味持っています。いつかお会いする機会があったらいいなぁ)、そういった「最短」「無駄のない」というキーワードから一歩置いた余裕のようなものが良かったです。

全国大会(Demo Day)

 そんなわけで、「東海地区代表」として参加することになりました。3月の Demo Day。
ghp.connpass.com

 東京が開催されなかったので、東海のほか、北陸、沖縄の代表チームのみなさんと。

 コロナ禍のため、開催そのものも危ぶまれましたが、発表団体のみが会場に集まり、聴衆はネットで視聴する「オンライン開催」の形となりました。この状況の中で開催形態に工夫を凝らし、そして実際に開催を決断してくださった主催者、運営者の皆様に感謝いたします。
 控え室では、各チームそれぞれ固まってお喋りしている感じだったのですが、開始前にはお互いにお話ができる雰囲気になってよかった! ご挨拶をしたところで開始時刻になってしまったのは「もうちょっと早く、こういう雰囲気にすればよかった!」と悔やむところではありましたが。もっと色々お話伺いたかったです。
f:id:sakaik:20200313135739j:plain

 発表は、沖縄チームさんが上手に発表コンテンツとしてまとめたな、というのが印象的でした。あったら便利そうで、使って見たくなる発表でした。北陸さんも、笑いポイントがいっぱいあって個人的には結構好きでしたよ(一番のツッコミどころは「イカって、そっちから攻撃弾を出すのかよ!」でした(笑))。

 我がチームは、本来4人のチームでしたが昨今の情勢から2人のみが参加でした。危機管理上の判断とは言え、正直なところ、心のどこかには「みんなで参加したかったな~」と寂しかった面もあります。憎っくきコロナ19め!!! (そして打上げとして、お気に入りのお肉屋さんにお連れしたかったです(笑))
 結果は「優秀賞」。「最」の字はつきませんでしたが、総務省さんの名前で「優秀な成績を収めましたので、ここに賞します」を頂戴しました。やっぱりちょっと嬉しい。

f:id:sakaik:20200313155539j:plain

まとめ

 今回はじめてハッカソンなるものに参加し、賞をいただくなど、とても楽しい思いをさせていただきました。チームのメンバにも恵まれて、すいすい物事が進んで行く過程にわくわくしました。一緒に、2日間、そして全国大会に向けての数週間を過ごさせていただいたメンバのみなさんに感謝です!ありがとうございました。
 一方で、バランスの取れないチーム、たとえばフロントを作れる人がいないとか、やりたい事はあるのにデータの扱い方が分からないとか、そうなってしまった時に、開発を進める2日間は結構しんどい時間になるだろうなとも想像しました。僧侶(または賢者)のいないドラクエウォークみたいな感じですかね。その中で頑張ってみる価値もあるだろうし、あるいはチームを組んだ(=仮決め)後、メンバのスキル情報をチーム内で交換し、もいっかい組み替え(=助っ人募集)のチャンスを作る手もあるのかな、とも思いました。いや、今回の場合は組み替えして、うちのメンバが他のチームに行っちゃったとしたら辛かったのですけど(笑)。

 ともあれ、無事に全日程が終わりました。2日間だけのつもりが約1.5ヶ月楽しませてもらえました。改めまして、メンバのみなさま、運営のみなさま、そして両会場でお会いし、お話させていただいた皆様、どうもありがとうございました!

Ubuntu仮想環境 "Multipass" にトライした話(2)Windows編

 レンタルサーバ上のUbuntuの上で multipass が動かなかったので、手元のWindows上で試すことにしました。とりあえずMySQLを動作させるところまでは成功。

環境・前提条件等

  • OS: Windows 10 Pro 64bit 1909
  • Memory: 十分にあるので細かいことは気にしない(32GB)
  • Disk: 十分にあるので細かいことは気にしない(>200GB)
  • CPU: i7-7700K@4.20GHz
  • Hyper-V: VirtualBox等を動作させるために、有効にはしない(有効にしてもVirtualBoxが動作するという話も散見されたが、私の環境では動作しなかったので、有効にしない)
    • multipass は基本的にHyper-Vを要求するが、VirtualBoxを使っての動作もするようなので、こちらを試みることにする(VirtualBox 6.1インストール済)
  • やりたいことは色々あるが、まずは1台のUbuntuを立ち上げてMySQLを動作させることを目的とする

multipass のインストールと環境設定

Multipass orchestrates virtual Ubuntu instances より multipass Windows版をダウンロードし、インストール。

 インストール後、試しにバージョンを確認してみた。

D:\>multipass version
multipass  1.1.0+win
multipassd 1.1.0+win

 今回、Hyper-Vではなく VirtualBoxの機能を使って(←よくわかっていないけど「ドライバーとしてVirtualBoxを使用」という感じになるらしい)動作させたいので、以下の指定を行う。この指定は管理者として立ち上げたDOS窓で行った(一般ユーザでは設定できない)。

D:\>multipass set local.driver=virtualbox

multipass で一台目のUbuntuの作成と起動

まずどんなイメージから作成可能かを確認する。find を使う。

D:\>multipass find
Image                   Aliases           Version          Description
16.04                   xenial            20200218.1       Ubuntu 16.04 LTS
18.04                   bionic,lts        20200218         Ubuntu 18.04 LTS

 Ubuntu上で試したときと比べて、使えるイメージが随分少ないが、今回はとくに色々なバージョンで試したいという目的ではないので、18.04を使うことでいいかなということにする。

 まず実行前に、念のため、作成済みの環境がないことの確認。list命令使用。

D:\>multipass list
No instances found.

 18.04 を myubu01 という名前で作成。作成後、list命令でできあがりを確認。

D:\>multipass launch --name myubu01 18.04
Launched: myubu01

D:\>multipass list
Name                    State             IPv4             Image
myubu01                 Running           N/A              Ubuntu 18.04 LTS

 できあがった「myubu01」に接続する。接続にも multipassを使用する。shell命令で。

D:\>multipass  shell myubu01
Welcome to Ubuntu 18.04.4 LTS (GNU/Linux 4.15.0-88-generic x86_64)
(略)
ubuntu@myubu01:~$

 立ち上がりました。

MySQLのインストール

 さくっと。

$ wget https://dev.mysql.com/get/mysql-apt-config_0.8.14-1_all.deb
$ sudo dpkg -i mysql-apt-config_0.8.14-1_all.deb 
$ sudo apt-get update 
$ sudo apt-get install mysql-server -y

立ち上がりました。

ubuntu@myubu01:~$ mysql -uroot -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 8
Server version: 8.0.19 MySQL Community Server - GPL

Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved.

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>

その他初期設定

 パスワード設定

ubuntu@myubu01:~$ sudo passwd ubuntu
  • sshd_config 書き換え
  • インストール時の設定ファイル削除

課題

 WindowsVirtualBox使用の場合、動作中のubuntuIPアドレスがわからない。以下のようにIPv4欄が N/A となっている。こうなるのは、VirtlalBoxを使っていれば、こういうものらしい。

D:\>multipass list
Name                    State             IPv4             Image
myubu01                 Running           N/A              Ubuntu 18.04 LTS

動作している Ubuntu 自体は、10.0.2.15 になっている。sshd_config を書き換えて(パスワードアクセス可能にして22番ポートで待ち受けるように設定)自分自身からsshで接続できることは確認済み。

root@myubu01:~# ip address
1: lo: <LOOPBACK,UP,LOWER_UP> mtu 65536 qdisc noqueue state UNKNOWN group default qlen 1000
    link/loopback 00:00:00:00:00:00 brd 00:00:00:00:00:00
    inet 127.0.0.1/8 scope host lo
       valid_lft forever preferred_lft forever
    inet6 ::1/128 scope host
       valid_lft forever preferred_lft forever
2: enp0s3: <BROADCAST,MULTICAST,UP,LOWER_UP> mtu 1500 qdisc fq_codel state UP group default qlen 1000
    link/ether 08:00:27:86:0f:a8 brd ff:ff:ff:ff:ff:ff
    inet 10.0.2.15/24 brd 10.0.2.255 scope global dynamic enp0s3
       valid_lft 81969sec preferred_lft 81969sec
    inet6 fe80::a00:27ff:fe86:fa8/64 scope link
       valid_lft forever preferred_lft forever

 2つ以上のubuntuを立ち上げて相互に通信させたいので、この問題を解決しないと先に進めないなぁ。要調査。

追記:cloud-config を使って諸々初期設定

 cloud-config を使うと、multipassでubuntuを立ち上げるときに諸々初期設定をしてくれるらしい。とりあえず「サラのMySQL環境を手軽に手に入れる」目的にはちょうど良さそう。
 以下のファイルを作成します。(cloud-config_my2.yamlという名前にしました)

#cloud-config

#package_update: true
#package_upgrade: true
#ssh_pwauth: true
#password: ubupass

write_files:
  - content: |
      # sudo sh -c "cat ./my.cnf >> /etc/mysql/my.cnf"
      # sudo systemctl restart mysqld
      [mysqld]
      skip-grant-tables
    path: /home/ubuntu/my.cnf

runcmd: 
  - wget https://dev.mysql.com/get/mysql-apt-config_0.8.14-1_all.deb
  - sudo DEBIAN_FRONTEND=noninteractive dpkg -i mysql-apt-config_0.8.14-1_all.deb 
  - sudo apt-get update -y
  - sudo DEBIAN_FRONTEND=noninteractive apt-get install mysql-server mysql-shell -y


 そして、新しいubuntu環境を作る(launch)際に、オプションで指定。

multipass launch --name myubu03 18.04 --cloud-init cloud-config_my2.yaml

これで、MySQLのインストールされたubuntu環境が手に入ります。
設定ファイルの先頭のほうをコメントアウトしているのは、launchの際にタイムアウト表示になってしまうことが多かったので、とりあえず時間のかかる処理を省いたもの。個人的には update はオンにしておきたいところ。

 MySQLのパスワード設定の方法がわからず(ヒントは多くいただいたのですが試しきれず)、noninteractive モードでインストールしています。error.logを見ると

2020-03-08T09:18:32.175386Z 5 [Warning] [MY-010453] [Server] root@localhost is created with an empty password ! Please consider switching off the --initialize-insecure option.

 となってるため、パスワードなしで接続できるようになっているのかなと思ったけど、違うみたい。skip-grant-tables でアクセスして確認してみたら、rootユーザの認証方法が auth_socket になっていました(caching_sha2_passwordとかではなくて)。 
 でも、 mysql --protocol=socket -uroot などを試しても Access denied のまま。もはや諦めかけていたころに、天のAPIからのレスポンスが!

 はい。sudo su - してrootになって、mysql -uroot だけで、あっさりつながりました。瞬間解決! ありがとうございます! sudo mysql -uroot でもOKでした。

 でも、mysqlを利用するためにrootになるのも何だな、という思いのほうが強いので、たぶん私は ski-grant-tables を当面使うことになりそうです(こっちのほうがより幅広いアクセスを許容しているので危ないのですが、ローカル(Windows)の virtualboxをドライバとしたmultipass上のubuntu上のMySQLなので、問題となることは少ないかな、と今のところ判断しています。

 その方法が、上記 cloud-config を使ってlaunchした際に、ubuntuユーザのフォルダ化に置かれているファイル。このファイルに書かれたとおりに、

  $  sudo sh -c "cat ./my.cnf >> /etc/mysql/my.cnf"
  $  sudo systemctl restart mysqld

 を実行すれば、skip-grant-tablesでのMySQLが起動されるという仕組み。繰り返しますが、普通はこの状態で稼働を続けてはいけません! パスワードなしでMySQLに接続できる設定です。←に書いた意味を理解してリスクを評価できる人以外は、やめたほうがいいです。

追記:顛末

 土日の2日間をかけて、結構ずっぽりと multipass三昧だったのですが、レンサバ(VPS)のUbuntu上で失敗→手元のWindows上でHyper-V関連で苦労→ドライバとしてVirtualBoxを使う方法に切り替え→IPアドレスが得られない&ホストマシン側からssh等で接続する方法がわからない(未解決)→cloud-configでMySQL自動インストールトライ→インストール中に対話型でいろいろ聞いてくるので聞かれないようにする方法に苦労→インストールできたけどMySQL接続パスワードがわからない→skip-grantの路線で一応の解決に→そもそも パスワードではなくauth_socket認証だと判明→解決できず悶々→神降臨 
 という流れで、まぁあっちいったりこっち来たり、解決すべき課題が多岐に亘ったため(時間にも制約があるので)半端につまみ食いしたような気分ではありました。ヒントも色々いただいていて、ひとつひとつ試したいんですけどね。。

 先ほど、VirtualBox上に Ubuntu をインストールしまして、その上で multipassを動かすことにしました。VirtualBox側で Hyper-V有効とかネットワーク設定を適切にするとか気遣いポイントはありますが、比較的あっさり設定できました(MySQL関連は、まだ)。 multipass list で得たインスタンス一覧にもIPアドレスがちゃんと表示されているので、

  • 素のまっさらなMySQLをとりあえず欲しいとき→Windows上のmultipassで
  • 2台以上のMySQLで遊びたいとき→VBox上のUbuntu上のmultipassで

ということになりそうです。

 twitterその他で色々教えてくださったみなさん、ありがとうございました!!
久々にトライ&エラー&エラー&エラー で、存分にシステムと戯れることができた気がします(笑)。

Ubuntu仮想環境 "Multipass" にトライした話(1)

 Ubuntu仮想マシンをぽんぽん立ち上げられる "Multipass" というものがあると知り、興味を持ちました。 例えば MySQLレプリケーションの例を示す時にも、MySQLサーバがどのように動作しているかを理解している人向けには「ポートを変えて立ち上げています」で通じますが、多くの人は「別々のサーバ上で動作しているMYSQL間で通信を行っている」ということを、その、ポートを変更して行うことに脳内で代替するのにエネルギーを必要とするようで、やはり、「いかにも別々のサーバで動作していますよ」というところからスタートできるのは大きいのです。
 ということで、トライ。

WindowsMacOSLinux上で動作するとのことで、とりあえず相性良さそうな Ubuntu上で試してみることにしました。手元の Windows 上で動作すれば良いのですが、VirtualBoxVMWare workstation と共存できない(multipassの動作には Hyper-Vを有効にする必要がある)ので、諦めました(注:この日記を書くために念のため検索してみたら、VirtualBox 6.0ではHyper-Vが有効でも動作するようになったみたい。あとで試したい。VMWareは、まだっぽい?)

Ubuntu環境の用意

 とりあえず、さくらインターネットVPSで、実験用に一台契約しているものがあるので、そちらに、Ubuntu 18.04 をインストール。sshで接続。
 たしなみとして、とりあえず最新化。

$ sudo apt-get update
||< 

** multipass のインストール
 multipass のインストールには snap というものを使うらしい。snapが何かはよく分かっていないが、とりあえず今日は「そういう道具がある」ということで進める。snap自体、Ubuntu 18.10には標準で入っていると書かれた記述も見かけたけど、今回の環境には入っていなかったので、インストール。
>||
$ sudo apt install snapd

 そして、snap を使って multipass をインストール。

$ sudo snap install multipass --classic

 インストール後、multipass help を実行して確認しようとしたところ、パスが通っていないとのこと。 共通の設定ファイルをsourceとかで読み込ませれば良いのだろうけど、今回は面倒なのでログインしなおし。ログインしなおすと、 multipass のあるフォルダにパスが通りました。

はじめての multipass

 ということで、helpを見てみます。

ubuntu:~$ multipass help                                                                                           
Usage: multipass [options] <command>
Create, control and connect to Ubuntu instances.

This is a command line utility for multipass, a
service that manages Ubuntu instances.

Options:
  -h, --help     Display this help
  -v, --verbose  Increase logging verbosity, repeat up to three times for more
                 detail
Available commands:

  find      Display available images to create instances from
  launch    Create and start an Ubuntu instance
  start     Start instances
  stop      Stop running instances
  restart   Restart instances
  suspend   Suspend running instances
  delete    Delete instances
  recover   Recover deleted instances
  purge     Purge all deleted instances permanently

  list      List all available instances
  info      Display information about instances

  exec      Run a command on an instance
  get       Get a configuration setting
  mount     Mount a local directory in the instance
  umount    Unmount a directory from an instance
  transfer  Transfer files between the host and instances
  set       Set a configuration setting
  shell     Open a shell on a running instance

  version   Show version details
  help      Display help about a command

(今後自分で使いやすいようにコマンド一覧部分の出力を、手作業で順序並べ替えてあります)

 いい感じ。
 使えるイメージを一覧するのは find らしいので、実行してみる。

ubuntu:~$ multipass find                                                                                           
Image                   Aliases           Version          Description
snapcraft:core          core16            20200221         Snapcraft builder for Core 16
snapcraft:core18                          20200221         Snapcraft builder for Core 18
core                    core16            20200213         Ubuntu Core 16
core18                                    20200210         Ubuntu Core 18
16.04                   xenial            20200218.1       Ubuntu 16.04 LTS
18.04                   bionic,lts        20200218         Ubuntu 18.04 LTS
19.10                   eoan              20200211         Ubuntu 19.10
daily:20.04             devel,focal       20200305         Ubuntu 20.04 LTS

multipass で Ubuntu 19.10環境を構築してみる

launch で新しい環境を作成できるそうなので、作ってみる。名前は myubu01 とした。

ubuntu:~$ multipass launch --name myubu01 19.10
launch failed: CPU does not support KVM extensions. 

 ここ、KVMが動いていないっぽい。

 終了。

MySQL ST_PointFromGeoHash で得られる点はどこの点

 ST_GeoHashで得たハッシュ文字列を ST_PointFromGeoHashでPOINTに変換する際に選ばれる点は、どこの点なのかという疑問が、先日発生しました。

MySQL の GeoHash系関数に、当該GeoHashの範囲を得られる何か(ST_PolyFromGeoHashとかST_MaxLatFromGeoHashとかのようなもの)があれば簡単にこの問いには答えられるのですが、あいにくそれらの関数はないため、とりあえず状況証拠から想像する作戦を採ることにしました。

概観してみる

 まずは、GeoHashの桁数に応じて PointFromGeoHash の値がどのように変化するのかを概観してみることにします。

mysql> set @pstr='POINT(35.123456789012 136.987654321098)';
Query OK, 0 rows affected (0.00 sec)
mysql> WITH keta (k) AS (
    -> VALUES ROW(10),ROW(9),ROW(8),ROW(7),ROW(6),ROW(5),ROW(4),ROW(3),ROW(2),ROW(1))
    -> SELECT ST_GeoHash(ST_GeomFromText(@pstr,4326), keta.k) gh,
    ->        ST_AsText(ST_PointFromGeoHash(ST_GeoHash(ST_GeomFromText(@pstr,4326), keta.k),4326)) pnt,
    ->        ST_latFromGeoHash(ST_GeoHash(ST_GeomFromText(@pstr,4326), keta.k)) lat,
    ->        keta.k
    -> FROM keta
    -> ORDER BY keta.k DESC;
+------------+----------------------------+-----------+----+
| gh         | pnt                        | lat       | k  |
+------------+----------------------------+-----------+----+
| xn1rv4p2gw | POINT(35.123455 136.98765) | 35.123455 | 10 |
| xn1rv4p2g  | POINT(35.12344 136.98765)  |  35.12344 |  9 |
| xn1rv4p2   | POINT(35.1234 136.9877)    |   35.1234 |  8 |
| xn1rv4p    | POINT(35.124 136.988)      |    35.124 |  7 |
| xn1rv4     | POINT(35.126 136.98)       |    35.126 |  6 |
| xn1rv      | POINT(35.13 137)           |     35.13 |  5 |
| xn1r       | POINT(35 137)              |        35 |  4 |
| xn1        | POINT(34 137)              |        34 |  3 |
| xn         | POINT(37 141)              |        37 |  2 |
| x          | POINT(22 158)              |        22 |  1 |
+------------+----------------------------+-----------+----+
10 rows in set (0.00 sec)

 ついでに、ST_PointFromGeoHash で得られる緯度と、ST_LatFromGeoHash で得られる緯度が同じものになることの確認もしています。

 さて、まず緯度に注目してみると、GeoHashの桁数が短くなるのに対して、緯度の値は上に行ったり下に行ったりしていることがわかります。緯度も同様。なんだか不思議な動きです。

端っこの値ではない

 6桁の GeoHash を例にとって、これが上下左右いずれかの端っこであるか否かを確認してみることにします。
6桁のPOINTは、これ。

| xn1rv4     | POINT(35.126 136.98) 

 まず緯度方向に微少量揺らした結果:

mysql> SELECT ST_GeoHash(ST_GeomFromText('POINT(35.1259 136.98)',4326),6) h UNION ALL
    -> SELECT ST_GeoHash(ST_GeomFromText('POINT(35.126  136.98)',4326),6) h UNION ALL
    -> SELECT ST_GeoHash(ST_GeomFromText('POINT(35.1261 136.98)',4326),6) h;
+--------+
| h      |
+--------+
| xn1rv4 |
| xn1rv4 |
| xn1rv4 |
+--------+
3 rows in set (0.01 sec)

 次に、経度方向に微少量揺らした結果:

mysql> SELECT ST_GeoHash(ST_GeomFromText('POINT(35.126  136.979)',4326),6) h UNION ALL
    -> SELECT ST_GeoHash(ST_GeomFromText('POINT(35.126  136.98)',4326),6) h  UNION ALL
    -> SELECT ST_GeoHash(ST_GeomFromText('POINT(35.126  136.981)',4326),6) h;
+--------+
| h      |
+--------+
| xn1rv4 |
| xn1rv4 |
| xn1rv4 |
+--------+
3 rows in set (0.00 sec)

 いずれも結果のGeoHashに変化がありませんね。得られたPOINTの少し上も下も、少し右も左も、同じGeoHashであることがわかります。ということは、得られたPOINTは、端っこではない、ということです。

 というところで「端っこでないなら、まぁ中心なんだろうな」と想像したところで、実験はおしまい。





 

きょうMySQLユーザ会20周年イベントをやっているはずだった

 2020年、日本MySQLユーザ会(MyNA)は設立20年を迎えます。15周年の時にはコロプラさんに全面的にご支援いただいて、記念イベントを開催しました。コロプラさん、その節はどうもありがとうございました。このご恩は5年経っても、たぶん10年経っても忘れません!
 20年目もまた、区切りの年でもあるので、記念にパーティを開催しようと昨年後半より企画を進めておりました。今日3月4日の18時半からです。ご承知のとおり、新型コロナウィルス(COVID-19)の感染拡大防止のため、本日の開催はとりやめ、延期することを発表しました。このイベントの開催責任者でもあり、中心となって企画を進めてきた立場から、今回の判断に至る経緯や思いなどを記録として残したいと思います。今後の何かに役に経つかもしれないし。

MyNA 20周年パーティの企画

 私の中では2019年になってすぐの頃から、そわそわしていたのですが、具体的に動き始めたのは昨年の後半。会場提供+スポンサーのお願いをし、日程を3月に設定し、こまごまと動き始めていました。 各種イベントなどで「3月4日にやります!」と広報をし、参加申込み受付を「開催の3週間ほど前に開始」とアナウンスしていました。100人程度の参加者を想定。セミナー+立食パーティの形式。

参加受付開始時に・・・

 リアルタイムで情報を得ていた方はご存じだと思いますが、MyNAは、他の多くのイベントが中止を発表するよりも早いタイミングで、3月の中止を発表しました。これは、当主催者の判断能力が非常に高かった、、、というわけではなく、判断をするべきタイミングがたまたま、良いタイミングに当たったというだけのことでした。
 2月12日。イベント開催の3週間前なので、参加受付を開始する時期です。Connpassページを作り、もう少し仕上げれば公開できりる、というところまで来ていました。しかしその頃世間では、新型コロナの猛威が広がりつつありました。バルセロナで開催予定だったMWCの中止が発表されたところで、ふと「このまま開催して良いのだろうか/開催できるだろうか」との思いが私の中に芽生えます。一晩の熟考(大半の時間は夢の中)を経て、目が覚めたときには私の心は「大きな反対がなければ延期」に傾いていました。
 幸い、スポンサー様からも「うちは問題ないので、そちらの判断で」と言っていただき、関係者への連絡の後、延期の発表となった次第。

判断の材料になったもの

 今回、「参加受付を開始する直前」という、ちょうど良い判断のタイミングがあったため、ここで延期の決定をすることができました。でも、受付を開始してしまう、という判断をすることもできわけです。ここで考えた材料は以下のようなものでした。

  • 受付を開始した後に中止を決定した場合 → 申込みをしてくれた人への中止の周知がたいへん →開催日が近くなればばるほど行き渡らないリスク
  • これから3週間内に中止の判断をする可能性はあるか → 十分にありそうな雰囲気
  • 新コロ騒ぎは、現在どのステージか → 接線の傾きがプラス、の印象。2,3週間待てば落ち着くと判断するに足る情報がなにひとつない
  • それでも開催した場合 → おとなしく椅子に座るセミナーならありかもしれない。でも立ち歩いて飲食して語り合う時間を多く取るイベント。リスク高し。

ステークホルダー

 当然ながら勝手に「やめることにしたんで」というわけにはいきません。当イベントは、他の大きなイベントに比べれば非常にコンパクトなものですが、それでも、判断から延期の周知とご理解のお願いまで、半日がかりでした(日中ほぼ使った、というのは半日じゃなくて一日と言うのか?)。
 今回の主なステークホルダは、次のようなものでした。スポンサー様には「相談」、それ以外の方には「連絡とご理解のお願い」です。

  • スポンサー担当者:社内の調整等にお手間をかけていただいているので、中止したときに社としてあるいは社内での関係としてご迷惑をおかけすることはないかとお伺い。幸い、情勢を理解していただき先述のとおりの言葉をいただいた
  • 運営メンバー:中止に至る意思決定の過程から情報共有しているので、OK
  • 講演者:講演をお願いし、または希望して名乗り出ていただき、何らかの準備を進めていた可能性あり、その点についてのお詫び
  • 支援者:イベント告知等の機会をくださったり、必要あれば手伝うよと言ってくださった方など。経緯と延期の連絡
  • 参加予定社:今までのイベント告知を見て手帳に書き入れてくださったり、楽しみにしてくれた方。こちらについては特に個別の対応はせずに、全体に向けた延期アナウンスで。

 おおむねこの程度の規模なので、速やかな延期決定ができたとも言えます。返金対応だったり、複数のスポンサー企業だったり、有料会場を押さえてしまっていたりなど、もっと大変なイベント中止の数々をオンラインで拝見していて、運営の皆さんの判断に畏敬の念をいだいております。

心ない言葉

 その後の、多くのイベントの中止発表が相次ぐ中で「同調圧力で中止」という心ない言葉を目にする機会が増えました。自分に向けられた言葉でないにせよ、非常に胸が痛みました。 イベントの種類にも依りますが、私が目にした範囲で、この言葉が投げつけられたイベントの多くは、「みんながやめてるから/やめろーみたいな圧力がつらいから、やーめた!」なんて言えるほど軽いものではありません。運営の皆さんは、中止することにより発生する多くの手間を受け入れ、まさに苦渋の決断をされたものと思います。 イベントに最も近いとことで準備を進めてきて、たぶん最もイベントを楽しみにしていたであろう人に向けてのこの言葉は、目にするのも辛かったです。

結果は

 そして本日3月4日。疑いの余地なく「本日中止」の判断は正解だったと言えるでしょう。私の判断ミスを「早々に延期を決めちゃったけど、全然開催できたじゃーん。神経質すぎたよねー、ははは」と言って笑っていられるくらいのほうが嬉しかったです。感染の爆発的な広がりを抑止し、対処方法が確立され、騒ぎが収束する日が早く来ることを願っています。



 以上、まとまっていませんが、心の中のもやもやの一部を吐きだしておきたく、文章化してみました。

MySQLの生成列を使って緯度・経度の列をGEOMETRYに

 緯度と経度それぞれが別々のカラムに格納されているデータに対して、生成列を使ってGEOMETRY型のカラムとして扱えるようにする方法を考えてみました。速度面で何かディスアドバンテージがあるのか否かは未知数ですが、こんな方法もあるのだというヒントになればと思い、書いてみました。

元データの用意

 まず、緯度、経度それぞれが別々のカラムに格納されているテーブル spot1 を作成し、データを投入します。

DROP TABLE IF EXISTS spot1;
CREATE TABLE spot1 (
  id integer auto_increment,
  name varchar(256) ,
  lat double ,
  lng double ,
  PRIMARY KEY (id)
);

INSERT INTO spot1 (name, lat, lng) VALUES ('サークルハッピー寺院', 35.16110304 ,136.87678426);
INSERT INTO spot1 (name, lat, lng) VALUES ('旋法学園螺旋',         35.16814112 ,136.88577498);
INSERT INTO spot1 (name, lat, lng) VALUES ('商店街入口',           35.17620956 ,136.89037992);
INSERT INTO spot1 (name, lat, lng) VALUES ('中央市場',             35.17004094 ,136.88814694);
INSERT INTO spot1 (name, lat, lng) VALUES ('乗り茸フォレスト',     35.1798643  ,136.8815835);
INSERT INTO spot1 (name, lat, lng) VALUES ('銭湯タワー',           35.1750197  ,136.8810447);
INSERT INTO spot1 (name, lat, lng) VALUES ('中四角',               35.1700075  ,136.8852028);

 内容確認。

mysql> SELECT * FROM spot1;
+----+--------------------------------+-------------+--------------+
| id | name                           | lat         | lng          |
+----+--------------------------------+-------------+--------------+
|  1 | サークルハッピー寺院           | 35.16110304 | 136.87678426 |
|  2 | 旋法学園螺旋                   | 35.16814112 | 136.88577498 |
|  3 | 商店街入口                     | 35.17620956 | 136.89037992 |
|  4 | 中央市場                       | 35.17004094 | 136.88814694 |
|  5 | 乗り茸フォレスト               |  35.1798643 |  136.8815835 |
|  6 | 銭湯タワー                     |  35.1750197 |  136.8810447 |
|  7 | 中四角                         |  35.1700075 |  136.8852028 |
+----+--------------------------------+-------------+--------------+
7 rows in set (0.00 sec)

生成列でGEOMETRY型カラムを追加

 spot1テーブルに対して ALTER TABLE で列を追加しても良いのですが、ここでは新たに別途 spot2テーブルを作成してみることにします。

DROP TABLE IF EXISTS spot2;
CREATE TABLE spot2 (
  id integer auto_increment,
  name varchar(256) ,
  lat double ,
  lng double ,
  p geometry GENERATED ALWAYS AS (ST_GeomFromText(CONCAT("POINT(",lat," ",lng,")"),4326)),
  PRIMARY KEY (id)
);

 spot2テーブルに spot1テーブルから値を登録。

INSERT INTO spot2 (id, name, lng, lat) SELECT id, name, lng, lat FROM spot1;


 一応内容確認。POINT値っぽい雰囲気のHEXが出ていますね。よさげ。

mysql> SELECT * FROM spot2;                                                                                                      
+----+--------------------------------+-------------+--------------+------------------------------------------------------+
| id | name                           | lat         | lng          | p                                                    |
+----+--------------------------------+-------------+--------------+------------------------------------------------------+
|  1 | サークルハッピー寺院           | 35.16110304 | 136.87678426 | 0xE610000001010000001F4BDD9D0E1C6140080B40069F944140 |
|  2 | 旋法学園螺旋                   | 35.16814112 | 136.88577498 | 0xE61000000101000000E256C544581C6140A4C1F1A585954140 |
|  3 | 商店街入口                     | 35.17620956 | 136.89037992 | 0xE6100000010100000048AD07FE7D1C6140A5B8EC088E964140 |
|  4 | 中央市場                       | 35.17004094 | 136.88814694 | 0xE61000000101000000F6AA21B36B1C6140FB23CAE6C3954140 |
|  5 | 乗り茸フォレスト               |  35.1798643 |  136.8815835 | 0xE610000001010000002FA699EE351C6140E51B1BCB05974140 |
|  6 | 銭湯タワー                     |  35.1750197 |  136.8810447 | 0xE610000001010000000D9AA784311C6140EFD3A70B67964140 |
|  7 | 中四角                         |  35.1700075 |  136.8852028 | 0xE610000001010000007C8AD294531C6140904946CEC2954140 |
+----+--------------------------------+-------------+--------------+------------------------------------------------------+
7 rows in set (0.00 sec)

Spatialの関数を使ったクエリだってバッチリ

 試しに、(35.170139 136.8808)な場所からの距離を計算してみましょう。

mysql> SELECT id, name, ST_AsText(p),
    ->    ST_Distance(ST_GeomFromText("POINT(35.170139 136.8808)",4326), p) dist
    -> FROM spot2 ORDER BY dist;
+----+--------------------------------+---------------------------------+--------------------+
| id | name                           | ST_AsText(p)                    | dist               |
+----+--------------------------------+---------------------------------+--------------------+
|  7 | 中四角                         | POINT(35.1700075 136.8852028)   |  401.3554092727646 |
|  2 | 旋法学園螺旋                   | POINT(35.16814112 136.88577498) |  504.5178361627275 |
|  6 | 銭湯タワー                     | POINT(35.1750197 136.8810447)   |   541.945760266082 |
|  4 | 中央市場                       | POINT(35.17004094 136.88814694) |  669.3863039818294 |
|  1 | サークルハッピー寺院           | POINT(35.16110304 136.87678426) | 1067.1591321918067 |
|  5 | 乗り茸フォレスト               | POINT(35.1798643 136.8815835)   | 1081.3275537403395 |
|  3 | 商店街入口                     | POINT(35.17620956 136.89037992) | 1102.3489276382384 |
+----+--------------------------------+---------------------------------+--------------------+
7 rows in set (0.00 sec)

 バッチリ!

一応簡単に生成列の解説

 ポイントは生成列を以下のようにポイントとして扱うように指定する部分です。

  p geometry GENERATED ALWAYS AS (ST_GeomFromText(CONCAT("POINT(",lat," ",lng,")"),4326)),

 上の列定義は、pという名前の列を GEOMETRY型で宣言(ここまでは通常の列と同じ)し、その後で、それが生成列(GENERATEDカラム)であることを宣言します。ASの後ろがその生成ルールで。やりたことは、単に

 ST_GeomFromText('POINT(latカラム値 lngカラム値)', 4326) 

 という形でルールを指定することなのですが、 POINT() の文字列(WKT)を作る部分に CONCAT を使っているのが、ここでの工夫です。Oracle的に書くなら(MySQLで動作オプションを変更しているなら) || を使って文字列結合しても良いでしょう。

 なお、上記列定義では、省略可能なキーワードなどもありますが、省略しすぎると何がなんだかぱっと見わからなくなるので、私は上記の書き方がおすすめ。
 また、lat/lng列の値に不正がないことは事前に確認(保証)済であることをここでの前提にしています。