- MacPorts? で、インストール
$ sudo port install postgis2 pgadmin3
インストール中に出てくるメッセージを覚えておく
...
---> Installing postgresql92-server @9.2.4_0
---> Activating postgresql92-server @9.2.4_0
To create a database instance, after install do
sudo mkdir -p /opt/local/var/db/postgresql92/defaultdb
sudo chown postgres:postgres /opt/local/var/db/postgresql92/defaultdb
sudo su postgres -c '/opt/local/lib/postgresql92/bin/initdb -D /opt/local/var/db/postgresql92/defaultdb'
To tweak your DBMS, consider increasing kern.sysv.shmmax by adding an increased
kern.sysv.shmmax .. to /etc/sysctl.conf
---> Cleaning postgresql92-server
...
- データベースを初期化
[~]$ sudo mkdir -p /opt/local/var/db/postgresql92/defaultdb
[~]$ sudo chown postgres:postgres /opt/local/var/db/postgresql92/defaultdb
[~]$ sudo su postgres -c '/opt/local/lib/postgresql92/bin/initdb -D /opt/local/var/db/postgresql92/defaultdb'
The files belonging to this database system will be owned by user "postgres".
This user must also own the server process.
The database cluster will be initialized with locale "ja_JP.UTF-8".
The default database encoding has accordingly been set to "UTF8".
initdb: could not find suitable text search configuration for locale "ja_JP.UTF-8"
The default text search configuration will be set to "simple".
fixing permissions on existing directory /opt/local/var/db/postgresql92/defaultdb ... ok
creating subdirectories ... ok
selecting default max_connections ... 20
selecting default shared_buffers ... 1600kB
creating configuration files ... ok
creating template1 database in /opt/local/var/db/postgresql92/defaultdb/base/1 ... ok
initializing pg_authid ... ok
initializing dependencies ... ok
creating system views ... ok
loading system objects' descriptions ... ok
creating collations ... ok
creating conversions ... ok
creating dictionaries ... ok
setting privileges on built-in objects ... ok
creating information schema ... ok
loading PL/pgSQL server-side language ... ok
vacuuming database template1 ... ok
copying template1 to template0 ... ok
copying template1 to postgres ... ok
WARNING: enabling "trust" authentication for local connections
You can change this by editing pg_hba.conf or using the option -A, or
--auth-local and --auth-host, the next time you run initdb.
Success. You can now start the database server using:
/opt/local/lib/postgresql92/bin/postgres -D /opt/local/var/db/postgresql92/defaultdb
or
/opt/local/lib/postgresql92/bin/pg_ctl -D /opt/local/var/db/postgresql92/defaultdb -l logfile start
- ログディレクトリ作成
$ sudo mkdir /var/log/postgresql92
$ sudo chown postgres:postgres /var/log/postgresql92/
- 起動・停止
$ sudo su postgres -c '/opt/local/lib/postgresql92/bin/pg_ctl -D /opt/local/var/db/postgresql92/defaultdb -l /var/log/postgresql92/logfile start'
server starting
$ sudo su postgres -c '/opt/local/lib/postgresql92/bin/pg_ctl -D /opt/local/var/db/postgresql92/defaultdb stop'
waiting for server to shut down.... done
server stopped
server starting
postgres コマンドで起動した場合には、foreground 実行になる
- 自動起動
$ sudo port load postgresql92-server
$ sudo launchctl list | grep macports
2279 - org.macports.postgresql92-server
- 0 org.macports.frameworks.macports
- 自動起動解除
$ sudo port unload postgresql92-server
$ sudo launchctl list | grep macports
- 0 org.macports.frameworks.macports
- PostgreSQLの レポジトリを yum に登録する
# yum -y install http://yum.postgresql.org/9.2/redhat/rhel-6-x86_64/pgdg-sl92-9.2-8.noarch.rpm
/etc/yum.repos.d/sl.repo で、SL Linux のレポジトリにある PostgreSQL を使わないように設定
[sl]
...
exclude=postgresql*
[sl-security]
...
exclude=postgresql*
- 現在インストールされている PostgreSQL を削除する
# rpm -e postgresql-server.
- EPEL のインストール (postgis や gdal から使われるライブラリが SL Linux のレポジトリにはない)
- https://fedoraproject.org/wiki/EPEL
- 同じパッケージがあった場合、SL Linux のレポジトリの方を優先するように設定する
# yum -y install yum-priorities
- /etc/yum.repos.d/sl.repo の優先度を 1 に設定
[sl]
...
priority=1
exclude=postgresql*
[sl-security]
...
priority=1
exclude=postgresql*
- EPEL のインストール
# rpm -ivh http://ftp.jaist.ac.jp/pub/Linux/Fedora/epel/6/i386/epel-release-6-8.noarch.rpm
# yum -y update epel-release
- ようやく postgis2 のインストール
# yum -y install postgis2_92
PostgreSQL を含めて、もろもろインストール完了
Installed:
postgis2_92.x86_64 0:2.0.3-2.rhel6
Dependency Installed:
cfitsio.x86_64 0:3.240-3.el6
gdal.x86_64 0:1.7.3-15.el6
geos.x86_64 0:3.3.8-1.rhel6
hdf5.x86_64 0:1.8.5.patch1-7.el6
json-c.x86_64 0:0.10-2.el6
libdap.x86_64 0:3.11.0-1.el6
libgeotiff.x86_64 0:1.2.5-5.el6
libgfortran.x86_64 0:4.4.7-3.el6
librx.x86_64 0:1.5-14.el6
netcdf.x86_64 0:4.1.1-3.el6.3
ogdi.x86_64 0:3.2.0-0.14.beta2.el6
postgresql92.x86_64 0:9.2.4-1PGDG.rhel6
postgresql92-libs.x86_64 0:9.2.4-1PGDG.rhel6
proj.x86_64 0:4.8.0-2.rhel6
unixODBC.x86_64 0:2.2.14-12.el6_3
xerces-c.x86_64 0:3.0.1-20.el6
ついで
# yum -y install postgresql92-server
# yum -y install pgadmin3
- データベース初期化
# service postgresql-9.2 initdb
データベースを初期化中: [ OK ]
- 起動・停止
# /sbin/service postgresql-9.2 start
postgresql-9.2 サービスを開始中: [ OK ]
# /sbin/service postgresql-9.2 stop
postgresql-9.2 サービスを停止中: [ OK ]
- 自動起動
# /sbin/chkconfig --level 35 postgresql-9.2 on
# /sbin/chkconfig --list | grep postgresql
postgresql-9.2 0:off 1:off 2:off 3:on 4:off 5:on 6:off
- 自動起動解除
# /sbin/chkconfig postgresql-9.2 off
# /sbin/chkconfig --list | grep postgresql
postgresql-9.2 0:off 1:off 2:off 3:off 4:off 5:off 6:off
- PostGIS 関数
- POINT (東経135度、北緯35度) は、どこの国?
demo=# SELECT sovereignt from countries_table where ST_Distance(geom, ST_GeomFromText('Point(135.0 35.0)', 4326)) = 0;
sovereignt
------------
Japan
(1 row)
- LINE (東経125度、北緯39度) → (東経135度、北緯35度) は、どこの国をまたぐ?
demo=# SELECT sovereignt from countries_table where ST_Distance(geom, ST_GeomFromText('LineString(125.0 39.0,135.0 35.0)', 4326)) = 0;
sovereignt
-------------
Japan
South Korea
North Korea
(3 rows)
- POLYGON (E0 N0)-(E0 N10)-(E10 N10)-(E10 N0)-(E0 N0) はどこの国をまたぐ?
demo=# SELECT sovereignt from countries_table where ST_Distance(geom, ST_GeomFromText('POLYGON((0 0, 0 10, 10 10, 10 0, 0 0))', 4326)) = 0;
sovereignt
-----------------------
Benin
Cameroon
Gabon
Ghana
Equatorial Guinea
Nigeria
Sao Tome and Principe
Togo
(8 rows)
- 日本を囲む矩形と矩形が重なる国は?
demo=# SELECT b.sovereignt from countries_table a, countries_table b where a.sovereignt = 'Japan' and a.geom && b.geom;
sovereignt
--------------------------
China
Japan
South Korea
North Korea
Russia
United States of America
United States of America
(7 rows)
へー、USA とも重なるのか
- 日本と国境を接する国は?
demo=# SELECT b.sovereignt from countries_table a, countries_table b where a.sovereignt = 'Japan' AND ST_Distance(a.geom, b.geom) = 0;
sovereignt
------------
Japan
(1 row)
Core2 2.4GHz だと、ちょっと遅い
- ブラジルに接する国は?
demo=# SELECT b.sovereignt from countries_table a, countries_table b where a.sovereignt = 'Brazil' AND ST_Distance(a.geom, b.geom) = 0;
sovereignt
------------
Argentina
Bolivia
Brazil
Colombia
France
Guyana
Peru
Paraguay
Suriname
Uruguay
Venezuela
(11 rows)
なんで France ? 次回乞うご期待
![[PukiWiki]](image/pukiwiki.png "[PukiWiki]")
qgis4.png
qgis3.png
qgis2.png
qgis1.png
