この記事はPostgreSQL Advent Calendar 2014の22日目の記事です。

みなさんingressやってますか?私最近サボってます!

ingressとは?というのはこちらの記事でも参考にして頂いて、ざっくりいうとgoogleが作ったARのゲームで、2つグループに別れて国盗り合戦するということと、三角形に領地を囲んで多いほうが勝ち、というゲームです。

今回の記事でもingressにならって囲む線のことをリンク、点のことをポータル、囲んだ領域/領地のことをコントロールフィールド(CF)で統一します。

あの画面とは

ポータルからリンクを貼るときにingressのルール上いくつかの条件があります。

  • リンクが他のリンクを横断しない
  • リンク元・リンク先の両方が自勢力(Faction)のポータルで、レゾネーターが8本設置済み
  • リンク先ポータルキーの所持
  • リンク元ポータルのRange(リンク可能距離)がリンク先に届いている(※後述)
  • リンク元ポータル「から」張るリンク本数が8本を超えない(Out Link 8本制約)
  • リンク「元」ポータルがコントロールフィールド(CF)内に沈んでいない
【攻略】Ingress初心者が早く知っておいた方が良い事まとめ【イングレス】さんから引用)

とまあいろいろあるのですが、以前からこのリンク貼れるポータルの画面て自分ならどうやるかなあ…っとぼーっと考えていたことと、幾何データ型でboxとかcircleは使ったことがあるけど、lsegとか使ったことないなあ…と思ったのが今回の記事のきっかけです。

IMG_1151

今回は「リンクが横断しない」ことのみに着目し、他の部分はとりあえず気にしないことにしました。

なにはなくともデータ

ingressがポータルのデータを公開してるはずがないので、とりあえずデータ集めから始めます。
ポータルの条件として、郵便局やモニュメントはポータルになりがちなので、探してみたら国土交通省から国土数値情報というものが出てるので、ここで文化施設+東京のダウンロードして使いました。

ダウンロードしたデータがXMLで、まあpostgresにぶっこめばなんとかなるでしょーって思ってたんですが実力不足でなんともならず…めんどくさかったですが、エディタの置換でINSERT文、UPDATE文を作って対応。

テーブル定義

テーブルの定義は次の通り。

ingress=# \d+ portals
                                                Table "public.portals"
   Column   |   Type    |                      Modifiers                       | Storage  | Stats target | Description
------------+-----------+------------------------------------------------------+----------+--------------+-------------
 id         | integer   | not null default nextval('portals_id_seq'::regclass) | plain    |              |
 name       | text      |                                                      | extended |              |
 key        | text      |                                                      | extended |              |
 geo        | point     |                                                      | plain    |              |
 address    | text      |                                                      | extended |              |
 resonators | integer[] |                                                      | extended |              |
Indexes:
    "portals_pkey" PRIMARY KEY, btree (id)

ingress=#
ingress=# \d+ links
                                                 Table "public.links"
     Column     |  Type   |                     Modifiers                      | Storage | Stats target | Description
----------------+---------+----------------------------------------------------+---------+--------------+-------------
 id             | integer | not null default nextval('links_id_seq'::regclass) | plain   |              |
 from_portal_id | integer |                                                    | plain   |              |
 to_portal_id   | integer |                                                    | plain   |              |
Indexes:
    "links_pkey" PRIMARY KEY, btree (id)

ingress=#
portalsにはポータルの情報、linksにはどこからどこにリンクが貼られたかの情報を持ちます。
当然ちゃんとしようとしたらこの情報だけじゃ足りないので、そこは察して下さい。
(なんの気なしに入れたレゾの情報も格納するようにしていますが、今回使いません。)

portalsに先ほどのデータから作ったINSERT文、UPDATE文を流し込む所ですが、緯度経度の情報が完全な?point型のデータになっていないので、一度textだらけのtestというテーブルに流し込んで、そこから流し込んでいます。
[postgres@localhost ~]$ psql ingress
psql (9.4.0)
Type "help" for help.

ingress=# \d+ test
                        Table "public.test"
 Column  | Type | Modifiers | Storage  | Stats target | Description
