観光情報とバス停情報の可視化

by afaik

投稿日: 12月 19, 2021

国内観光旅行管理者の勉強をした時に、国内の観光地を学ぶのに非常に苦労したので、地図上に観光地を表示して国内の観光資源を記憶しやすくしてみました。GitからCloneして頂ければ簡単に検証出来る様にしています。

環境 (WLS Ubuntu 20.04.3 LTS)

DESKTOP-8B:$ sudo lsb_release -a
No LSB modules are available.
Distributor ID: Ubuntu
Description:    Ubuntu 20.04.3 LTS
Release:        20.04
Codename:       focal

Docker

DESKTOP-8B:$ docker --version
Docker version 20.10.11, build dea9396

DESKTOP-8B:$ docker-compose --version
docker-compose version 1.16.1, build 6d1ac21

観光地データ

国土交通省(観光資源データ)

備考: 2021年夏に、「北海道・北東北の縄文遺跡群」や「奄美大島、徳之島、沖縄島北部及び西表島」等が新規に世界遺産に登録されました。

観光とバス停データ

国土交通省国土政策局「国土数値情報（バス停留所データ）

基本データの取り込み「PostgreSQL、MySQL」

先ず最初に可視化する対象となるデータを、ShapeFileからデータベースに取り込んで行きます。
Shapefileの詳細に関しては以下参照ください。

シェープファイル (英語: Shapefile) は、地理情報システム（GIS）間でのデータの相互運用におけるオープン標準として用いられるファイル形式である。例えば、井戸、川、湖などの空間要素がベクター形式であるポイント、ライン、ポリゴンで示され、各要素に固有名称や温度などの任意の属性を付与できる。

参照：Wikiシェープファイル

PostgreSQL (PostGIS)の場合

こちらは、業務で利用している、オープンソースのPostgreSQLでの取り込み方法になります。

❶ PostgreSQLの起動

DESKTOP-8B:~/git/rdbms-docker/postgresql$ docker-compose -f ./docker-compose-with-postgis.yml up -d
Creating network "postgresql_default" with the default driver
Creating volume "postgresql_postgis-store" with default driver
Creating postgresql_postgres_1 ...
Creating postgresql_postgres_1 ... done
DESKTOP-8B:~/git/rdbms-docker/postgresql$ docker-compose ps
        Name                       Command              State                    Ports
--------------------------------------------------------------------------
postgresql_postgres_1   docker-entrypoint.sh postgres   Up      0.0.0.0:5432->5432/tcp,:::5432->5432/tcp

❷ ShapeFileのダウンロード

※ ここでは、東京の観光資源のデータのみを流し込んでいます。

DESKTOP-8B:~/win/GIS$ unzip ~/win/GIS/P12-14_13_GML.zip
Archive:  /home/shinya/win/GIS/P12-14_13_GML.zip
  inflating: KS-META-P12_14-13.xml
  inflating: P12-14_13.xml
  inflating: P12a-14_13.dbf
  inflating: P12a-14_13.prj
  inflating: P12a-14_13.shp
  inflating: P12a-14_13.shx
  inflating: P12c-14_13.dbf
  inflating: P12c-14_13.prj
  inflating: P12c-14_13.shp
  inflating: P12c-14_13.shx
DESKTOP-8B:~/win/GIS$

❸ Host OSにpostgis関連のコマンドのみインストール

※コンテナ側でもインストールは可能ですが、ここではシンプルな方法を選んでいます。

DESKTOP-8B:~/git/rdbms-docker/postgresql$ sudo apt install postgis --no-install-recommends

参照：Install only commandline tools

➍ shp2pgsqlにてShapeFileをデータベースに取り込む為のSQLに変換

DESKTOP-8B:~/win/GIS$ shp2pgsql -s 4326 -D -i -I -W SJIS P12a-14_13.shp > kanko_point_tokyo.sql
Shapefile type: Point
Postgis type: POINT[2]

DESKTOP-8B:~/win/GIS$ file kanko_point_tokyo.sql
kanko_point_tokyo.sql: UTF-8 Unicode text

➎ 対象スキーマにてEXTENTIONを有効化

DESKTOP-8B:~/win/GIS$ psql -h 127.0.0.1 -p 5432 -U postgres POC
Password for user postgres:
psql (13.5 (Ubuntu 13.5-2.pgdg20.04+1), server 13.4 (Debian 13.4-4.pgdg110+1))
Type "help" for help.

POC=# CREATE EXTENSION IF NOT EXISTS postgis;
CREATE EXTENSION
POC=# \q

➏ 対象スキーマへデータを流し込み

DESKTOP-8B:~/win/GIS$ psql -h 127.0.0.1 -p 5432 -U postgres POC < kanko_point_tokyo.sql
Password for user postgres:
SET
SET
BEGIN
CREATE TABLE
ALTER TABLE
                  addgeometrycolumn
