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

## 環境及び想定
環境は CentOS 7.3.1611, Postgresql 9.2.18
店舗情報などに緯度経度情報をもって、現在位置から一番近い店舗を検索する程度を想定
## インストール対象確認
```shell-session
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
```
## インストール
```shell-session
# yum install postgis
```
## 確認
```shell-session
# 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を持つ空間データ型|
国内で地図データを扱うならジオメトリだけで十分
##テーブル作成
```shell-session
CREATE TABLE geometries (name varchar, geom geometry);
```
## 値の設定
```shell-session
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 ぐらい？
## 値の取得
```shell-session
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 のインストール
インストール、初期化、自動起動登録、起動 の順

```shell-session
# yum install postgresql-server
# service postgresql initdb
# systemctl enable postgresql
# systemctl start postgresql
```
## ユーザー追加
新しいユーザーが(の)...
-P：パスワードのプロンプト表示
-no-adduser：他ユーザー作成禁止
--no-createdb：データベース作成禁止
--no-createrole：ロール作成禁止

```shell-session
# su - postgres
$ createuser -P --no-adduser --no-createdb --no-createrole 新しいユーザー名
```
## データベース作成・接続・削除
```shell-session
$ createdb --owner ユーザー名 DB名
$ psql DB名 -U ユーザー名
$ dropdb DB名
```
## トラシュー
### Peer authentication failed といわれたら
パスワードを変更

```shell-session
# 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 にして保存

```shell-session
# 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
```