Encodage de base de données Postgresql

Je suis tombé sur un problème d'encodage de ma base de données OSM dernièrement.  Mon besoin était pourtant très simple: je voulais imprimer sur ma carte les noms de rue en MAJUSCULES.  Je pensais donc utiliser la fonction upper() de postgresql via un mapfile, mais étrangement les caractères accentués ne suivaient pas:

osm= select upper('échap');
upper
-------
éCHAP
(1 row)

L'encodage da la base de données était bien UTF-8 puisque tous les caractères accentués apparaissaient convenablement.  Par contre le "Ctype" était à la valeur C. Dans la documentation de Postgresql on y mentionne que:

initdb initializes the database cluster's default locale and character set encoding. The character set encoding, collation order (LC_COLLATE) and character set classes (LC_CTYPE, e.g. upper, lower, digit) can be set separately for a database when it is created. initdb determines those settings for the template1 database, which will serve as the default for all other databases.

postges=l
Name    | Owner    | Encoding  | Collate |    Ctype
osm     | osm      | UTF8      | C       |    C

On a pas vraiment l'habitude de se soucier de cette valeur, mais elle affecte le fonctionnement de certaines fonctions qui traitent les caractères.  Pour contourner le problème, il y a deux options:

  1. Créer une nouvelle COLLATION dans Postgresql;
  2. Recréer le cluster de base de données;

Option 1

Une solution temporaire de contournement serait de créer une nouvelle COLLATION dans la base de données.  Avant on doit s'assurer d'avoir la Collation sur le serveur:

sudo locale-gen fr_CA.UTF-8 en_CA.UTF-8

Ensuite, on peux ajouter une nouvelle Collation correspondant dans la Base de données:

osm= CREATE COLLATION fr_ca_c (LOCALE='fr_CA.utf8');
osm= select upper('échap' COLLATE "fr_ca_c");;
upper
-------
ÉCHAP
(1 row)

Option 2

Cette option plus drastique sera plus robuste et efficiente à long terme.  Elle consiste à recréer le cluster de base de données pour supporter le bon encodage et la bonne Collation(Ctype). En principe, on doit être en mesure de créer la base de données avec les bons réglages de la façon suivante:

sudo su postgres -c'createdb -E UTF8 --lc-ctype en_CA.UTF-8 -T template0 template_postgis'
sudo su postgres -c'createlang -d template_postgis plpgsql;'
sudo su postgres -c'psql -U postgres -d template_postgis -c"CREATE EXTENSION postgis;"'
# # sudo su postgres -c'psql -U postgres -d template_postgis -c"select postgis_lib_version();"'
sudo su postgres -c'psql -U postgres -d template_postgis -c "GRANT ALL ON geometry_columns TO PUBLIC;"'
sudo su postgres -c'psql -U postgres -d template_postgis -c "GRANT ALL ON spatial_ref_sys TO PUBLIC;"'
sudo su postgres -c'psql -U postgres -d template_postgis -c "GRANT ALL ON geography_columns TO PUBLIC;"'
Par la suite, on doit refaire une nouvelle base de données avec ce template et le problème sera réglé: La création d'une nouvelle base de données avec le template UTF-8:
 sudo su postgres
 createdb -E utf8 -T template_postgis osm
 psql -d osm
 postgres= CREATE USER osm WITH PASSWORD 'osm';
 postgres= GRANT ALL PRIVILEGES ON DATABASE osm to osm;
 postgres=\l
 ----------------------------------
 Name | Owner | Encoding | Collate | Ctype
 osm | osm | UTF8 | C | en_CA.UTF-8
 postgres=\q
Il est possible d'avoir des problèmes de création de cette bd J'ai eu des problèmes de création d'une base de données template Postgresql supportant les caractères accentués et/ou l'encodage UTF-8.
sudo su postgres -c'createdb -E UTF8 --lc-ctype en_CA.UTF-8 -T template0 template_postgis'
createdb: database creation failed: ERROR:  invalid locale name en_CA.UTF-8

