PostGIS and PostgreSQL and Chinatown.
麺とポスグレと私中華街とラーメンと私
View Slide
自己紹介● ぬこ＠横浜 (@nuko_yokohama)● にゃーん● 趣味でポスグレをやってる者だ
中華街、知ってる？● 日本には中華街が 3 箇所ある。– 横浜、神戸、長崎● 実はアジア圏最大の中華街は横浜中華街● 飲食店、何軒あるんだっけ・・・。● 横浜中華街は横浜市中区にある。自分も中区住み中華街はご近所
横浜は広い。● なにげに広い横浜市。– 中区はその中でも小さい区のようにみえるが・・・「横浜市中区」神奈川県町田市
中区にもいろんなエリアがある「中華街」の店をピンポイントで探したい・・・みなとみらい野毛関内伊勢佐木中華街山手・本牧PostGIS を使おう！デンジャーゾーン
PostGIS を使ってみる● PostgreSQL の GIS 拡張機能。● 機能は盛りだくさん– ジオメトリ型、幾何関数、空間インデクス、座標系管理機能 等々– 今の自分では全然使いこなせてないけど● ドキュメントはちょい癖あるな・・・– 自分の GIS の知識が乏しいせい＆慣れの問題？● PostgreSQL 13 + PostGIS 3.0.3 を使用– PostgreSQL 14-devel だと SEGV る・・・
やりたいこと● 「中華街」を示すエリアを POLYGON で表現する。● 以前、収集した（主にラーメンの）店舗の緯度・経度情報が、「中華街」エリアの POLYGON に含まれるかを評価する。● 「横浜市中区」よりも更に狭い範囲での絞り込みを行う。「中華街」の🍜 を検索したい
中華街の位置情報を生成・格納する
中華街 POLYGON を定義するオレンジ色の領域を「中華街」とみなすこの多角形の頂点をつなぐことでPOLYGON を定義
中華街 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------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------0103000020E61000000100000006000000A2BE1BC17C74614069BE94CB90B84140FED5E3BE95746140534953F30BB94140BC7262B4B3746140957C9175DDB84140C21D030DD17461402EE3A6069AB841403D92DCABA07461409DB2E43B42B84140A2BE1BC17C74614069BE94CB90B84140(1 row)ramendb=# 謎い文字列 =geometry 型経度 緯度 を引数にする
中華街 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))') );
店舗の座標● 店舗座標（経度 緯度）情報をもつテーブルramendb=# \d+ shops_locationTable "public.shops_location"Column | Type | Collation | Nullable | Default | Storage | Stats target | Description-----------+---------+-----------+----------+---------+---------+--------------+-------------sid | integer | | | | plain | |latitude | real | | | | plain | |longitude | real | | | | plain | |Access method: heapramendb=# TABLE shops_location LIMIT 3;sid | latitude | longitude-----+-----------+-----------1 | 35.66959 | 139.703482 | 35.65721 | 139.703783 | 35.660694 | 139.69803(3 rows)約 12 万軒の店舗情報を収集
店舗の座標● 店舗座標（経度 緯度）を 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_geometrySELECT sid,st_geomfromtext(((('POINT('::text || shops_location.longitude) || ' '::text)|| shops_location.latitude) || ')'::text, 4326) AS geoFROM shops_location;AddGeometryColumn() を実行するために、 MVIEW でなくフツーのテーブルを使う
店舗の座標● 店舗の geometry 情報をもつテーブルramendb=# \d shops_location_geometryTable "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 | 0101000020E61000007C2C7DE88276614044DD0720B5D541402 | 0101000020E61000008672A25D85766140554D10751FD441403 | 0101000020E61000000FB4024356766140EB54F99E91D44140(3 rows)
中華街の位置情報を使った検索
テーブルと関連geo 型どうしをPostGIS の演算子や関数を使って比較するsidlatutitudelongitudesidlatutitudelongitudesidlatutitudelongitudeidnamegeosidlatutitudesidgeosidlatutitudelongitudesidnameprefsid・・・ridlatutitudelongitudesidmenuscoresiduid・・・nameuidarea_geometryshops_location_geometryshops_locationshops reviews usersPostGIS のgeo 生成関数結果を挿入する。POLYGON を示す「経度 緯度」文字列( 約 12 万件 )( 約 100 万件 )( 約 22 万件 )( 約 12 万件 )
中華街に含まれる店を検索する● && 演算子による検索ramendb=# SELECT s.name, s.branch, s.status, s.reg_dateFROM shops s, shops_location_geometry slg, area_geometry agWHERE s.sid = slg.sidAND ag.geo && slg.geoAND ag.id = 1ORDER BY s.reg_dateLIMIT 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)お、それっぽい結果かな・・・？
中華街に含まれる店を検索する● && 演算子による検索ramendb=# SELECT s.name, s.branch, s.status, s.reg_dateFROM shops s, shops_location_geometry slg, area_geometry agWHERE s.sid = slg.sidAND ag.geo && slg.geoAND ag.id = 1ORDER BY s.reg_dateLIMIT 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)あれ？ちょっとおかしいぞ
意図通りの結果になってない・・・この場所の店がヒットしちゃった・・・★
（余談） Twitter マジ便利つよつよな人のアドバイス得られるのでTwitter マジ便利
&& だとこういう検索になるっぽい&& だと、青点線内の範囲がヒットする★
中華街に含まれる店を検索する● ST_Contains による検索ramendb=# SELECT s.name, s.branch, s.status, s.reg_dateFROM shops s, shops_location_geometry slg, area_geometry agWHERE s.sid = slg.sidAND ST_Contains(ag.geo, slg.geo)AND ag.id = 1ORDER BY s.reg_dateLIMIT 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) 今度はあってそう
中華街に含まれる店を検索する● 自分が中華街のラーメンをどの程度食べているか確認ramendb=# SELECT COUNT(*)FROM shops s, shops_location_geometry slg, area_geometry agWHERE s.sid = slg.sidAND ST_Contains(ag.geo, slg.geo)AND ag.id = 1AND s.category @> '{"ramendb":true}'AND s.status = 'open';count-------194(1 row)ramendb=# SELECT COUNT(*)FROM shops s, shops_location_geometry slg, area_geometry agWHERE s.sid = slg.sidAND ST_Contains(ag.geo, slg.geo)AND ag.id = 1AND 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% の店舗で食べたことがあるようだ。
応用編● 環状 2 号線ラーメン街道を LINESTRING で表現。ramendb=# SELECT s.sid, s.name, s.branch, s.statusFROM line_geometry lg ,shops_location_geometry slg JOIN shops s ON (slg.sid = s.sid)JOIN shops_location sl ON (slg.sid = sl.sid)WHERElg.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 | 麺匠 るい斗 | | open93752 | ni るい斗 | | open15923 | 豚そば 成 | | open80653 | すずき家 | 下永谷 2 号店 | open1614 | 環 2 家 | | open4279 | 本牧家 | 本店 | open53814 | 神勝軒 | 下永谷店 | open11078 | 大雄 | 永谷店 | open7767 | 藤山屋 | | open121625 | 環 2 の麺処 あさ川 | | open(10 rows)
PostgreSQL + PostGIS で果てしなく快適な ライフを追求する🍜
おわり
nuko_yokohama
daihiraoka
shin_higuchi
bun913
asei
hayaosuzuki
niryuu
alexanderbyndyu
soracom
spinscale
k1low
sat
doradora09
destraynor
vanstee
nonsquared
brettharned
aki_iinuma
panda_program
speakerdeck
marcelosomers
maltzj
ammeep
holman
morganepeng