Postgresql でジオメトリを扱う基礎の基礎

環境及び想定

環境は CentOS 7.3.1611, Postgresql 9.2.18
店舗情報などに緯度経度情報をもって、現在位置から一番近い店舗を検索する程度を想定

インストール対象確認

yum list | grep postgis

postgis.x86_64                          2.0.7-1.el7                    epel
postgis-docs.x86_64                     2.0.7-1.el7                    epel
postgis-utils.x86_64                    2.0.7-1.el7                    epel

インストール

# yum install postgis

確認

# psql -U postgres
psql (9.2.18)
"help" でヘルプを表示します.

postgres=# CREATE DATABASE test;
CREATE DATABASE
postgres=# \connect test
データベース "test" にユーザ"postgres"として接続しました。
test=# CREATE EXTENSION postgis;
CREATE EXTENSION
test=# select PostGIS_full_version();
                                                                postgis_full_version
----------------------------------------------------------------------------------------------------------------------------------------------------
 POSTGIS="2.0.7 r13406" GEOS="3.4.2-CAPI-1.8.2 r3921" PROJ="Rel. 4.8.0, 6 March 2012" GDAL="GDAL 1.11.4, released 2016/01/25" LIBXML="2.9.1" RASTER
(1 行)

test-# \q

追加されるデータ形

|型|定義|説明|
|:------|:------|:------|:------|
|ジオグラフィ |geography |地理座標(基礎が球面) |
|ジオメトリ |geometry |平面座標(基礎が平面) |
|ジオメトリダンプ|geometry_dump|geomとpathを持つ空間データ型|
|ジオムバル|geomval|geomとvalを持つ空間データ型|
国内で地図データを扱うならジオメトリだけで十分
##テーブル作成

CREATE TABLE geometries (name varchar, geom geometry);

値の設定

INSERT INTO geometries VALUES
  ('Point', 'POINT(0 0)'),
  ('Linestring', 'LINESTRING(0 0, 1 1, 2 1, 2 2)'),
  ('Polygon', 'POLYGON((0 0, 1 0, 1 1, 0 1, 0 0))'),
  ('PolygonWithHole', 'POLYGON((0 0, 10 0, 10 10, 0 10, 0 0),(1 1, 1 2, 2 2, 2 1, 1 1))'),
  ('Collection', 'GEOMETRYCOLLECTION(POINT(2 0),POLYGON((0 0, 1 0, 1 1, 0 1, 0 0)))');

通常使うのは POINT ぐらい？

値の取得

SELECT ST_X(geom) FROM geometries WHERE name = 'Point'; # X座標取得
SELECT ST_Y(geom) FROM geometries WHERE name = 'Point'; # Y座標取得
SELECT name, ST_Distance(geom, ST_GeomFromText('POINT(0 0)')) AS dist
  FROM geometries ORDER BY dist limit 1; # ２点間の最短距離を持つレコードの取得

おまけ

Postgresql のインストール

インストール、初期化、自動起動登録、起動 の順

# yum install postgresql-server
# service postgresql initdb
# systemctl enable postgresql
# systemctl start postgresql

ユーザー追加

新しいユーザーが(の)...
-P：パスワードのプロンプト表示
-no-adduser：他ユーザー作成禁止
--no-createdb：データベース作成禁止
--no-createrole：ロール作成禁止

# su - postgres
$ createuser -P --no-adduser --no-createdb --no-createrole 新しいユーザー名

データベース作成・接続・削除

$ createdb --owner ユーザー名 DB名
$ psql DB名 -U ユーザー名
$ dropdb DB名

トラシュー

Peer authentication failed といわれたら

パスワードを変更

# psql DB名 -U ユーザー名 -W
psql: FATAL:  Peer authentication failed for user "ユーザー名"
# psql
postgres=# ALTER USER ユーザー名 WITH PASSWORD '新パスワード';
postgres=# \q

Ident authentication failed といわれたら

pg_hba.confを修正 ident の部分をすべて trust にして保存

# psql DB名 -U ユーザー名 -W
psql: FATAL:  Ident authentication failed for user "ユーザー名"
# exit
$ sudo vi /var/lib/pgsql/data/pg_hba.conf
- local   all         all                               ident sameuser とか peer とか
- host    all         all          127.0.0.1/32         ident
- host    all         all          ::1/128              ident
+ local   all         all                               trust
+ host    all         all          127.0.0.1/32         trust
+ host    all         all          ::1/128              trust
$ systemctl restart postgresql