MySQL 8.0 で強化されたGIS機能のご紹介 @ FOSS4G Okayama

FOSS4G 2018 Okayama MySQL 8.0 で強化 されたGIS機能のご紹介 2018/11/04 日本MySQLユーザ会(MyNA) Twitterハッシュタグ： #mysql_jp 坂井 恵 (@sakaik)

今日お伝えしたいこと • MySQLにもGIS機能があります。ぜひ触って みてください • MySQL？なにそれ？という方はちょっぴり MySQLについて知ってください • 触ってみて気づいた点があれば、ぜひ開発者に フィードバックください！

MySQLユーザ会(MyNA) • 日本でのMySQL情報を交換するための集いの場 • メーリングリストが中心 • 最近情報提供の場としてTwitterはじめました(@mysql_myna) • MyNA: MySQL Nippon Association • 会費なし、入会手続きなし(MLに入るだけ)のゆるいつな がりの集団 • 勉強会の単独開催や、他の横断的イベント（FOSS4G や オープンソースカンファレンス(OSC)など）に出展、講演 など

自己紹介 • • • • 坂井 恵（さかいけい） @sakaik 日本MySQLユーザ会 副代表 有限会社アートライ 代表取締役 • MySQL 8.0 でGIS機能に興味を持ち、猛勉強（本人談）。 各種イベントで講演。 • GISの話をするフリをして関係ない話をたくさんする人 • 持論：DB屋はデータ発生元と、もっと仲良くなるべき

自己紹介 • 「データ発生元と仲良くなる」 • GIS機能に注目するなら・・・・

自己紹介 • 「データ発生元と仲良くなる」 • GIS機能に注目するなら・・・・

自己紹介 • 「データ発生元と仲良くなる」 • GIS機能に注目するなら・・・・

自己紹介 • 「データ発生元と仲良くなる」 • GIS機能に注目するなら・・・・

自己紹介 • 「データ発生元と仲良くなる」 • GIS機能に注目するなら・・・・

自己紹介 • 「データ発生元と仲良くなる」 • GIS機能に注目するなら・・・・

自己紹介 • 「データ発生元と仲良くなる」 • GIS機能に注目するなら・・・・

自己紹介 ・・・・とこんな感じに 「（ネタを）足で稼ぐDB屋」 をやっていた、ある日。 出会った本。 私の行った「聖地」もたくさん。 ・・・・ 面白い！！！！！ 感動した！！！！ お話したい！！！ 関係者の皆様におかれましては 何卒お取り次ぎをいただけますよう お願い申し上げる次第です。

RDBMSとは • 「リレーショナルデータベース管理システム」 • MySQLのほか、PostgreSQLとかOracleとかDb2とかMSSQLとか • 「SQL」による、簡易なデータアクセス（出し入れ）を提供 • データが大量になっても、それなりに高速に動作する工夫 • データを安全に管理するための工夫

MySQLとは • One of the RDBMS • 「The world's most popular open source database」（主催 者発表） • GPLと商用ライセンスより選択可能(デュアルライセン ス） • スウェーデンのMySQL AB 社が開発→2度の買収を経 て、現在は 米Oracle社が開発 • 現在主に使われる RDBMSの中ではもっ とも新しい RDBの発展の歴史 「達人に学ぶSQL徹底指南書第二版」 (ミック著 翔泳社, 2018) より

MySQLのGIS機能の歴史 • MySQL 4.1.0 (2003): MyISAM に "Spatial Extensions" 実装 http://ftp.nchu.edu.tw/MySQL/doc/refman/4.1/en/spatial-extensions.html • MySQL 5.0.16 (2005): InnoDB にも実装 • MySQL 5.7.1 (2013): spatialデータ専用の型を実装（これまでは BLOBに保管） Boostライブラリを利用して InnoDBに再実装 • MySQL 8.0.0 (2016): 関数名をST_* 系に統一したり関数増やしたり 地球が丸いことを知ったり、良い感じに 2018/04/19 8.0.11でGAに！ 2018/10/24 8.0.13 リリース ※MySQLでは、「GIS」ではなく「Spatial（空間情報）」というキーワード

• http://ftp.nchu.edu.tw/MySQL/doc/refman/4.1/en/spatial-extensions.html