---------+------+-----------+----------+--------------+-------------
 key     | text |           | extended |              |
 geo     | text |           | extended |              |
 name    | text |           | extended |              |
 address | text |           | extended |              |

ingress=#
[postgres@localhost ~]$ 
[postgres@localhost ~]$ head insert.sql
INSERT INTO test (key , geo ) values ('n1','35.681014 139.734103');
INSERT INTO test (key , geo ) values ('n2','35.683808 139.754098');
INSERT INTO test (key , geo ) values ('n3','35.653188 139.713705');
INSERT INTO test (key , geo ) values ('n4','35.676599 139.760572');
INSERT INTO test (key , geo ) values ('n5','35.690537 139.754618');
INSERT INTO test (key , geo ) values ('n6','35.689707 139.750384');
INSERT INTO test (key , geo ) values ('n7','35.678571 139.771544');
INSERT INTO test (key , geo ) values ('n8','35.686938 139.773055');
INSERT INTO test (key , geo ) values ('n9','35.675627 139.770512');
INSERT INTO test (key , geo ) values ('n10','35.666294 139.730264');
[postgres@localhost ~]$ head update.sql
UPDATE test SET name = 'ニューオータニ美術館', address = '紀尾井町4-1(ニューオータニガーデンコート6階)' WHERE key = 'n1';
UPDATE test SET name = '宮内庁三の丸尚蔵館', address = '千代田1-1(皇居東御苑内)' WHERE key = 'n2';
UPDATE test SET name = '山種美術館', address = '広尾3-12-36' WHERE key = 'n3';
UPDATE test SET name = '出光美術館', address = '丸の内3-1-1(帝劇ビル9階)' WHERE key = 'n4';
UPDATE test SET name = '独立行政法人国立美術館・東京国立近代美術館本館', address = '北の丸公園3-1' WHERE key = 'n5';
UPDATE test SET name = '独立行政法人国立美術館・東京国立近代美術館工芸館', address = '北の丸公園1-1' WHERE key = 'n6';
UPDATE test SET name = 'ブリヂストン美術館', address = '京橋1-10-1' WHERE key = 'n7';
UPDATE test SET name = '三井記念美術館', address = '日本橋室町2-1-1(三井本館7階)' WHERE key = 'n8';
UPDATE test SET name = '独立行政法人国立美術館・東京国立近代美術館フィルムセンター', address = '京橋3-7-6' WHERE key = 'n9';
UPDATE test SET name = 'サントリー美術館', address = '赤坂9-7-4(東京ミッドタウンガーデンサイド)' WHERE key = 'n10';
[postgres@localhost ~]$ psql ingress < insert.sql
[postgres@localhost ~]$ psql ingress < update.sql
と思ってたら、テストデータが2000件以上あって、よく見たら、box型で使えるように?施設によっては複数位置情報をもってるよう。ちょっといらないので、とりあえず減らす!ということでもう一回同じようなテーブル(test2)に減らした状態で突っ込み直します。
-- test -> test2に位置情報をキャストしてtest2に突っ込む
insert into test2 (name,key, geo, address) SELECT name, key, replace(geo,' ',',')::point,address FROM test ORDER BY replace(key,'n','')::int;

-- 一意のポータルと位置情報を対としてportalsに突っ込み直す
insert into portals (name, key,geo,address) SELECT a.name, a.key, a.geo,a.address FROM test2 a, (SELECT max(key) as key FROM test2 GROUP BY name,address) as b WHERE a.key = b.key ORDER BY replace(a.key,'n','')::int;

これで…ようやっとテストデータが出来た…はず。
ingress=# SELECT * FROM portals LIMIT 10
ingress-# ;
 id |                            name                            | key |          geo           |                    address                     |    resonators
