FOSS4G Hokkaido 2024 データベースで地理情報を扱おう入門 / lets try gis data on the rdbms

2024年2月15日に札幌で開催された FOSS4G Hokkaido 2024 コアデイにて発表した
「データベースで地理情報を扱おう入門」の発表資料です。
https://foss4g.hokkaido.jp/2024/

あまりデータベースの活用が進んでいるとは言えない地理情報データを、MySQLなどのDBMSで扱うということはどういうことなのかを、まだそういう経験のない人を対象として概要説明しました。

Transcript

1. データベースで 地理情報を扱おう入門 ～データを「ちゃんと管理」するといいことがある？～ FOSS4G Hokkaido 2024 2025/02/15 有限会社アートライ 坂井 恵 ( @sakaik ) かもしれない
2. 自己紹介 坂井 恵 @sakaik From 千葉県我孫子市 市役所の位置はおよそ 北緯35.864352度、東経140.028402度 平面直角座標系では 9系 (-15032.1217 , 17616.4302) 市内に東経140度線が通る街（モニュメントはない） • 日本MySQLユーザ会副代表 ： データベース • OSGeo.JP 運営委員 ： 地理情報(GIS) • （有）アートライ代表取締役 ： おしごと • 測量士補 ： 趣味
3. 趣味： 測量関係のモニュメント等を見ること • 道央道の北緯43度の看板とか
4. 自己紹介：ちょっとだけ宣伝 • データベースの本がでます（2025年4月） • 『データベースエンジニア養成講座』 • MySQLのお勧め本（私も共著） • 『MySQL徹底入門 第4版』 • ...ですが今 買わないでください！ こちらの本の 弟分です！
5. 今日のお話の背景 「地理情報系の人って、 意外とデータベース使ってないんじゃない？」 ファイルで十分？ ？？？「ブラウザで表示するための情報を返すのにファイルが便利だし高速だし」
6. 今日のお話「データベースへのお誘い」 • データベースを使うとはどういうことか？を知ってもらいたい • 基本中の基本の中の基本のお話をします。 • 今日の参加者の半分くらいは知ってるかもしれないこと • 残りの半分の人に、「へぇぇ」と思ってもらいたい • 既にデータベースをご存じの方には当たり前のお話ばかりのはず • → こういう話を「伝える側」にぜひなっていただきたい！ • →界隈が成熟するにつれ、こういう話がされなくなってきています • 入り口の話は、常に誰かがしつづけなくては！！
7. データベースへのお誘い • データベースとは • データ管理する仕組み、ソフトウェア • MySQL、PostgreSQL、Oracle、MSSQL、Db2 等色々なソフト • 様々なデータ管理の方式があるが、ここでは「RDBMS」のお話 • RDBMS: Relational Database Management System • 上で挙げたソフトは全部 RDBMS • 「SQL」でデータ操作の命令を行う • 用語 • 今日は基本的に「データベース」「DB」「DBMS」「RDBMS」ぜん ぶ同じ意味で使います。
8. データベースを使うと何が良いの？ • 一元管理、整理されたデータ構造データ構造の整理 • 大量データの扱い（速度） • 安全への管理（大量登録中に止まったりしても大丈夫→atomic
9. 地理情報エンジニアにとってのDB利用 • 単なるデータ格納場所として • 近傍や都道府県等のエリア内などの検索に • 集約や より複雑な演算機能の活用 測地系や座標系の変換もできるので便利
10. 地理情報エンジニアにとってのDB利用 • QGISの「属性テーブル」で十分じゃない？ • →どれくらいの件数を扱えるのかな • テキストファイルで管理してRとかで読み込んで使えば.. • → （更新されるものでなく期間や時期で確定されるものならアリかも） 「単なるデータの格納場所として」 →データの一元管理のメリット
11. データベースのきほん • 「表（ひょう）」形式でデータを管理 • 表のことを一般に「テーブル」と呼ぶ（表と言う場合もある） • データベースだけ覚えても何もできない • Pythonなど開発言語から使うのが一般的（Rもいいぞ(と昨日思った)）
12. テーブル（表）の例 とあるファストフード店舗情報 ここには日付だけ ここには数値だけ ここは文字列 ここには数値だけ POINT • 各カラムには登録可能な「型」を定める • これはイメージを示すための例。実際のテー ブルを作るときはデータ管理の効率や安全性 を考慮し色々な工夫をする（複数のテーブル に分けるなど） ⇒ 「データ型」の理解
13. テーブル（表）の例(位置情報つき(1)) • 緯度と経度を単純に数値として登録するやり方 ここには数値だけ 数値が単に2つ管理されているだけ。 距離計算や包含検索など、位置情報データとして使いたい時には POINTに変換しながら使用する。
14. テーブル（表）の例(位置情報つき(2)) • 「数値」「日付」「文字」以外に「位置情報専用のデータ型」もある ここにはPOINTデータだけ POINT型： 点をあらわす。さまざまな測地系での緯度経度表現のほか、平面直角座標系をはじめとする投影座標系な ども扱える LINESTRING型： 複数の点を並べることで「線」を表せる。川や鉄道、道路などでよく使われる POLYGON型： 複数の点を並べてぐるりと囲んだ「エリア」「形」を表せる。自治体境界や湖沼の形などに使われる
15. 参考：テーブル作成とデータ登録の例 CREATE TABLE shops1 ( id integer primary key not null , name varchar(40), pref varchar(5), address varchar(120), open_date date, wifi_ari smallint, p_ari smallint, asa smallint, site_m2 integer, build_m2 integer, lat float, lon float); INSERT INTO shops1 VALUES ('101','西町店','北海道','札幌市西区某某1-4-1','1997/10/10','1','0','1','1930','351','43.0773043','141.292534'); INSERT INTO shops1 VALUES ('102','白石店','北海道','札幌市白石区某某1-2-3','1998/2/14','1','0','1','1000','500','43.0446331','141.400104'); INSERT INTO shops1 VALUES ('105','某ロラタウン店','北海道','札幌市中央区某某1-4-6','1998/6/21','1','0','0','1010','505','43.061023','141.355693'); INSERT INTO shops1 VALUES ('109','平岸店','北海道','札幌市豊平区某某2-3-3','1998/10/26','1','1','1','1020','510','43.0306221','141.368216'); INSERT INTO shops1 VALUES ('110','南新川店','北海道','札幌市北区某某1-4-3','1999/3/2','1','0','1','1030','515','43.0876822','141.327977'); INSERT INTO shops1 VALUES ('115','南二条店','北海道','札幌市中央区某某3-2-3','1999/7/7','1','0','1','1040','520','43.0580542','141.355736'); INSERT INTO shops1 VALUES ('120','すすきの店','北海道','札幌市中央区某某42-3','1999/11/11','1','0','1','1050','525','43.0558891','141.352973'); INSERT INTO shops1 VALUES ('189','宮の沢店','北海道','札幌市西区某某1-2-31','2000/3/17','0','0','0','1060','530','43.0891312','141.276136'); ：
16. 参考：データ確認の例 mysql> SELECT * FROM shops1; +-----+--------------------------+-----------+---------------------------------+------------+----------+-------+------+---------+----------+---------+---------+ | id | name | pref | address | open_date | wifi_ari | p_ari | asa | site_m2 | build_m2 | lat | lon | +-----+--------------------------+-----------+---------------------------------+------------+----------+-------+------+---------+----------+---------+---------+ | 101 | 西町店 | 北海道 | 札幌市西区某某1-4-1 | 1997-10-10 | 1 | 0 | 1 | 1930 | 351 | 43.0773 | 141.293 | | 102 | 白石店 | 北海道 | 札幌市白石区某某1-2-3 | 1998-02-14 | 1 | 0 | 1 | 1000 | 500 | 43.0446 | 141.4 | | 105 | 某ロラタウン店 | 北海道 | 札幌市中央区某某1-4-6 | 1998-06-21 | 1 | 0 | 0 | 1010 | 505 | 43.061 | 141.356 | | 109 | 平岸店 | 北海道 | 札幌市豊平区某某2-3-3 | 1998-10-26 | 1 | 1 | 1 | 1020 | 510 | 43.0306 | 141.368 | | 110 | 南新川店 | 北海道 | 札幌市北区某某1-4-3 | 1999-03-02 | 1 | 0 | 1 | 1030 | 515 | 43.0877 | 141.328 | | 115 | 南二条店 | 北海道 | 札幌市中央区某某3-2-3 | 1999-07-07 | 1 | 0 | 1 | 1040 | 520 | 43.0581 | 141.356 | | 120 | すすきの店 | 北海道 | 札幌市中央区某某42-3 | 1999-11-11 | 1 | 0 | 1 | 1050 | 525 | 43.0559 | 141.353 |
17. 参考：POINT型を使ったデータ作成例 • 先ほど lat, lon を別々のカラムに格納したものを、ひとつの POINT型のカラムにした新たなテーブルを作成する例 CREATE TABLE shops2 AS SELECT id, name, pref, address, open_date, wifi_ari, p_ari, asa, site_m2, build_m2, ST_SRID(Point(lon,lat),4326) pos FROM shops1;
18. 距離の計算 • 今日の会場(43.071917, 141.359673) との距離を求める • 距離が 2500m 以内のもののみを結果として返す • 結果は距離が近い順に並べる mysql> SELECT id, name, address, ST_AsText(pos), -> ST_Distance_Sphere(ST_GeomFromText('POINT(43.071917 141.359673)',4326),pos) distance -> FROM shops2 -> WHERE ST_Distance_Sphere(ST_GeomFromText('POINT(43.071917 141.359673)',4326),pos) < 2500 -> ORDER BY distance; +-----+--------------------------+---------------------------------+---------------------------------------------+--------------------+ | id | name | address | ST_AsText(pos) | distance | +-----+--------------------------+---------------------------------+---------------------------------------------+--------------------+ | 194 | ピアピ店 | 札幌市中央区某某11-2-31 | POINT(43.06763458251953 141.3516845703125) | 804.8774762814844 | | 232 | 札幌モール店 | 札幌市東区某某2-5-1 | POINT(43.07170867919922 141.37005615234375) | 843.7188769011758 | | 261 | 札幌ビル店 | 札幌市中央区某某1-5-1 | POINT(43.06507873535156 141.35057067871094) | 1060.6105446992144 | | 202 | 札幌家電量販店店 | 札幌市北区某某2-3-1 | POINT(43.06837844848633 141.34722900390625) | 1084.7062919625173 | | 105 | 某ロラタウン店 | 札幌市中央区某某1-4-6 | POINT(43.0610237121582 141.35569763183594) | 1253.5902939317725 | | 115 | 南二条店 | 札幌市中央区某某3-2-3 | POINT(43.05805587768555 141.35574340820312) | 1574.0002828961349 | | 120 | すすきの店 | 札幌市中央区某某42-3 | POINT(43.05588912963867 141.35296630859375) | 1863.6416348760308 | | 225 | 札幌桑園店 | 札幌市中央区某某5-2-2 | POINT(43.069671630859375 141.3324432373047) | 2225.8980236801376 | +-----+--------------------------+---------------------------------+---------------------------------------------+--------------------+ 8 rows in set (0.00 sec)
19. 参考 • DBからの取得結果を地図にプ ロットしたもの ★は現在地(筆者が手動でプロット) (GUIツール DBeaverを使用) ★
20. その他のDB活用例
21. 測地系の変換 • 地理座標系と投影座標系の変換なども、命令(関数)ひとつで！ • 抽出、集計、演算などの処理の中で当たり前のように変換できる • たぶん、この次のセッションで、この変換まわりの話を詳しく 聞ける・・・はず
22. エリアに含まれるポイントを抽出 • 「札幌市」のポリゴン（これもDBに登録済とする）の内部に含 まれるポイントを取得 SELECT p.gid, p.x, p.y FROM tdfk j, geopoint p WHERE ST_Contains(j.geom,ST_SetSRID(ST_MakePoint(x,y), 4326)) AND j.n03_004 like '札幌市'
23. エリアごとに含まれるポイントを数え上げ • 「北海道」の各市町村のポリゴン（これもDBに登録済とする） ごとの内部に含まれるポイントをカウント SELECT n03_001 || n03_004, COUNT(*) cnt FROM tdfk j, geopoint p WHERE ST_Contains(j.geom, ST_SetSRID(ST_MakePoint(x,y), 4326)) AND n03_001 LIKE '北海道' GROUP BY n03_001, n03_004 ORDER BY COUNT(*) DESC ◦◦市 1256 ◦◦市 823 ◦◦市 6899 ◦◦市 21 ：
24. ポリゴンどうしの演算 • 地域メッシュポリゴン と「危険区域」ポリゴンの演算 • 共通部分を算出 • 地域メッシュの面積を算出 • 「危険区域」の面積を算出 • 各地域メッシュ内の危険区域割合を算出
25. 参考：ポリゴンどうしの演算(クエリ) • 地域メッシュポリゴン と「危険区域」ポリゴンの演算 • 共通部分を算出 • 地域メッシュの面積を算出 cat > sql_tmpl_years.txt INSERT INTO result_shinsui_pg_years SELECT j.year,m.key_code, SUM(ST_Area(ST_Intersection(m.shape, s.g))) / ST_Area(m.shape) wariai, SUM((ST_Area(ST_Intersection(m.shape, s.g))) / ST_Area(m.shape)) * MAX(j.total) target_jinko, MAX(j.total) mesh_jinko FROM chiri_mesh m LEFT OUTER JOIN shinsui_geom s ON (ST_Intersects(m.shape, s.g)) LEFT OUTER JOIN jinko_mesh_4th_allyear j ON (m.key_code=j.key_code) WHERE m.key_code like '@@@@%' AND s.meshcode='@@@@' AND s.dataname LIKE '%_10_%' AND j.year='yyyy' GROUP BY j.year,m.key_code;
26. まとめ • データベースいいよ！ • ただ「管理」させたいときにも • いろいろ「発見」したいときにも • データベースいいよ！ • 件数多いとき • 演算したいとき • Visualizeは仕事の範囲外 • ぜひ触ってみて • ぜひ伝えてみて • 実はまわりに詳しい人がいっぱいいるから、悩んだら訊いてみて