Advanced Postgis Topic

http://www.bostongis.com/PrinterFriendly.aspx?content_name=postgis_tut01

PG SCHEMA

http://www.mail-archive.com/postgis-users@postgis.refractions.net/msg02169.html
http://www.paolocorti.net/2008/01/30/installing-postgis-on-ubuntu/


http://postgis.refractions.net/pipermail/postgis-users/2007-April/015206.html

Enabling Remote Access pgsql

If you wish to enable remote access, modify the cluster settings. The configuration files for each cluster are in the directory /etc/postgresql/version/clustername/, e.g. /etc/postgresql/8.1/main/.
Edit the file postgresql.conf, and remove the comment marker on the line for the listen_addresses setting, so that it reads:

listen_addresses = '*'

Open the file pg_hba.conf:

# nano /etc/postgresql/8.1/main/pg_hba.conf

To permit users to connect from remote systems on your network with any role, add this line:

host all all 192.168.1.0/24  md5

Replace 192.168.1.0/24 with the appropriate subnet definition for your network.
This enables md5 authentication, which means that login roles are secured with passwords that PostgreSQL itself stores in an encrypted form, and that PostgreSQL will require a valid password for any remote connection to use a role. After you make this change, ident authentication remains enabled for local logins.
You may, of course, change the local line in pg_hba.conf to disable ident authentication. Make sure that you can actually login to your PostgreSQL cluster with the postgres role and a password first!
To make your changes take effect, restart the service:

# /etc/init.d/postgresql-8.1 restart

Once the service restarts, you may access your PostgreSQL service from remote systems, either using tools such as psql or pgAdmin III, or with applications that support SQL. The Debian version of PostgreSQL is configured to automatically protect network connections with SSL, so that all communication between the server and remote clients is encrypted.
By default, the postgres system account on Debian is locked, and you should not unlock it. Cracking tools now try to use postgres, root, and other well-known system account names when they attempt to gain access to UNIX-like operating systems.
Under no circumstance should you enable PostgreSQL ident authentication for any remote access. The ident system cannot safely verify or guarantee the identity of any user on a remote system.

Apache2 et proxypass

On se connecte sous root.
On commence par installer apache

apt-get install apache2

On active le mode proxy et les dépendances nécessaires, puis on recharge :

a2enmod proxy
a2enmod proxy_http
a2enmod proxy_connect
/etc/initd/apache2 force-reload

On ouvre les permissions sur notre domaine

nano /etc/apache2/mods-enabled/proxy.conf
Allow from nc.run

PostGIS in Action is the first book devoted entirely to PostGIS. It will help both new and experienced users write spatial queries to solve real-world problems. For those with experience in more traditional relational databases, this book provides a background in vector-based GIS so you can quickly move to analyzing, viewing, and mapping data. Advanced users will learn how to optimize queries for maximum speed, simplify geometries for greater efficiency, and create custom functions suited specifically to their applications. It also discusses the new features available in PostgreSQL 8.4 and provides tutorials on using additional open source GIS tools in conjunction with PostGIS.

PuTTY for SSH Tunneling to PostgreSQL Server

What SSH Tunneling allows you to do is to tunnel all your traffic to the server thru your SSH connection. It is basically a Virtual Private Network (VPN) using SSH. The basic idea is you map local ports on your pc to remote service ports on the server. When you launch your SSH session, you can then connect with any application e.g. PgAdmin III, MS Access whatever to this remote port via the local port. Instead of specifying the remote server port when setting up your PgAdmin III or MS Access connection, you specify the ip as localhost and port as whatever port you configured to receive traffic via the Tunnel.
For more info clik here

Linux: Iptables Allow PostgreSQL server incoming request

PostgreSQL is an object relational database system that has the features of traditional commercial database systems with enhancements to be found in next-generation DBMS systems. PostgreSQL is free and the complete source code is available.

Open port 5432

