初めに

この記事は、RDBMS-GIS(MySQL,PostgreSQLなど) Advent Calendar 2021のカレンダー | Advent Calendar 2021 - Qiitaのエントリーです。

検証環境

MySQLで検証しています。

root@6438500bc2d7:/# mysql --version
mysql  Ver 8.0.24 for Linux on x86_64 (MySQL Community Server - GPL)
root@6438500bc2d7:/# uname -a
Linux 6438500bc2d7 5.4.72-microsoft-standard-WSL2 #1 SMP Wed Oct 28 23:40:43 UTC 2020 x86_64 GNU/Linux

dockerにMySQL構築。

とある日

この記事は、GISで使うテーブル設計ってどうするんだろうと言う疑問を記事にしたものです。

GIS初心者的に気になった内容を調べてみようと思います。

一通り、設計からSQLの発行までやってみようと思います。

検証

私がMySQLを始めた理由

上記の記事にあったISUCON10の問題を少しだけ変更してやっていきたいと思います。

設計編

MySQLだとPOINTで緯度と経度の情報を保持します。

CREATE TABLE advents (
    id int PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(64) NOT NULL,
    description VARCHAR(4096) NOT NULL,
    address VARCHAR(128) NOT NULL,
    latlon POINT not null
);

テストデータを生成してみる。

INSERT INTO
    advents (name, description, address, latlon)
VALUES
    (
        "茶禅花",
        "三ツ星",
        "東京都 港区 南麻布 4-7-5",
        ST_GeomFromText('POINT(35.64980785547675 139.72841580761832)')
    );

INSERT INTO
    advents (name, description, address, latlon)
VALUES
    (
        "ヘレタ",
        "ハイセンス",
        "東京都 中央区 銀座 1-21-14",
        ST_GeomFromText('POINT(35.672680675384115 139.7706892178345)')
    );

INSERT INTO
    advents (name, description, address, latlon)
VALUES
    (
        "RhinoiR",
        "いくら",
        "東京都 港区 白金台 4-2-7",
        ST_GeomFromText('POINT(35.63955098605981 139.72810047607342)')
    );

INSERT INTO
    advents (name, description, address, latlon)
VALUES
    (
        "256 Chinese Restaurant",
        "肉",
        "東京都港区南青山7-10-10",
        ST_GeomFromText('POINT(35.659105441114136 139.7187464797685)')
    );

INSERT INTO
    advents (name, description, address, latlon)
VALUES
    (
        "エノキ飯店",
        "熱そう",
        "東京都 目黒区 三田 2-10-30",
        ST_GeomFromText('POINT(35.63924488847674 139.71247107704005)')
    );

INSERT INTO
    advents (name, description, address, latlon)
VALUES
    (
        "中国飯店 山麗川",
        "辛そう",
        "東京都 港区 東麻布 3-7-5",
        ST_GeomFromText('POINT(35.656449510639035 139.73851674670985)')
    );

INSERT INTO
    advents (name, description, address, latlon)
VALUES
    (
        "グンゲ",
        "フカヒレ",
        "東京都 中央区 銀座 7-4-5",
        ST_GeomFromText('POINT(35.670336627726805 139.76115233409124)')
    );

INSERT INTO
    advents (name, description, address, latlon)
VALUES
    (
        "相厨房 無",
        "アワビ",
        "    東京都 港区 白金 6-5-5",
        ST_GeomFromText('POINT(35.6445676673225 139.72518049284164)')
    );

INSERT INTO
    advents (name, description, address, latlon)
VALUES
    (
        "翡翠",
        "すごそう",
        "東京都 港区 六本木 4-4-2",
        ST_GeomFromText('POINT(35.66527837632412 139.7323430295452)')
    );

INSERT INTO
    advents (name, description, address, latlon)
VALUES
    (
        "猫峰",
        "上から",
        "東京都 港区 六本木 3-8-7",
        ST_GeomFromText('POINT(35.66344164600342 139.73458798966266)')
    );