MySQL 8.0 のGIS機能 「地球が丸いことを知った」とは？ → SRS(SRID)サポート！ 479 の地理座標系 (GEOGCS) 4,628 の投影座標系 (PROJCS) MySQL は、バージョン8.0になり、地球の形 を覚えました！ 実は Ver 5.7 でも、地球が丸っぽい事は知っていたのですが、 8.0で「ちゃんとした形(モデル)」を知ったのです。

MySQLで使える型 GEOMETRYデータ型 点：POINT, MULTIPOINT 線：LINESTRING, MULTILINESTRING 面：POLYGON, MULTIPOLYGON 全てを格納可能な型として  GEOMETRY(一情報),  GEOMETRYCOLLEMCTION(複数情報) ※Open GISジオメトリモデルに基づく

Spatialの ３つの表現 テキスト／バイナリ／MySQL内部バイナリ表現 • テキスト：Well known text (WKT) • バイナリ：Well known binary (WKB) • 内部バイナリ： WKB にSRID情報を追加したもの mysql> SELECT HEX(ST_GeomFromText('POINT(35 135)', 6668)) mygeo; +----------------------------------------------------+ | mygeo | +----------------------------------------------------+ | 0C1A000001010000000000000000E060400000000000804140 | +----------------------------------------------------+ (SRID: 0C1A0000 = 6668)

MySQLで扱える各種の関数 • MySQL 8.0 で使える Spatial 関数(リファレンスマニュアル) • 12.15.1 Spatial Function Reference https://dev.mysql.com/doc/refman/8.0/en/spatial-function-reference.html • MySQL 8.0.13 では、約100の関数 • 例  ST_Distance_Sphere() • ２つのジオメトリの最小球面距離を出力  ST_Crosses() • ２つのジオメトリが交差しているか否かを判定  ST_SRID() • ジオメトリのSRS ID を返す  ST_Envelope() • ジオメトリの最小外接矩形を返す  ST_Transform() • あるSRIDから別のSRIDへ変換する（MySQL 8.0.13では地理座標系間のみ対応）

• https://dev.mysql.com/doc/refman/8.0/en/spatial-function-reference.html

MySQLのその他のGIS機能 • GeoHash • GeoJSON 対応

GeoJSON
• 空間データを扱うためのフォーマットのひとつ
例
{"type": "Point",
"coordinates": [34.68711, 133.9188]}
{"type": "LineString",
"coordinates": [34.68711, 133.9188],
[34.89664, 133.7132] }
• MySQL Reference Manual:
12.15.11 Spatial GeoJSON Functions
https://dev.mysql.com/doc/refman/8.0/en/spatial-geojson-functions.html
※MySQL 5.7から

• https://dev.mysql.com/doc/refman/8.0/en/spatial-geojson-functions.html

GeoHash

GeoHash • 緯度経度が含まれるエリアを、指定された桁数 のハッシュ値であらわす仕組み • 桁数が大きいほど、エリアの広さが狭くなる mysql> SELECT ST_GeoHash(133.9188, 34.68711, 5) GH; +-------+ | GH | +-------+ | wypjr | +-------+ mysql> SELECT ST_GeoHash(133.9188, 34.68711, 10) GH; +------------+ | GH | +------------+ | wypjr6sen1 | +------------+

GeoHashのしくみ • • • • [0-9a-z]の中の32文字を使用する 1桁目を決めるために、地球全体を32分割して値を決定する 2桁目を決めるために、1桁目で定まったエリア内を32分割して値を割り振る 以下同様に3桁目4桁目、、、と指定桁数まで分割を繰り返す

GeoHashのしくみ(まとめ) • このように、桁数が増えるごとに、エリア内を さらに分割していく仕組みなので、長いハッ シュ値の先頭部分だけを採用しても、必ず元の エリアが含まれる • (赤道付近の場合) 5桁の時 一辺4.9km、7桁で153m、 9桁で4.7m、11桁で15cm！！！