By default PostgreSQLt listen on TCP port 5432. Use the following iptables rules allows incoming client request (open port 5432) for server IP address 202.54.1.20 :

iptables -A INPUT -p tcp -s 0/0 --sport 1024:65535 -d 202.54.1.20 --dport 5432 -m state --state NEW,ESTABLISHED -j ACCEPT
iptables -A OUTPUT -p tcp -s 202.54.1.20 --sport 5432 -d 0/0 --dport 1024:65535 -m state --state ESTABLISHED -j ACCEPT

As posted earlier, you do not wish give access to everyone. For example in web hosting company or in your own development center, you need to gives access to POSTGRES database server from web server only. Following example allows POSTGRES database server access (202.54.1.20) from Apache web server (202.54.1.50) only:

iptables -A INPUT -p tcp -s 202.54.1.50 --sport 1024:65535 -d 202.54.1.20 --dport 5432 -m state --state NEW,ESTABLISHED -j ACCEPT
iptables -A OUTPUT -p tcp -s 202.54.1.20 --sport 5432 -d 202.54.1.50 --dport 1024:65535 -m state --state ESTABLISHED -j ACCEPT

Allow outgoing POSTGRES client request (made via postgresql command line client or perl/php script), from firewall host 202.54.1.20:

iptables -A OUTPUT -p tcp -s 202.54.1.20 --sport 1024:65535 -d 0/0 --dport 5432 -m state --state NEW,ESTABLISHED -j ACCEPT
iptables -A INPUT -p tcp -s 0/0 --sport 5432 -d 202.54.1.20 --dport 1024:65535 -m state --state ESTABLISHED -j ACCEPT

How Do I Enable remote access to PostgreSQL database server?

by Vivek Gite · 18 comments

By default, PostgreSQL database server remote access disabled for security reasons. However, some time you need to provide the remote access to database server from home computer or from web server.

Step # 1: Login over ssh if server is outside your IDC

Login over ssh to remote PostgreSQL database server:
$ ssh user@remote.pgsql.server.com

Step # 2: Enable client authentication

Once connected, you need edit the PostgreSQL configuration file, edit the PostgreSQL configuration file /var/lib/pgsql/data/pg_hba.conf (or /etc/postgresql/8.2/main/pg_hba.conf for latest 8.2 version) using a text editor such as vi.
Login as postgres user using su / sudo command, enter:
$ su - postgres
Edit the file:
$ vi /var/lib/pgsql/data/pg_hba.conf
OR
$ vi /etc/postgresql/8.2/main/pg_hba.conf
Append the following configuration lines to give access to 10.10.29.0/24 network:
host all all 10.10.29.0/24 trust
Save and close the file. Make sure you replace 10.10.29.0/24 with actual network IP address range of the clients system in your own network.

Step # 2: Enable networking for PostgreSQL

You need to enable TCP / IP networking. Use either step #3 or #3a as per your PostgreSQL database server version.

Step # 3: Allow TCP/IP socket

If you are using PostgreSQL version 8.x or newer use the following instructions or skip to Step # 3a for older version (7.x or older).
You need to open PostgreSQL configuration file /var/lib/pgsql/data/postgresql.conf or /etc/postgresql/8.2/main/postgresql.conf.
# vi /etc/postgresql/8.2/main/postgresql.conf
OR
# vi /var/lib/pgsql/data/postgresql.conf
Find configuration line that read as follows:
listen_addresses='localhost'
Next set IP address(es) to listen on; you can use comma-separated list of addresses; defaults to 'localhost', and '*' is all ip address:
listen_addresses='*'
Or just bind to 202.54.1.2 and 202.54.1.3 IP address
listen_addresses='202.54.1.2 202.54.1.3'
Save and close the file. Skip to step # 4.

Step #3a - Information for old version 7.x or older

Following configuration only required for PostgreSQL version 7.x or older. Open config file, enter:
# vi /var/lib/pgsql/data/postgresql.conf
Bind and open TCP/IP port by setting tcpip_socket to true. Set / modify tcpip_socket to true:
tcpip_socket = true
Save and close the file.

