PostgreSQL PostGIS

How to installare e configurare PostgreSQL e PostGIS su Xubuntu/Ubuntu 11.10 In particolare questa guida fa riferimento alle versioni: PostgreSQL 9.1 PgAdmin3 1.12.3 PostGIS 1.5.3-1

• Installare PostgrSQL:

   -sudo apt-get install postgresql

• Editare /etc/postgresql/9.1/main/pg_hba.conf in modo che ci sia trust:

1. Database administrative login by Unix domain socket

local all postgres trust

1. TYPE DATABASE USER ADDRESS METHOD

1. "local" is for Unix domain socket connections only

local all all trust

1. IPv4 local connections:

host all all 127.0.0.1/32 md5

1. IPv6 local connections:

host all all  ::1/128 md5

1. Allow replication connections from localhost, by a user with the
2. replication privilege.
3. local replication postgres peer
4. host replication postgres 127.0.0.1/32 md5
5. host replication postgres  ::1/128 md5

• Restartare PostgreSQL

   -sudo service postgresql restart

• Verificare che PostgreSQL funzioni, loggandosi:

   -psql -U postgres

uscire con il comando: \q

• Installare PgAdmin3

   -sudo apt-get install pgadmin3

• Installare PostGIS

   -sudo apt-get install postgresql-9.1-postgis

• Creare link simbolico a shp2pgsql. Servirà per importare i file .shp in PostgreSQL

   -sudo ln -s /usr/lib/postgresql/9.1/bin/shp2pgsql-gui /usr/bin/shp2pgsql-gui

Modificare (o creare se assente) il file plugins.ini come segue. Se state usando pgadmin3 1.14.0, il file dovrebbe andar messo in /usr/share/pgadmin3/plugins.d/ le versioni inferiori lo mettono in /usr/share/pgadmin3/:

   ;
   ; pgShapeLoader (Linux):
   ;
   Title=PostGIS Shapefile and DBF loader
   Command=$$PGBINDIR/shp2pgsql-gui -U $$USERNAME -d $$DATABASE -p $$PORT -h $$HOSTNAME
   Description=Open a PostGIS ESRI Shapefile or Plain dbf loader console to the current database.
   KeyFile=$$PGBINDIR/shp2pgsql-gui
   Platform=unix
   ServerType=postgresql
   Database=Yes
   SetPassword=No

In questo modo avrete l'estensione grafica per integrare i file .shp integrata in PgAdmin3

• Comandi per creare un database geografico:

   -createdb -U postgres postgisDB
   -createlang -U postgres plpgsql postgisDB
   -psql -U postgres -d postgisDB -f /usr/share/postgresql/9.1/contrib/postgis-1.5/postgis.sql

D'ora in avanti si può continuare ad usare PgAdmin.