MySQL Workbench • MySQL用の便利なGUIツール • GIS関連の機能もあります SELECT ST_GeomFromText('POLYGON( (35.85873223 140.02100778, 35.85869833 140.02062944,35.85888028 140.02060361,35.85897278 140.02051249,35.85900056 140.02043305,35.85901916 140.02028500,35.85899250 140.02019805,35.85874584 140.01964528,35.85866333 140.01933389,35.85865083 140.01537528,35.85856528 140.01014805,35.85915806 140.00486083,35.85928417 140.00362361,35.86107278 140.00104444,35.86336194 139.99552111,35.86575972 139.99472472,35.86587055 139.99334139,35.86686195 139.99228361,35.86761528 139.99480750,35.86880167 140.00325528,35.86642639 140.01075083,35.86487528 140.01799417,35.86318584 140.02477583,35.86158583 140.03061722,35.85924249 140.03565667,35.85898556 140.03987861,35.86172250 140.04636667,35.85976195 140.05298139,35.85561222 140.06166361,35.85536722 140.06428416,35.84988639 140.06080861,35.84853612 140.05586139,35.84666167 140.05300583,35.84836056 140.05074888,35.85040945 140.04659194,35.85418500 140.04156111,35.85737916 140.03717278,35.85649945 140.02996416,35.85437083 140.02620166,35.85863722 140.02138112 140.01920167,35.85872666 140.01520778,35.85869167 140.01004667,35.85914527 140.00410027,35.85937306 140.00363750,35.86110916 140.00094056,35.86349472 139.99533694,35.86575333 139.99478277,35.86593000 139.99319556,35.86682695 139.99232083,35.86772278 139.99519138,35.86878666 140.00395972,35.86630889 140.01152528,35.86493250 140.01833000,35.86319417 140.02497111,35.86151111 140.03073945,35.85915139 140.03580916,35.85900194 140.04018417,35.86176055 140.04652972,35.85960472 140.05403973,35.85552917 140.06179611,35.85523472 140.06413750,35.84992027 140.06067695,35.84854528 140.05566139,35.84670806 140.05297584,35.84842778 140.05065889,35.85048861 140.04653194,35.85424750 140.04139611,35.85742195 140.03714278,35.85631028 140.02959528,35.85464528 140.02599778,35.85875834 140.01879694,35.85872666 140.01516111,35.85878028 140.00996111,35.85915806 140.00402111,35.85947333 140.00361527,35.86113333 140.00082917,35.86361556 139.99528695,35.86574694 139.99483445,35.86599666 139.99313750,35.86675389 139.99237695,35.86779722 139.99534416,35.86876639 140.00443306,35.86623250 140.01293417,35.86520000 140.01861500,35.86330056 140.02519528,35.86142833 140.03095333,35.85914333 140.03597222,35.85909305 140.04069611,35.86177694 140.04669250,35.85948056 140.05665889,35.85547945 140.06211167,35.85374639 140.06381250,35.84990334 140.06064416,35.84852723 140.05553778,35.84670806 140.05301333,35.84850694 140.05057639,35.85075111 140.04644528,35.85452667 140.04128361,35.85755611 140.03714278,35.85626167 140.02929722,35.85478778 140.02579389,35.85893611 140.01870333,35.85866333 140.01497445,35.85884361 140.00987556,35.85918945 140.00393223,35.85959611 140.00355583,35.86114528 140.00068083,35.86368195 139.99523694,35.86566139 139.99486639,35.86607722 139.99304528,35.86666111 139.99242250,35.86786000 139.99545556,35.86870611 140.00452694,35.86619444 140.01293417,35.86529528 140.01896416,35.86323416 140.02569361,35.86128778 140.03120778,35.85918472 140.03612500,35.85934139 140.04113389,35.86171917 140.04687583,35.85939778 140.05915139,35.85544639 140.06315667,35.85307639 140.06369695,35.84983916 140.06056722,35.84842917 140.05543306,35.84668473 140.05305083,35.84857417 140.05028389,35.85133250 140.04605944,35.85476000 140.04105111,35.85757444 140.03712028,35.85613333 140.02920222,35.85499194 140.02528167,35.85910056 140.01850888,35.85866333 140.01478750,35.85885000 140.00978222,35.85920473 140.00402805,35.85972444 140.00333333,35.86127806 140.00054722,35.86374834 139.99507362,35.86545611 139.99485361,35.86633194 139.99297250,35.86660694 139.99243667,35.86791555 139.99565639,35.86860556 140.00462084,35.86621361 140.01357972,35.86516167 140.02067417,35.86321750 140.02596861,35.86130444 140.03143139,35.85923445 140.03650167,35.85954000 140.04166306,35.86166112 140.04707916,35.85925861 140.05939583,35.85540500 140.06361611,35.85297694 140.06362778,35.84971361 140.06044694,35.84821500 140.05532916,35.84664194 140.05302833,35.84861694 140.04972028,35.85160084 140.04577334,35.85509555 140.04099861,35.85757444 140.03685000,35.85597917 140.02921028,35.85517084 140.02485472,35.85911444 140.01840000,35.85872334 140.01428972,35.85917639 140.00948056,35.85918584 140.00407028,35.85977723 140.00299917,35.86130222 140.00036167,35.86379666 139.99469333,35.86541111 139.99480416,35.86642361 139.99293083,35.86654223 139.99243389,35.86806055 139.99601417,35.86859528 140.00485584,35.86587000 140.01360333,35.86525722 140.02111194,35.86319444 140.02613139,35.86139528 140.03159445,35.85940834 140.03637945,35.85974695 140.04185639,35.86161973 140.04728417,35.85907639 140.05948750,35.85534694 140.06354500,35.85261278 140.06359139,35.84966916 140.06019445,35.84807222 140.05529167,35.84653222 140.05297584,35.84867778 140.04942000,35.85173500 140.04539833,35.85513222 140.04096111,35.85754389 140.03664277,35.85575945 140.02920222,35.85537500 140.02481861,35.85910805 140.01805889,35.85872334 140.01234500,35.85939222 140.00920027,35.85910389 140.00408750,35.86001250 140.00287306,35.86133250 140.00030972,35.86392417 139.99456972,35.86541111 139.99477805,35.86647417 139.99288556,35.86651777 139.99252889,35.86818861 139.99609973,35.86847166 140.00613528,35.86586055 140.01386166,35.86535278 140.02131333,35.86309500 140.02627388,35.86148638 140.03173694,35.85942472 140.03656250,35.86010306 140.04190722,35.86145417 140.04745722,35.85861278 140.05951778,35.85527250 140.06374861,35.85258667 140.06353694,35.84961750 140.05997528,35.84794722 140.05529917,35.84651389 140.05282583,35.84873278 140.04933750,35.85202389 140.04529333,35.85512611 140.04087861,35.85753194 140.03635000,35.85566778 140.02913944,35.85583388 140.02463806,35.85898695 140.01784861,35.85869806 140.01112861,35.85944917 140.00902916,35.85897722 140.00413195,35.86006694 140.00278389,35.86138667 140.00022667,35.86404472 139.99436167,35.86541333 139.99476750,35.86656500 139.99284916,35.86650723 139.99261306,35.86819861 139.99656555,35.86842445 140.00640528,35.86583194 140.01461278,35.86429694 140.02160861,35.86308695 140.02637584,35.86194166 140.03179806,35.85939166 140.03667445,35.86048667 140.04197862,35.86134528 140.04810861,35.85838111 140.05950778,35.85520639 140.06375722,35.85221528 140.06335500,35.84955833 140.05982166,35.84769888 140.05527667,35.84652000 140.05275084,35.84907667 140.04922833,35.85209722 140.04518083,35.85516250 140.04081834,35.85755611 140.03614000,35.85553361 140.02891916,35.85600000 140.02447333,35.85874444 140.01738194,35.85873584 140.01098084,35.85950611 140.00885000,35.85897084 140.00410972,35.86009111 140.00273194,35.86148333 140.00010056,35.86414750 139.99411972,35.86542833 139.99472222,35.86663833 139.99281055,35.86651084 139.99265028,35.86822055 139.99673944,35.86837333 140.00655778,35.86570389 140.01462583,35.86427778 140.02191388,35.86297917 140.02654889,35.86196278 140.03197111,35.85936666 140.03682277,35.86070194 140.04217972,35.86133833 140.04830195,35.85826278 140.05943638,35.85508194 140.06388083,35.85204139 140.06324584,35.84946194 140.05929305,35.84742222 140.05520167,35.84653833 140.05243083,35.84897917 140.04918333,35.85215806 140.04511333,35.85529667 140.04060083,35.85734889 140.03581000,35.85541362 140.02878583,35.85617222 140.02421417,35.85870639 140.01717167,35.85877389 140.01091862,35.85958166 140.00878778,35.85904694 140.00408000,35.86016334 140.00266528,35.86161000 139.99992222,35.86428805 139.99391695,35.86570722 139.99463222,35.86669084 139.99274611,35.86653361 139.99269361,35.86825250 139.99706889,35.86831055 140.00711916,35.86547778 140.01486055,35.86426833 140.02212778,35.86289639 140.02658166,35.86183861 140.03232722,35.85932500 140.03730111,35.86078472 140.04219250,35.86126361 140.04842027,35.85812222 140.05916166,35.85501584 140.06401333,35.85194361 140.06310361,35.84944722 140.05882139,35.84727944 140.05512666,35.84661750 140.05237834,35.84895473 140.04907861,35.85231056 140.04505334,35.85535167 140.03961250,35.85725722 140.03547834,35.85526111 140.02861305,35.85625528 140.02415944,35.85875028 140.01707833,35.85878667 140.01089528,35.85969583 140.00871000,35.85909083 140.00383500,35.86022389 140.00252416,35.86183945 139.99962445,35.86454167 139.99297278,35.86576834 139.99455500,35.86670639 139.99263250,35.86657195 139.99273278,35.86852944 139.99741750,35.86819500 140.00811083,35.86542055 140.01501333,35.86425889 140.02222972,35.86286333 140.02672417,35.86177195 140.03262528,35.85917583 140.03735223,35.86090889 140.04233500,35.86121389 140.04857305,35.85769167 140.05903944,35.85497445 140.06414639,35.85181138 140.06302194,35.84967667 140.05850305,35.84710111 140.05507417,35.84662361 140.05231833,35.84898528 140.04880111,35.85249500 140.04498584,35.85545417 140.03925972,35.85718417 140.03523833,35.85498666 140.02845611,35.85638277 140.02389666,35.85878723 140.01698500,35.85878028 140.01092639,35.85990472 140.00863222,35.85910972 140.00382028,35.86032222 140.00243500,35.86206167 139.99925333,35.86459000 139.99317639,35.86579278 139.99450139,35.86671000 139.99250361,35.86660333 139.99282528,35.86863388 139.99762389,35.86809000 140.00822834,35.86537277 140.01525973,35.86416361 140.02235167,35.86280528 140.02697250,35.86161167 140.03312416,35.85914278 140.03739278,35.86101667 140.04239611,35.86111500 140.04896000,35.85743612 140.05902917,35.85475916 140.06437055,35.85175333 140.06282166,35.84970611 140.05816333,35.84708334 140.05503666,35.84658694 140.05223584,35.84905222 140.04856750,35.85256805 140.04475195,35.85557611 140.03904973,35.85705583 140.03499111,35.85406667 140.02822084,35.85645944 140.02370305,35.85884000 140.01672806,35.85878028 140.01102750,35.85993638 140.00857000,35.85910361 140.00401695,35.86046695 140.00223667,35.86223667 139.99910528,35.86460195 139.99325139,35.86582611 139.99437472,35.86670305 139.99243500,35.86666278 139.99286945,35.86867778 139.99798556,35.86780639 140.00851000,35.86532500 140.01555334,35.86406806 140.02265722,35.86276389 140.02713195,35.86127584 140.03343972,35.85914667 140.03757611,35.86116778 140.04244695,35.86098833 140.04913389,35.85666611 140.05916166,35.85475083 140.06444166,35.85115723 140.06274000,35.84971361 140.05807556,35.84710111 140.05497667,35.84637361 140.05215333,35.84946722 140.04849250,35.85272666 140.04455694,35.85574695 140.03885472,35.85695834 140.03345472,35.85374500 140.02814222,35.85656805 140.02330389,35.85887583 140.01661139,35.85885000 140.01101972,35.85997416 140.00849222,35.85904694 140.00443250,35.86072056 140.00216250,35.86235139 139.99841473,35.86505000 139.99330833,35.86590305 139.99431611,35.86668556 139.99235139,35.86668556 139.99288778,35.86887750 139.99847611,35.86764889 140.00863917,35.86528667 140.01587028,35.86385694 140.02275861,35.86266444 140.02745722,35.86108528 140.03358667,35.85915111 140.03784278,35.86128361 140.04287194,35.86093056 140.04977528,35.85661639 140.05920222,35.85476750 140.06495056,35.85104945 140.06260389,35.84969139 140.05792195,35.84709305 140.05497667,35.84617833 140.05171083,35.84961472 140.04841000,35.85287306 140.04433194,35.85606416 140.03874222,35.85683639 140.03243000,35.85377000 140.02805583,35.85678500 140.02295306,35.85893167 140.01648667,35.85887528 140.01094194,35.85996167 140.00832889,35.85893139 140.00454389,35.86076889 140.00202889,35.86250222 139.99741056,35.86524305 139.99342000,35.86591528 139.99425611,35.86667694 139.99234055,35.86673611 139.99296000,35.86890472 139.99890195,35.86748083 140.00868611,35.86520083 140.01623694,35.86380917 140.02290139,35.86254056 140.02768111,35.86075945 140.03375527,35.85911778 140.03799555,35.86134167 140.04305528,35.86094694 140.04987695,35.85657333 140.05929389,35.85502417 140.06510305,35.85097500 140.06246750,35.84966167 140.05783611,35.84705444 140.05490917,35.84617222 140.05172111,35.84974278 140.04821500,35.85291583 140.04404666,35.85633528 140.03856222,35.85678750 140.03232889,35.85376361 140.02797750,35.85694694 140.02260944,35.85894556 140.01641667,35.85890056 140.01054500,35.85992361 140.00823472,35.85877944 140.00453639,35.86080472 140.00182111,35.86255056 139.99687611,35.86529139 139.99344778,35.86591695 139.99420666,35.86667694 139.99234694,35.86680944 139.99297278,35.86896833 139.99965056,35.86719028 140.00872138,35.86509584 140.01628388,35.86373278 140.02313527,35.86232528 140.02824833,35.86041972 140.03422333,35.85915111 140.03826027,35.86143278 140.04315722,35.86111250 140.05002639,35.85629194 140.05964000,35.85533861 140.06530666,35.85081944 140.06234000,35.84958028 140.05775055,35.84693056 140.05486417,35.84620889 140.05165361,35.84980389 140.04811750,35.85294638 140.04382834,35.85675611 140.03838222,35.85671305 140.03219528,35.85365500 140.02784306,35.85717028 140.02244639,35.85892472 140.01635445,35.85890027 140.01045917,35.85980973 140.00818806,35.85853889 140.00449194,35.86090750 140.00159834,35.86258667 139.99673944,35.86538306 139.99347778,35.86590305 139.99414444,35.86669084 139.99237055,35.86684250 139.99306778,35.86900528 140.00052000,35.86682750 140.00874472,35.86498111 140.01631917,35.86353222 140.02336945,35.86221778 140.02889944,35.86018806 140.03449833,35.85920056 140.03844361,35.86163139 140.04334027,35.86110444 140.05033194,35.85618639 140.05981306,35.85538833 140.06536306,35.85067778 140.06224916,35.84925500 140.05766500,35.84677583 140.05477389,35.84664806 140.05157861,35.84985277 140.04803500,35.85302584 140.04335584,35.85691444 140.03797500,35.85665194 140.03172444,35.85363583 140.02765473,35.85747000 140.02230028,35.85893861 140.01628444,35.85887500 140.01042028,35.85974000 140.00674972,35.85844361 140.00427666,35.86098583 140.00156862,35.86267723 139.99639027,35.86540861 139.99353806,35.86578084 139.99410361,35.86677806 139.99237278,35.86689666 139.99315639,35.86901528 140.00176444,35.86670334 140.00899138,35.86503861 140.01667139,35.86340805 140.02355277,35.86207056 140.02939834,35.86007222 140.03467139,35.85928333 140.03882000,35.86168111 140.04339139,35.86103805 140.05048111,35.85606861 140.05987417,35.85541333 140.06539666,35.85035944 140.06199444,35.84917361 140.05719889,35.84665223 140.05448889,35.84673972 140.05150361,35.84991972 140.04795222,35.85328194 140.04301083,35.85701833 140.03784750,35.85658500 140.03152028,35.85365500 140.02748222,35.85765528 140.02219722,35.85902945 140.01610528,35.85887528 140.01035806,35.85964500 140.00589222,35.85895195 140.00413556,35.86101611 140.00162055,35.86275556 139.99634028,35.86543000 139.99375250,35.86570389 139.99379250,35.86681139 139.99235556,35.86694750 139.99324111,35.86899334 140.00196417,35.86670334 140.00916750,35.86503861 140.01682417,35.86322694 140.02369667,35.86184722 140.02954083,35.85991500 140.03486472,35.85927528 140.03905417,35.86167278 140.04365583,35.86104639 140.05064723,35.85592778 140.06010833,35.85539222 140.06547472,35.85020972 140.06188555,35.84909222 140.05676111,35.84660583 140.05439139,35.84775750 140.05145111,35.85001139 140.04767472,35.85363445 140.04286833,35.85707305 140.03775750,35.85655444 140.03108861,35.85366778 140.02728584,35.85782000 140.02183639,35.85905028 ,35.85876000 140.02121250,35.85873223 140.02100778))', 6668) tega; 140.01596667,35.85885639 140.01026472,35.85958166 140.00532778,35.85901528 140.00406888,35.86102222 140.00159834,35.86287028 139.99632473,35.86556277 139.99399305,35.86568472 139.99363333,35.86682695 139.99231945,35.86705833 139.99358167,35.86896278 140.00205778,35.86676055 140.00930834,35.86500027 140.01693555,35.86316084 140.02385944,35.86170639 140.02986667,35.85979084 140.03500722,35.85922527 140.03924750,35.86163139 140.04386973,35.86095527 140.05099333,35.85577028 140.06056445,35.85537555 140.06542945,35.85012500 140.06164889,35.84888416 140.05647583,35.84660278 140.05363278,35.84781639 140.05142861,35.85011027 140.04727166,35.85396389 140.04271056,35.85725000 140.03761500,35.85655444 140.03101028,35.85375083 140.02702583,35.85795417 140.02171611,35.85904333 140.01572555,35.85879944 140.01026472,35.85937945 140.00531222,35.85916722 140.00400944,35.86097389 140.00150195,35.86303000 139.99627861,35.86559278 139.99410472,35.86568472 139.99356028,35.86685861 139.99225362,35.86712694 139.99390472,35.86890916 140.00239833,35.86675111 140.00950805,35.86501000 140.01700666,35.86314416 140.02409361,35.86164000 140.03027389,35.85974778 140.03520861,35.85921694 140.03936972,35.86149889 140.04403222,35.86074833 140.05150250,35.85560472 140.06074777,35.85539222 140.06531167,35.85009083 140.06129139,35.84874139 140.05646861,35.84649139 140.05351250,35.84787444 140.05134167,35.85029333 140.04700917,35.85416055 140.04242555,35.85725000 140.03751750,35.85659723 140.03083750,35.85379528 140.02682167,35.85805639 140.02162167,35.85900861 140.01557750,35.85871056 140.01031917,35.85930361 140.00538223,35.85921778 140.00395000,35.86092555 140.00128944,35.86307833 139.99626278,35.86561639 139.99426556,35.86573361 139.99350444,35.86688111 139.99222667,35.86722445 139.99420806,35.86889417 140.00265666,35.86669361 140.00959000,35.86505750 140.01708806,35.86315250 140.02433806,35.86162361 140.03043417,35.85966500 140.03540195,35.85918417 140.03951222,35.86145750 140.04424611,35.86044194 140.05210306,35.85556277 140.06103278,35.85537555 140.06506028,35.85006527 140.06107195,35.84853612 140.05619389,35.84647611 140.05343584,35.84793945 140.05108667,35.85034833 140.04680945,35.85417278 140.04238083,35.85724389 140.03734500,35.85659667 140.03070417,35.85387806 140.02671972,35.85822861 140.02149278,35.85893861 140.01548417,35.85860972 140.01030361,35.85925278 140.00533556,35.85945194 140.00374223,35.86096167 140.00106667,35.86310833 139.99622861,35.86562695 139.99447361,35.86576500 139.99344222,35.86689334 139.99222500,35.86742612 139.99433834,35.86881695 140.00282083,35.86662695 140.00964889,35.86504806 140.01755639,35.86322694 140.02453139,35.86162361 140.03052555,35.85957389 140.03547333,35.85908472 140.03959361,35.86145750 140.04486722,35.86005139 140.05240111,35.85557916 140.06128694,35.85529277 140.06467222,35.85003972 140.06095139,35.84851834 140.05611777,35.84649139 140.05333584,35.84814083 140.05096666,35.85036056 140.04675694,35.85416055 140.04211084,35.85730611 140.03733555,35.85659111 140.03029611,35.85391778 140.02658639,35.85829250 140.02141556,35.85884083 140.01543750,35.85854639 140.01024917,35.85921500 140.00493083,35.85946472 140.00364583,35.86101611 140.00100750,35.86323528 139.99618639,35.86574056 139.99461527,35.86581833 139.99337583,35.86689334 139.99225944,35.86752138 139.99466500,35.86880666 140.00296166,35.86658861 140.00978972,35.86489417 140.01782111,35.86322694 140.02465972,35.86162361 140.03056638,35.85936666 140.03554472,35.85901861 140.03973611,35.86149083 140.04585777,35.85985306 140.05265556,35.85562055 140.06157194,35.85529277 140.06449389,35.84990334 140.06089638,35.84851834 140.05596555,35.84650694 140.05316333,35.84829945 140.05087639,35.85037278 140.04665195,35.85416055 140.04183111,35.85734278 140.03724778,35.85656056 140.03004250,35.85395583 140.02650777,35.85841362 140.02138112,35.85877805