Comme mentionné plus haut, Postgresql va créer un moteur cluster (service, fichiers config, fichiers de données, logfile, etc) de base de données et ce moteur utilise les configurations d'encodages au moment de l'installation.  Si par mégarde, l'encodage UTF-8 n'était pas disponible, il faut détruire le cluster Postgresql avec pg_dropcluster, mettre à jour les variables locales d'encodage, et en créer un nouveau cluster avec pg_createcluster:

NOTE: Attention vous allez perdre toutes les BD de votre serveur!
sudo /etc/init.d/postgres stop
sudo su postgres
export LANGUAGE=en_CA.UTF-8
export LANG=en_CA.UTF-8
export LC_ALL=en_CA.UTF-8
locale-gen en_CA.UTF-8
pg_dropcluster --stop 9.1 main
pg_createcluster 9.1 main
exit
sudo /etc/init.d/postgres start
Juste pour être certain que tout est ok avec l'encodage de votre user :
export LANGUAGE=en_CA.UTF-8
export LANG=en_CA.UTF-8
export LC_ALL=en_CA.UTF-8
sudo localedef -i en_CA -f UTF-8 en_CA
sudo locale-gen en_CA.UTF-8
sudo update-locale
sudo sudo dpkg-reconfigure locales

Mon premier test avec CartoDB

Dernièrement la firme Vizzuality de Madrid (maintenant aussi a New York) lance son programme CartoDB Beta pour donner un accès à PostGIS dans la Cloud via un API.  J'ai assisté à la présentation du produit au FOSS4G de Denver et je m'étais promis de l'essayer à sa sortie.   Justement hier soir suite aux élections en Espagne un des fondateurs de la firme (@saleiva) à fait ce petit démo avec cartodb pour visualiser les résultats 

Le produit est interessant et pourra aider très rapidement les développeurs désirant avec accès à des données géographiques via Postgresql / PostGIS.  En gros c'est un API genre Google fusion table  qui permet d'interagir avec une BD (select, insert, update, delete)  géo directement via un URL.  C'est assez bien fait à mon avis, et permettra d'éviter l'utilisation des services OGC plus lourd à implanter et supporter pour certaines catégories d'applications Web.

 

On a un problème de sécurité (SQL Injection) vous allez dire? Et oui c'est possible si vous ne protéger pas votre table de données.  Un billet d'un collègue Bills Dollins du Maryland a soulevé ce point le mois dernier et un développeur de CartoDB a répondu :

" ... the security model around CartoDB is based on public/private tables. When you make a table public what you are essentially doing is giving read permissions to a “public” user. That means that anybody can fire requests to your table, but they will hit PostgreSQL security if they try to modify something. So yeah, SQL injection as you want, as soon as you try to write, it will fail. You can actually try manually by taking the URL bill is calling on the example and run it directly."

C'est donc la responsabilité du programmeur d'inclure une sécurité via authentification "OAuth" pour protéger la table de données s'il le désire. Reste que l'API offre de belles options on peut par exemple visualiser rapidement les données d'une table via un petite app "embed_map"
On pourra aussi ajuster l'affichage des données on modifiant la thématique complexe via CartoCSS ou plus simplement via un contrôle manuel:
On aurra aussi quelques options pour configurer le fond de carte par défaut de votre API.  Pour l'instant on doit utiliser Google Maps mais j'imagine qu'on pourra éventuellement faire pointer la table sur le dépôt d'une carte tuilée:
Je n'ai pas fait une petite application pour tester l'API, mais j'ai fait quelques essais avec des requêtes.  Comme par exemple, un SQL "st_intersects" avec un Linestring en WKT.  Cette requête SQL retourne un segment dans le secteur du Vieux-Québec directement dans la carte configurée de mon compte:
Pour les amateurs de Geojson je dirais ceci:

Le potentiel est bon et on peut tester le service avec un maximum de 100 MB pour l'instant.  On peut imaginer qu'on aura un plan payable au mois, pour acheter plus d'espace ... On ne peut pas télécharger CartoDB et l'installer sur votre serveur.  À moins que le projet ne devienne Open Source mais rien ne transpire pour l'instant.  On peut télécharcher le projet sur Github (Merci a Alan Boudreault - mapgears.com).  En passant, le site pour charger et gérer les tables PostGIS est vraiment bien fait et est monté en Ruby on rails.

Charger un CSV avec psql dans Postgresql

