Share
Embed
FOSS4G Hokkaido 2024 データベースで地理情報を扱おう入門 / lets try gis data on the rdbms
2024年2月15日に札幌で開催された FOSS4G Hokkaido 2024 コアデイにて発表した
「データベースで地理情報を扱おう入門」の発表資料です。
https://foss4g.hokkaido.jp/2024/
あまりデータベースの活用が進んでいるとは言えない地理情報データを、MySQLなどのDBMSで扱うということはどういうことなのかを、まだそういう経験のない人を対象として概要説明しました。
sakaik
March 01, 2025
Transcript
27 slides
-
自己紹介 坂井 恵 @sakaik From 千葉県我孫子市 市役所の位置はおよそ 北緯35.864352度、東経140.028402度 平面直角座標系では 9系
(-15032.1217 , 17616.4302) 市内に東経140度線が通る街(モニュメントはない) • 日本MySQLユーザ会副代表 : データベース • OSGeo.JP 運営委員 : 地理情報(GIS) • (有)アートライ代表取締役 : おしごと • 測量士補 : 趣味 -
自己紹介:ちょっとだけ宣伝 • データベースの本がでます(2025年4月) • 『データベースエンジニア養成講座』 • MySQLのお勧め本(私も共著) • 『MySQL徹底入門 第4版』
• ...ですが今 買わないでください! こちらの本の 弟分です! -
今日のお話「データベースへのお誘い」 • データベースを使うとはどういうことか?を知ってもらいたい • 基本中の基本の中の基本のお話をします。 • 今日の参加者の半分くらいは知ってるかもしれないこと • 残りの半分の人に、「へぇぇ」と思ってもらいたい •
既にデータベースをご存じの方には当たり前のお話ばかりのはず • → こういう話を「伝える側」にぜひなっていただきたい! • →界隈が成熟するにつれ、こういう話がされなくなってきています • 入り口の話は、常に誰かがしつづけなくては!! -
データベースへのお誘い • データベースとは • データ管理する仕組み、ソフトウェア • MySQL、PostgreSQL、Oracle、MSSQL、Db2 等色々なソフト • 様々なデータ管理の方式があるが、ここでは「RDBMS」のお話
• RDBMS: Relational Database Management System • 上で挙げたソフトは全部 RDBMS • 「SQL」でデータ操作の命令を行う • 用語 • 今日は基本的に「データベース」「DB」「DBMS」「RDBMS」ぜん ぶ同じ意味で使います。 -
地理情報エンジニアにとってのDB利用 • QGISの「属性テーブル」で十分じゃない? • →どれくらいの件数を扱えるのかな • テキストファイルで管理してRとかで読み込んで使えば.. • → (更新されるものでなく期間や時期で確定されるものならアリかも)
「単なるデータの格納場所として」 →データの一元管理のメリット -
テーブル(表)の例 とあるファストフード店舗情報 ここには日付だけ ここには数値だけ ここは文字列 ここには数値だけ POINT • 各カラムには登録可能な「型」を定める •
これはイメージを示すための例。実際のテー ブルを作るときはデータ管理の効率や安全性 を考慮し色々な工夫をする(複数のテーブル に分けるなど) ⇒ 「データ型」の理解 -
参考:テーブル作成とデータ登録の例 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'); : -
参考:データ確認の例 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 | -
参考: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; -
距離の計算 • 今日の会場(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) -
エリアに含まれるポイントを抽出 • 「札幌市」のポリゴン(これも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 '札幌市' -
エリアごとに含まれるポイントを数え上げ • 「北海道」の各市町村のポリゴン(これも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 : -
ポリゴンどうしの演算 • 地域メッシュポリゴン と「危険区域」ポリゴンの演算 • 共通部分を算出 • 地域メッシュの面積を算出 • 「危険区域」の面積を算出
• 各地域メッシュ内の危険区域割合を算出 -
参考:ポリゴンどうしの演算(クエリ) • 地域メッシュポリゴン と「危険区域」ポリゴンの演算 • 共通部分を算出 • 地域メッシュの面積を算出 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; -
まとめ • データベースいいよ! • ただ「管理」させたいときにも • いろいろ「発見」したいときにも • データベースいいよ! •
件数多いとき • 演算したいとき • Visualizeは仕事の範囲外 • ぜひ触ってみて • ぜひ伝えてみて • 実はまわりに詳しい人がいっぱいいるから、悩んだら訊いてみて