フィードバック募集中 開発元である Oracle社MySQLチームへ直接伝えられます • MySQL Bugs https://bugs.mysql.com/ • バグ報告だけでなく、機能追加リクエストも受け付けています • 特に、日本固有のGIS事情に合わせた機能追加リクエストを登録 いただけると助かります • 登録済みの報告／リクエストに対して「Affect me」ボタンを押す ことで、  他の人が登録したバグと同じバグに遭遇した  その機能追加を私も望んでいる ということを伝えることができます。

• https://bugs.mysql.com/

フィードバック募集中 • MySQL側で対応できないような、ツール側の 課題などは、周辺ツール側のコミュニティへの バグ報告等の働きかけもしていただけると、大 変嬉しいです。 • 特に各ツールの開発コミュニティとご縁がある 方、宜しくお願いします！ • GDALとかQGISとかUshahidiとかMapServerとか 色々

いまMySQLを始めましょう いまからMySQLのGIS機能をはじめるメリット 1. RDBMSで GIS機能を扱える選択肢が広がります 2. 関数など、これから増えていくので、MySQLの 進化に合わせて、一緒に成長していけます 3. まだ詳しい人がそれほどいないので、GIS業界の 皆様なら、きっとすぐに第一人者に！ 一緒に MySQL の GIS 機能の情報交換で盛り上 げていきましょう！