Pour convertir un fichier CSV avec des coordonnées x,y en champ de type géométrie dans PostGIS, on peux utiliser la commande COPY en ligne de commande psql! 1) Copier le fichier CSV dans un répertoire visible du serveur Posrgresql. Dans le cas de Linux utiliser sudo et changer les droits du fichier CSV: sudo cp fichier_csv dans_repertoire_postgresql sudo chown postgres:postgres fichier_csv 2)Créer la table dans Postgres create table ma_table ( code character(10) NOT NULL, poste character(100) NOT NULL, adresse character(100) NOT NULL, municipalite character(100) NOT NULL, cp character(7) NOT NULL, _lat float, _long float, CONSTRAINT ma_table_pk PRIMARY KEY (code) ); 3)Charger les données du fichier CSV: set client_encoding='LATIN1'; COPY ma_table FROM '/srv/postgresql/scripts/ma_table.csv' delimiter ';'; 4)Ajouter la colonne geométrique select AddGeometryColumn ('public','ma_table','the_geom',4326,'POINT',2); 5)faire le update à partir des coordonnées x,y UPDATE ma_table set the_geom = ST_GeomFromText('POINT('||_long||' '|| _lat||')',4326); 6) Enjoy!

Rownum dans Postgresql

Dans Oracle ceux qui ont l'habitude d'utiliser l'option rownum pour limiter le nombre de résultat d'une requête SQL pourront retrouver le même effet avec l'option limit dans Postgresql select no_appart from immeubles_p where rownum select no_appart from immeubles_p limit 10; no_appart ----------- 25 703 543 204 14 234 890 27 184 765 (10 rows) ------------------------------------------------------------

Un autre roadmap de création d’une BD spatiale sous Postgresql/PostGIS

Je reprends dans ce billet, la procédure complète pour monter rapidement une BD spatiale sous Postgresql/PostGIS. On utilisera souvent cette base de donnée simplement pour avoir un contenant de nos données géométriques.  Plusieurs utiliseront la chemin le plus court pour se connecter au serveur avec l’usager/mot de passe par défaut soit: postgres/postgres.  Il faut par contre idéalement, créer un autre utilisateur pour accéder au données SANS oublier de changer le mot de passe par défaut du serveur.

 

1) Installation du serveur Postgresql/PostGIS ET de l’utilitaire pgadmin pour un client ubuntu sudo apt-get update sudo apt-get install postgresql postgresql-client postgresql-contrib postgresql-8.4-postgis sudo apt-get install pgadmin3 sudo su postgres psql alter user postgres with password 'pw_defaut_a_changer_de_grace';

2) Un fois l’extension PostGIS intallée dans Postgresql, il ne reste qu’a trouver les deux fichiers SQL permettant d’accéder au fonctions spatiales et de la cherger avec psql.

Note: On va créer la BD template avec un encodage UTF-8.  Dépendant des versions à venir, le répertoire et même le nom des fichiers SQL peut varier... sudo su postgres createdb -E utf8 -O postgres postgis createlang plpgsql postgis psql -d postgis -f /usr/share/postgresql/8.4/contrib/postgis-1.5/postgis.sql psql -d postgis -f /usr/share/postgresql/8.4/contrib/postgis-1.5/spatial_ref_sys.sql 3) Vérifier l’installation Postgresql / PostGIS est bien correcte. On doit avoir la table geometry_table et spatial_ref_sys sudo su postgres psql postgis d Liste des relations Schéma |        Nom        | Type  | Propriétaire --------+-------------------+-------+-------------- public | geography_columns | vue   | postgres public | geometry_columns  | table | postgres public | spatial_ref_sys   | table | postgres 4) Par la suite on peux créer des BD (une bd par projet par exemple) avec le template postgis et un user sudo su postgres createdb -E utf8 -W -O postgres -T postgis osm psql -d osm CREATE USER osmusr WITH PASSWORD 'osm'; GRANT ALL PRIVILEGES ON DATABASE osm to osmusr; GRANT ALL ON TABLE geometry_columns TO osmusr; GRANT ALL ON TABLE spatial_ref_sys TO osmusr; alter user postgres with password 'petitpou'; q exit (sortir du super user postgres) psql -d osm -U osmusr -W