id	name	description	address	latlon
1	茶禅花	三ツ星	東京都港区南麻布 4-7-5	POINT (35.64980785547675 139.72841580761832)
2	ヘレタ	ハイセンス	東京都中央区銀座 1-21-14	POINT (35.672680675384115 139.7706892178345)
3	RhinoiR	いくら	東京都港区白金台 4-2-7	POINT (35.63955098605981 139.72810047607342)
4	256 Chinese Restaurant	肉	東京都港区南青山7-10-10	POINT (35.659105441114136 139.7187464797685)
5	エノキ飯店	熱そう	東京都目黒区三田 2-10-30	POINT (35.63924488847674 139.71247107704005)
6	中国飯店山麗川	辛そう	東京都港区東麻布 3-7-5	POINT (35.656449510639035 139.73851674670985
7	グンゲ	フカヒレ	東京都中央区銀座 7-4-5	POINT (35.670336627726805 139.76115233409124)
8	相厨房無	アワビ	東京都港区白金 6-5-5	POINT (35.6445676673225 139.72518049284164)
9	翡翠	すごそう	東京都港区六本木 4-4-2	POINT (35.66527837632412 139.7323430295452)
10	猫峰	上から	東京都港区六本木 3-8-7	POINT (35.66344164600342 139.73458798966266)

ST_GeomFromText('POINT(35.64980785547675 139.72841580761832)')は、文字列からGeometryに変換するための関数です。

検索編

近場のお店を検索

SELECT * from advents a order by ST_Distance(ST_GeomFromText('POINT(35.66344164600342 139.73458798966266)'),latlon) ;

id	name	description	address	latlon
10	猫峰	上から	東京都港区六本木 3-8-7	POINT (35.66344164600342 139.73458798966266)
9	翡翠	すごそう	東京都港区六本木 4-4-2	POINT (35.66527837632412 139.7323430295452)
6	中国飯店山麗川	辛そう	東京都港区東麻布 3-7-5	POINT (35.656449510639035 139.73851674670985)
1	茶禅花	三ツ星	東京都港区南麻布 4-7-5	POINT (35.64980785547675 139.72841580761832)
4	256 Chinese Restaurant	肉	東京都港区南青山7-10-10	POINT (35.659105441114136 139.7187464797685)
8	相厨房無	アワビ	東京都港区白金 6-5-5	POINT (35.6445676673225 139.72518049284164)
3	RhinoiR	いくら	東京都港区白金台 4-2-7	POINT (35.63955098605981 139.72810047607342)
7	グンゲ	フカヒレ	東京都中央区銀座 7-4-5	POINT (35.670336627726805 139.76115233409124)
5	エノキ飯店	熱そう	東京都目黒区三田 2-10-30	POINT (35.63924488847674 139.71247107704005)
2	ヘレタ	ハイセンス	東京都中央区銀座 1-21-14	POINT (35.672680675384115 139.7706892178345)

ST_Distance

二点間の距離を図ります。

比較対象は適当に東京タワーの位置情報を使ってます。

東京タワー：ST_GeomFromText('POINT(35.66344164600342 139.73458798966266)'

ST_Distance(*`g1`*, *`g2`* [, *`unit`*])
g1 と g2 の間の距離を戻します。ジオメトリ引数の空間参照システム (SRS) の長さ単位で測定されるか、オプションの unit 引数 (指定されている場合) の単位で測定されます。

特定の区にあるお店を検索

港区にあるお見せを検索します。

港区の座標を適当に取ってきてます。

去年初めて色々とGISを触ったときPolygonは出発点と最終点が同じにしないと怒られることを思い出した。

範囲を設定するためにPolygonを定義してます。

SET
    @json = '{
  "type": "Polygon",
  "coordinates": [
    [
      [ 139.70856889128842,35.66437406359891],
      [ 139.76220227537425,35.66633039323733],
      [ 139.73594533214896,35.623490121041364],
      [ 139.71939832682938,35.67824257816885],
      [ 139.70856889128842,35.66437406359891]
    ]
  ],
  "crs": {
    "type":"name",
      "properties": {
        "name":"EPSG:4326"
      }
  }
}';
SET @area = ST_GeomFromGeoJSON(@json);
SELECT * FROM advents a where 1 = ST_Contains(@area, latlon);

id	name	description	address	latlon
1	茶禅花	三ツ星	東京都港区南麻布 4-7-5	POINT (139.72841580761832 35.64980785547675)
6	中国飯店山麗川	辛そう	東京都港区東麻布 3-7-5	POINT (139.73851674670985 35.656449510639035)
10	猫峰	上から	東京都港区六本木 3-8-7	POINT (139.73458798966266 35.66344164600342)

ST_GeomFromGeoJSON

ST_GeomFromGeoJSON(*`str`* [, *`options`* [, *`srid`*]])
GeoJSON オブジェクトを表す文字列 str を解析し、ジオメトリを戻します。

srids: 4326

はじめのINSERT文のままだと下記のエラーが出る。

Binary geometry function st_contains given two geometries of different srids: 4326 and 0, which should have been identical.

なのでINSERT文を修正。

insert into advents (name,description,address,latlon) values (
    "茶禅花","三ツ星","東京都 港区 南麻布 4-7-5",ST_GeomFromText('POINT(35.64980785547675 139.72841580761832)', 4326)
);

見た目は何も変わらない。

ちなみに、addressで住所も保持しているので下記のようにも取得可能。

select * from advents where address like "%港区%";

id	name	description	address	latlon
1	茶禅花	三ツ星	東京都港区南麻布 4-7-5	POINT (139.72841580761832 35.64980785547675)
3	RhinoiR	いくら	東京都港区白金台 4-2-7	POINT (139.72810047607342 35.63955098605981)
4	256 Chinese Restaurant	肉	東京都港区南青山7-10-10	POINT (139.7187464797685 35.659105441114136)
6	中国飯店山麗川	辛そう	東京都港区東麻布 3-7-5	POINT (139.73851674670985 35.656449510639035
8	相厨房無	アワビ	東京都港区白金 6-5-5	POINT (139.72518049284164 35.6445676673225)
9	翡翠	すごそう	東京都港区六本木 4-4-2	POINT (139.7323430295452 35.66527837632412)
10	猫峰	上から	東京都港区六本木 3-8-7	POINT (139.73458798966266 35.66344164600342)

適当な範囲から港区の範囲を抽出したのでその部分が弾かれている。

参考記事

MySQLに超・雑に大量のPOINTデータ(位置データ)を作成する方法 - sakaikの日々雑感～(T)編

MySQLでGISデータを扱う - Qiita

mysql:mysql8_gis_bug [あにねこ電脳覚書]

〆

一年に1回ぐらいしかGISを触らないがなかなかに難しい。

順番とか気にするところが多くて気軽に触るのが大変。

明日（2021年12月20日）は@RDBMS さんの記事です。

今日はなにの日。

気になったこと勉強になったことのメモ。

今日は、MySQLのGISを使って検索を試してみるの日。

初めに