Upgrade to Pro — share decks privately, control downloads, hide ads and more …

pgunconf26-postgis-chinatown.pdf

 pgunconf26-postgis-chinatown.pdf

PostGIS and PostgreSQL and Chinatown.

nuko_yokohama

August 23, 2021
Tweet

More Decks by nuko_yokohama

Other Decks in Technology

Transcript

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

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

  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))') )
    ;

    View Slide

  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 万軒の
    店舗情報を収集

    View Slide

  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 でなく
    フツーのテーブルを使う

    View Slide

  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)

    View Slide

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

    View Slide

  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 万件 )

    View Slide

  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)
    お、それっぽい
    結果かな・・・?

    View Slide

  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)
    あれ?
    ちょっとおかしいぞ

    View Slide

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

    View Slide

  20. (余談) Twitter マジ便利
    つよつよな人の
    アドバイス得られるので
    Twitter マジ便利

    View Slide

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

    View Slide

  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) 今度はあってそう

    View Slide

  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% の店舗で
    食べたことが
    あるようだ。

    View Slide

  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)

    View Slide

  25. PostgreSQL + PostGIS で
    果てしなく快適な ライフを追求する
    🍜

    View Slide

  26. おわり

    View Slide