Skip to Content

PostgreSQL/PostGis et les langages procéduraux ou comment utiliser Python, Perl ou R pour créer des procédures/fonctions

mar 21-12-2010 Martin Laloux

 

Généralement, des langages comme Python, Ruby, Perl ou R sont utilisés pour traiter les données contenues dans des bases PostgreSQL/PostGIS en les interrogeant à l'aide des librairies ou paquets ad hoc.

Pourtant, il y a moyen de faire mieux, c'est-à-dire d'installer ces langages comme langages internes de PostgreSQL/PostGIS. C'est ce qu'on appelle les langages procéduraux ou de procédure (Procedural Languages). PostgreSQL permet en effet l'écriture de fonctions et de procédures dans des langages différents du SQL et du C. Le livre « Postgis in action » , chez Manning en constitue une belle synthèse (disponible, pour le moment, au format PDF au prix de $29,99 USD).

Cette possibilité est un des éléments qui rend PostgreSQL unique vis-à-vis des autres bases de données relationnelles. Elle permet d'écrire des fonctions dans des langages comme Perl, Python, Java, TCL, R, Ruby, Sh, etc. Ces fonctions seront parfaitement utilisables pour des requêtes SQL. Ces langages sont dénommés avec le préfixe PL - (PL/Python, PL/Perl etc.)

Rappelons que le SQL de base n'est pas un langage procédural, car il n'autorise pas l'utilisation des commandes permettant de créer des procédures / fonctions (pas d’IF, ni de FOR, ou de WHILE, entre autres choses)

Les langages procéduraux

Il existe quatre langages de procédures dans la distribution standard de PostgreSQL: PL/pgSQL, PL/Tcl, PL/Perl  et PL/Python (voir  docs.postgresqlfr.org/9.0/xplang.html et www.postgresonline.com/journal/archives/6-Language-Architecture-in-PostgreSQL.html).

PL/pgSQL est le langage procédural de base et le plus connu. C'est plus ou moins l'équivalent de Transact SQL ( SQL Server / Sybase), PL / SQL (Oracle), SQL (MySQL5 +) ou  PL SQL (DB2). Il n'est pas toujours installé par défaut dans une base de données, mais on peut l'installer très facilement

createlang plpgsql mabase

Les fonctions de PostGIS sont écrites en PL/pgSQL, c'est pourquoi il faut installer le langage pour créer une base PostGIS. 

Il existe aussi d'autres langages qui ne sont pas inclus dans la distribution principale et parmi ceux-ci PL/R (www.joeconway.com/plr/ ) ou PL/Java, PL/Ruby, PL/Py, PL/Scheme ou PL/sh  (developer.postgresql.org/pgdocs/postgres/external-pl.html)

Tout ça c'est bien beau, mais à quoi ça sert en pratique ?

Comme leur statut l'implique, ils permettent d'écrire des fonctions dans le langage concerné.  Plutôt que d'écrire une fonction en PL/pgSQL, vous pourrez le faire en Python, Perl,  R ou en un autre langage. Il faut évidemment que ce langage soit préalablement installé sur votre machine.

Le gros avantage est que tous les modules/paquets installés dans vos distributions Python, Tcl, Ruby ou R seront directement accessibles dans les fonctions.

Ainsi (tiré de www2.t-hashi.org/node/10)

en PL/Python

  1. CREATE OR REPLACE FUNCTION py_numeric_median(_numeric) RETURNS text AS
  2. $$
  3. 	import numpy
  4. #	t = eval(args[0].replace("{","[").replace("}","]"))
  5. 	t = args[0].replace("{","").replace("}","").split(",")
  6.  
  7. 	t1 = []
  8. 	for v in t:
  9. 		t1.append(float(v))
  10. 	return numpy.median(t1)
  11. $$ LANGUAGE 'plpythonu';
  • 4825 lectures

vous permettra d'utiliser la librairie numpy directement dans une fonction PostgreSQL. La même chose est possible avec tous les autres langages.

Avec PL/R vous pourrez même créer directement des graphiques (tiré de www.varlena.com/GeneralBits/Tidbits/bernier/art13mar04/graphingWithR.html) :

en PL/R

  1. CREATE OR REPLACE FUNCTION f_graph() RETURNS text AS
  2. '
  3. str <<- pg.spi.exec (''select x as "mon a" ,y as "mon b" from temp order by x,y'');
  4. pdf(''monplot.pdf'');
  5. plot(str,type="l",main="Démonstration",sub="Graphique");
  6. dev.off();
  7. print(''OK'');
  8. '
  9. LANGUAGE plr;
  • 4389 lectures

Le graphique en PDF est créé par

PL/R suite

  1. SELECT f_graph();
  • 4818 lectures

Comment les installer ?

PL/Python étant présent par défaut, l'installer dans une base est aisé

createlang plpythonu mabase

ou

CREATE LANGUAGE plpythonu;

ou

CREATE PROCEDURAL LANGUAGE 'plpythonu' HANDLER plpython_call_handler;

Pourquoi plpythonu ?

