Edited at

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

More than 1 year has passed since last update.


【準備編】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','白石南郷通店:北海道札幌市白石区南郷通2丁目南10-15',ST_GeomFromText('POINT(43.0446098617 141.400065559)',4326));

INSERT INTO mcd(id,name,geom) VALUES ('01007','オーロラタウン店:北海道札幌市中央区大通西1丁目地下街オーロラタウン',ST_GeomFromText('POINT(43.0610008653 141.355638957)',4326));
INSERT INTO mcd(id,name,geom) VALUES ('01008','北5西20店:北海道札幌市中央区北五条西20丁目1-1',ST_GeomFromText('POINT(43.0635659529 141.325258838)',4326));
INSERT INTO mcd(id,name,geom) VALUES ('01011','平岸店:北海道札幌市豊平区平岸二条10丁目3-12',ST_GeomFromText('POINT(43.0306296872 141.368123528)',4326));
INSERT INTO mcd(id,name,geom) VALUES ('01012','南新川店:北海道札幌市北区北二十三条西14丁目6-1',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


結果


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

name1
name2
dist

40号稚内店:北海道稚内市潮見3ー5-17
イオン名寄店:北海道名寄市徳田80-1イオン名寄ショッピングセンター2F
124664

235静内店:北海道日高郡新ひだか町静内末広町2-1-1
36号苫小牧店:北海道苫小牧市新開町3-1-1
83060

中標津東武店:北海道標津郡中標津町南町3-10東武サウスヒルズ1F
釧路貝塚通り店:北海道釧路市武佐3丁目47番20号
79326

網走店:北海道網走市駒場南8-1-18
イオン北見店:北海道北見市北進町1-1-1
48710

イオン名寄店:北海道名寄市徳田80-1イオン名寄ショッピングセンター2F
39号旭川永山店:北海道旭川市永山2条8-1-27
48132

12号滝川店:北海道滝川市南滝の川132-1
12号岩見沢店:北海道岩見沢市八条東10丁目2-55
36217

38号富良野店:北海道富良野市新富町2-5
旭川ツインハープ店:北海道旭川市旭神町三条5丁目1-12
34348

イオン小樽店:北海道小樽市築港11-6
手稲星置店:北海道札幌市手稲区星置3条1-1-30
22318

37号伊達店:北海道伊達市舟岡町236-1
イオン室蘭店:北海道室蘭市東町2-4-32
19627

苫小牧パセオ店:北海道苫小牧市川沿町6-16-6
苫小牧ステイ店:北海道苫小牧市三光町5-6
9931

230藤野店:北海道札幌市南区藤野三条4丁目1-1
西岡生協店:北海道札幌市南区澄川六条4丁目2-1
8889

36号恵庭店:北海道恵庭市黄金南6-10-2
36号千歳店:北海道千歳市錦町3-10-1
7938

あいの里生協店:北海道札幌市北区あいの里一条5丁目2
北49条店:北海道札幌市東区北四十九条東8丁目3-17
5828

241音更店:北海道河東郡音更町木野大通西17-1-4
帯広西5条店:北海道帯広市西五条南十八丁目11-1
5408

39号旭川永山店:北海道旭川市永山2条8-1-27
旭川宮前通店:北海道旭川市宮前1条4丁目1-21
5391

岩見沢大和タウンプラザ店:北海道岩見沢市やまと一条9丁目1番3号
12号岩見沢店:北海道岩見沢市八条東10丁目2-55
5231

12号岩見沢店:北海道岩見沢市八条東10丁目2-55
岩見沢大和タウンプラザ店:北海道岩見沢市やまと一条9丁目1番3号
5231

38号札内店:北海道中川郡幕別町札内共栄町164-1
帯広西5条店:北海道帯広市西五条南十八丁目11-1
4929

帯広柏林台店:北海道帯広市西18条南3-27-36
帯広西5条店:北海道帯広市西五条南十八丁目11-1
4346

上磯トライアル店:北海道北斗市七重浜7-14-1
5号線函館昭和店:北海道函館市昭和二丁目46-3
4265

イオン室蘭店:北海道室蘭市東町2-4-32
登別若草町店:北海道登別市若草町4-12-1
3931

登別若草町店:北海道登別市若草町4-12-1
イオン室蘭店:北海道室蘭市東町2-4-32
3931

函館松風店:北海道函館市松風町11-15
5号線函館昭和店:北海道函館市昭和二丁目46-3
3780

イオンモール旭川西店:北海道旭川市緑町23丁目2161-3
神楽生協店:北海道旭川市神楽四条5丁目
3709

星が浦ぴあざフクハラ店:北海道釧路市星が浦大通2-6
38号釧路店:北海道釧路市鳥取大通3-12-26
3706

花川ビッグハウス店:北海道石狩市樽川六条1丁目5
札幌新発寒店:北海道札幌市手稲区新発寒五条5丁目
3263

苫小牧ステイ店:北海道苫小牧市三光町5-6
イオンモール苫小牧店:北海道苫小牧市柳町3-1-20
3205

手稲星置店:北海道札幌市手稲区星置3条1-1-30
手稲西友店:北海道札幌市手稲区前田一条11丁目
3187

湯川生協店:北海道函館市湯川町1-34-15
函館鍛治店:北海道函館市鍛治2-39
3003

神楽生協店:北海道旭川市神楽四条5丁目
旭川宮前通店:北海道旭川市宮前1条4丁目1-21
2978

手稲西友店:北海道札幌市手稲区前田一条11丁目
札幌新発寒店:北海道札幌市手稲区新発寒五条5丁目
2884

275東雁来店:北海道札幌市東区東雁来九条4丁目7-1
札幌インター店:北海道札幌市白石区菊水元町七条1丁目10-22
2852

旭川宮前通店:北海道旭川市宮前1条4丁目1-21
旭川ツインハープ店:北海道旭川市旭神町三条5丁目1-12
2787

旭川ツインハープ店:北海道旭川市旭神町三条5丁目1-12
旭川宮前通店:北海道旭川市宮前1条4丁目1-21
2787

白石ルーシー店:北海道札幌市白石区栄通18丁目5-35
厚別通り川下店:北海道札幌市白石区川下五条4-2-1
2668

38号釧路店:北海道釧路市鳥取大通3-12-26
釧路春日フクハラ店:北海道釧路市春日町6-1
2551

釧路春日フクハラ店:北海道釧路市春日町6-1
38号釧路店:北海道釧路市鳥取大通3-12-26
2551

5号線函館昭和店:北海道函館市昭和二丁目46-3
函館イトーヨーカドー店:北海道函館市美原1-3-1
2475

イオン札幌元町店:北海道札幌市東区北三十一条東15丁目1
北49条店:北海道札幌市東区北四十九条東8丁目3-17
2390

釧路貝塚通り店:北海道釧路市武佐3丁目47番20号
イオン釧路店:北海道釧路郡釧路町桂木1-1-1
2379

厚別通り川下店:北海道札幌市白石区川下五条4-2-1
厚別西友店:北海道札幌市厚別区厚別西四条6丁目700-126
2315

屯田イトーヨーカドー店:北海道札幌市北区屯田八条3丁目5-1
北49条店:北海道札幌市東区北四十九条東8丁目3-17
2218

羊ヶ丘通清田店:北海道札幌市清田区真栄47-1
イオンモール札幌平岡店:北海道札幌市清田区平岡三条5丁目3-1
2199

新琴似店:北海道札幌市北区新琴似2条7-1-1
南新川店:北海道札幌市北区北二十三条西14丁目6-1
2104

北49条店:北海道札幌市東区北四十九条東8丁目3-17
イオン札幌麻生店:北海道札幌市北区北三十九条西4丁目1-5
2086

イオン札幌麻生店:北海道札幌市北区北三十九条西4丁目1-5
北49条店:北海道札幌市東区北四十九条東8丁目3-17
2086

36号里塚店:北海道札幌市清田区里塚二条7丁目1-5
イオンモール札幌平岡店:北海道札幌市清田区平岡三条5丁目3-1
2048

イオンモール札幌平岡店:北海道札幌市清田区平岡三条5丁目3-1
札幌平岡店:北海道札幌市厚別区上野幌三条2丁目1-7
1933

札幌平岡店:北海道札幌市厚別区上野幌三条2丁目1-7
イオンモール札幌平岡店:北海道札幌市清田区平岡三条5丁目3-1
1933

札幌アリオ店:北海道札幌市東区北七条東9丁目2-20
オーロラタウン店:北海道札幌市中央区大通西1丁目地下街オーロラタウン
1860

帯広西5条店:北海道帯広市西五条南十八丁目11-1
帯広イトーヨーカドー店:北海道帯広市稲田町南8線西10-1
1858

帯広イトーヨーカドー店:北海道帯広市稲田町南8線西10-1
帯広西5条店:北海道帯広市西五条南十八丁目11-1
1858

平岸店:北海道札幌市豊平区平岸二条10丁目3-12
西岡生協店:北海道札幌市南区澄川六条4丁目2-1
1769

白石南郷通店:北海道札幌市白石区南郷通2丁目南10-15
札幌月寒ゼビオ店:北海道札幌市豊平区月寒東三条11丁目1-10
1760

上江別高台ショッピングセンター店:北海道江別市上江別427-3
イオン江別店:北海道江別市幸町35
1729

イオン江別店:北海道江別市幸町35
上江別高台ショッピングセンター店:北海道江別市上江別427-3
1729

札幌新発寒店:北海道札幌市手稲区新発寒五条5丁目
イオンモール札幌発寒店:北海道札幌市西区発寒八条12丁目1-1
1726

南新川店:北海道札幌市北区北二十三条西14丁目6-1
イオン札幌桑園店:北海道札幌市中央区北八条西14丁目28
1682

環状通伏古店:北海道札幌市東区伏古一条5丁目1-10
イオンモール札幌苗穂店:北海道札幌市東区東苗穂二条3丁目1-1イオンモール札幌苗穂2Fフードコート
1464

厚別西友店:北海道札幌市厚別区厚別西四条6丁目700-126
12号新札幌店:北海道札幌市厚別区厚別中央二条4丁目4-12
1387

西岡生協店:北海道札幌市南区澄川六条4丁目2-1
西岡出光SS店:北海道札幌市豊平区西岡一条2丁目
1348

西岡出光SS店:北海道札幌市豊平区西岡一条2丁目
西岡生協店:北海道札幌市南区澄川六条4丁目2-1
1348

函館イトーヨーカドー店:北海道函館市美原1-3-1
函館鍛治店:北海道函館市鍛治2-39
1089

函館鍛治店:北海道函館市鍛治2-39
函館イトーヨーカドー店:北海道函館市美原1-3-1
1089

36号苫小牧店:北海道苫小牧市新開町3-1-1
イオンモール苫小牧店:北海道苫小牧市柳町3-1-20
1079

イオンモール苫小牧店:北海道苫小牧市柳町3-1-20
36号苫小牧店:北海道苫小牧市新開町3-1-1
1079

イオン北見店:北海道北見市北進町1-1-1
39号北見店:北海道北見市西富町1-1-6
1027

39号北見店:北海道北見市西富町1-1-6
イオン北見店:北海道北見市北進町1-1-1
1027

北5西20店:北海道札幌市中央区北五条西20丁目1-1
イオン札幌桑園店:北海道札幌市中央区北八条西14丁目28
938

イオン札幌桑園店:北海道札幌市中央区北八条西14丁目28
北5西20店:北海道札幌市中央区北五条西20丁目1-1
938

福住イトーヨーカドー店:北海道札幌市豊平区福住二条1丁目
札幌月寒ゼビオ店:北海道札幌市豊平区月寒東三条11丁目1-10
841

札幌月寒ゼビオ店:北海道札幌市豊平区月寒東三条11丁目1-10
福住イトーヨーカドー店:北海道札幌市豊平区福住二条1丁目
841

36号千歳店:北海道千歳市錦町3-10-1
イオン千歳店:北海道千歳市栄町6-51
789

イオン千歳店:北海道千歳市栄町6-51
36号千歳店:北海道千歳市錦町3-10-1
789

イオンモール札幌苗穂店:北海道札幌市東区東苗穂二条3丁目1-1イオンモール札幌苗穂2Fフードコート
札幌インター店:北海道札幌市白石区菊水元町七条1丁目10-22
767

札幌インター店:北海道札幌市白石区菊水元町七条1丁目10-22
イオンモール札幌苗穂店:北海道札幌市東区東苗穂二条3丁目1-1イオンモール札幌苗穂2Fフードコート
767

12号新札幌店:北海道札幌市厚別区厚別中央二条4丁目4-12
新さっぽろカテプリ店:北海道札幌市厚別区厚別中央二条5-7
638

新さっぽろカテプリ店:北海道札幌市厚別区厚別中央二条5-7
12号新札幌店:北海道札幌市厚別区厚別中央二条4丁目4-12
638

宮の沢ターミナルビル店:北海道札幌市西区宮の沢1条1-17-14
イオンモール札幌発寒店:北海道札幌市西区発寒八条12丁目1-1
628

イオンモール札幌発寒店:北海道札幌市西区発寒八条12丁目1-1
宮の沢ターミナルビル店:北海道札幌市西区宮の沢1条1-17-14
628

札幌アピア店:北海道札幌市中央区北五条西3丁目
札幌ヨドバシカメラ店:北海道札幌市北区北六条西5丁目1-22
495

札幌ヨドバシカメラ店:北海道札幌市北区北六条西5丁目1-22
札幌アピア店:北海道札幌市中央区北五条西3丁目
495

札幌すすきの店:北海道札幌市中央区南四条西4丁目16
札幌南二条店:北海道札幌市中央区南二条西2丁目1
364

札幌南二条店:北海道札幌市中央区南二条西2丁目1
オーロラタウン店:北海道札幌市中央区大通西1丁目地下街オーロラタウン
253

オーロラタウン店:北海道札幌市中央区大通西1丁目地下街オーロラタウン
札幌南二条店:北海道札幌市中央区南二条西2丁目1
253

イオン釧路店:北海道釧路郡釧路町桂木1-1-1
イオン釧路フードコート店:北海道釧路郡釧路町桂木1-1-1
179

イオン釧路フードコート店:北海道釧路郡釧路町桂木1-1-1
イオン釧路店:北海道釧路郡釧路町桂木1-1-1
179