RDBMSにおける空間情報

地理空間情報・GIS(地理情報システム)とは？

地理空間情報とは、地理・空間に関係づけられた情報を指す。「地理情報」、「空間情報」もほぼ同義である。 日本では平成19年8月29日に施行された、地理空間情報活用推進基本法第2条第1項に定義されている用語で、次の情報を指す。 空間上の特定の地点又は区域の位置を示す情報

参照： 地理空間情報 – Wikipedia

MySQLとPostgreSQLにて2拠点間の距離を測定してみました。

標準化のおかげで、Google Map, PostgreSQL, MySQL共に概ね同じ結果になりました。

Evaluate distance between old office and new office with MySQL and PostgreSQL.

Version Info：MySQL： 8.0.21 , PostgreSQL： 11.8

オフィス	緯度・経度
トリトンスクエア	35.65796, 139.78291
恵比寿ファーストスクエア	35.64753, 139.71478

MySQLでの距離の算出方法

root@localhost [mysql]> select ST_Distance(
    -> ST_GeomFromText('POINT(35.65796 139.78291)',4326),
    -> ST_GeomFromText('POINT(35.64753 139.71478)',4326),'metre') as "from_old_office_to_new_office";
+-------------------------------+
| from_old_office_to_new_office |
+-------------------------------+
|             6277.260893339336 |
+-------------------------------+
1 row in set (0.00 sec)

PostgreSQLでの距離の算出方法

postgres=# select ST_Distance(
ST_GeomFromText('POINT(139.71478 35.64753)',4326),
ST_GeomFromText('POINT(139.78291 35.65796)',4326),true) as "from_old_office_to_new_office";
 from_old_office_to_new_office 
-------------------------------
                 6277.26149066
(1 行)

備考：　

MySQLではGIS関連の関数は、基本的にはDefaultのまま利用出来ますが、PostgreSQLの場合はEXTENSIONを追加する必要があります。(以下、WLS上のPG9.6で検証です。Flushされて無いので、WARNINGが出てますが実際に運用する事が無いWLSなのでスルーでOK)