-----------------------------------------------------
 public.p12a-14_13.geom SRID:4326 TYPE:POINT DIMS:2
(1 row)

COPY 36
CREATE INDEX
COMMIT
ANALYZE
DESKTOP-8B:~/win/GIS$

❼ 取り込んだデータの確認

MySQLの場合

こちらは、個人的に最も得意なオープンソースデータベースのMySQLにおける取り込み方法になります。 MySQLでは、空間情報を扱う関数などが標準で実装されているので、特に追加のExtention等のインストールは不要ですが、 PostgreSQLと比較すると関数、ツール含めて若干不足気味ではある印象なので、ユーザーのニーズが増えて行ったら、是非拡張して行って頂ければと思います。

❶ MySQLの起動

※ port 33060は Protocol Bufferベースに開発されたX Protocolのポートなので今回は利用していません。

DESKTOP-8B:~/git/rdbms-docker/mysql$ docker-compose -f ./docker-compose-with-volume.yml up -d
Creating network "mysql_default" with the default driver
Creating volume "mysql_mysql-store" with default driver
Creating mysql_db_1 ...
Creating mysql_db_1 ... done
DESKTOP-8B:~/git/rdbms-docker/mysql$



DESKTOP-8B:~/git/rdbms-docker/mysql$ docker-compose ps
   Name                Command             State                           Ports
----------------------------------------------------------------------
mysql_db_1   docker-entrypoint.sh mysqld   Up      0.0.0.0:3306->3306/tcp,:::3306->3306/tcp, 33060/tcp
DESKTOP-8B:~/git/rdbms-docker/mysql$

❷ MySQLではデータを取り込む為の専用ツールは提供していないのでGDALを利用します。

DESKTOP-8B:~/git/rdbms-docker/mysql$ sudo apt install gdal-bin
[sudo] password for shinya:
Reading package lists... Done
Building dependency tree
Reading state information... Done
The following packages were automatically installed and are no longer required:
<SNIP>
DESKTOP-8B:~/git/rdbms-docker/mysql$ ogrinfo --version
GDAL 3.0.4, released 2020/01/28

参考：MySQL 8.0で強化されたGIS機能と使用事例のご紹介＋α

❸ ogr2ogrにてデータの変換とデータベースへのデータの取り込み

DESKTOP-8B:~/win/GIS$ ogr2ogr -f "ESRI Shapefile" -lco ENCODING=UTF-8 -oo ENCODING=CP932 P12a-14_13_utf8.shp P12a-14_13.shp
DESKTOP-8B:~/win/GIS$ ogr2ogr -f "MySQL" MySQL:"POC,host=127.0.0.1,user=root,password=password,port=3306" P12a-14_13_utf8.shp

➍ 取り込んだデータの確認

データ(sharefile)の取り込みに関しては以上になります。

観光資源データの可視化

時間の都合上MySQLのみでの検証となります。
東京のデータはGPS等で利用されているSRID4326か4612が設定されていたので無事に取り込めましたが,全国版のShapefileはSRIDが設定されて無い様だったので、Oracle MySQLチームから紹介して頂いたshp2mysqlを使わせて頂きました。👍

参照：shp2mysql(MySQL8へのインポート用SQLに変換するコマンドラインツール)

❶ nginx, php, mysqlを構成するコンテナの起動

DESKTOP-8B:~/git/rdbms-docker/mysql$ docker-compose ps
Name   Command   State   Ports
------------------------------
DESKTOP-8B:~/git/rdbms-docker/mysql$ docker-compose -f ./docker-compose-with-volume-web.yml up -d
Creating network "mysql_default" with the default driver
Creating mysql-container ...
Creating mysql-container ... done
Creating php-container ...
Creating php-container ... done
Creating nginx-container ...
Creating nginx-container ... done

❷ shapefileの取り込み

# 1.データダウンロードと解凍

 unzip P12-10_GML.zip

# 2. shp2mysqlのダウンロード(Compile済みなので,展開してそのまま利用出来ます)

 wget https://github.com/hajime-miyauchi/shp2mysql/releases/download/v0.3/shp2mysql-linux.tar.gz

# 3.データの変換

 ./shp2mysql-linux/shp2mysql -s 4612 -W CP932 P12-10-g_TourismResource_Point.shp > P12-10-g_TourismResource_Point_convert.shp

# 4.データの取り込み
 mysql -h 127.0.0.1 -u root -p POC < P12-10-g_TourismResource_Point_convert.shp

❸ インポートしたテーブルとデータの確認

DESKTOP-8B$ mysql -h 127.0.0.1 -u root -p -e "show tables from POC"
Enter password:
+--------------------------------+
| Tables_in_POC                  |
+--------------------------------+
| p12-10-g_tourismresource_point |
+--------------------------------+