Step # 4: Restart PostgreSQL Server

Type the following command:
# /etc/init.d/postgresql restart

Step # 5: Iptables firewall rules

Make sure iptables is not blocking communication, open port 5432 (append rules to your iptables scripts or file /etc/sysconfig/iptables):

iptables -A INPUT -p tcp -s 0/0 --sport 1024:65535 -d 10.10.29.50  --dport 5432 -m state --state NEW,ESTABLISHED -j ACCEPT
iptables -A OUTPUT -p tcp -s 10.10.29.50 --sport 5432 -d 0/0 --dport 1024:65535 -m state --state ESTABLISHED -j ACCEPT

Restart firewall:
# /etc/init.d/iptables restart

Step # 6: Test your setup

Use psql command from client system. Connect to remote server using IP address 10.10.29.50 and login using vivek username and sales database, enter:
$ psql -h 10.10.29.50 -U mbb -d sales
PgAdmin NT:
The service field specifies parameters to control the database service process. Its meaning is operating system dependent.
If pgAdmin is running on a Windows machine, it can control the postmaster service if you have enough access rights. Enter the name of the service. In case of a remote server, it must be prepended by the machine name (e.g. PSE1\pgsql-8.0). pgAdmin will automatically discover services running on your local machine.

PostGIS: Update geometry_columns table

If you add or remove tables with geometry columns, the geometry_columns table isn't updated automatically. To update the geometry_columns table try:

SELECT probe_geometry_columns();

template-postgis database

Create a template_postgis database
Some might find this useful for creating PostGIS databases without having to be PostgreSQL
super users. The idea is to create a template_postgis database, install plpgsql and postgis into
it, and then use this database as a template when creating new PostGIS databases.

$ psql template1
\c template1
CREATE DATABASE template_postgis WITH TEMPLATE = template1 ENCODING = 'LATIN1';
 
-- next set the 'datistemplate' record in the 'pg_database' table for
-- 'template_postgis' to TRUE indicating its a template
UPDATE pg_database SET datistemplate = TRUE WHERE datname =
'template_postgis';
\c template_postgis
CREATE LANGUAGE plpgsql;
\i /usr/share/postgresql/contrib/lwpostgis.sql;
\i /usr/share/postgresql/contrib/spatial_ref_sys.sql;
 
-- windows 
-- C:\Program Files\PostgreSQL\8.2\share\contrib\lwpostgis.sql
-- C:\Program Files\PostgreSQL\8.2\share\contrib\spatial_ref_sys.sql
 
GRANT ALL ON geometry_columns TO PUBLIC;
GRANT ALL ON spatial_ref_sys TO PUBLIC;
 
-- vacuum freeze: it will guarantee that all rows in the database are
-- "frozen" and will not be subject to transaction ID wraparound
-- problems.
VACUUM FREEZE;

Now non-super users can create PostGIS databases using template_postgis:
postgres=# CREATE DATABASE corolle_db WITH OWNER=corolle TEMPLATE= template_postgis ;

HOWTO PostgreSQL

Les fichiers de configuration de trouvent dans $HOME/data et notamment pg_hba.cong pour le gestion des droits et postgresql.conf pour la configuration générale du service. Sous Debian/UBUNTU ces fichiers sont des liens symboliques vers des fichiers se trouvant dans /etc/postgresql/

Voici un exemple simple de fichier data/pg_hba.conf qui règle certaines permissions :

host    all         all         192.168.0.2     255.255.255.255   password
host    all         all         1.2.3.4     255.255.255.255   password
local   all         postgres                                        ident sameuser
local   all         all                                             password
host    all         all         127.0.0.1         255.255.255.255   password
host    all         all         0.0.0.0           0.0.0.0           reject

Sous Debian/UBUNTU, les journaux de PosgreSQL se trouvent dans /var/log/postgresql/postgresql.log

