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

Slide 1

Slide 1 text

GeoHashをMySQLで活用する方法 FOSS4G 2024 Japan 山﨑由章 MySQL Master Principal Solution Engineer & MySQL Cloud Evangelist, MySQL Community Team 2024年11月10日

Slide 2

Slide 2 text

• 名前：山﨑由章（やまさきよしあき） • 所属：日本オラクル株式会社 MySQL Community Team / MySQL Global Business Unit • 役割：MySQLのプリセールス、MySQL 及び HeatWave MySQL Database Service の普及促進活動、など • Twitter ID：@yyamasaki1 • 出身地：明石市 • 趣味：美味しいものを食べること、色々な所に行くこと、ボードゲーム、など自己紹介 Copyright © 2024, Oracle and/or its affiliates 2

Slide 3

Slide 3 text

Slide 4

Slide 4 text

Slide 5

Slide 5 text

Slide 6

Slide 6 text

• Canonical(※)のCTOであるGustavo Niemeyerが開発した経緯度に基づくジオコーディング方法の一つ • 地上の地点を特定するための短いURLを提供することを目的に発明された • 例）専修大学生田キャンパス 7号館のGeoHash：xn74xdsx91 ※精度が10桁の場合 • 地球上のある地点を含む矩形(長方形)を文字列で表現可能 Copyright © 2024, Oracle and/or its affiliates 6 GeoHashとは？ ※Ubuntuの支援及び開発援助のために設立された企業

Slide 7

Slide 7 text

• 文字列の長さによって精度が変わる • 桁数が多いほど、より狭いエリアを特定できる • 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文字を使用してハッシュ化している

Slide 8

Slide 8 text

• 文字列の長さによって精度が変わる • 桁数が多いほど、より狭いエリアを特定できる • 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 [経度] [緯度]

Slide 9

Slide 9 text

• 文字列の長さによって精度が変わる • 桁数が多いほど、より狭いエリアを特定できる • 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

Slide 10

Slide 10 text

Slide 11

Slide 11 text

Slide 12

Slide 12 text

Slide 13

Slide 13 text

• 以下の関数が存在し、標準で使用可能（プラグインの追加等は不要） • ST_GeoHash：経緯度をGeoHashに変換する関数 • ST_LatFromGeoHash：GeoHashを緯度にデコードする関数 • ST_LongFromGeoHash： GeoHashを経度にデコードする関数 • ST_PointFromGeoHash：GeoHashをPointデータに変換する関数 Copyright © 2024, Oracle and/or its affiliates 13 1. GeoHashを扱うための関数が標準で使用できる ※Pointデータ：経緯度の情報を格納できるデータ型

Slide 14

Slide 14 text

• 専修大学生田キャンパス 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) 経度緯度精度(桁数)

Slide 15

Slide 15 text

• 専修大学生田キャンパス 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) ※デコード後の値は変換前の値と完全一致するわけではないので注意

Slide 16

Slide 16 text

• 専修大学生田キャンパス 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) ※デコード後の値は変換前の値と完全一致するわけではないので注意

Slide 17

Slide 17 text

• 専修大学生田キャンパス 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

Slide 18

Slide 18 text

➢ 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機能に関する参考情報

Slide 19

Slide 19 text

Slide 20

Slide 20 text

◼ 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機能に関する参考情報

Slide 21

Slide 21 text

• GeoHashは文字列であるため、データベース上は文字列として扱える • 文字列データに対する検索 • インデックスを使って検索を高速化可能 • LIKE検索を使って前方一致での絞り込みも可能 • GeoHashは桁数を少なくすることで近隣のエリアを指定できるため、前方一致検索と相性がいい Copyright © 2024, Oracle and/or its affiliates 21 2. 文字列データはデータベースと相性がいい

Slide 22

Slide 22 text

Slide 23

Slide 23 text

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

Slide 24

Slide 24 text

Slide 25

Slide 25 text

• 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アドベントカレンダー、参加者絶賛募集中！！

Slide 26

Slide 26 text

Slide 27

Slide 27 text

Slide 28

Slide 28 text

Slide 29

Slide 29 text

Slide 30

Slide 30 text

Slide 31

Slide 31 text

Slide 32

Slide 32 text

• 以下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リリース後のものに更新されていませんが、基本的な手順は確認可能です

Slide 33

Slide 33 text

• 最新バージョンのみ使用可能 (本日時点では、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

Slide 34

Slide 34 text

Slide 35

Slide 35 text

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 テクニカルアップデート ※コミュニティ版を除く

Slide 36

Slide 36 text

• 最大の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

Slide 37

Slide 37 text

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

Slide 38

Slide 38 text

Slide 39

Slide 39 text

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

Slide 40

Slide 40 text

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

Slide 41

Slide 41 text

MySQL 無償認証制度 • MySQL コミュニティチームは、Oracle University および Oracle Academy と協力し、 mylearn.oracle.com を介して、2ヶ月間の指定期間内に使用できる無料のトレーニングバウチャー/クレジットを受講者に提供します。 • ご興味のある方は、以下についての詳細をお知らせくだされば、MySQL コミュニティから連絡いたします。 • 名前 • 姓 • Email アドレス • 居住国 • https://education.oracle.com/ Copyright © 2024, Oracle and/or its affiliates 41

Slide 42

Slide 42 text

連絡先 • 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

Slide 43

Slide 43 text

No content