GeoHashをMySQLで活用する方法 / How to use GeoHash with MySQL

2024年11月10日(日)に開催された「FOSS4G 2024 Japan」での発表資料です。
https://www.osgeo.jp/events/2024-2/foss4g-2024-japan/coreday

GeoHashの概要と、MySQLでGeoHashを扱える関数の使い方、GIS機能が使えるMySQLのマネージドサービスを無料で使用する方法などについて説明しています。

Transcript

1. GeoHashをMySQLで活用する方法 FOSS4G 2024 Japan 山﨑 由章 MySQL Master Principal Solution Engineer & MySQL Cloud Evangelist, MySQL Community Team 2024年11月10日
2. • 名前：山﨑 由章（やまさき よしあき） • 所属：日本オラクル株式会社 MySQL Community Team / MySQL Global Business Unit • 役割：MySQLのプリセールス、MySQL 及び HeatWave MySQL Database Service の 普及促進活動、など • Twitter ID：@yyamasaki1 • 出身地：明石市 • 趣味：美味しいものを食べること、 色々な所に行くこと、ボードゲーム、など 自己紹介 Copyright © 2024, Oracle and/or its affiliates 2
3. • 今年のテーマは「とびだせFOSS4G」ですが、 残念ながらMySQLのGIS機能では3Dデータは取り扱いできません・・・ Copyright © 2024, Oracle and/or its affiliates 3 はじめに
4. 1. GeoHashとは？ 2. GeoHashがMySQLと相性がいい理由 3. MySQLのマネージドサービスを無料で使用する方法 Appendix Copyright © 2024, Oracle and/or its affiliates 4 アジェンダ
5. Copyright © 2024, Oracle and/or its affiliates 5 1. GeoHashとは？
6. • Canonical(※)のCTOであるGustavo Niemeyerが開発した 経緯度に基づくジオコーディング方法の一つ • 地上の地点を特定するための短いURLを提供することを目的に発明された • 例）専修大学 生田キャンパス 7号館のGeoHash：xn74xdsx91 ※精度が10桁の場合 • 地球上のある地点を含む矩形(長方形)を文字列で表現可能 Copyright © 2024, Oracle and/or its affiliates 6 GeoHashとは？ ※Ubuntuの支援及び開発援助のために設立された企業
7. • 文字列の長さによって精度が変わる • 桁数が多いほど、より狭いエリアを特定できる • 1桁増やすと1/32の面積に絞り込める • 桁数を10桁にすると、精度は南北、東西共に大体1m以内に収まる Copyright © 2024, Oracle and/or its affiliates 7 GeoHashの特徴 b c f g u v y z 8 9 d e s t w x 2 3 6 7 k m q r 0 1 4 5 h j n p -180 -135 -90 -45 0 45 90 135 180 90 45 0 45 90 [経度] [緯度] ※[0-9,a-z]のうち、a,i,l,oを除いた32文字を使用してハッシュ化している
8. • 文字列の長さによって精度が変わる • 桁数が多いほど、より狭いエリアを特定できる • 1桁増やすと1/32の面積に絞り込める • 桁数を10桁にすると、精度は南北、東西共に大体1m以内に収まる Copyright © 2024, Oracle and/or its affiliates 8 GeoHashの特徴 b c f g u v y z 8 9 d e s t w x 2 3 6 7 k m q r 0 1 4 5 h j n p -180 -135 -90 -45 0 45 90 135 180 90 45 0 45 90 [経度] [緯度]
9. • 文字列の長さによって精度が変わる • 桁数が多いほど、より狭いエリアを特定できる • 1桁増やすと1/32の面積に絞り込める • 桁数を10桁にすると、精度は南北、東西共に大体1m以内に収まる Copyright © 2024, Oracle and/or its affiliates 9 GeoHashの特徴 b c f g u v y z 8 9 d e s t w x 2 3 6 7 k m q r 0 1 4 5 h j n p xp xr xx xz xn xq xw xy xj xm xt xv xh xk xs xu x5 x7 xe xg x4 x6 xd xf x1 x3 x9 xb x0 x2 x8 xa
10. Copyright © 2024, Oracle and/or its affiliates 10 参考：文字列の長さを1桁増やした場合の精度(範囲)の違い ※出典：Geohash Explorer https://geohash.softeng.co
11. Copyright © 2024, Oracle and/or its affiliates 11 2. GeoHashがMySQLと相性がいい理由
12. 1. GeoHashを扱うための関数が標準で使用できる 2. 文字列データなので、データベースと相性がいい Copyright © 2024, Oracle and/or its affiliates 12 GeoHashがMySQLと相性がいい理由
13. • 以下の関数が存在し、標準で使用可能（プラグインの追加等は不要） • ST_GeoHash：経緯度をGeoHashに変換する関数 • ST_LatFromGeoHash：GeoHashを緯度にデコードする関数 • ST_LongFromGeoHash： GeoHashを経度にデコードする関数 • ST_PointFromGeoHash：GeoHashをPointデータに変換する関数 Copyright © 2024, Oracle and/or its affiliates 13 1. GeoHashを扱うための関数が標準で使用できる ※Pointデータ：経緯度の情報を格納できるデータ型
14. • 専修大学 生田キャンパス 7号館のGeoHashを求めた場合 Copyright © 2024, Oracle and/or its affiliates 14 ST_GeoHashの例 mysql > SELECT ST_GeoHash(139.554563,35.610730,10); +-------------------------------------+ | ST_GeoHash(139.554563,35.610730,10) | +-------------------------------------+ | xn74xdsx91 | +-------------------------------------+ 1 row in set (0.0003 sec) 経度 緯度 精度(桁数)
15. • 専修大学 生田キャンパス 7号館のGeoHashを緯度に戻した場合 Copyright © 2024, Oracle and/or its affiliates 15 ST_LatFromGeoHashの例 mysql > SELECT ST_LatFromGeoHash('xn74xdsx91'); +---------------------------------+ | ST_LatFromGeoHash('xn74xdsx91') | +---------------------------------+ | 35.61073 | +---------------------------------+ 1 row in set (0.0002 sec) ※デコード後の値は変換前の値と完全一致するわけではないので注意
16. • 専修大学 生田キャンパス 7号館のGeoHashを緯度に戻した場合 Copyright © 2024, Oracle and/or its affiliates 16 ST_LongFromGeoHashの例 mysql > SELECT ST_LongFromGeoHash('xn74xdsx91'); +----------------------------------+ | ST_LongFromGeoHash('xn74xdsx91') | +----------------------------------+ | 139.55457 | +----------------------------------+ 1 row in set (0.0003 sec) ※デコード後の値は変換前の値と完全一致するわけではないので注意
17. • 専修大学 生田キャンパス 7号館のGeoHashをPointデータに変換し、 Pointデータをテキストに変換して表示した場合 Copyright © 2024, Oracle and/or its affiliates 17 ST_PointFromGeoHashの例 mysql > SET @gh = ST_GeoHash(139.554563,35.610730,10); Query OK, 0 rows affected (0.0002 sec) mysql > SELECT ST_AsText(ST_PointFromGeoHash(@gh,4326)); +------------------------------------------+ | ST_AsText(ST_PointFromGeoHash(@gh,4326)) | +------------------------------------------+ | POINT(35.61073 139.55457) | +------------------------------------------+ 1 row in set (0.0112 sec) SRID
18. ➢ MySQL 8.0で強化されたGIS機能と使用事例のご紹介＋α https://speakerdeck.com/yoshiakiyamasaki/mysql-8-dot-0deqiang-hua-saretagisji-neng-toshi-yong-shi- li-falsegoshao-jie-a ➢ MySQL 8.0 GIS機能チュートリアル https://speakerdeck.com/yoshiakiyamasaki/mysql-8-dot-0-gisji-neng-tiyutoriaru-6052f01e-445a-4f55- bcda-a0e3fad06332 ➢ 周辺知識から理解するMySQL の GIS機能 ～ClubMySQL #4 ※坂井さん(OSGeo.JP 運営委員＆日本MySQLユーザ会 副代表)の発表資料 https://www.slideshare.net/sakaik/mysql-gis-clubmysql-4 Copyright © 2024, Oracle and/or its affiliates 18 MySQLのGIS機能に関する参考情報
19. ➢ MySQLで処理するGIS ～地球が丸いことを覚えたMySQL～ https://speakerdeck.com/yoshiakiyamasaki/mysql-gis-foss4g-tokai-2023 Copyright © 2024, Oracle and/or its affiliates 19 MySQLのGIS機能に関する参考情報
20. ◼ MySQLにシェープファイルをインポートする方法については、 宮内さん(FOSS4G TOKAI実行委員)の発表資料やQiitaの記事を参考にして下さい ➢ MySQLにシェープファイルをインポートするツール(shp2mysql)を作った https://qiita.com/miyauchi/items/b4e810b3becf2cf07e2f ➢ MySQLにシェープファイルをインポートしてWebGISを作る https://www.osgeo.jp/events/foss4g-2020/foss4g-2020-japan-online/foss4g-japan-2020- online-coreday#presentation_a6 Copyright © 2024, Oracle and/or its affiliates 20 MySQLのGIS機能に関する参考情報
21. • GeoHashは文字列であるため、データベース上は文字列として扱える • 文字列データに対する検索 • インデックスを使って検索を高速化可能 • LIKE検索を使って前方一致での絞り込みも可能 • GeoHashは桁数を少なくすることで近隣のエリアを指定できるため、 前方一致検索と相性がいい Copyright © 2024, Oracle and/or its affiliates 21 2. 文字列データはデータベースと相性がいい
22. Copyright © 2024, Oracle and/or its affiliates 22 完全一致検索と前方一致検索の例 mysql > SELECT * FROM place WHERE geohash='xn74xdsx9'; +----+-------------------------------+-----------+ | id | location_name | geohash | +----+-------------------------------+-----------+ | 10 | 専修大学 生田キャンパス 7号館 | xn74xdsx9 | +----+-------------------------------+-----------+ 1 row in set (0.0004 sec) mysql > SELECT * FROM place WHERE geohash LIKE 'xn74xdsx%'; +----+-------------------------------+-----------+ | id | location_name | geohash | +----+-------------------------------+-----------+ | 1 | 専修大学 生田キャンパス 7号館 | xn74xdsx1 | | 2 | 専修大学 生田キャンパス 7号館 | xn74xdsx0 | <略> | 31 | 専修大学 生田キャンパス 6号館 | xn74xdsxy | | 32 | 専修大学 生田キャンパス 6号館 | xn74xdsxz | +----+-------------------------------+-----------+ 32 rows in set (0.0003 sec)
23. Copyright © 2024, Oracle and/or its affiliates 23 完全一致検索と前方一致検索の例 SELECT * FROM place WHERE geohash='xn74xdsx9'; SELECT * FROM place WHERE geohash LIKE 'xn74xdsx%'; ※出典：Geohash Explorer https://geohash.softeng.co
24. Copyright © 2024, Oracle and/or its affiliates 24 一旦お知らせ
25. • RDBMS-GIS(地理情報・位置情報) Advent Calendar 2024 https://qiita.com/advent-calendar/2024/rdbms_gis • 「また「RDBMS」と名乗っていますが、“Relational”の部分はあまり厳密に捉え なくて良いです。」 と説明されている通り、DBとGISに関係するネタならなんでも OKです！！ • 例えば、今日の話を聞いて「GeoHashをMySQLに格納して検索してみた」 という風なネタを投稿して頂けると、私が喜びます！！ Copyright © 2024, Oracle and/or its affiliates 25 RDBMS-GISアドベントカレンダー、参加者絶賛募集中！！
26. Copyright © 2024, Oracle and/or its affiliates 26 3. MySQLのマネージドサービスを無料で使用する方法
27. Copyright © 2024, Oracle and/or its affiliates 27 オラクルからもMySQLのマネージドサービスを 提供しています！
28. Copyright © 2024, Oracle and/or its affiliates 28 HeatWave MySQL MySQLだけでトランザクション処理、データ分析、機械学習、データレイクを実現 Transactions Analytics Machine Leaning Lakehouse
29. Copyright © 2024, Oracle and/or its affiliates 29 オラクルのクラウドにはAlways Freeというサービスがあり、 いくつかのリソースが常時無料で使用できます ＋ 今月HeatWave MySQLがAlways Freeの対象に 含まれました！！
30. Copyright © 2024, Oracle and/or its affiliates 30 出典：https://www.oracle.com/jp/cloud/free/
31. Copyright © 2024, Oracle and/or its affiliates 31 出典：https://www.oracle.com/jp/cloud/free/
32. • 以下URLからオラクルクラウドのトライアルアカウントを作成します https://signup.cloud.oracle.com/ • ホームリージョンは後から変更できないので注意 • 日本には現在東京リージョンと大阪リージョンがあります • 以下URLのチュートリアルを参考にし、HeatWave MySQL環境を構築する • OCIチュートリアル 入門編：その9 - クラウドでMySQL Databaseを使う https://oracle-japan.github.io/ocitutorials/beginners/creating-mds/ Copyright © 2024, Oracle and/or its affiliates 32 Always FreeでHeatWave MySQLを使用する方法 ※本日時点で、上記チュートリアルのスクリーンショットは、まだAlways Freeリリース後のものに更新されていませんが、 基本的な手順は確認可能です
33. • 最新バージョンのみ使用可能 (本日時点では、9.1.0) • MySQL.Freeシェイプ、HeatWave.Freeのみ使用可能 (Always Free専用のスペックがあまり高くないシェイプ) • ストレージサイズは50GB • HeatWaveノードは1台のみ追加可能 • HeatWave AutoML と HeatWave Lakehouse は使用可能 • HeatWave GenAI は使用不可 • レプリケーション機能は使用可能 • 高可用性、リードレプリカは使用不可 • 自動バックアップは1日だけ取得される • 手動バックアップやポイントインタイムリカバリは使用不可 • Database Management and Ops Insights サービスは使用不可 (データベースの監視ツール) Copyright © 2024, Oracle and/or its affiliates 33 Always FreeでHeatWave MySQLを使用する場合の制限事項 ※原文：https://docs.oracle.com/en-us/iaas/mysql-database/doc/features-mysql-heatwave-service.html#MYAAS-GUID-772BD870-57C1-4B21-9205-FFC5B4290044
34. Copyright © 2024, Oracle and/or its affiliates 34 Appendix
35. MySQLサーバーは全て共通のソースコードのためハイブリッド構成も可能 MySQLを自社で運用管理 HeatWave MySQL Database Service • MySQLチームが100％開発・ 提供するクラウド・サービス • Amazon RDS (MySQL)の 1/3以下のコスト • データ損失なし、自動フェイルオー バーの高可用性機能をマネージ ドサービスで提供 MySQL Operator for k8s • MySQL サーバーをKubernetes上に 構築し運用管理 柔軟なMySQLの利用方法 35 オンプレミスでのMySQL • バージョン選択や構成を 最も柔軟に選択可能 IaaS上でのMySQL • OCIのマーケットプレイスの イメージから簡単に環境構築 商用版MySQL • コミュニティ版に加え、サポートや セキュリティに優れた商用版も MySQLのマネージドサービス クラウドネイティブなMySQL いずれの利用方法でもMySQL開発チームと連携した 専門部隊によるサポートサービスをご利用いただけます※ Copyright © 2024, Oracle and/or its affiliates MySQL :: MySQL Operator for Kubernetes テクニカルアップデート ※コミュニティ版を除く
36. • 最大のMySQLのエンジニアリングおよびサポート組織 • MySQL開発チームによるサポート • 29言語で世界クラスのサポートを提供 • メンテナンス・リリース、バグ修正、パッチ、アップデートの提供 • 24時間x365日サポート • MySQL コンサルティング・サポート 開発チームと一体となったサポートサービス ⇒ 商用版MySQL サーバー及びHeatWave MySQL Database Services利用者に提供 Oracle Premier Support for MySQL Copyright © 2024, Oracle and/or its affiliates 36 Get immediate help for any MySQL issue, plus expert advice
37. MySQLホームページ http://www.mysql.com/jp MySQL イベント http://www.mysql.com/jp/news-and-events/ MySQLニュースレター 英語版&日本語版（月刊） https://www.mysql.com/jp/news-and-events/newsletter/ MySQL Twitter 日本語公式アカウント @mysql_jp MySQLの最新情報配信 37 Copyright © 2024, Oracle and/or its affiliates
38. MySQL コミュニティ Copyright © 2024, Oracle and/or its affiliates 38 11/10/2024 ▪ MySQL コミュニティの紹介 ▪ MySQL への貢献 ▪ Oracle Contribution Agreement (OCA) ▪ MySQL無償認証制度
39. MySQL コミュニティの紹介 Copyright © 2024, Oracle and/or its affiliates 39 • MySQL コミュニティへの貢献プロセスの運営 • MySQL ユーザーグループへの支援 https://dev.mysql.com/community/mug/ • 全世界でのサードパーティによるカンファレンスやイベントへの支援や参加 https://dev.mysql.com/community/ • 教育ビデオの作成 • MySQL 短編動画 (MySQL Shorts) • MySQL 入門編シリーズ (MySQL 101 for Biginners) • https://www.youtube.com/@mysql • MySQL RockStar プログラム • MySQLの利用促進に最も精力的に取り組んだ MySQL コミュニティ・メンバーへの表彰 • 第1回: https://blogs.oracle.com/mysql/post/mysql-rockstars-2022 • MySQL ACE プログラム • MySQL プロジェクトでの ACE プログラムの運営 • https://ace.oracle.com/pls/apex/ace_program/r/oracle-aces/home
40. MySQL への貢献 • MySQL オープンソースプロジェクトのコントリビューターコミュニティへの参加: https://forums.oracle.com/ords/apexds/post/contributing-code-to-mysql-8037 • コントリビュータになるために持つべきこと • MySQL の機能を変更/修正したい、あるいは新しい機能を追加したいといった要望 • MySQL ソースコードのダウンロード http://dev.mysql.com/downloads/ • bugs.mysql.com のアカウント http://bugs.mysql.com or • 有効な GitHub アカウント https://github.com • Oracle Contribution Agreement (OCA) への署名 https://oca.opensource.oracle.com/ • OCAは、コントリビューターとオラクルの両方を法的攻撃から保護する短い法的契約です。OCAに署名することにより、コントリビュー ターはオラクルがコントリビューターのコードをオラクル・ソフトウェアで使用することが法的に許可されていること、およびコントリビューター の知る限りにおいて、そのコードに特許的な問題がないことに同意することになります。 Copyright © 2024, Oracle and/or its affiliates 40
41. MySQL 無償認証制度 • MySQL コミュニティチームは、Oracle University および Oracle Academy と協力し、 mylearn.oracle.com を介して、2ヶ月間の指定期間内に使用できる無料のトレーニングバウチャー/クレジッ トを受講者に提供します。 • ご興味のある方は、以下についての詳細をお知らせくだされば、MySQL コミュニティから連絡いたします。 • 名前 • 姓 • Email アドレス • 居住国 • https://education.oracle.com/ Copyright © 2024, Oracle and/or its affiliates 41
42. 連絡先 • MySQL コミュニティとのコンタクト先一覧: • MySQL コミュニティページ, https://dev.mysql.com/community/ • MySQL Slack, https://mysqlcommunity.slack.com • The Oracle MySQL ブログ, https://blogs.oracle.com/mysql/ • The Oracle MySQL Japan ブログ, https://blogs.oracle.com/mysql-jp/ • Planet MySQL, https://planet.mysql.com/ • LinkedIn, https://www.linkedin.com/groups/60715/ • ブログ, https://lefred.be/ • MySQL フォーラム, http://lists.mysql.com/ • ディスカッションフォーラム, http://forums.mysql.com Copyright © 2024, Oracle and/or its affiliates 42