国土交通省の観光資源データ：2019件

ST_Geohash(ST_Y(geom),ST_X(geom),2) = ‘xn’

➍ 表示範囲の絞り込みとGeoHash

2019件の全データを表示させると、Google Mapの表示に時間がかかったので以下の様にGeoHashを利用して範囲を絞りました。

GeoHashとは？

GeoHashは、エリアを区画に分割して、その区画をBase32でHash化した文字列で表現した値になります。例えば、以下の例だと東京駅の緯度経度を中心として、計算した5桁のHash値は±2.4kmのエリアを選択します。この範囲内に存在するデータを生成列で緯度・軽度から自動生成しておいて、インデックスを付与してサクッと同じエリアにあるバス停等を高速に検索する事も可能です。

例)東京駅を中心として±2.4kmにある観光資源

ST_Geohash(ST_Y(geom),ST_X(geom),5) = ‘xn76u’

参照：Movable Type Scripts

±2.4kmだと少々データ量的には寂しい感じになるので、以下のGeoHashリストから関東エリアをカバー出来そうな2桁のHash値を選択しました。

PHP側にはベタで以下のSQLを埋め込んでいます

$sql = <<<SQL
select p12_003 as 'name',
p12_004 as 'category',
ST_X(geom) AS "lat",ST_Y(geom) AS "lng", 
'kanko' as 'type'  FROM `p12-10-g_tourismresource_point`
where ST_Geohash(ST_Y(geom),ST_X(geom),2) = 'xn'
SQL;

➎ Google Mapでロケーションを可視化

例）霞ケ浦：日本で琵琶湖についで、二番目に大きい面積を持つ湖

これで、基本的な観光資源の可視化が出来たので、データを拡充させてバス停留所データ等を組み合わせたりする事でも便利なコンテンツが出来そうです。

補足： ST_Longitude , ST_Latitude

東京駅(35.6809591, 139.767306)を中心としたGeoHash

root@localhost [POC]> SET @tokyo_station = ST_GeomFromText('POINT(35.6809591 139.767306)', 4326);
Query OK, 0 rows affected (0.00 sec)

root@localhost [POC]> SELECT ST_Latitude(@tokyo_station), ST_Longitude(@tokyo_station);
+-----------------------------+------------------------------+
| ST_Latitude(@tokyo_station) | ST_Longitude(@tokyo_station) |
+-----------------------------+------------------------------+
|                  35.6809591 |                   139.767306 |
+-----------------------------+------------------------------+
1 row in set (0.00 sec)

root@localhost [POC]> SELECT ST_X(@tokyo_station), ST_Y(@tokyo_station);
+----------------------+----------------------+
| ST_X(@tokyo_station) | ST_Y(@tokyo_station) |
+----------------------+----------------------+
|           35.6809591 |           139.767306 |
+----------------------+----------------------+
1 row in set (0.00 sec)

root@localhost [POC]> select st_geohash(ST_Longitude(@tokyo_station),ST_Latitude(@tokyo_station),5);
+------------------------------------------------------------------------+
| st_geohash(ST_Longitude(@tokyo_station),ST_Latitude(@tokyo_station),5) |
+------------------------------------------------------------------------+
| xn76u                                                                  |
+------------------------------------------------------------------------+
1 row in set (0.00 sec)

root@localhost [POC]> select st_geohash(ST_Y(@tokyo_station),ST_X(@tokyo_station),5);
+---------------------------------------------------------+
| st_geohash(ST_Y(@tokyo_station),ST_X(@tokyo_station),5) |
+---------------------------------------------------------+
| xn76u                                                   |
+---------------------------------------------------------+
1 row in set (0.00 sec)

参照：ジオハッシュ

備考：こちらのコンテナでサクット検証する場合は以下の変更が必要です。

No	概要
1	リポジトリーをCloneしてdocker-compose-with-volume-web.ymlで環境を構築。
2	既にお持ちであれば、Google Map APIキーの変更
3	サンプルとしてユーザー名, パスワード,データベースをそのまま記載しているので必要に応じて.envファイルに変更して下さい。
4	Google Map APIを表示する為に利用するURLを、Google Cloud PlatformにてWeb Site RestrictionsにURLを登録して下さい。

Dockerをインストールするところから対応される場合は、以下のマニュアルを参照下さい。

docker	docker-compose
dockerのインストール	docker-composeのインストール

バス停情報の追加

千葉県

DESKTOP-8B:~/win/GIS/bus/P11-10_12_GML$ shp2mysql -s 4612 -W CP932 P11-10_12-jgd-g_BusStop.shp > P11-10_12-jgd-g_BusStop_convert.shp
Shapefile type: Point
Postgis type: POINT[2]
DESKTOP-8B:~/win/GIS/bus/P11-10_12_GML$

