PostgreSQL

From ArgeoWiki

PostgreSQL
Web site	http://www.postgresql.org/
All third party software

Contents 1 Installation on CentOS 2 Create user and database 3 PostGIS 3.1 Import shape files

Installation on CentOS

Install it:

yum install postgresql-server postgresql-docs pgadmin3*

Start it once to initialize the database:

sudo /sbin/service postgresql restart

Edit the access authorizations:

sudo gedit /var/lib/pgsql/data/pg_hba.conf

On Ubuntu:

sudo gedit /etc/postgresql/8.4/main/pg_hba.conf

# TYPE  DATABASE    USER        CIDR-ADDRESS          METHOD
local   all         all                               trust
host	all	all	192.168.1.0/24	trust
host	all	all	127.0.0.1/32	trust

(very UNSAFE, and needs to be refined when everything is working)

Restart:

sudo /sbin/service postgresql restart

Create user and database

Create user

sudo -u postgres psql -c "create user argeo with password 'argeo' CREATEUSER CREATEDB;"

Create database

 sudo -u postgres psql -c "CREATE DATABASE gis_test WITH OWNER argeo;"

PostGIS

Examples with:

• database: gis_test
• user: argeo (owning the database and with super user rights required by creating procedural language)

sudo -u postgres createlang -W plpgsql gis_test
# PostGIS 1.3
#sudo -u postgres psql -d gis_test -f /usr/share/pgsql/contrib/lwpostgis.sql
sudo -u postgres psql -d gis_test -f /usr/share/pgsql/contrib/postgis.sql
sudo -u postgres psql -d gis_test -f /usr/share/pgsql/contrib/spatial_ref_sys.sql

(from http://postgis.refractions.net/documentation/manual-1.3/ch02.html#PGInstall)

Import shape files

#!/bin/sh
SHP_ID=$1
shp2pgsql europe_$SHP_ID.shp osm_$SHP_ID > europe_$SHP_ID.sql
psql -U argeo -d gis_test -q -f europe_$SHP_ID.sql

If issue with encoding add:

shp2pgsql -W LATIN1 ...