La première chose à faire est de mettre un mot de passe à l'admin postgres :

# su postgres
$ psql -d template1
template1=# alter user postgres with password 'MDP';

Puis éditer data/pg_hba.conf et mettre une ligne ressemblant à :

local  all  postgres  password

Ensuite pour gérer une base de données.
On crée un utilisateur :

$ createuser login -P -D -A

Puis on crée une base de données "pour" cet utilisateur :

$ createdb -O login -E LATIN1 base

On prendra bien garde à l'encodage de la table : LATIN9, LATIN1, UNICODE, etc.

Pour supprimer une base de données :

$ dropdb base

Pour supprimer un utilisateur :

$ dropuser login

Pour lister les base de données :

$ psql -l

On peut maintenant "utiliser" notre base de données avec le client en ligne PostgreSQL en ligne de commande :

$ psql base login
ma_base=#

Voici quelques commandes pratiques à retenir :

\d [NAME]      describe table, index, sequence, or view
 \d{t|i|s|v|S} [PATTERN] (add "+" for more detail)
                list tables/indexes/sequences/views/system tables
 \da [PATTERN]  list aggregate functions
 \db [PATTERN]  list tablespaces (add "+" for more detail)
 \dc [PATTERN]  list conversions
 \dC            list casts
 \dd [PATTERN]  show comment for object
 \dD [PATTERN]  list domains
 \df [PATTERN]  list functions (add "+" for more detail)
 \dg [PATTERN]  list groups
 \dn [PATTERN]  list schemas (add "+" for more detail)
 \do [NAME]     list operators
 \dl            list large objects, same as \lo_list
 \dp [PATTERN]  list table, view, and sequence access privileges
 \dT [PATTERN]  list data types (add "+" for more detail)
 \du [PATTERN]  list users
 \l             list all databases (add "+" for more detail)

\z [PATTERN] list table, view, and sequence access privileges (same as \dp)
\q = quitter
\h = aide
SELECT version(); = version PostgreSQL
SELECT current_date; = date actuelle
\i fichier.sql = lit les instructions du fichier fichier.sql
\d table = décrit une table (comme DESCRIBE avec MySQL)

Création de table

Voici les différents types de données pour les champs d'une table :

char(n)
varchar(n)
int
real
double precision
date
time
timestamp
interval

Remarque : on peut aussi définir ses propres types de données

claSiQuaL, la syntaxe :

CREATE TABLE ma_table (col1 type, [...], coln type);
DROP TABLE ma_table;

Pour la forme un petit exemple tiré de la doc de PostgreSQL :

CREATE TABLE weather (
    city            varchar(80),
    temp_lo         int,           -- low temperature
    temp_hi         int,           -- high temperature
    prcp            real,          -- precipitation
    date            date
);

Rq : deux tirets -- introduisent des commentaires...

Insertion de données

Insertion de tous les champs d'une table :

INSERT INTO weather VALUES ('San Francisco', 46, 50, 0.25, '1994-11-27');

Insertion en précisant les champs :

INSERT INTO weather (city, temp_lo, temp_hi, prcp, date)
    VALUES ('San Francisco', 43, 57, 0.0, '1994-11-29');

Insertion à partir d'un fichier externe :

COPY weather FROM '/home/user/weather.txt';

Rq : voir www.postgresql.org/docs/7.4/interactive/sql-copy.html

Extraction de données

Rien ne vaut des exemples :

SELECT * FROM weather;
SELECT city, (temp_hi+temp_lo)/2 AS temp_avg, date FROM weather;
SELECT * FROM weatherWHERE city = 'San Francisco' AND prcp > 0.0;
SELECT DISTINCT city FROM weather ORDER BY city;

Avec des jointures :