postgres=# create database gis owner 'postgres' encoding 'UTF8' lc_collate 'C.UTF-8' lc_ctype 'C.UTF-8' template 'template0';
WARNING:  could not flush dirty data: Function not implemented
CREATE DATABASE
postgres=# \c gis
psql (13.3 (Ubuntu 13.3-1.pgdg18.04+1), server 9.6.22)
You are now connected to database "gis" as user "postgres".
gis=# create extension postgis;
CREATE EXTENSION
gis=# select ST_Distance(
gis(# ST_GeomFromText('POINT(139.71478 35.64753)',4326),
gis(# ST_GeomFromText('POINT(139.78291 35.65796)',4326),true) as "from_old_office_to_new_office";
 from_old_office_to_new_office
-------------------------------
                 6277.26149066
(1 row)

gis=#

Sample : PostgreSQLでGIS情報を取り込んで可視化

❶　ShapeFileのダウンロード

地球地図日本 | 国土地理院

shinya@ubuntu:~/win/tmp$ wget http://www1.gsi.go.jp/geowww/globalmap-gsi/download/data/gm-japan/gm-jpn-trans_u_2_2.zip
Will not apply HSTS. The HSTS database must be a regular and non-world-writable file.
ERROR: could not open HSTS store at '/home/shinya/.wget-hsts'. HSTS will be disabled.
--2020-12-16 14:50:24--  http://www1.gsi.go.jp/geowww/globalmap-gsi/download/data/gm-japan/gm-jpn-trans_u_2_2.zip
Resolving www1.gsi.go.jp (www1.gsi.go.jp)... 163.42.90.151
Connecting to www1.gsi.go.jp (www1.gsi.go.jp)|163.42.90.151|:80... connected.
HTTP request sent, awaiting response... 301 Moved Permanently
Location: https://www1.gsi.go.jp/geowww/globalmap-gsi/download/data/gm-japan/gm-jpn-trans_u_2_2.zip [following]
--2020-12-16 14:50:24--  https://www1.gsi.go.jp/geowww/globalmap-gsi/download/data/gm-japan/gm-jpn-trans_u_2_2.zip
Connecting to www1.gsi.go.jp (www1.gsi.go.jp)|163.42.90.151|:443... connected.
HTTP request sent, awaiting response... 200 OK
Length: 4848845 (4.6M) [application/zip]
Saving to: ‘gm-jpn-trans_u_2_2.zip’

gm-jpn-trans_u_2_2.zip                  100%[=============================================================================>]   4.62M  3.02MB/s    in 1.5s

2020-12-16 14:50:26 (3.02 MB/s) - ‘gm-jpn-trans_u_2_2.zip’ saved [4848845/4848845]

shinya@ubuntu:~/win/tmp$

❷　ダウンロードしたファイルの展開

root@ubuntu:/mnt/c/Linux/tmp# unzip gm-jpn-trans_u_2_2.zip
Archive:  gm-jpn-trans_u_2_2.zip
  inflating: gm-jpn-trans_u_2_2/airp_jpn.dbf
  inflating: gm-jpn-trans_u_2_2/airp_jpn.prj
  inflating: gm-jpn-trans_u_2_2/airp_jpn.shp
  inflating: gm-jpn-trans_u_2_2/airp_jpn.shx
  inflating: gm-jpn-trans_u_2_2/ferryl_jpn.dbf
  inflating: gm-jpn-trans_u_2_2/ferryl_jpn.prj
  inflating: gm-jpn-trans_u_2_2/ferryl_jpn.shp
  inflating: gm-jpn-trans_u_2_2/ferryl_jpn.shx
  inflating: gm-jpn-trans_u_2_2/portp_jpn.dbf
  inflating: gm-jpn-trans_u_2_2/portp_jpn.prj
  inflating: gm-jpn-trans_u_2_2/portp_jpn.shp
  inflating: gm-jpn-trans_u_2_2/portp_jpn.shx
  inflating: gm-jpn-trans_u_2_2/raill_jpn.dbf
  inflating: gm-jpn-trans_u_2_2/raill_jpn.prj
  inflating: gm-jpn-trans_u_2_2/raill_jpn.shp
  inflating: gm-jpn-trans_u_2_2/raill_jpn.shx
  inflating: gm-jpn-trans_u_2_2/roadl_jpn.dbf
  inflating: gm-jpn-trans_u_2_2/roadl_jpn.prj
  inflating: gm-jpn-trans_u_2_2/roadl_jpn.shp
  inflating: gm-jpn-trans_u_2_2/roadl_jpn.shx
  inflating: gm-jpn-trans_u_2_2/rstatp_jpn.dbf
  inflating: gm-jpn-trans_u_2_2/rstatp_jpn.prj
  inflating: gm-jpn-trans_u_2_2/rstatp_jpn.shp
  inflating: gm-jpn-trans_u_2_2/rstatp_jpn.shx
  inflating: gm-jpn-trans_u_2_2/trans_jpn.met
root@ubuntu:/mnt/c/Linux/tmp#

❸　空港に関してのPOINTデータが入っているので先ずはshapeファイルからSQLを作成

shinya@ubuntu:~/win/tmp/gm-jpn-trans_u_2_2$ shp2pgsql -s 4326 -D -i -I -W utf8 airp_jpn.shp > airport.sql
Shapefile type: Point
Postgis type: POINT[2]

❹　作成したファイルをデータベースに取り込む

shinya@ubuntu:~/win/tmp/gm-jpn-trans_u_2_2$ psql -h 192.168.193.200 -U postgres GIS < airport.sql
Password for user postgres:
SET
SET
BEGIN
CREATE TABLE
ALTER TABLE
                 addgeometrycolumn
---------------------------------------------------
 public.airp_jpn.geom SRID:4326 TYPE:POINT DIMS:2
(1 row)

COPY 92
CREATE INDEX
COMMIT
ANALYZE
shinya@ubuntu:~/win/tmp/gm-jpn-trans_u_2_2$

➎ 緯度経度データを抽出

select iko,ita,nam,
ST_X(ST_Centroid(ST_Transform(geom, 4326))) AS long,
ST_Y(ST_Centroid(ST_Transform(geom, 4326))) AS lat
from airp_jpn;

詳細：　https://techblog.lclco.com/entry/2020/12/17/104231

MySQLの場合： https://github.com/YoshiakiYamasaki/MySQL-GIS-Data-Japan-eStat