Go to Qiita Advent Calendar Top
LoginSignup
8
3

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?

More than 5 years have passed since last update.

@qoAop(q∂~∂p)

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

Last updated at Posted at 2018-05-04

【準備編】POIデータ入手

  • マクドナルド店舗の入手先 ＞ 勝手に抜いてるだけなので省略します。
  • 住所データと緯度経度 ＞ 国土交通省の位置参照情報ダウンロードサービスから北海道のデータを入手しました。

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

.
├── 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を作成します。
  • シェルスクリプトでいくつかの離島の住所を除外しました。

【準備編】テーブル生成

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（抜粋）

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（抜粋）

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はここに置くようにします。
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はこんな感じです。
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

理由

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

setana.png

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

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

【補足】

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

  • SQLあってるかな〜（あってませんでした...2018.05.11修正）
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
8
3
3

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
Sign upLogin
8
3

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?