pgunconf26-postgis-chinatown.pdf

Slide 1

Slide 1 text

麺とポスグレと私中華街とラーメンと私

Slide 2

Slide 2 text

自己紹介 ● ぬこ＠横浜 (@nuko_yokohama) ● にゃーん ● 趣味でポスグレをやってる者だ

Slide 3

Slide 3 text

中華街、知ってる？ ● 日本には中華街が 3 箇所ある。 – 横浜、神戸、長崎 ● 実はアジア圏最大の中華街は横浜中華街 ● 飲食店、何軒あるんだっけ・・・。 ● 横浜中華街は横浜市中区にある。自分も中区住み中華街はご近所

Slide 4

Slide 4 text

横浜は広い。 ● なにげに広い横浜市。 – 中区はその中でも小さい区のようにみえるが・・・「横浜市中区」神奈川県町田市

Slide 5

Slide 5 text

中区にもいろんなエリアがある「中華街」の店をピンポイントで探したい・・・みなとみらい野毛関内伊勢佐木中華街山手・本牧 PostGIS を使おう！デンジャーゾーン

Slide 6

Slide 6 text

PostGIS を使ってみる ● PostgreSQL の GIS 拡張機能。 ● 機能は盛りだくさん – ジオメトリ型、幾何関数、空間インデクス、座標系管理機能等々 – 今の自分では全然使いこなせてないけど ● ドキュメントはちょい癖あるな・・・ – 自分の GIS の知識が乏しいせい＆慣れの問題？ ● PostgreSQL 13 + PostGIS 3.0.3 を使用 – PostgreSQL 14-devel だと SEGV る・・・

Slide 7

Slide 7 text

やりたいこと ● 「中華街」を示すエリアを POLYGON で表現する。 ● 以前、収集した（主にラーメンの）店舗の緯度・経度情報が、「中華街」エリアの POLYGON に含まれるかを評価する。 ● 「横浜市中区」よりも更に狭い範囲での絞り込みを行う。「中華街」の 🍜 を検索したい

Slide 8

Slide 8 text

中華街の位置情報を生成・格納する

Slide 9

Slide 9 text

中華街 POLYGON を定義するオレンジ色の領域を「中華街」とみなすこの多角形の頂点をつなぐことで POLYGON を定義

Slide 10

Slide 10 text

中華街 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 型経度緯度を引数にする

Slide 11

Slide 11 text

中華街 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))') ) ;

Slide 12

Slide 12 text

Slide 13

Slide 13 text

店舗の座標 ● 店舗座標（経度緯度）を 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 でなくフツーのテーブルを使う

Slide 14

Slide 14 text

店舗の座標 ● 店舗の 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)

Slide 15

Slide 15 text

中華街の位置情報を使った検索

Slide 16

Slide 16 text

テーブルと関連 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 万件 )

Slide 17

Slide 17 text

Slide 18

Slide 18 text

Slide 19

Slide 19 text

意図通りの結果になってない・・・この場所の店がヒットしちゃった・・・ ★

Slide 20

Slide 20 text

（余談） Twitter マジ便利つよつよな人のアドバイス得られるので Twitter マジ便利

Slide 21

Slide 21 text

&& だとこういう検索になるっぽい && だと、青点線内の範囲がヒットする ★

Slide 22

Slide 22 text

Slide 23

Slide 23 text

中華街に含まれる店を検索する ● 自分が中華街のラーメンをどの程度食べているか確認 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% の店舗で食べたことがあるようだ。

Slide 24

Slide 24 text

応用編 ● 環状 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)