----+------------------------------------------------------------+-----+------------------------+------------------------------------------------+-------------------
  1 | ニューオータニ美術館                                       | n1  | (35.681014,139.734103) | 紀尾井町4-1(ニューオータニガーデンコート6階) | {8,8,8,8,8,8,8,8}
  2 | 宮内庁三の丸尚蔵館                                         | n2  | (35.683808,139.754098) | 千代田1-1(皇居東御苑内)                      | {8,8,8,8,8,8,8,8}
  3 | 山種美術館                                                 | n3  | (35.653188,139.713705) | 広尾3-12-36                                    | {8,8,8,8,8,8,8,8}
  4 | 出光美術館                                                 | n4  | (35.676599,139.760572) | 丸の内3-1-1(帝劇ビル9階)                     | {8,8,8,8,8,8,8,8}
  5 | 独立行政法人国立美術館・東京国立近代美術館本館             | n5  | (35.690537,139.754618) | 北の丸公園3-1                                  | {8,8,8,8,8,8,8,8}
  6 | 独立行政法人国立美術館・東京国立近代美術館工芸館           | n6  | (35.689707,139.750384) | 北の丸公園1-1                                  | {8,8,8,8,8,8,8,8}
  7 | ブリヂストン美術館                                         | n7  | (35.678571,139.771544) | 京橋1-10-1                                     | {8,8,8,8,8,8,8,8}
  8 | 三井記念美術館                                             | n8  | (35.686938,139.773055) | 日本橋室町2-1-1(三井本館7階)                 | {8,8,8,8,8,8,8,8}
  9 | 独立行政法人国立美術館・東京国立近代美術館フィルムセンター | n9  | (35.675627,139.770512) | 京橋3-7-6                                      | {8,8,8,8,8,8,8,8}
 10 | サントリー美術館                                           | n10 | (35.666294,139.730264) | 赤坂9-7-4(東京ミッドタウンガーデンサイド)    | {8,8,8,8,8,8,8,8}
(10 rows)

ingress=#

CFを作る

今回はぶっちゃけた話CFを作る必要はない(というか囲まれた箇所が閉じられた領域かどうかを判定できなかった…)ので、linksにレコードぶっこみます。

あまりわからない所にリンクを貼ったり、CF作ってもぴんとこないので、わが足立区のランドマーク、足立区総合スポーツセンターからをリンクを貼ることを前提とし、足立区立保塚図書館、足立区生物園、保木間公園でCFを作ることとします。
insert into links (from_portal_id, to_portal_id)  values (427, 596);
insert into links (from_portal_id, to_portal_id)  values (596,1056);
insert into links (from_portal_id, to_portal_id)  values (1056, 427);
で、やっぱりそれでもデータが多いので、総合スポーツセンターから半径3km以内のデータをテストデータとします。
create view test_portals as 
SELECT * FROM
(
	SELECT 
		name,
		geo,
		sqrt(power((p.geo[0] - 35.792379) * 111, 2) + power((p.geo[1] - 139.813099) * 91, 2)) AS distance
	FROM portals p
) AS target
WHERE distance < 3

ingress=# SELECT * FROM test_portals ORDER BY distance;
          name          |          geo           |     distance
------------------------+------------------------+-------------------
 総合スポーツセンター   | (35.792379,139.813099) |                 0
 足立区生物園           | (35.792459,139.807169) | 0.539703058450329
 足立区立保塚図書館     | (35.786715,139.816078) | 0.684659014062435
 保木間公園             | (35.789863,139.802022) |  1.04597953910439
 平野運動場             | (35.783366,139.805251) |  1.22919572423253
 花畑地域体育館         | (35.802685,139.807495) |  1.25248612385626
 足立区立花畑図書館     | (35.80283,139.807496)  |  1.26716691870155
 竹の塚6号公園         | (35.792265,139.795193) |  1.62949513366156
 足立区立竹の塚図書館   | (35.792614,139.794681) |  1.67624097452248
 スイムスポーツセンター | (35.803498,139.797091) |  1.90927429188816
 足立区立やよい図書館   | (35.772274,139.80973)  |  2.25261508524259
 足立区立佐野図書館     | (35.788097,139.840204) |  2.51193263429402
 足立区立郷土博物館     | (35.784533,139.841339) |  2.71340282421167
 谷中公園               | (35.776356,139.835719) |   2.7203572688554
 梅田地域体育館         | (35.770487,139.798321) |  2.77730804574258
 足立区立梅田図書館     | (35.768349,139.80069)  |  2.89651253732086
 足立区立伊興図書館     | (35.791178,139.780982) |  2.92568578444945
 東綾瀬公園温水プール   | (35.770276,139.83137)  |  2.96374342452352