DESKTOP-8B:~/win/GIS/bus/P11-10_12_GML$ mysql -h 127.0.0.1 -u root -p POC < P11-10_12-jgd-g_BusStop_convert.shp
Enter password:
Table   Op      Msg_type        Msg_text
POC.p11-10_12-jgd-g_busstop     analyze status  OK
DESKTOP-8B:~/win/GIS/bus/P11-10_12_GML$

東京都

DESKTOP-8B:~/win/GIS/bus/P11-10_13_GML$ shp2mysql -s 4612 -W CP932 P11-10_13-jgd-g_BusStop.shp > P11-10_13-jgd-g_BusStop_convert.shp
Shapefile type: Point
Postgis type: POINT[2]
DESKTOP-8B:~/win/GIS/bus/P11-10_13_GML$ mysql -h 127.0.0.1 -u root -p POC < P11-10_13-jgd-g_BusStop_convert.shp
Enter password:
Table   Op      Msg_type        Msg_text
POC.p11-10_13-jgd-g_busstop     analyze status  OK
DESKTOP-8B:~/win/GIS/bus/P11-10_13_GML$

神奈川県

DESKTOP-8B:~/win/GIS/bus/P11-10_14_GML$ shp2mysql -s 4612 -W CP932 P11-10_14-jgd-g_BusStop.shp > P11-10_14-jgd-g_BusStop_convert.shp
Shapefile type: Point
Postgis type: POINT[2]

DESKTOP-8B:~/win/GIS/bus/P11-10_14_GML$ mysql -h 127.0.0.1 -u root -p POC < P11-10_14-jgd-g_BusStop_convert.shp
Enter password:
Table   Op      Msg_type        Msg_text
POC.p11-10_14-jgd-g_busstop     analyze status  OK
DESKTOP-8B:~/win/GIS/bus/P11-10_14_GML$

データの結合

/**** SRID 4326 or 0 ****/

CREATE TABLE `tourism_and_busstop` (
  `gid` bigint unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(100) DEFAULT NULL,
  `category` varchar(50) DEFAULT NULL,
  `type` varchar(50) DEFAULT NULL,
  `geom` point /*!80003 SRID 4326 */ DEFAULT NULL,
  `geohash` varchar(20) GENERATED ALWAYS AS (st_geohash(`geom`,5)) VIRTUAL,
  PRIMARY KEY (`gid`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;


/**** SRID 4326 or 0以外 ****/


CREATE TABLE `tourism_and_busstop` (
  `gid` bigint unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(100) DEFAULT NULL,
  `category` varchar(50) DEFAULT NULL,
  `type` varchar(50) DEFAULT NULL,
  `geom` point /*!80003 SRID 4612 */ DEFAULT NULL,
  `geohash` varchar(20) GENERATED ALWAYS AS (st_geohash(ST_Longitude(geom),ST_Latitude(geom),5)) VIRTUAL,
  PRIMARY KEY (`gid`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;


mysql> insert into tourism_and_busstop(name,category,type,geom)
    -> select p12_003,p12_004,"kanko",geom from `p12-10-g_tourismresource_point`;
Query OK, 2019 rows affected (0.13 sec)
Records: 2019  Duplicates: 0  Warnings: 0


mysql> insert into tourism_and_busstop(name,category,type,geom)
    -> select p11_001,"バス停","busstop",geom from `p11-10_12-jgd-g_busstop`;
Query OK, 10048 rows affected (0.54 sec)
Records: 10048  Duplicates: 0  Warnings: 0

mysql> insert into tourism_and_busstop(name,category,type,geom) select p11_001,"バス停","busstop",geom from `p11-10_13-jgd-g_busstop`;
Query OK, 9446 rows affected (0.31 sec)
Records: 9446  Duplicates: 0  Warnings: 0

mysql> insert into tourism_and_busstop(name,category,type,geom) select p11_001,"バス停","busstop",geom from `p11-10_14-jgd-g_busstop`;
Query OK, 8466 rows affected (0.31 sec)
Records: 8466  Duplicates: 0  Warnings: 0

mysql>

上記を地図上に表示してみましたが、バス停が多いのでPINアイコンを変えたり更に地域を絞り込まないと難しいですね。ただ、観光地からバス停までの距離などを関数で計算して出してあげれば使える情報になりそうです。

$sql = <<<SQL
select name,
category,
ST_X(geom) AS "lat",
ST_Y(geom) AS "lng",
type
from tourism_and_busstop
where ST_Geohash(ST_Y(geom),ST_X(geom),5) = 'xn76u'
SQL;

カテゴリー:

GIS RDBMS Tech

タグ:

GDAL geohash GIS MySQL ogr2ogr PostgreSQL shp2mysql