SELECT * FROM weather, cities WHERE city = name;
SELECT weather.city, weather.temp_lo, cities.location 
FROM weather, cities WHERE cities.name = weather.city;
SELECT * FROM weather INNER JOIN cities ON (weather.city = cities.name);
SELECT * FROM weather LEFT OUTER JOIN cities ON 
(weather.city = cities.name);
SELECT * FROM weather w, cities c WHERE w.city = c.name;

Avec des fonctions (Aggregate Functions) :

SELECT max(temp_lo) FROM weather;

Attention, les "Aggregate Functions" ne peuvent être utilsées dans la clause WHERE
Ainsi la requête suivante est fausse :

SELECT city FROM weather WHERE temp_lo = max(temp_lo);

On devra donc faire :

SELECT city FROM weather WHERE 
temp_lo = (SELECT max(temp_lo) FROM weather);

On pourra bien sûr utilise "GROUP BY ...", "HAVING ...", etc.

Mise à jour des données

Toujours avec un exemple :

UPDATE weather SET temp_hi = temp_hi - 2, 
temp_lo = temp_lo - 2 WHERE date > '1994-11-28';

Suppression des données

Encore avec un exemple :

DELETE FROM weather WHERE city = 'Hayward';

Pour effacer toutes les données d'une table :

DELETE FROM weather;

PostgreSQL et les sauvegardes

Pour sauvegarder une base de données :

$ pg_dump NOM_BASE > NOM_FICHIER

Pour restaurer une base de données :

$ createdb -O login -E LATIN1 newbase
psql newbase < NOM_FICHIER

Sauvegarder une base complète :

$ pg_dumpall > NOM_FICHIER

Rq : Attention, il y a certaines limitations dans la sauvegarde "à chaud". Voir http://traduc.postgresqlfr.org/pgsql-fr/backup.html


Liens

Documentation officielle
http://www.postgresql.org/docs/7.4/interactive/tutorial.html

Les Roles PostgreSQL

ALTER ROLE nom [ [ WITH ] option [ ... ] ]

où option peut être :
    
      SUPERUSER | NOSUPERUSER
    | CREATEDB | NOCREATEDB
    | CREATEROLE | NOCREATEROLE
    | CREATEUSER | NOCREATEUSER
    | INHERIT | NOINHERIT
    | LOGIN | NOLOGIN
    | CONNECTION LIMIT limiteconnexion
    | [ ENCRYPTED | UNENCRYPTED ] PASSWORD 'motdepasse'
    | VALID UNTIL 'dateheure' 

ALTER ROLE nom RENAME TO nouveaunom

ALTER ROLE nom SET parametre_configuration { TO | = } { valeur | DEFAULT }
ALTER ROLE nom RESET parametre_configuration

PostgreSQL-postgis Installation avec QGIS et Pgadmin3

1-Utilisation de finger pour plus d'informations sur les utilisateurs.

finger postgres

-Création d'utilisateur postgres:

$ sudo -s -u postgres 
Password: 

Aprés: 

$sudo -s 

entrez la mot de passe root

aprés

$ su postgres 

$psql

Pour avoir l'interface interactive de PostgreSQL.

création de user et de rôle:

$ createuser -P 

ainsi, le programme vous demanderas naturellement 2 fois votre mot pass.

création de la base: 

$ createdb -O  -E UTF8  

Ajouter la mot de passe: 

psql -d postgres -c "ALTER USER postgres WITH PASSWORD 'mot_de_passe';"

Reboot PostgreSQL

$ sudo /etc/init.d/postgresql-8.4 restart

Ensuite, on peut se connecter via :

$ psql -U 
Mot de passe pour l'utilisateur  : 

Par raisons de securité dans postgreSQL il faut impirativement ajouter des rôles utilisateur ( NOSUPERUSER) et (LOGIN)

Recap:

Lancez PgAdmin3:

remplissez les champs concernées: 

Login, Mot de passe,base, nom de la connection, param de securité,Hôte et base de maintenace (template1).

6 details.

Pour QGIS il faut installer des tables géometriques tous dabord pour pouvoir se connecter à la base correctement.

