pgunconf26-postgis-chinatown.pdf

PostGIS and PostgreSQL and Chinatown.

Transcript

1. 麺とポスグレと私 中華街とラーメンと私
2. 自己紹介 • ぬこ＠横浜 (@nuko_yokohama) • にゃーん • 趣味でポスグレをやってる者だ
3. 中華街、知ってる？ • 日本には中華街が 3 箇所ある。 – 横浜、神戸、長崎 • 実はアジア圏最大の中華街は横浜中華街 • 飲食店、何軒あるんだっけ・・・。 • 横浜中華街は横浜市中区にある。 自分も中区住み 中華街はご近所
4. 横浜は広い。 • なにげに広い横浜市。 – 中区はその中でも小さい区のようにみえるが・・・ 「横浜市中区」 神奈川県 町田市
5. 中区にもいろんなエリアがある 「中華街」の店を ピンポイントで 探したい・・・ みなとみらい 野毛 関内 伊勢佐木 中華街 山手・本牧 PostGIS を 使おう！ デンジャーゾーン
6. PostGIS を使ってみる • PostgreSQL の GIS 拡張機能。 • 機能は盛りだくさん – ジオメトリ型、幾何関数、空間インデクス、 座標系管理機能 等々 – 今の自分では全然使いこなせてないけど • ドキュメントはちょい癖あるな・・・ – 自分の GIS の知識が乏しいせい＆慣れの問題？ • PostgreSQL 13 + PostGIS 3.0.3 を使用 – PostgreSQL 14-devel だと SEGV る・・・
7. やりたいこと • 「中華街」を示すエリアを POLYGON で表現する。 • 以前、収集した（主にラーメンの）店舗の緯度・経度情報が、 「中華街」エリアの POLYGON に含まれるかを評価する。 • 「横浜市中区」よりも更に狭い範囲での絞り込みを行う。 「中華街」の 🍜 を検索したい
8. 中華街の位置情報を生成・格納する
9. 中華街 POLYGON を定義する オレンジ色の 領域を「中華街」 とみなす この多角形の 頂点をつなぐことで POLYGON を定義
10. 中華街 POLYGON の例 • PostGIS の SQL 関数を使って POLYGON を構築する。 ramendb=# SELECT ST_GeomFromText('SRID=4326;POLYGON(( 139.6402288 35.4419188, 139.6432795 35.4456772, 139.6469366 35.4442584, 139.6505189 35.4422005, 139.6446132 35.4395213, 139.6402288 35.4419188 ))') ; st_geomfromtext ----------------------------------------------------------------------------------------------------------------------------- ------------------------------------------------------------------------------------------------------- 0103000020E61000000100000006000000A2BE1BC17C74614069BE94CB90B84140FED5E3BE95746140534953F30BB94140BC7262B4B3746140957C9175DD B84140C21D030DD17461402EE3A6069AB841403D92DCABA07461409DB2E43B42B84140A2BE1BC17C74614069BE94CB90B84140 (1 row) ramendb=# 謎い文字列 = geometry 型 経度 緯度 を引数にする
11. 中華街 POLYGON を格納する • 中華街 POLYGON をテーブルに格納する。 CREATE TABLE area_geometry (id int primary key, name text); SELECT AddGeometryColumn('', 'area_geometry', 'geo', 4326, 'POLYGON', 2); TRUNCATE area_geometry ; INSERT INTO area_geometry VALUES (1, ' 中華街 ', ST_GeomFromText('SRID=4326;POLYGON(( 139.6402288 35.4419188, 139.6432795 35.4456772, 139.6469366 35.4442584, 139.6505189 35.4422005, 139.6446132 35.4395213, 139.6402288 35.4419188))') ) ;
12. 店舗の座標 • 店舗座標（経度 緯度）情報をもつテーブル ramendb=# \d+ shops_location Table "public.shops_location" Column | Type | Collation | Nullable | Default | Storage | Stats target | Description -----------+---------+-----------+----------+---------+---------+--------------+------------- sid | integer | | | | plain | | latitude | real | | | | plain | | longitude | real | | | | plain | | Access method: heap ramendb=# TABLE shops_location LIMIT 3; sid | latitude | longitude -----+-----------+----------- 1 | 35.66959 | 139.70348 2 | 35.65721 | 139.70378 3 | 35.660694 | 139.69803 (3 rows) 約 12 万軒の 店舗情報を収集
13. 店舗の座標 • 店舗座標（経度 緯度）を geometry に変換したテーブルを作る CREATE TABLE shops_location_geometry (sid int primary key); SELECT AddGeometryColumn('', 'shops_location_geometry', 'geo', 4326, 'POINT', 2); TRUNCATE shops_location_geometry; INSERT INTO shops_location_geometry SELECT sid,st_geomfromtext(((('POINT('::text || shops_location.longitude) || ' '::text) || shops_location.latitude) || ')'::text, 4326) AS geo FROM shops_location; AddGeometryColumn() を 実行するために、 MVIEW でなく フツーのテーブルを使う
14. 店舗の座標 • 店舗の geometry 情報をもつテーブル ramendb=# \d shops_location_geometry Table "public.shops_location_geometry" Column | Type | Collation | Nullable | Default --------+----------------------+-----------+----------+--------- sid | integer | | not null | geo | geometry(Point,4326) | | | Indexes: "shops_location_geometry_pkey" PRIMARY KEY, btree (sid) ramendb=# TABLE shops_location_geometry LIMIT 3; sid | geo -----+---------------------------------------------------- 1 | 0101000020E61000007C2C7DE88276614044DD0720B5D54140 2 | 0101000020E61000008672A25D85766140554D10751FD44140 3 | 0101000020E61000000FB4024356766140EB54F99E91D44140 (3 rows)
15. 中華街の位置情報を使った検索
16. テーブルと関連 geo 型どうしを PostGIS の演算子や 関数を使って比較する sid latutitude longitude sid latutitude longitude sid latutitude longitude id name geo sid latutitude sid geo sid latutitude longitude sid name pref sid ・・・ rid latutitude longitude sid menu score sid uid ・・・ name uid area_geometry shops_location_geometry shops_location shops reviews users PostGIS の geo 生成関数結果を 挿入する。 POLYGON を示す 「経度 緯度」文字列 ( 約 12 万件 ) ( 約 100 万件 ) ( 約 22 万件 ) ( 約 12 万件 )
17. 中華街に含まれる店を検索する • && 演算子による検索 ramendb=# SELECT s.name, s.branch, s.status, s.reg_date FROM shops s, shops_location_geometry slg, area_geometry ag WHERE s.sid = slg.sid AND ag.geo && slg.geo AND ag.id = 1 ORDER BY s.reg_date LIMIT 5; name | branch | status | reg_date --------------------------+--------+--------+------------ 東光飯店 | | open | 2005-12-03 横濱元町 塩らー麺 本丸亭 | 元町店 | open | 2006-01-15 四川料理 京華樓 | | open | 2006-09-16 徳記 | | open | 2006-10-24 東光飯店 | 別館 | open | 2006-10-30 (5 rows) お、それっぽい 結果かな・・・？
18. 中華街に含まれる店を検索する • && 演算子による検索 ramendb=# SELECT s.name, s.branch, s.status, s.reg_date FROM shops s, shops_location_geometry slg, area_geometry ag WHERE s.sid = slg.sid AND ag.geo && slg.geo AND ag.id = 1 ORDER BY s.reg_date LIMIT 5; name | branch | status | reg_date --------------------------+--------+--------+------------ 東光飯店 | | open | 2005-12-03 横濱元町 塩らー麺 本丸亭 | 元町店 | open | 2006-01-15 四川料理 京華樓 | | open | 2006-09-16 徳記 | | open | 2006-10-24 東光飯店 | 別館 | open | 2006-10-30 (5 rows) あれ？ ちょっとおかしいぞ
19. 意図通りの結果になってない・・・ この場所の店が ヒットしちゃった・・・ ★
20. （余談） Twitter マジ便利 つよつよな人の アドバイス得られるので Twitter マジ便利
21. && だとこういう検索になるっぽい && だと、 青点線内の 範囲がヒットする ★
22. 中華街に含まれる店を検索する • ST_Contains による検索 ramendb=# SELECT s.name, s.branch, s.status, s.reg_date FROM shops s, shops_location_geometry slg, area_geometry ag WHERE s.sid = slg.sid AND ST_Contains(ag.geo, slg.geo) AND ag.id = 1 ORDER BY s.reg_date LIMIT 5; name | branch | status | reg_date -----------------------+--------------------+--------+------------ 東光飯店 | | open | 2005-12-03 四川料理 京華樓 | | open | 2006-09-16 徳記 | | open | 2006-10-24 東光飯店 | 別館 | open | 2006-10-30 中国ラーメン 揚州商人 | 横浜スタジアム前店 | open | 2007-01-21 (5 rows) 今度はあってそう
23. 中華街に含まれる店を検索する • 自分が中華街のラーメンをどの程度食べているか確認 ramendb=# SELECT COUNT(*) FROM shops s, shops_location_geometry slg, area_geometry ag WHERE s.sid = slg.sid AND ST_Contains(ag.geo, slg.geo) AND ag.id = 1 AND s.category @> '{"ramendb":true}' AND s.status = 'open' ; count ------- 194 (1 row) ramendb=# SELECT COUNT(*) FROM shops s, shops_location_geometry slg, area_geometry ag WHERE s.sid = slg.sid AND ST_Contains(ag.geo, slg.geo) AND ag.id = 1 AND s.category @> '{"ramendb":true}' AND s.status = 'open' AND s.sid IN ( SELECT DISTINCT sid FROM reviews WHERE uid = 8999 ) ; count ------- 167 (1 row) 86% の店舗で 食べたことが あるようだ。
24. 応用編 • 環状 2 号線ラーメン街道を LINESTRING で表現。 ramendb=# SELECT s.sid, s.name, s.branch, s.status FROM line_geometry lg , shops_location_geometry slg JOIN shops s ON (slg.sid = s.sid) JOIN shops_location sl ON (slg.sid = sl.sid) WHERE lg.id = 1 -- ラーメン街道 AND ST_DWithin(slg.geo, lg.geo, 0.0010) AND category @> '{"ramendb":true}' AND s.status = 'open' ORDER BY sl.latitude DESC ; sid | name | branch | status --------+------------------+-------------+-------- 90169 | 麺匠 るい斗 | | open 93752 | ni るい斗 | | open 15923 | 豚そば 成 | | open 80653 | すずき家 | 下永谷 2 号店 | open 1614 | 環 2 家 | | open 4279 | 本牧家 | 本店 | open 53814 | 神勝軒 | 下永谷店 | open 11078 | 大雄 | 永谷店 | open 7767 | 藤山屋 | | open 121625 | 環 2 の麺処 あさ川 | | open (10 rows)
25. PostgreSQL + PostGIS で 果てしなく快適な ライフを追求する 🍜
26. おわり