PostGIS

• We assume you are using Ubuntu 16.04.
• for SQlite, use "spatialite"

Because version mismatch of PostgreSQL and PostGIS often causes hazards, we install PostgreSQL and PostGIS at a same time. https://www.ubuntuupdates.org/ppa/postgresql?dist=xenial-pgdg

1. Set the repository for new PostGIS. Because the default repository of Ubuntu 16.04 contains older versions of PostgreSQL and PostGIS, which have a conflict with each other, we need a special repository for these softwares.

$ wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo apt-key add -
$ sudo sh -c 'echo "deb http://apt.postgresql.org/pub/repos/apt/ xenial-pgdg main" >> /etc/apt/sources.list.d/postgresql.list'

2. Delete all the existing PostgreSQL and PostGIS packages. If they do not exist, you may not have to do this. But for just in case...

$ sudo apt purge postgresql* postgis* pgadmin*
$ sudo apt update
$ sudo apt upgrade

3. Install PostGIS. Then PostgreSQL will be automatically installed from the special repository.

$ sudo apt install postgis

Then PostgreSQL ver 11 and PostGIS ver 3 will be installed.

4. Install GUI of PostgreSQL

$ sudo apt install pgadmin4

Replace "nishida" with your account name.

$ sudo su postgres
postgres:$ psql
postgres=# create role nishida with SUPERUSER CREATEROLE CREATEDB LOGIN;
postgres=# alter role nishida with password '****';
postgres=# create database nishida owner nishida;
(hit CTRL+D)
postgres:$ 
(hit CTRL+D)
$ psql
nishida=#

nishida=# create database testgis;
(hit CTRL+D)
$ psql testgis
testgis=# create extension postgis;
testgis=# select postgis_full_version();

Then you will get following message:

postgis_full_version
---------------------------------------------
POSTGIS="3.0.0 r17983" [EXTENSION] PGSQL="110"
GEOS="3.7.1-CAPI-1.11.1 27a5e771" 
PROJ="Rel. 4.9.2, 08 September 2015" 
LIBXML="2.9.3" LIBJSON="0.11.99" LIBPROTOBUF="1.2.1" 
WAGYU="0.4.3 (Internal)"

• 参考

$ shp2pgsql -s 4612 -D -i -I -W cp932 N03-951001_08-g_AdministrativeBoundary.shp testgis > t.sql
$ psql testgis -f t.sql 

$ qgis
レイヤ -> レイヤの追加 -> PostGISレイヤの追加
 新規をクリック
 名称: 適当
 サービス: 空欄でOK
 ホスト: localhost
 ポート: 5432 (デフォルトのまま)
 データベース: testgis
その上で, 「接続」をクリック。
テーブルを選んで, 「追加」をクリック。

GEOMETRY型

[table] … テーブル名

=# CREATE INDEX idx_table_geo on [table] USING GIST (wkb_geometry);

# ALTER TABLE [table] SET WITH OIDS;
=# CREATE INDEX idex_table_oid on [table] (oid); 

PostgreSQL 8.1以降ではデフォルトでOIDが付与されなくなった。 対処法としては上記（# ALTER TABLE ...）のように書くか、/etc/postgresql/9.3/main/postgresql.conf　の default_with_oids = off を onに書き換える。

• 参考

• Introduction to PostGIS