Le mieux c'est de créer un database avec le template mentionnées si dessus.

Aprés il reste que d'injecter des shp dans le postgis databse .

 Qgis:

1-Importer des shapes dans PostgreSQL.

2-Attention au codage de caractéres (pb).

3-Ajouter une couche postgis.

NB:il faut confirmer la connection à la base à chaque fois. 

Enjoy, you can edit on WYSIWYG mode the postgis database also.

Manual Data Loading with 

$   shp2pgsql -I -s 32633 POI.shp gis_schema.poi > poi.sql
Shapefile type: Point
Postgis type: POINT[2]
$   shp2pgsql -I -s 32633 vestizioni.shp gis_schema.vestizioni > vestizioni.sql
Shapefile type: Arc
Postgis type: MULTILINESTRING[2]
$  shp2pgsql -I -s 32633 compfun.shp gis_schema.compfun > compfun.sql
Shapefile type: Polygon
Postgis type: MULTIPOLYGON[2]
$  shp2pgsql -I -s 32633 zone.shp gis_schema.zone > zone.sql
Shapefile type: Polygon
Postgis type: MULTIPOLYGON[2] 

Note that we used 2 options of the shp2pgsql:

-I will also create a GiST index on the geometry column

-s will give to PostGIS the information of the srid of the data
(srid=32633 is for gis data with a spatial reference WGS84, UTM 33 N) 

Now it is time to execute the *.sql scripts with the gis user:

$ psql -d gisdb -h localhost -U gis -f poi.sql 
 
BEGIN
psql:poi.sql:4: NOTICE:  CREATE TABLE will create implicit sequence "poi_gid_seq" for serial column "poi.gid"
psql:poi.sql:4: NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "poi_pkey" for table "poi"
CREATE TABLE
                  addgeometrycolumn                   
------------------------------------------------------
 gis_schema.poi.the_geom SRID:32633 TYPE:POINT DIMS:2
 
(1 row)
 
CREATE INDEX
COMMIT

Do the same with the other 3 sqls generated from the previous step:

$ psql -d gisdb -h localhost -U gis -f compfun.sql 
$ psql -d gisdb -h localhost -U gis -f vestizioni.sql 
$ psql -d gisdb -h localhost -U gis -f zone.sql

Spatial OLAP

Notre sauce c'est l'open source spatial, ça fait long temps que je cherche à créer un datawarehouse Spatial sous postgis, j'ai trouvé des bonnes outils open source qui ferais bien ça ( PostGeoOLAP+JUMP+ROLAP)

"Spatial OLAP can be defined as a visual platform built especially to support rapid and easy spatiotemporal analysis and exploration of data following a multidimensional approach comprised of aggregation levels available in cartographic displays as well as in tabular and diagram displays." Bédard, 1997.

Concepts:

http://spatialolap.scg.ulaval.ca/concepts.asp

Create GIST Index for PostgreSQL/Postgis

PostgreSQL/Postgis can't update automatically her  spatial reference system for
updating PostgreSQL/Postgis table You can proceed for all the tables which have Geometry collumns.
A script named 'createGISTIndex.sql' is provided in the 'SQL' dir in the Gisgraphy distribution to create all the GIST indexes for all the tables and this can be a good exemple.

- usage : psql -UYOURUSER -h 127.0.0.1 -d gisgraphy -f /path/to/file/createGISTIndex.sql


\connect gisgraphy
\echo will create all the index needed by gisgraphy to improve performance,

\this make take a while depends on how many data are in database
\echo will create Geonames Index
CREATE INDEX locationIndexAdm ON adm USING GIST (location);
CREATE INDEX locationIndexAirport ON airport USING GIST (location);
CREATE INDEX locationIndexAmusePark ON amusePark USING GIST (location);
CREATE INDEX locationIndexAqueduc ON quay USING GIST (location);

..

..

VACUUM FULL ANALYZE;