memo-pad’s blog

自分のためのメモをまとめています。ここに書いてある内容については責任は負いません。全て自己責任でお願いします。

PostgreSQLとPostGISで位置情報を使用する

現在地と近いデータ持ってくるのにPostgreSQLPostGISを使ったので、その時の構築メモ

現在地取得の仕方とかは書いてないです

環境

PostgreSQLインストール

$ wget https://download.postgresql.org/pub/repos/yum/10/redhat/rhel-7-x86_64/pgdg-centos10-10-2.noarch.rpm
$ sudo rpm -ivh pgdg-centos10-10-2.noarch.rpm
$ ls -l /etc/yum.repos.d/
-rw-r--r--  1 root root 1004 Sep 24 18:54 pgdg-10-centos.repo
$ sudo yum install postgresql-server --disablerepo=* --enablerepo=pgdg10
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~略~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Installed:
  postgresql10-server.x86_64 0:10.2-1PGDG.rhel7

Dependency Installed:
  postgresql10.x86_64 0:10.2-1PGDG.rhel7
  postgresql10-libs.x86_64 0:10.2-1PGDG.rhel7

Complete!

PostGISインストール

$ sudo yum install postgis24_10
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~略~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Installed:
  postgis24_10.x86_64 0:2.4.3-1.rhel7

Dependency Installed:
  CGAL.x86_64 0:4.7-1.rhel7
  CharLS.x86_64 0:1.0-5.el7
  SFCGAL.x86_64 0:1.2.2-1.rhel7
  SFCGAL-libs.x86_64 0:1.2.2-1.rhel7
  SuperLU.x86_64 0:5.2.0-5.el7
  armadillo.x86_64 0:8.300.0-1.el7
  arpack.x86_64 0:3.1.3-2.el7
  atk.x86_64 0:2.22.0-3.el7
  atlas.x86_64 0:3.10.1-12.el7
  avahi-libs.x86_64 0:0.6.31-17.el7
  blas.x86_64 0:3.4.2-8.el7
  boost-serialization.x86_64 0:1.53.0-27.el7
  cairo.x86_64 0:1.14.8-2.el7
  cfitsio.x86_64 0:3.370-10.el7
  cups-libs.x86_64 1:1.6.3-29.el7
  fontconfig.x86_64 0:2.10.95-11.el7
  fontpackages-filesystem.noarch 0:1.44-8.el7
  freexl.x86_64 0:1.0.4-1.el7
  gdal-libs.x86_64 0:1.11.4-12.rhel7
  geos36.x86_64 0:3.6.2-3.1.rhel7
  giflib.x86_64 0:4.1.6-9.el7
  graphite2.x86_64 0:1.3.10-1.el7_3
  gtk-update-icon-cache.x86_64 0:3.22.10-5.el7_4
  gtk2.x86_64 0:2.24.31-1.el7
  harfbuzz.x86_64 0:1.3.2-1.el7
  hdf5.x86_64 0:1.8.12-10.el7
  hicolor-icon-theme.noarch 0:0.12-7.el7
  lapack.x86_64 0:3.4.2-8.el7
  lcms2.x86_64 0:2.6-3.el7
  libICE.x86_64 0:1.0.9-9.el7
  libSM.x86_64 0:1.2.2-2.el7
  libXcomposite.x86_64 0:0.4.4-4.1.el7
  libXcursor.x86_64 0:1.1.14-8.el7
  libXdamage.x86_64 0:1.1.4-4.1.el7
  libXext.x86_64 0:1.3.3-3.el7
  libXfixes.x86_64 0:5.0.3-1.el7
  libXft.x86_64 0:2.3.2-2.el7
  libXi.x86_64 0:1.7.9-1.el7
  libXinerama.x86_64 0:1.1.3-2.1.el7
  libXrandr.x86_64 0:1.5.1-2.el7
  libXrender.x86_64 0:0.9.10-1.el7
  libXxf86vm.x86_64 0:1.1.4-1.el7
  libaec.x86_64 0:1.0.2-1.el7
  libdap.x86_64 0:3.13.1-2.el7
  libgeotiff.x86_64 0:1.4.0-1.rhel7
  libgfortran.x86_64 0:4.8.5-16.el7_4.1
  libgta.x86_64 0:1.0.4-1.el7
  libquadmath.x86_64 0:4.8.5-16.el7_4.1
  libthai.x86_64 0:0.1.14-9.el7
  libtool-ltdl.x86_64 0:2.4.2-22.el7_3
  libwebp.x86_64 0:0.3.0-7.el7
  libxshmfence.x86_64 0:1.2-1.el7
  libxslt.x86_64 0:1.1.28-5.el7
  lyx-fonts.noarch 0:2.2.3-1.el7
  mesa-libEGL.x86_64 0:17.0.1-6.20170307.el7
  mesa-libGL.x86_64 0:17.0.1-6.20170307.el7
  mesa-libGLU.x86_64 0:9.0.0-4.el7
  mesa-libgbm.x86_64 0:17.0.1-6.20170307.el7
  mesa-libglapi.x86_64 0:17.0.1-6.20170307.el7
  mpfr.x86_64 0:3.1.1-4.el7
  netcdf.x86_64 0:4.3.3.1-5.el7
  ogdi.x86_64 0:3.2.0-4.rhel7
  openblas-openmp.x86_64 0:0.2.20-3.el7
  openjpeg-libs.x86_64 0:1.5.1-17.el7
  openjpeg2.x86_64 0:2.1.0-7.el7
  pango.x86_64 0:1.40.4-1.el7
  pixman.x86_64 0:0.34.0-1.el7
  poppler.x86_64 0:0.26.5-17.el7_4
  poppler-data.noarch 0:0.4.6-3.el7
  postgresql10-contrib.x86_64 0:10.2-1PGDG.rhel7
  proj49.x86_64 0:4.9.3-3.rhel7
  unixODBC.x86_64 0:2.3.1-11.el7
  xerces-c.x86_64 0:3.1.1-8.el7_2