« Depuis PostgreSQL 7.4, PL/Python est seulement disponible en tant que langage " sans confiance " (ceci signifiant qu'il n'offre aucun moyen de restreindre ce que les utilisateurs en font). Il a donc été renommé en plpythonu. Le codeur d'une fonction dans PL/Python sans confiance doit faire attention à ce que cette fonction ne puisse pas être utilisée pour réaliser quelque chose qui n'est pas prévu car il sera possible de faire tout ce que peut faire un utilisateur connecté en tant qu'administrateur de la base de données. Seuls les superutilisateurs peuvent créer des fonctions dans des langages sans confiance comme plpythonu » (docs.postgresqlfr.org/9.0/plpython.html)

La procédure est la même pour les autres langages présents dans la distribution standard. Pour les autres, c'est un peu plus complexe.

Prenons l'exemple de PL/R. Pour les utilisateurs de Linux ou Mac OS X, il est très facile de compiler et d'installer la librairie à partir des sources (même procédure que pour les contributions) pour autant que R soit préalablement installé (www.joeconway.com/plr/doc/plr-install.html). Pour les utilisateurs de Windows, il faut télécharger un des binaires proposés par www.joeconway.com/plr/ ou utiliser une des distributions « clés en main » de www.enterprisedb.com/, toujours avec R installé.

L'installation dans une base se fait par:

psql mabase < plr.sql

ou

en chargeant et en exécutant le fichier plr.sql dans PGAdmin III, par exemple

Ces langages ne sont pas exclusifs, c'est-à-dire qu'une même base peut contenir plusieurs langages et donc plusieurs possibilités de traitements.

Pratique

En pratique, plutôt que de vous « noyer » avec des exemples qui ne peuvent être que des cas particuliers, je me contenterai de vous proposer des liens vers des tutoriels ou exemples avec PL/Python, PL/R et PL/Perl. PL/R offre en particulier de très belles possibilités avec PostGIS (possibilité d'utiliser directement tous les paquets graphiques et/ou géospatiaux de R).

PL/Python

 - fichier Excel

création de la table dans Postgis

  1. CREATE TABLE matable (place_id serial PRIMARY KEY, place text, geom geometry);
  • 3692 lectures

création de la fonction en PL/Python: lecture du fichier excel et importation dans matable

  1. CREATE OR REPLACE FUNCTION lectexcel(param_filename text)
  2. RETURNS SETOF matable AS
  3. $BODY$
  4. 	import xlrd
  5. 	book = xlrd.open_workbook(param_filename)
  6. 	sh = book.sheet_by_index(0)
  7. 	# on assume que la première ligne contient les en-tête de colonnes 
  8. 	# et on n'en tient pas compte
  9. 	for rx in range(1,sh.nrows):
  10. 		yield(sh.cell_value(rowx=rx, colx=0), 
  11. 			sh.cell_value(rowx=rx, colx=1), 
  12. 			sh.cell_value(rowx=rx, colx=2)
  13. 			)
  14. $BODY$
  15. LANGUAGE 'plpythonu';
  • 4369 lectures

insertion dans matable avec la fonction précédente

  1. INSERT INTO matable (place, geom) 
  2. SELECT f.place, ST_SetSRID(ST_Point(f.lon,f.lat),4326) 
  3. FROM lectexcel('/postgis_in_action_data/ch10/Test.xls') AS f;
  • 4288 lectures

résultats

  1. select place, AsText(geom)from matable
  2.  
  3.   place  |        astext        
  4. ---------+----------------------
  5.  Place 1 | POINT(-120 50)
  6.  Place 2 | POINT(70 40)
  7.  Place 3 | POINT(-23.5 10.5678)
  8. (3 lignes)
  9.  
  10.  
  • 4181 lectures

 PL/R

de:

vers

en passant vers une simple fonction

fonction plot

  1. select from plotxy_result()
  • 3841 lectures

PL/Perl (le plus ancien, le plus utilisé, mais moins utilisé avec PostGIS)

Conclusions

À vous de jouer, maintenant. Pour ma part, je continue à tester des fonctions utilisant des modules Python ou des paquets R avec plus ou moins de réussite:  je suis en train d'essayer de charger les shapefiles automatiquement dans PostGIS avec les modules/paquets GDAL de Python ou de R, suivant l'exemple décrit avec Excel.

Tous les traitements ont étés faits sur Mac OS X, avec PostgreSQL 9, PostGIS 1.5.2,  Python 2.6.1 , R 2.11.1 (et Perl 5.10.0).


Site officiel : Postgis in action
Site officiel : site du livre avec codes, données ...
Site officiel : xlrd: module Python
Autres Liens : Python: géocodage - géolocalisation
Autres Liens : R - spatial : principes généraux et exemples de représentations cartographiques brutes (sans traitements (geo)statistiques)


GNU FDL Copyright (C) Permission is granted to copy, distribute and/or modify this document under the terms of the GNU Free Documentation License, Version 1.3 or any later version published by the Free Software Foundation; with no Invariant Sections, no Front-Cover Texts, and no Back-Cover Texts. A copy of the license is included in the section entitled "GNU Free Documentation License".

  • 14495 lectures
  • Email this page
  • del.icio.us
  • Facebook
  • LinkedIn
  • Twitter

Commentaires

suite

Martin Laloux - 30/12/2010 - 11:38

il faut rajouter le lien

ageoguy.blogspot.com/2010/12/squelettisation.html

pour l'utilisation de PL/R

Poster un nouveau commentaire

Le contenu de ce champ sera maintenu privé et ne sera pas affiché publiquement.

Similar entries



Theme by Dr. Radut.