おしまい • FOSS4G 2018 TOKYOでもオラクル社MySQL チームの山﨑さんがお話＆ハンズオンをします。 • 参加される方はぜひ、そちらでもMySQLの GIS機能のお話に触れてみてください。 • RDBMS での GIS についての アドヴェントカ レンダーをやるかもしれません(準備中)。気が 向いたらご参加を。

その他参考資料 • FOSS4G Tokai 2018「MySQL 8.0で強化されたGIS 機能のご紹介＋α」 • https://www.slideshare.net/yoyamasaki/mysql-80gisfoss4g-tokai-2018 • MySQLリファレンスマニュアル • 11.5 Spatial Data Types https://dev.mysql.com/doc/refman/8.0/en/spatial-types.html • 12.15.8 Spatial Operator Functions https://dev.mysql.com/doc/refman/8.0/en/spatial-operatorfunctions.html • ClubMySQL #4 「周辺知識から理解するMySQL の GIS機能」 • https://www.slideshare.net/sakaik/mysql-gis-clubmysql-4 • Twitter ハッシュタグ #mysql_jp

• https://www.slideshare.net/yoyamasaki/mysql-80gisfoss4g-tokai-2018
• https://dev.mysql.com/doc/refman/8.0/en/spatial-types.html
• https://dev.mysql.com/doc/refman/8.0/en/spatial-operator-
• https://www.slideshare.net/sakaik/mysql-gis-clubmysql-4