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

それPostgreSQLでできるよ @ Rails Developer Meetup 2018 Day 1

それPostgreSQLでできるよ @ Rails Developer Meetup 2018 Day 1

Fujimura Daisuke

March 24, 2018
Tweet

More Decks by Fujimura Daisuke

Other Decks in Technology

Transcript

  1. それPostgreSQL
    でできるよ
    @Rails Developer Meetup 2018 Day 1
    Daisuke Fujimura
    2018-03-24

    View full-size slide

  2. この発表について
    PostgreSQLには便利な機能がたくさんあります。業務の中で「これできないかな?」
    と思って調べると関数や拡張が用意されていた、ということも多々ありました。
    ActiveRecordおよび標準SQLを使って仕事をしていると見えてこない「レールの外」
    のPostgreSQLの世界をご紹介しようと思います。
    PostgreSQLの便利な機能をそれぞれユースケースを交えて解説します。
    原則、AWS RDS、Google Cloud SQL for PosterSQLで使える機能のみ紹介します。
    利用したPostgreSQLのバージョンは10.1、PostGISのバージョンは2.4.3です。
    発表で登場するコード例は https://github.com/fujimura/railsdm_2018_postgresql にありま
    す。

    View full-size slide

  3. 自己紹介
    藤村大介
    ご近所SNSマチマチを運営する株式会社マチマチのCTO。Railsは2.2位から仕事で使って
    いる。フロントエンド開発も得意。
    twitter.com/ffu_
    fujimuradaisuke.com
    SQL歴は足掛け10年程度。PostgreSQL歴は4年程度。内部構造の詳しい知識などはあり
    ません。

    View full-size slide

  4. マチマチについて(1

    View full-size slide

  5. マチマチについて(2

    チーム
    @fujimura @knu @imaz(フリーランス)
    テクノロジー
    バックエンド:Rails, PostgreSQL
    フロントエンド:React, Flowtype
    ネイティブアプリ:これからReact Nativeで作る
    エンジニア募集中です!藤村まで気軽にお声かけ下さい。

    View full-size slide

  6. Window
    関数(1

    最近MySQLにも入ったWindow関数ですが皆さん使っていますか?データ分析ではよく
    使いますが、アプリケーション開発ではあまり使わないかもしれません。

    View full-size slide

  7. Window
    関数(1
    )やりたいこと
    このテーブルの各行のposition
    を重複なしタイムスタンプ順で振りなおしたい
    name | position | updated_at
    ­­­­­­+­­­­­­­­­­+­­­­­­­­­­­­­­­­­­­­­­­­­­­­
    A | 1 | 2018­03­21 22:17:49.215978
    B | 2 | 2018­03­21 22:17:49.215978
    F | 3 | 2018­03­23 10:17:49.220138
    C | 3 | 2018­03­21 22:17:49.215978
    D | 4 | 2018­03­21 22:17:49.215978
    E | 5 | 2018­03­21 22:17:49.215978

    View full-size slide

  8. Window
    関数(1
    )そもそもWindow
    関数とは
    Window関数は「現在の行」の情報を使った値を計算する機能です。例えば
    ROW_NUMBER
    を使うと、特定のカラムでソートした際の現在の行番号がわかります。
    例えばさきほどのテーブルにこのクエリを実行すると
    position
    、updated_at
    の順にソートした場合の行番号が取得できます。
    name | position | i | updated_at
    ­­­­­­+­­­­­­­­­­+­­­+­­­­­­­­­­­­­­­­­­­­­­­­­­­­
    A | 1 | 1 | 2018­03­21 22:17:49.215978
    B | 2 | 2 | 2018­03­21 22:17:49.215978
    F | 3 | 3 | 2018­03­21 22:17:49.215978
    C | 3 | 4 | 2018­03­21 22:17:49.215978
    D | 4 | 5 | 2018­03­21 22:17:49.215978
    E | 5 | 6 | 2018­03­21 22:17:49.215978
    SELECT name, position,
    ROW_NUMBER() OVER ( ORDER BY position, updated_at DESC) AS i
    FROM categories;

    View full-size slide

  9. Window
    関数(1
    )position
    を振り直す
    実際にposition
    を振り直すには、下記のSQLを実行します。新たにposition
    を振っ
    た表をWindow関数を使って用意して、それを元のテーブルと結合してUPDATEしてい
    ます。
    name | position | updated_at
    ­­­­­­+­­­­­­­­­­+­­­­­­­­­­­­­­­­­­­­­­­­­­­­
    A | 1 | 2018­03­21 22:17:49.215978
    B | 2 | 2018­03­21 22:17:49.215978
    F | 3 | 2018­03­23 10:17:49.220138
    C | 4 | 2018­03­21 22:17:49.215978
    D | 5 | 2018­03­21 22:17:49.215978
    E | 6 | 2018­03­21 22:17:49.215978
    UPDATE categories
    SET POSITION = c.row_number
    FROM
    (SELECT name,
    ROW_NUMBER() OVER (ORDER BY POSITION, updated_at DESC) AS row_number
    FROM categories) AS c
    WHERE c.name = categories.name;

    View full-size slide

  10. Window
    関数(2)
    やりたいこと
    アクセスログから30分以内の連続したアクセスを一つのセッションとして、セッション
    の一覧を出したい。

    View full-size slide

  11. Window
    関数(2)
    アクセスログのデータ
    ユーザーIDとしてid
    、タイムスタンプとしてtime
    を持った簡単なログテーブルがある
    とします。
    id | time
    ­­­­+­­­­­­­­­­­­­­­­­­­­­­­­­­­­
    1 | 2018­03­10 06:14:06.265533
    1 | 2018­03­10 07:04:06.265533
    2 | 2018­03­10 07:31:06.265533
    1 | 2018­03­10 07:35:06.265533
    2 | 2018­03­10 08:02:06.265533
    2 | 2018­03­10 08:03:06.265533
    2 | 2018­03­10 08:04:06.265533
    1 | 2018­03­10 08:05:06.265533
    2 | 2018­03­10 08:14:06.265533
    1 | 2018­03­10 08:14:06.265533

    View full-size slide

  12. Window
    関数(2)
    現在の行と前の行の差を取得
    LAG
    で前の行が取得できます。この例ではid
    ごとに前の行のtime
    を取得、現在の行の
    time
    との差を求めています。
    id | current_row | previous_row | difference
    ­­­­+­­­­­­­­­­­­­­­­­­­­­­­­­­­­+­­­­­­­­­­­­­­­­­­­­­­­­­­­­+­­­­­­­­­­­­
    1 | 2018­03­10 06:14:06.265533 | ¤ | ¤
    1 | 2018­03­10 07:04:06.265533 | 2018­03­10 06:14:06.265533 | 00:00:50
    1 | 2018­03­10 07:35:06.265533 | 2018­03­10 07:04:06.265533 | 00:00:31
    1 | 2018­03­10 08:05:06.265533 | 2018­03­10 07:35:06.265533 | 00:00:30
    1 | 2018­03­10 08:14:06.265533 | 2018­03­10 08:05:06.265533 | 00:00:09
    2 | 2018­03­10 07:31:06.265533 | ¤ | ¤
    2 | 2018­03­10 08:02:06.265533 | 2018­03­10 07:31:06.265533 | 00:00:31
    2 | 2018­03­10 08:03:06.265533 | 2018­03­10 08:02:06.265533 | 00:00:01
    2 | 2018­03­10 08:04:06.265533 | 2018­03­10 08:03:06.265533 | 00:00:01
    2 | 2018­03­10 08:14:06.265533 | 2018­03­10 08:04:06.265533 | 00:00:10
    SELECT
    id,
    time as current_row,
    LAG(time) OVER (PARTITION BY id ORDER BY time) as previous_row,
    (time ­ (LAG(time) OVER (PARTITION BY id ORDER BY time))) / 60 as difference
    FROM access_logs
    ORDER BY id, current_row ASC

    View full-size slide

  13. Window
    関数(2)
    セッション一覧
    さきほどのSQLより、前回との差がNULL(初回アクセス)、前回との差が30分以上
    (新しいセッション)の行を取得すると、セッション一覧になります。
    SELECT
    id, s.time as timestamp
    FROM (
    SELECT
    CASE
    WHEN LAG(time) OVER (PARTITION BY id ORDER BY time) IS NULL THEN time
    WHEN EXTRACT(epoch FROM time ­ (LAG(time) OVER (PARTITION BY id ORDER BY time))) > 60 * 30 THEN time
    ELSE NULL
    END AS time,
    id
    FROM
    access_logs
    ORDER BY
    time ) as s
    WHERE
    time IS NOT NULL
    order by timestamp, id

    View full-size slide

  14. Window
    関数(2)
    セッション一覧
    id | current_row | previous_row | difference
    ­­­­+­­­­­­­­­­­­­­­­­­­­­­­­­­­­+­­­­­­­­­­­­­­­­­­­­­­­­­­­­+­­­­­­­­­­­­
    1 | 2018­03­10 06:14:06.265533 | ¤ | ¤
    1 | 2018­03­10 07:04:06.265533 | 2018­03­10 06:14:06.265533 | 00:00:50
    1 | 2018­03­10 07:35:06.265533 | 2018­03­10 07:04:06.265533 | 00:00:31
    1 | 2018­03­10 08:05:06.265533 | 2018­03­10 07:35:06.265533 | 00:00:30
    1 | 2018­03­10 08:14:06.265533 | 2018­03­10 08:05:06.265533 | 00:00:09
    2 | 2018­03­10 07:31:06.265533 | ¤ | ¤
    2 | 2018­03­10 08:02:06.265533 | 2018­03­10 07:31:06.265533 | 00:00:31
    2 | 2018­03­10 08:03:06.265533 | 2018­03­10 08:02:06.265533 | 00:00:01
    2 | 2018­03­10 08:04:06.265533 | 2018­03­10 08:03:06.265533 | 00:00:01
    2 | 2018­03­10 08:14:06.265533 | 2018­03­10 08:04:06.265533 | 00:00:10
    id | timestamp
    ­­­­+­­­­­­­­­­­­­­­­­­­­­­­­­­­­
    1 | 2018­03­10 06:14:06.265533
    1 | 2018­03­10 07:04:06.265533
    2 | 2018­03­10 07:31:06.265533
    1 | 2018­03­10 07:35:06.265533
    2 | 2018­03­10 08:02:06.265533

    View full-size slide

  15. Window
    関数 Rails
    では?
    ActiveRecord::Connection.execute
    ActiveRecord::Querying#find_by_sql
    ActiveRecord::QueryMethods#select

    View full-size slide

  16. Trigger
    PostgreSQLではTriggerという仕組みを使って、行への操作(挿入・更新・削除)があっ
    た時に特定の関数を実行することができます。

    View full-size slide

  17. Trigger -
    やりたいこと
    テーブルの変更履歴をアプリケーション側ではなくデータベース側のみで自動で記録し
    たい。

    View full-size slide

  18. Trigger -
    とは?
    公式ドキュメントによると、
    CREATE TRIGGERは新しいトリガを作成します。 作成したトリガは指定したテーブ
    ルまたはビューと関連付けられ、特定のイベントが発生した時に指定した関数
    function_nameを実行します
    https://www.postgresql.jp/document/10/html/sql-createtrigger.html
    とのこと。
    関数はPL/pgSQLという手続き型プログラミング言語で記述し、コードの中にSQLを書い
    て行の操作をすることができます。

    View full-size slide

  19. Trigger -
    レコードの変更をJSON
    で保存するトリ
    ガーの関数を定義する
    下記のトリガーでレコードに更新があった際にchanges
    というテーブルに変更を保存
    します。変更前、変更後の行をOLD
    とNEW
    で参照することができるので、それを
    row_to_json
    でJSONに変換して保存しています。
    CREATE OR REPLACE FUNCTION audit_changes() RETURNS trigger
    LANGUAGE plpgsql
    AS $$
    BEGIN
    IF (TG_OP = 'UPDATE') THEN
    INSERT INTO changes (table_name, operation, old_content, new_content, created_at)
    VALUES (TG_TABLE_NAME, TG_OP, row_to_json(OLD), row_to_json(NEW), now());
    RETURN NEW;
    END IF;
    END;
    $$;

    View full-size slide

  20. Trigger -
    トリガーをテーブルに適用する
    CREATE TRIGGER
    でトリガーをテーブルに適用します。
    CREATE TRIGGER audit_items_changes BEFORE UPDATE ON items FOR EACH ROW EXECUTE PROCEDURE audit_changes();

    View full-size slide

  21. Trigger -
    実行結果
    このテーブルを
    name | type
    ­­­­­­­­­­­+­­­­­­­
    Apple | Fruit
    Wine | Drink
    Beer | Drink
    Chocolate | Food
    変更すると
    UPDATE items SET name = 'Orange' WHERE name = 'Apple';
    履歴が保存されます。
    table_name | operation | old_content | new_content | created_at
    ­­­­­­­­­­­­+­­­­­­­­­­­+­­­­­­­­­­­­­­­­­­­+­­­­­­­­­­­­­­­­­­­­+­­­­­­­­­­­­­­­­­­­­­­­­­­­­
    items | UPDATE | {"name": "Apple", | {"name": "Orange", | 2018­03­10 09:27:05.213614
    | | "type": "Fruit"} | "type": "Fruit"} |

    View full-size slide

  22. Trigger - Rails
    では?
    マイグレーション内でActiveRecord::Connection.execute

    View full-size slide

  23. Materialized view
    要はキャッシュされたビューです。

    View full-size slide

  24. Materialized view -
    やりたいこと
    下記のように、「ワイン」テーブルと「ビール」テーブルがあるとします。これを「飲
    み物」テーブルとして横断して検索したくなりました。
    CREATE EXTENSION pgcrypto; ­­ gen_random_uuid﴾﴿
    のために必要
    CREATE TABLE wines (
    id uuid DEFAULT gen_random_uuid() NOT NULL,
    name varchar,
    color varchar,
    price integer

    CREATE TABLE beers (
    id uuid DEFAULT gen_random_uuid() NOT NULL,
    name varchar,
    type varchar,
    price integer

    View full-size slide

  25. Materialized view -
    そもそもビューとは?
    ビューはSELECT文を保存してあたかもテーブルかのように扱える機能です。

    View full-size slide

  26. Materialized view -
    とは?
    通常のビューは毎回その定義のSELECT文を実行します。マテリアライズド・ビューは
    通常のビューとは違い、SELECT文の結果が保存されます。
    結果が保存されているので通常のビューよりも多くの場合高速ですが、データの更新は
    手動で行う必要があります。また、インデックスを貼ることも可能です。

    View full-size slide

  27. Materialized view -
    テーブル定義
    「ワイン」テーブルと「ビール」テーブルをまとめた、「飲み物」マテリアライズド・
    ビューを定義してみましょう。

    View full-size slide

  28. Materialized view -
    テーブル定義(おさらい)
    改めて先程の「ワイン」テーブルと「ビール」テーブルの定義です。
    CREATE TABLE wines (
    id uuid DEFAULT gen_random_uuid() NOT NULL,
    name varchar,
    color varchar,
    price integer

    CREATE TABLE beers (
    id uuid DEFAULT gen_random_uuid() NOT NULL,
    name varchar,
    type varchar,
    price integer

    View full-size slide

  29. Materialized view -
    マテリアライズド・ビュー
    の定義
    「ワイン」テーブルと「ビール」テーブルの型を揃えてUNION
    したものを「飲み物」ビ
    ューとして定義します。
    beverages.id
    の型はUUIDなので重複がありません。なのでユニークインデックスを
    貼ることができます。
    CREATE MATERIALIZED VIEW beverages AS
    SELECT w.id AS id,
    w.name AS name,
    w.price AS price,
    'wines' AS type
    FROM wines AS w
    UNION ALL
    SELECT b.id AS id,
    b.name AS name,
    b.price AS price,
    'beers' AS type
    FROM beers AS b
    ;
    CREATE UNIQUE INDEX index_beverages_id ON beverages USING btree (id);

    View full-size slide

  30. Materialized view -
    データ投入、更新
    データを投入した後、REFRESH MATERIALIZED VIEW
    でデータを更新します。ユニー
    クインデックスがあるとCONCURRENTLY
    オプションを使うことができます。これを指
    定すると更新中のビューへの読み込みロックが回避できます。
    INSERT INTO wines
    (name, color, price)
    VALUES
    ('Rotten Highway', 'White', 10000),
    ('yellow tail Chardonnay', 'White', 1000)
    ;
    INSERT INTO beers
    (name, type, price)
    VALUES
    ('Old Rasputin', 'Imperial Stout', 1300),
    ('Ichiban Shibori', 'Lager', 300)
    ;
    REFRESH MATERIALIZED VIEW CONCURRENTLY beverages;

    View full-size slide

  31. Materialized view -
    飲み物ビューを検索
    マテリアライズド・ビューを使って1000円以下の「ビール」と「ワイン」を一度に検索
    することができました
    id | name | price | type
    ­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­+­­­­­­­­­­­­­­­­­­­­­­­­+­­­­­­­+­­­­­­­
    1f17edd2­f3df­4cd7­b72e­faff06df6130 | Ichiban Shibori | 300 | beers
    73afb8eb­9f6f­4e1c­a3ef­5f4ee36ff3af | yellow tail Chardonnay | 1000 | wines
    SELECT * FROM beverages
    WHERE price <= 1000;

    View full-size slide

  32. Materialized view - Rails
    では?
    https://github.com/thoughtbot/scenic でマイグレーションとして管理できます。とても
    便利!

    View full-size slide

  33. Materialized view -
    補足
    ディスクスペースが必要になる、リフレッシュが必要、リフレッシュのコストがかか
    るというデメリットがあります。詳しくは
    https://www.slideshare.net/SoudaiSone/postgre-sql-54919575 を参照ください。
    個人的には 1) リフレッシュが遅れても問題なく 2) 更新頻度が低い 場合のみマテリアライズ
    ド・ビュー使用可としています。
    ソースとなるテーブルの主キー(id
    )をRailsでよくあるincremental idにするとビュー
    で主キー(的なもの)が作れません。
    キーが衝突するのでユニークインデックスが貼れないのでリフレッシュ時に
    CONCURRENTLY
    オプションが使えず、更新中にソースとなっているテーブルにロックがかか
    ってしまいます。
    id
    をUUIDにするとこれを回避できます。
    ちなみにRails 5からActiveRecordでprimary keyをUUIDにできるようになりました。詳しく
    http://blog.bigbinary.com/2016/04/04/rails-5-provides-application-config-to-use-UUID-as-primary-key.html を
    参照ください。

    View full-size slide

  34. PostGIS -
    やりたいこと
    東京都の近くに公園が多い駅ランキングを出したい。

    View full-size slide

  35. PostGIS -
    って何?
    地理空間情報を扱うための拡張です。そもそも地理空間情報って何?という問いについ
    ては、これからお話しする実例を通してお答えできればと思います。
    PostGISが具体的に提供する機能としては、1) ジオメトリカラムの定義 2) ジオメトリ関
    数 の2つです。
    ジオメトリは点、線、面など、空間上を占める何らかの情報のことです。

    View full-size slide

  36. PostGIS -

    駅のテーブル(stations
    、定義はのちほど)を使って、代々木公園駅から距離が近い
    駅ランキングを出してみましょう。 下記のようにST_Distance_Sphere
    で二点間の
    距離を出すことができます。
    +­­­­­­­­­­­­+­­­­­­­­­­­­+­­­­­­­­­­­­+
    | name | name | distance |
    |­­­­­­­­­­­­+­­­­­­­­­­­­+­­­­­­­­­­­­|
    |
    代々木公園 |
    代々木上原 | 0.00894604 |
    |
    代々木公園 |
    参宮橋 | 0.0104074 |
    |
    代々木公園 |
    駒場東大前 | 0.0115478 |
    |
    代々木公園 |
    初台 | 0.0123519 |
    |
    代々木公園 |
    神泉 | 0.0127556 |
    +­­­­­­­­­­­­+­­­­­­­­­­­­+­­­­­­­­­­­­+
    SELECT s1.name, s2.name,
    ST_Distance_Sphere(s1.geom, s2.geom) AS distance
    FROM stations s1
    CROSS JOIN stations s2
    WHERE s1.name = '
    代々木公園' AND s1.name <> s2.name
    ORDER BY distance
    LIMIT 5;

    View full-size slide

  37. PostGIS -
    公園が多い駅ランキングを出すにあた
    っての戦略
    「駅の最寄りエリア」の面を作って、その中にある公園を数えるという方向で挑みま
    す。

    View full-size slide

  38. PostGIS -
    テーブル定義
    geometry(Point, 4326)
    ※ という設定で空間上の「点」を表すジオメトリ型のカラ
    ムを定義できます。要は緯度経度です。geometry(Polygon, 4326)
    でポリゴン、つ
    まり「面」を定義できます。
    公園(parks
    )にはgeom
    として公園を代表する地点を、駅(stations
    )にはgeom
    とし
    て駅の地点、nearby
    として最寄りエリアを定義しました。
    ※: 4326
    って何?と思う方へ: 緯度経度の測り方は何種類もあって、それぞれ測地系と呼ばれています。4326
    は世界測地系WGS84というよく使われている測地系を表すコード
    です。
    CREATE EXTENSION postgis;
    CREATE TABLE parks (
    prefecture varchar,
    city varchar,
    name varchar,
    type varchar,
    geom geometry(Point, 4326)

    CREATE TABLE stations (
    code integer,
    name varchar,
    prefecture_code integer,
    address varchar,
    geom geometry(Point, 4326),
    nearby geometry(Polygon, 4326)

    View full-size slide

  39. PostGIS -
    「駅の最寄りエリア」テーブルを作成
    平面上にいくつか点があって、その平面を「どの点に一番近いか」で分割した図がボロ
    ノイ図です。これを使うと「最寄りエリア」のポリゴンを作れます。 下記のように駅
    の地点を使ってボロノイ図を描画し、それを最寄りエリアテーブル(polygons
    )とし
    て保存します。
    CREATE TABLE polygons (geom geometry(Polygon, 4326));
    CREATE INDEX index_polygons_geom ON polygons USING gist (geom);
    INSERT INTO polygons (geom)
    SELECT g.geom
    FROM
    (
    SELECT (
    ­­ ST_Dump:
    複数のジオメトリを行に展開する。ボロノイ図は面の集合として返されるので、展開が必要
    ST_Dump(
    ­­ ST_VoronoiPolygons:
    複数のジオメトリ(駅の地点の集合)からボロノイ図を描画する
    ST_VoronoiPolygons(
    ­­ ST_Union:
    複数のジオメトリ(ここでは駅の地点)を一つのジオメトリ(駅の地点の集合)にまとめる
    ST_Union(geom)
    )
    )
    ).geom AS geom
    FROM stations
    ) as g;

    View full-size slide

  40. PostGIS -
    「駅の最寄りエリア」テーブルのイメ
    ージ
    地図上に表示すると、このように区分けができました。がしかし、これだとどこがどの
    駅がわかりません。

    View full-size slide

  41. PostGIS -
    「駅の最寄りエリア」データを駅テー
    ブルに挿入
    最寄りエリアにどの駅(の地点)が含まれているかがわかれば、エリアのポリゴンと駅
    の対応がわかるはず。 PostGISの関数ST_Contains
    で包含関係を判定できるので、これ
    を使って駅テーブルと駅の最寄りエリアテーブルを結合し、更新します。
    UPDATE stations
    SET nearby = polygons.geom
    FROM polygons
    WHERE ST_Contains(polygons.geom, stations.geom);

    View full-size slide

  42. PostGIS -
    「駅の最寄りエリア」と駅の対応がで
    きた!

    View full-size slide

  43. PostGIS -
    近くに公園が多い駅ランキングを計算
    さきほど登場したST_Contains
    を使って、最寄りエリアに入っている公園を数えま
    す。 ジオメトリの面積をST_Area
    で求められるので※、それを使って平方キロメート
    ルあたりの公園数をp_sqkm
    として計算しています。
    ※ メートル単位での計算はジオグラフィ型で行う必要があるので、ST_Transform
    を変換しています。
    SELECT stations.name,
    stations.address,
    COUNT(parks.name) p,
    FLOOR(ST_Area(ST_Transform(stations.nearby, 4326)::geography)) area,
    FLOOR(COUNT(parks.name) / ST_Area(ST_Transform(stations.nearby, 4326)::geography) / (1000 * 1000))
    p_sqkm
    FROM stations
    LEFT OUTER JOIN parks ON st_contains(stations.nearby, parks.geom)
    WHERE prefecture_code = 13
    GROUP BY stations.address, stations.name, stations.nearby
    ORDER BY p_sqkm DESC

    View full-size slide

  44. PostGIS -
    近くに公園が多い駅ランキング
    まさかの「新大久保」駅がナンバーワンでした!
    name | address | p | area | p_sqkm
    ­­­­­­­­­­­­­­­­­­­­­­­­­­+­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­+­­­­­+­­­­­­­­­­­+­­­­­­­­
    新大久保 |
    東京都新宿区百人町一丁目10­15 | 12 | 513173 | 23
    京成曳舟 |
    墨田区京島1­
    39­
    1 | 13 | 712581 | 18
    大鳥居 |
    大田区西糀谷3­
    37­
    18 | 31 | 1638396 | 18
    大森町 |
    大田区大森西3­
    24­
    7 | 25 | 1464670 | 17
    千鳥町 |
    大田区千鳥1­
    20­
    1 | 14 | 844929 | 16
    松陰神社前 |
    世田谷区若林4­
    21­
    16 | 10 | 604776 | 16
    志村三丁目 |
    板橋区志村3­
    23­
    1 | 27 | 1615272 | 16

    View full-size slide

  45. PostGIS -
    新大久保駅の様子
    結果をビジュアライズしました。ポケットパークという公園が沢山あります。

    View full-size slide

  46. PostGIS -
    京成曳舟駅の様子
    多いような気もします。

    View full-size slide

  47. PostGIS -
    玉川学園前駅の様子
    面積を考慮しない公園の数ではここがトップです。広い。

    View full-size slide

  48. PostGIS - Rails
    では?
    https://github.com/rgeo/activerecord-postgis-adapter
    ジオメトリ型がマイグレーションで定義できます。
    class CreateStations < ActiveRecord::Migration[5.1]
    def change
    create_table :stations do |t|
    t.string :name
    t.string :line_name
    t.st_point :geom
    t.timestamps
    end
    add_index :welfare_facilities, :geom, using: :gist
    end
    end

    View full-size slide

  49. PostGIS -
    データの出典とクレジット
    公園: 国土数値情報 都市公園データ
    http://nlftp.mlit.go.jp/ksj/gml/datalist/KsjTmplt-P13.html
    駅: 駅データ.jp
    http://www.ekidata.jp/
    地図: OSM Standard on QGIS, ©OpenStreetMap contributors

    View full-size slide

  50. まとめ
    PostgreSQLには便利な機能が沢山
    少しRailsのレールを外れると面白い世界が広がっている
    ご利用は計画的に

    View full-size slide