Complete!

PostgreSQL+PostGISでサンプル実行

準備

$ sudo /usr/pgsql-10/bin/postgresql-10-setup initdb
Initializing database ... OK
$ sudo systemctl start postgresql-10
  • postgresユーザになり、DBに接続
$ sudo su postgres

$ psql -U postgres
could not change directory to "/root": Permission denied
psql (10.2)
Type "help" for help.
  • テスト用DBを作成
postgres=# CREATE DATABASE test;
CREATE DATABASE
  • テスト用DBに接続
postgres=# \connect test
You are now connected to database "test" as user "postgres".
  • PostGISが有効になっていることを確認
test=# CREATE EXTENSION postgis;
CREATE EXTENSION

test=# select PostGIS_full_version();
                                                                                postgis_full_version

-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 POSTGIS="2.4.3 r16312" PGSQL="100" GEOS="3.6.2-CAPI-1.10.2 4d2925d6" PROJ="Rel. 4.9.3, 15 August 2016" GDAL="GDAL 1.11.4, released 2016/01/25" LIBXML="2.9.1" LIBJSON="0.11" RASTER
(1 row)

サンプルの実行

  • サンプル用のテーブル作成
postgres=# \connect test
You are now connected to database "test" as user "postgres".
test=# CREATE TABLE locations
test-# (
test(#   id bigserial NOT NULL,
test(#   name character varying(128) NOT NULL,
test(#   "position" geometry,
test(#   CONSTRAINT id PRIMARY KEY (id)
test(# )
test-# WITH (
test(#   OIDS=FALSE
test(# );
CREATE TABLE

(コピペ用)

CREATE TABLE locations
(
  id bigserial NOT NULL,
  name character varying(128) NOT NULL,
  "position" geometry,
  CONSTRAINT id PRIMARY KEY (id)
)
WITH (
  OIDS=FALSE
);
  • データの挿入(POINTは経度Lon 緯度Latの順番なので注意)
test=# INSERT INTO locations (id, name, position) VALUES (1, '東京駅', ST_GeomFromText('POINT(139.767052 35.681167)', 4326));
INSERT 0 1
test=# INSERT INTO locations (id, name, position) VALUES (2, '大阪駅', ST_GeomFromText('POINT(135.495951 34.702485)', 4326));
INSERT 0 1
test=# INSERT INTO locations (id, name, position) VALUES (3, '名古屋駅', ST_GeomFromText('POINT(136.906398 35.181446)', 4326));
INSERT 0 1
  • 博多駅(130.420611 33.590045)から近い順でデータを取得
test=# SELECT * FROM locations ORDER BY ST_DistanceSphere(position, ST_GeomFromText('POINT(130.420611 33.590045)', 4326)) OFFSET 0 LIMIT (5);
 id |   name   |                      position
----+----------+----------------------------------------------------
  2 | 大阪駅   | 0101000020E610000064ADA1D4DEEF60401D774A07EB594140
  3 | 名古屋駅 | 0101000020E61000001DE56036011D6140B9FE5D9F39974140
  1 | 東京駅   | 0101000020E61000009BCAA2B08B786140A60EF27A30D74140
(3 rows)

(補足)postgresユーザ以外からでもパスワードで接続できるように設定変更

$ sudo su postgres -c 'psql --username=postgres'
could not change directory to "/home/user": 許可がありません
psql (10.1)
Type "help" for help.
  • DBユーザのパスワード設定
postgres=# ALTER USER postgres with encrypted password 'postgres';
ALTER ROLE

postgres-# \q
  • pg_hba.confの設定を追加
$ sudo vim /var/lib/pgsql/10/data/pg_hba.conf

# 下記を他の設定より上に追加
local   all             postgres                                md5
host    all             postgres        127.0.0.1/32            md5
host    all             postgres        ::1/128                 md5

(追記後はこんな感じ)

# TYPE  DATABASE        USER            ADDRESS                 METHOD

local   all             postgres                                md5
host    all             postgres        127.0.0.1/32            md5
host    all             postgres        ::1/128                 md5
# "local" is for Unix domain socket connections only
local   all             all                                     peer
# IPv4 local connections:
host    all             all             127.0.0.1/32            ident
# IPv6 local connections:
host    all             all             ::1/128                 ident
# Allow replication connections from localhost, by a user with the
# replication privilege.
local   replication     all                                     peer
host    replication     all             127.0.0.1/32            ident
host    replication     all             ::1/128                 ident
  • 設定反映のためにPostgreSQLをリスタート
$ sudo systemctl restart postgresql-10
  • postgresqlユーザ以外からパスワード認証で接続できることを確認
psql -U postgres -h localhost -W