(18 rows)

ingress=#
距離計算は以前に記事にしたことがあったんですが、そねさんの記事のほうが数値的にあってそうなので、そちらを参考しています。

ポータル対象をgooglemapにさしこんでみたので、そちらのほうがイメージできるかもしれません。
赤が対象の総合スポーツセンター、緑のポータルでCFが作られてて、残りが全て青のポータルでどこにでもリンクが貼れるイメージですね。

ようやく本題

総合スポーツセンターからリンクを貼るときに既にリンクが貼られた箇所を横切るかどうかということをチェックする関数を作ります。
CREATE OR REPLACE FUNCTION checklink(point, point)
RETURNS POINT
AS '
	SELECT 
		lseg # lseg (point($1), point($2)) 
	FROM (
			SELECT 
				lseg(p1.geo  ||'',''|| p2.geo) as lseg
			FROM 
				links l, portals p1, portals p2 
			WHERE 
				l.from_portal_id = p1.id AND l.to_portal_id = p2.id 
		) as p
'
LANGUAGE SQL;
サブクエリpの中でlinksとportalsを内部結合させ、位置情報を保持したテーブルを作成し、それをlseg型にキャストし、その結果をパラメータから2点のリンクと交わった場合、その位置情報を返すことにしています。

本来であれば #ではなく、?#(booleanが返ります)でいいと思いますが、検証ができなかったので、#としています。
詳しくはこちら

で、それをtest_portalsとぶつけます。
ingress=# SELECT name, case when geo ~= checklink(geo,'(35.792379,139.813099)') then null else checklink(geo,'(35.792379,139.813099)') end FROM test_portals ORDER BY distance;
          name          |             checklink2
------------------------+-------------------------------------
 総合スポーツセンター   |
 足立区生物園           |
 足立区立保塚図書館     |
 保木間公園             | (35.7914038106898,139.808805608907)
 平野運動場             | (35.7899815954784,139.811011478566)
 花畑地域体育館         |
 足立区立花畑図書館     |
 竹の塚6号公園         | (35.7923423975801,139.80734985149)
 足立区立竹の塚図書館   | (35.7924545748224,139.807175863494)
 スイムスポーツセンター |
 足立区立やよい図書館   | (35.7890006764994,139.812532893466)
 足立区立佐野図書館     |
 足立区立郷土博物館     |
 谷中公園               |
 梅田地域体育館         | (35.7897708299335,139.811338377981)
 足立区立梅田図書館     | (35.7895706887914,139.811648798844)
 足立区立伊興図書館     | (35.7921737922991,139.807611359925)
 東綾瀬公園温水プール   |
(18 rows)

ingress=#

ちょっとわかりづらいこれでchecklink()から値が返ってきてる場合はリンクをまたがってるので、WHERE句ではずせばいいって感じですね。

リンクが既に貼ってある場合、checklink()はその両端のポータルの位置情報を返してしまうので、それを外すようにCASE文で対応しましたが(いやfunctionで対応すべき)、そもそも緑じゃんそれ貼れないじゃんってのあとから気づきましたが、そこは無視して下さい…

まとめ(というか宿題)

  • 閉じられた領域かどうかをチェックする方法を検討
  • XMLをpostgresで扱う場合どうするか再度確認
  • テストデータがないとかなりピンと来ない記事になってることが否めないですが、再配布していいものかどうなのかよくわからなかったのでしてません。しても問題なければgithubにでもアップします。もしくはこっそり連絡下さい。
  • 距離計算はPostGIS使った方がいいのかも。サンプルがゴロゴロある。あとlsegのサンプルというか幾何データのサンプル結構ないので、ちょっとハマった…
  • ちゃんと事前に準備しておくこと!前日とかに焦るのやめること!

明日は江川さんです!


Add Comments

名前
 
  絵文字
 
 
プロフィール

john_doe_

Twitter
instagram(SnapWidget)
タグクラウド
  • ライブドアブログ