日本一マクドナルドから遠い場所はPostGISで求められるかな？


- j_catfishさんの[日本一マクドナルドから遠い場所](https://qiita.com/j_catfish/items/0d36a21b4744aa3e4870)や、330Kさんの[日本一マクドナルドから遠い場所をより精確に求めてみた](https://qiita.com/330k/items/a2cf16ab67fac6ab314e)の記事を読んで、PostgreSQL+PostGISでも求められるかな？と思い、試してみました。

- 地図からのアプローチではなくて、マクドナルド店舗緯度経度と住所（代表点）の緯度経度を使って計算することにします。

## 【準備編】POIデータ入手

- マクドナルド店舗の入手先 ＞ 勝手に抜いてるだけなので省略します。
- 住所データと緯度経度 ＞ 国土交通省の[位置参照情報ダウンロードサービス](http://nlftp.mlit.go.jp/isj/index.html)から北海道のデータを入手しました。

## 【準備編】POIデータ生成のプログラム構成

```sh
.
├── README.md
├── create_data.sh                  -- POI生成シェル
├── far_mcd
│   ├── docker-compose.yaml        -- PostGISを起動するdocker-composeファイル
│   └── sql
│       ├── 01_create_table.sql    -- テーブルの生成SQL
│       ├── 02_insert_mcd_poi.sql  -- マクドナルドPOIの生成SQL
│       └── 03_insert_mlit_poi.sql -- 住所POIの生成SQL
├── parse_mcd.py    -- マクドナルドのPOIデータをSQLに変換
└── poi_data
    ├── 01_2016.csv -- 北海道のPOIデータ
    └── poi.json    -- マクドナルドのPOIデータ
```

- create_data.shを実行することで、POIデータからPOIのSQLを作成します。
- シェルスクリプトでいくつかの離島の住所を除外しました。

## 【準備編】テーブル生成

```sql:01_create_table.sql
CREATE EXTENSION IF NOT EXISTS postgis;

-- 住所のテーブル

CREATE TABLE mlit (
  id TEXT PRIMARY KEY, -- ID 住所コード
  name TEXT, -- 住所
  geom GEOMETRY(POINT, 4326) -- JGD2000 to WGS84
);

CREATE INDEX ix_mlit ON mlit USING GiST (geom);

-- マックのテーブル

CREATE TABLE mcd (
  id TEXT PRIMARY KEY, -- 店舗key
  name TEXT, -- 店名＋住所
  geom GEOMETRY(POINT, 4326) -- WGS84
);

CREATE INDEX ix_mcd ON mcd USING GiST (geom);
```

### マクドナルドPOIの生成SQL（抜粋）

```sql:02_insert_mcd_poi.sql
INSERT INTO mcd(id,name,geom) VALUES ('01004','白石南郷通店:北海道札幌市白石区南郷通２丁目南１０－１５',ST_GeomFromText('POINT(43.0446098617 141.400065559)',4326));
INSERT INTO mcd(id,name,geom) VALUES ('01007','オーロラタウン店:北海道札幌市中央区大通西１丁目地下街オーロラタウン',ST_GeomFromText('POINT(43.0610008653 141.355638957)',4326));
INSERT INTO mcd(id,name,geom) VALUES ('01008','北５西２０店:北海道札幌市中央区北五条西２０丁目１－１',ST_GeomFromText('POINT(43.0635659529 141.325258838)',4326));
INSERT INTO mcd(id,name,geom) VALUES ('01011','平岸店:北海道札幌市豊平区平岸二条１０丁目３－１２',ST_GeomFromText('POINT(43.0306296872 141.368123528)',4326));
INSERT INTO mcd(id,name,geom) VALUES ('01012','南新川店:北海道札幌市北区北二十三条西１４丁目６－１',ST_GeomFromText('POINT(43.0876603306 141.327923798)',4326));
```

### 住所POIの生成SQL（抜粋）

```sql:03_insert_mlit_poi.sql
INSERT INTO mlit(id,name,geom) VALUES ('011010001001','北海道札幌市中央区旭ヶ丘一丁目',ST_Transform(ST_GeomFromText('POINT(43.041403 141.319980)',4612),4326));
INSERT INTO mlit(id,name,geom) VALUES ('011010001002','北海道札幌市中央区旭ヶ丘二丁目',ST_Transform(ST_GeomFromText('POINT(43.039804 141.321595)',4612),4326));
INSERT INTO mlit(id,name,geom) VALUES ('011010001003','北海道札幌市中央区旭ヶ丘三丁目',ST_Transform(ST_GeomFromText('POINT(43.039789 141.319717)',4612),4326));
INSERT INTO mlit(id,name,geom) VALUES ('011010001004','北海道札幌市中央区旭ヶ丘四丁目',ST_Transform(ST_GeomFromText('POINT(43.038765 141.322800)',4612),4326));
INSERT INTO mlit(id,name,geom) VALUES ('011010001005','北海道札幌市中央区旭ヶ丘五丁目',ST_Transform(ST_GeomFromText('POINT(43.037356 141.322718)',4612),4326));
```

## 【準備編】DockerでPostGISを起動します。

- Dockerでは```/docker-entrypoint-initdb.d/```にファイルを置くと起動時に実行されますので、POIのSQLはここに置くようにします。

```yaml:docker-compose.yaml
version: '3'
services:
  postgres:
    image: mdillon/postgis
    ports:
      - "5432:5432"
    environment:
      POSTGRES_PASSWORD:  postgres
    volumes:
      - ./sql:/docker-entrypoint-initdb.d/
```

- ```docker-compose up```でコンテナを初回起動すると初期SQLを取り込んでDBを生成します。
- これでPostGISを利用できます。

## 【検索編】マクドナルドが遠い住所を探します！

- 答えはわかってますw 尾花岬、GoogleMapでの住所は「北海道久遠郡せたな町大成区太田」だそうです。
- SQLでここが求まるでしょうか？SQLはこんな感じです。

```sql
WITH MIN_DIST AS (
SELECT mlit.id AS id ,min(ST_DISTANCE(mcd.geom,mlit.geom)) AS dist FROM mlit,mcd
GROUP BY mlit.id
ORDER BY dist DESC
LIMIT 30
)
SELECT mlit.id,mlit.name,ST_AsText(ST_Transform(geom,4326)) FROM mlit 
INNER JOIN MIN_DIST ON mlit.id = MIN_DIST.id;
```

### あれ？？

- 12位です...

![sql.png](https://qiita-image-store.s3.amazonaws.com/0/51116/61966364-e06a-fcc0-273f-7d46248a56a7.png "sql.png")

### 理由

-「北海道久遠郡せたな町大成区太田」の地域は下図のような領域なんですが、代表点はこの真ん中あたりで、尾花岬は左上の一番はじっこなんですよねぇ...

![setana.png](https://qiita-image-store.s3.amazonaws.com/0/51116/3c206b73-b4be-747f-3525-2ec6eba8cf2b.png "setana.png")

## 【結論】場所のあたりをつけるのにご利用ください

- 最後には細かい地形から比較するのは省けなさそうです...とはいえ結果ではせたな町が占めていたのであたりをつけるにはいいのかな？と...
- PostGISでpolylineとかとの距離を使えばもっと正確に出るかもしれませんが劇重そうなので...

## 【補足】

- [GitHubにプログラム置きました。](https://github.com/qoAopx/far_mcdonald)

# 【おまけ】一番遠い店と一番近い店(北海道しばり)

- SQLあってるかな〜（あってませんでした...2018.05.11修正）

```sql
WITH shop_dist AS (
SELECT A.id,A.name AS name1,B.name AS name2,ST_DISTANCE(A.geom::geography,B.geom::geography) AS dist,
dense_rank() OVER (PARTITION BY A.id ORDER BY A.id ASC, ST_DISTANCE(A.geom::geography,B.geom::geography) ASC)
FROM  mcd AS A , mcd AS B
WHERE A.id != B.id
)
SELECT name1,name2,floor(dist) as dist FROM shop_dist WHERE dense_rank = 1 ORDER BY dist DESC
```

## 結果

- 一番遠い店同士は４０号稚内店とイオン名寄店です。一番近い店同士はイオン釧路フードコート店とイオン釧路店です。

|name1|name2|dist|
|:--|:--|--:|
|４０号稚内店:北海道稚内市潮見３ー５－１７|イオン名寄店:北海道名寄市徳田８０－１イオン名寄ショッピングセンター２Ｆ|124664|
|２３５静内店:北海道日高郡新ひだか町静内末広町２－１－１|３６号苫小牧店:北海道苫小牧市新開町３－１－１|83060|
|中標津東武店:北海道標津郡中標津町南町３－１０東武サウスヒルズ１Ｆ|釧路貝塚通り店:北海道釧路市武佐３丁目４７番２０号|79326|
|網走店:北海道網走市駒場南８－１－１８|イオン北見店:北海道北見市北進町１－１－１|48710|
|イオン名寄店:北海道名寄市徳田８０－１イオン名寄ショッピングセンター２Ｆ|３９号旭川永山店:北海道旭川市永山２条８－１－２７|48132|
|１２号滝川店:北海道滝川市南滝の川１３２－１|１２号岩見沢店:北海道岩見沢市八条東１０丁目２－５５|36217|
|３８号富良野店:北海道富良野市新富町２－５|旭川ツインハープ店:北海道旭川市旭神町三条５丁目１－１２|34348|
|イオン小樽店:北海道小樽市築港１１－６|手稲星置店:北海道札幌市手稲区星置３条１－１－３０|22318|
|３７号伊達店:北海道伊達市舟岡町２３６－１|イオン室蘭店:北海道室蘭市東町２－４－３２|19627|
|苫小牧パセオ店:北海道苫小牧市川沿町６－１６－６|苫小牧ステイ店:北海道苫小牧市三光町５－６|9931|
|２３０藤野店:北海道札幌市南区藤野三条４丁目１－１|西岡生協店:北海道札幌市南区澄川六条４丁目２－１|8889|
|３６号恵庭店:北海道恵庭市黄金南６－１０－２|３６号千歳店:北海道千歳市錦町３－１０－１|7938|
|あいの里生協店:北海道札幌市北区あいの里一条５丁目２|北４９条店:北海道札幌市東区北四十九条東８丁目３－１７|5828|
|２４１音更店:北海道河東郡音更町木野大通西１７－１－４|帯広西５条店:北海道帯広市西五条南十八丁目１１－１|5408|
|３９号旭川永山店:北海道旭川市永山２条８－１－２７|旭川宮前通店:北海道旭川市宮前１条４丁目１－２１|5391|
|岩見沢大和タウンプラザ店:北海道岩見沢市やまと一条９丁目１番３号|１２号岩見沢店:北海道岩見沢市八条東１０丁目２－５５|5231|
|１２号岩見沢店:北海道岩見沢市八条東１０丁目２－５５|岩見沢大和タウンプラザ店:北海道岩見沢市やまと一条９丁目１番３号|5231|
|３８号札内店:北海道中川郡幕別町札内共栄町１６４－１|帯広西５条店:北海道帯広市西五条南十八丁目１１－１|4929|
|帯広柏林台店:北海道帯広市西１８条南３－２７－３６|帯広西５条店:北海道帯広市西五条南十八丁目１１－１|4346|
|上磯トライアル店:北海道北斗市七重浜７－１４－１|５号線函館昭和店:北海道函館市昭和二丁目４６－３|4265|
|イオン室蘭店:北海道室蘭市東町２－４－３２|登別若草町店:北海道登別市若草町４－１２－１|3931|
|登別若草町店:北海道登別市若草町４－１２－１|イオン室蘭店:北海道室蘭市東町２－４－３２|3931|
|函館松風店:北海道函館市松風町１１－１５|５号線函館昭和店:北海道函館市昭和二丁目４６－３|3780|
|イオンモール旭川西店:北海道旭川市緑町２３丁目２１６１－３|神楽生協店:北海道旭川市神楽四条５丁目|3709|
|星が浦ぴあざフクハラ店:北海道釧路市星が浦大通２－６|３８号釧路店:北海道釧路市鳥取大通３－１２－２６|3706|
|花川ビッグハウス店:北海道石狩市樽川六条１丁目５|札幌新発寒店:北海道札幌市手稲区新発寒五条５丁目|3263|
|苫小牧ステイ店:北海道苫小牧市三光町５－６|イオンモール苫小牧店:北海道苫小牧市柳町３－１－２０|3205|
|手稲星置店:北海道札幌市手稲区星置３条１－１－３０|手稲西友店:北海道札幌市手稲区前田一条１１丁目|3187|
|湯川生協店:北海道函館市湯川町１－３４－１５|函館鍛治店:北海道函館市鍛治２－３９|3003|
|神楽生協店:北海道旭川市神楽四条５丁目|旭川宮前通店:北海道旭川市宮前１条４丁目１－２１|2978|
|手稲西友店:北海道札幌市手稲区前田一条１１丁目|札幌新発寒店:北海道札幌市手稲区新発寒五条５丁目|2884|
|２７５東雁来店:北海道札幌市東区東雁来九条４丁目７－１|札幌インター店:北海道札幌市白石区菊水元町七条１丁目１０－２２|2852|
|旭川宮前通店:北海道旭川市宮前１条４丁目１－２１|旭川ツインハープ店:北海道旭川市旭神町三条５丁目１－１２|2787|
|旭川ツインハープ店:北海道旭川市旭神町三条５丁目１－１２|旭川宮前通店:北海道旭川市宮前１条４丁目１－２１|2787|
|白石ルーシー店:北海道札幌市白石区栄通１８丁目５－３５|厚別通り川下店:北海道札幌市白石区川下五条４－２－１|2668|
|３８号釧路店:北海道釧路市鳥取大通３－１２－２６|釧路春日フクハラ店:北海道釧路市春日町６－１|2551|
|釧路春日フクハラ店:北海道釧路市春日町６－１|３８号釧路店:北海道釧路市鳥取大通３－１２－２６|2551|
|５号線函館昭和店:北海道函館市昭和二丁目４６－３|函館イトーヨーカドー店:北海道函館市美原１－３－１|2475|
|イオン札幌元町店:北海道札幌市東区北三十一条東１５丁目１|北４９条店:北海道札幌市東区北四十九条東８丁目３－１７|2390|
|釧路貝塚通り店:北海道釧路市武佐３丁目４７番２０号|イオン釧路店:北海道釧路郡釧路町桂木１－１－１|2379|
|厚別通り川下店:北海道札幌市白石区川下五条４－２－１|厚別西友店:北海道札幌市厚別区厚別西四条６丁目７００－１２６|2315|
|屯田イトーヨーカドー店:北海道札幌市北区屯田八条３丁目５－１|北４９条店:北海道札幌市東区北四十九条東８丁目３－１７|2218|
|羊ヶ丘通清田店:北海道札幌市清田区真栄４７－１|イオンモール札幌平岡店:北海道札幌市清田区平岡三条５丁目３－１|2199|
|新琴似店:北海道札幌市北区新琴似２条７－１－１|南新川店:北海道札幌市北区北二十三条西１４丁目６－１|2104|
|北４９条店:北海道札幌市東区北四十九条東８丁目３－１７|イオン札幌麻生店:北海道札幌市北区北三十九条西４丁目１－５|2086|
|イオン札幌麻生店:北海道札幌市北区北三十九条西４丁目１－５|北４９条店:北海道札幌市東区北四十九条東８丁目３－１７|2086|
|３６号里塚店:北海道札幌市清田区里塚二条７丁目１－５|イオンモール札幌平岡店:北海道札幌市清田区平岡三条５丁目３－１|2048|
|イオンモール札幌平岡店:北海道札幌市清田区平岡三条５丁目３－１|札幌平岡店:北海道札幌市厚別区上野幌三条２丁目１－７|1933|
|札幌平岡店:北海道札幌市厚別区上野幌三条２丁目１－７|イオンモール札幌平岡店:北海道札幌市清田区平岡三条５丁目３－１|1933|
|札幌アリオ店:北海道札幌市東区北七条東９丁目２－２０|オーロラタウン店:北海道札幌市中央区大通西１丁目地下街オーロラタウン|1860|
|帯広西５条店:北海道帯広市西五条南十八丁目１１－１|帯広イトーヨーカドー店:北海道帯広市稲田町南８線西１０－１|1858|
|帯広イトーヨーカドー店:北海道帯広市稲田町南８線西１０－１|帯広西５条店:北海道帯広市西五条南十八丁目１１－１|1858|
|平岸店:北海道札幌市豊平区平岸二条１０丁目３－１２|西岡生協店:北海道札幌市南区澄川六条４丁目２－１|1769|
|白石南郷通店:北海道札幌市白石区南郷通２丁目南１０－１５|札幌月寒ゼビオ店:北海道札幌市豊平区月寒東三条１１丁目１－１０|1760|
|上江別高台ショッピングセンター店:北海道江別市上江別４２７－３|イオン江別店:北海道江別市幸町３５|1729|
|イオン江別店:北海道江別市幸町３５|上江別高台ショッピングセンター店:北海道江別市上江別４２７－３|1729|
|札幌新発寒店:北海道札幌市手稲区新発寒五条５丁目|イオンモール札幌発寒店:北海道札幌市西区発寒八条１２丁目１－１|1726|
|南新川店:北海道札幌市北区北二十三条西１４丁目６－１|イオン札幌桑園店:北海道札幌市中央区北八条西１４丁目２８|1682|
|環状通伏古店:北海道札幌市東区伏古一条５丁目１－１０|イオンモール札幌苗穂店:北海道札幌市東区東苗穂二条３丁目１－１イオンモール札幌苗穂２Ｆフードコート|1464|
|厚別西友店:北海道札幌市厚別区厚別西四条６丁目７００－１２６|１２号新札幌店:北海道札幌市厚別区厚別中央二条４丁目４－１２|1387|
|西岡生協店:北海道札幌市南区澄川六条４丁目２－１|西岡出光ＳＳ店:北海道札幌市豊平区西岡一条２丁目|1348|
|西岡出光ＳＳ店:北海道札幌市豊平区西岡一条２丁目|西岡生協店:北海道札幌市南区澄川六条４丁目２－１|1348|
|函館イトーヨーカドー店:北海道函館市美原１－３－１|函館鍛治店:北海道函館市鍛治２－３９|1089|
|函館鍛治店:北海道函館市鍛治２－３９|函館イトーヨーカドー店:北海道函館市美原１－３－１|1089|
|３６号苫小牧店:北海道苫小牧市新開町３－１－１|イオンモール苫小牧店:北海道苫小牧市柳町３－１－２０|1079|
|イオンモール苫小牧店:北海道苫小牧市柳町３－１－２０|３６号苫小牧店:北海道苫小牧市新開町３－１－１|1079|
|イオン北見店:北海道北見市北進町１－１－１|３９号北見店:北海道北見市西富町１－１－６|1027|
|３９号北見店:北海道北見市西富町１－１－６|イオン北見店:北海道北見市北進町１－１－１|1027|
|北５西２０店:北海道札幌市中央区北五条西２０丁目１－１|イオン札幌桑園店:北海道札幌市中央区北八条西１４丁目２８|938|
|イオン札幌桑園店:北海道札幌市中央区北八条西１４丁目２８|北５西２０店:北海道札幌市中央区北五条西２０丁目１－１|938|
|福住イトーヨーカドー店:北海道札幌市豊平区福住二条１丁目|札幌月寒ゼビオ店:北海道札幌市豊平区月寒東三条１１丁目１－１０|841|
|札幌月寒ゼビオ店:北海道札幌市豊平区月寒東三条１１丁目１－１０|福住イトーヨーカドー店:北海道札幌市豊平区福住二条１丁目|841|
|３６号千歳店:北海道千歳市錦町３－１０－１|イオン千歳店:北海道千歳市栄町６－５１|789|
|イオン千歳店:北海道千歳市栄町６－５１|３６号千歳店:北海道千歳市錦町３－１０－１|789|
|イオンモール札幌苗穂店:北海道札幌市東区東苗穂二条３丁目１－１イオンモール札幌苗穂２Ｆフードコート|札幌インター店:北海道札幌市白石区菊水元町七条１丁目１０－２２|767|
|札幌インター店:北海道札幌市白石区菊水元町七条１丁目１０－２２|イオンモール札幌苗穂店:北海道札幌市東区東苗穂二条３丁目１－１イオンモール札幌苗穂２Ｆフードコート|767|
|１２号新札幌店:北海道札幌市厚別区厚別中央二条４丁目４－１２|新さっぽろカテプリ店:北海道札幌市厚別区厚別中央二条５－７|638|
|新さっぽろカテプリ店:北海道札幌市厚別区厚別中央二条５－７|１２号新札幌店:北海道札幌市厚別区厚別中央二条４丁目４－１２|638|
|宮の沢ターミナルビル店:北海道札幌市西区宮の沢１条１－１７－１４|イオンモール札幌発寒店:北海道札幌市西区発寒八条１２丁目１－１|628|
|イオンモール札幌発寒店:北海道札幌市西区発寒八条１２丁目１－１|宮の沢ターミナルビル店:北海道札幌市西区宮の沢１条１－１７－１４|628|
|札幌アピア店:北海道札幌市中央区北五条西３丁目|札幌ヨドバシカメラ店:北海道札幌市北区北六条西５丁目１－２２|495|
|札幌ヨドバシカメラ店:北海道札幌市北区北六条西５丁目１－２２|札幌アピア店:北海道札幌市中央区北五条西３丁目|495|
|札幌すすきの店:北海道札幌市中央区南四条西４丁目１６|札幌南二条店:北海道札幌市中央区南二条西２丁目１|364|
|札幌南二条店:北海道札幌市中央区南二条西２丁目１|オーロラタウン店:北海道札幌市中央区大通西１丁目地下街オーロラタウン|253|
|オーロラタウン店:北海道札幌市中央区大通西１丁目地下街オーロラタウン|札幌南二条店:北海道札幌市中央区南二条西２丁目１|253|
|イオン釧路店:北海道釧路郡釧路町桂木１－１－１|イオン釧路フードコート店:北海道釧路郡釧路町桂木１－１－１|179|
|イオン釧路フードコート店:北海道釧路郡釧路町桂木１－１－１|イオン釧路店:北海道釧路郡釧路町桂木１－１－１|179|