PostGIS Spatial Database Engine UMN Mapserver Boston Geographic Information Systems    Checkout our PostGIS in Action book.  First chapter is a free download   PostGreSQL Object Relational Database Management System
GIS Books  Home   Consulting Services  About Boston GIS   Boston GIS Blog  Postgres OnLine Journal
PostGIS in Action is out in hard-copy,
download the first chapter
and SQL Primer for free. Tips and Tricks for PostGIS
  GIS Article comments Comments Rss
PLR Part 1: Up and Running with PL/R (PLR) in PostgreSQL: An almost Idiot's Guide

R is both a language as well as an environment for doing statistical analysis. R is available as Free Software under the GPL. For those familiar with environments such as S, MatLab, and SAS - R serves the same purpose. It has powerful constructs for manipulating arrays, packages for importing data from various datasources such as relational databases, csv, dbf files and spreadsheets. In addition it has an environment for doing graphical plotting and outputting the results to both screen, printer and file.

For more information on R check out R Project for Statistical Computing.

What is PL/R?

PL/R is a PostgreSQL language extension that allows you to write PostgreSQL functions and aggregate functions in the R statistical computing language.

With the R-language you can write such things as aggregate function for median which doesn't exist natively in PostgreSQL and exists only in a few relational databases natively (e.g. Oracle) I can think of. Even in Oracle the function didn't appear until version 10.

Another popular use of R is for doing Voronoi diagrams using the R Delaunay Triangulation and Dirichlet (Voronoi) Tesselation (deldir) Comprehensive R Archive Network (CRAN) package. When you combine this with PostGIS you have an extremely powerful environment for doing such things as nearest neighbor searches and facility planning.

In the past, PL/R was only supported on PostgreSQL Unix/Linux/Mac OSX environments. Recently that has changed and now PLR can be run on PostgreSQL windows installs. For most of this exercise we will focus on the Windows installs, but will provide links for instructions on Linux/Unix/Mac OSX installs.

Installing R and PL/R

In order to use PLR, you must first have the R-Language environment installed on the server you have PostgreSQL on. In the next couple of sections, we'll provide step by step instructions.

Installing PostgreSQL and PostGIS

It goes without saying. If you don't have PostgreSQL already - please install it and preferably with PostGIS support. Checkout Getting started with PostGIS

Installing R

  1. Next install R-Language:
    1. Pick a CRAN Mirror from http://cran.r-project.org/mirrors.html
    2. In Download and Install R section - pick your OS from the Precompiled binary section. In my case I am picking Windows (95 and later). Note there are binary installs for Linux, MacOS X and Windows.
    3. If you are given a choice between base and contrib. Choose base. This will give you an install containing the base R packages. Once you are up and running with R, you can get additional packages by using the builit in package installer in R or downloading from the web which we will do later.
    4. Run the install package. As of this writing the latest version of R is 2.10.1. The windows install file is named R-2.10.1-win32.exe
  2. Once you have installed the package - open up the RGUI. NOTE: For windows users - this is located on Start menu - Start -> Programs - >R -> R. 2.10.1. If for some reason you don't find it on the start menu - it should be located at "C:\Program Files\R\R-2.10.1\bin\Rgui.exe". If you are on a 64-bit system this will be in C:\Program Files (x86)\R\R-2.10.1
  3. Run the following command at the R GUI Console.
    update.packages()
  4. Running the above command should popup a dialog requesting for a CRAN MIRROR - pick one closest to you and then click OK.
  5. A sequence of prompts will then follow requesting if you would like to replace existing packages. Go ahead and type y to each one. After that you will be running the latest version of the currently installed packages.

Installing PL/R

Now that you have both PostgreSQL and R installed, you are now ready to install PLR procedural language for PostgreSQL.

  1. Go to http://www.joeconway.com/plr/
  2. For non-Windows users, follow the instructions here http://www.joeconway.com/plr/doc/plr-install.html.

    For Windows users:
    1. download the installation file from step 6 of http://www.joeconway.com/web/guest/pl/r
    2. As of this writing, there is no install setup for PostgreSQL 8.3/8.4/9* for windows. So what you need to do is copy the plr.dll into your PostgreSQL/(8.3/8.4/9.1/9.2/9.3)/lib folder. If you are installing on PostgreSQL 9.1+, make share to copy the .control, .sql files to share/extension folder.
    3. Set the enviroment variable (you get here by going to Control Panel -> System ->Advanced ->
      Environment Variables
    4. Add an R_HOME system variable and the R_HOME location of your R install. If you are on a 64-bit system and running 32-bit - it will be installed in Program Files (x86). On 32-bit (or a 64-bit running 64-bit install it will be installed in Program Files.
      If you are running R version 2.12 or above on Windows, the R bin folder has changed. Instead of bin it's bin\i386 or bin\x64. Also if you install the newer version, you'll need to use the binaries and manually register the paths and R_HOME yourself since the installer will not install. You can still use the plr.dll etc. See our other Quick Intro to PL/R for more details and examples.
    5. Edit Path system variable and add the R bin folder to the end of it. Do not remove existing ones, just add this to the end
    6. Restart your PostgreSQL service from control panel -> Services. On rare circumstances, you may need to restart the computer for changes to take effect.

Loading PL/R functionality into a database

In order to start using PL/R in a database, you need to load the help functions in the database. To do so do the following.

  1. Using PgAdmin III - select the database you want to enable with PL/R and then click the SQL icon to get to the query window.
  2. For users running PostgreSQL 9.1+, install by typing in SQL window:


    CREATE EXTENSION plr;

    If you are running on PostgreSLQ 9.0 or lower you have to install using the plr.sql file. Choose -> File -> Open -> path/to/PostgreSQL/8.4/contrib/plr.sql (NOTE: on Windows the default location is C:\Program Files\PostgreSQL\8.4\contrib\plr.sql

  3. Click the Green arrow to execute

Testing out PL/R

Next run the following commands from PgAdminIII or psql to test out R

SELECT * FROM plr_environ();
SELECT load_r_typenames();
SELECT * FROM r_typenames();
SELECT plr_array_accum('{23,35}', 42);

Next try to create a helper function (this was copied from (http://www.joeconway.com/plr/doc/plr-pgsql-support-funcs.html) - and test with the following


CREATE OR REPLACE FUNCTION plr_array (text, text)
RETURNS text[]
AS '$libdir/plr','plr_array'
LANGUAGE 'C' WITH (isstrict);

select plr_array('hello','world');

Using R In PostgreSQL

Creating Median Function in PostgreSQL using R

Below is a link creating a median aggregate function. This basically creates a stub aggregate function that calls the median function in R. http://www.joeconway.com/plr/doc/plr-aggregate-funcs.html NOTE: I ran into a problem here installing median from the plr-aggregate-funcs via PgAdmin. Gave R-Parse error when trying to use the function. I had to install median function by removing all the carriage returns (\r\n) so put the whole median function body in single line like below to be safe. Evidentally when copying from IE - IE puts in carriage returns instead of unix line breaks. When creating PL/R functions make sure to use Unix line breaks instead of windows carriage returns by using an editor such as Notepad++ that will allow you to specify unix line breaks.

create or replace function r_median(_float8) 
	returns float as 'median(arg1)' language 'plr';

CREATE AGGREGATE median (
  sfunc = plr_array_accum,
  basetype = float8,
  stype = _float8,
  finalfunc = r_median
);

create table foo(f0 int, f1 text, f2 float8);
insert into foo values(1,'cat1',1.21);
insert into foo values(2,'cat1',1.24);
insert into foo values(3,'cat1',1.18);
insert into foo values(4,'cat1',1.26);
insert into foo values(5,'cat1',1.15);
insert into foo values(6,'cat2',1.15);
insert into foo values(7,'cat2',1.26);
insert into foo values(8,'cat2',1.32);
insert into foo values(9,'cat2',1.30);

select f1, median(f2) from foo group by f1 order by f1;

In the next part of this series, we will cover using PL/R in conjunction with PostGIS.



Post Comments About PLR Part 1: Up and Running with PL/R (PLR) in PostgreSQL: An almost Idiot's Guide




 CommenterComment
1/18/2014 6:19:48 PMRegina1) Make sure you restarted the PostgeSQL service after you put in the environment variables.
2) Also make sure your path setting is right (this is often the cause)
the default bin path for x64 is C:\Program Files\R\R-3.0.2\bin\x64


3) Also if you are on 9.3, you are installing it wrong. You should install using


CREATE EXTENSION plr;
1/16/2014 9:52:27 AMEvelynHi,

Im trying to install PLR but got an error. I'm running Windows7, PostGreSQL 9.3 and R 3.0.2. I downloaded "plr-8.3.0.15-pg9.3-win64" and copied the plr.dll to {postgredir}/lib and the "plr.control", "plr--8.3.0.15.sql", and "plr--unpackaged--8.3.0.15.sql" to {postgredir}/share/extension. Also I add {postgresdir} and {Rdir} to PATH and set R_HOME.

When I open the pgadmin, and try to run the plr.sql:


SET autocommit TO 'on';

CREATE FUNCTION plr_call_handler()
RETURNS LANGUAGE_HANDLER
AS '$libdir/plr' LANGUAGE C;


The result is:


ERROR: could not load library «C:/Program Files/PostgreSQL/9.3/lib/plr.dll»: unknown error 126
SQL state: 58P01


Any idea of how could I solve it?

Many Thanks in advance!


4/12/2013 3:32:54 AMPeter SeifertHello Regina,

thank you very much! Sorry I missed the R_HOME path in the screen shot. -_-
Now lets play with R.

With best regards,

Peter
4/11/2013 10:48:07 PMReginaPeter,

I fixed your comments. I'll take care of the back slash commenting issue as well. I must have some legacy code in here.
4/11/2013 12:47:35 PMPeter SeifertUnfortunately the comment system mask backslashes with another backslash automatically .. ?
4/11/2013 12:45:31 PMPeter SeifertWell R_HOME has to point to:
c:\Program Files\R\R-3.0.0

....

Throwing an error would be nice .…
4/11/2013 12:07:41 PMPeter SeifertHello,

I have a strange problem on Windows 7 Enterprise ( 64-bit ).
-I installed R (R-3.0.0-win.exe).
-Set R_HOME to c:\Program Files\R\R-3.0.0\bin\x64\
and appended this path to PATH as well
-Copied plr.dll to c:\Program Files\PostgreSQL\9.1\lib\
-Restarted the PostgreSQL-server
-Installed plr with plr.sql in the database
-Example took forever:
http://lists.pgfoundry.org/pipermail/plr-general/2012-December/000719.html

Furthermore queries using those 'hanging' plr functions can't be killed and the PostgreSQL-server can't be stopped nicely ...

So I reinstalled PostgreSQL ( postgresql-9.1.9-1-windows-x64.exe ) together with PostGIS using the 'Application Stack Builder'.
-Added PostGIS to the same database:
CREATE EXTENSION postgis

No success.

The 'Hello, world' example, 'SELECT * FROM plr_environ();' and 'SELECT plr_array_accum('{23,35}', 42);' work. However, 'SELECT load_r_typenames();' hangs again ...

Any idea what is causing this? A missing R package perhaps?
Giving postgres all rights on the R directory did not make any change either.

Thank you very much in advance,

Peter
2/13/2013 4:05:50 PMLuisI typed too fast: '\i' not '\\i'.
2/13/2013 4:04:47 PMLuis Carrasco...it should be '/i' not '//i'
2/13/2013 4:02:56 PMLuis CarrascoI had better luck installing plr in Win 64 when (1) restarted not only PG but also the PC and (2) ran plr.sql from psql command line using:
\\i 'C:/pathto/plr.sql'
PG Admin will run plr.sql once, but if you try to run it again in another database it won't as some of the elements maybe have already been created and PG Admin will (I think) run a sql script only if all of it can be run. Hope this is helpful.
7/15/2011 5:13:51 PMgarretHi, I am attempting to install plr. I am running Window XP 32bit, PostgreSQL 8.4, PostGIS 1.5, and R 2.11.0. I have downloaded plr (plr-8.3.0.10-pg84.win32.zip)and copied and pasted the dll into ...\PostgreSQL\8.4\lib. After restarting my computer. No sql file was created, I attempted to create my own (http://www.joeconway.com/plr/doc/plr-install.html) but no dice there either I get "error: could not load library...". Do you have any suggestions for trouble shooting? Thank you.
1/27/2009 9:42:24 PMLeoJens,

Thanks - we have corrected
1/26/2009 4:37:06 AMJensI found the external link "How to Graph data in PostgreSQL with PL/R" under: http://www.varlena.com/GeneralBits/Tidbits/bernier/art13mar04/graphingWithR.html
11/18/2008 12:21:37 AMReginaUnfortunately no windows binary has come out for PostgreSQL 8.3 yet.
11/15/2008 8:37:14 PMzhengbuquanhow to install PL/R through windows binary
 
PostGIS
PostGIS in Action
Part 1: Getting Started With PostGIS: An almost Idiot's Guide (PostGIS 2.0)
How to Graph data in PostgreSQL with PL/R
Aggregate Functions in PL/R - Example creating Median Function
PostgreSQL PL/R Support Functions
Batch of PL/R Tests to run accumulated from the PLR source docs (plr.out)
From the sources here is a file with examples taken from the docs too, with more functions.
PLR Part 2: PL/R and PostGIS
PLR Part 3: PL/R and Geospatial Data Abstraction Library (GDAL) RGDAL
Voronoi Diagrams
http://www.cs.sunysb.edu/~algorith/files/voronoi-diagrams.shtml
SUNY Stony Brook note on Voronoi Diagrams and what they are used for

http://en.wikipedia.org/wiki/Voronoi
WikiPedia: Voronoi (Voronoy) Diagram

I asked a GIS analyst friend of mine, named Larry Braman, to give me his definition of what a voronoi diagram is and its purpose. He gave me this very nice succinct definition. His definition is as follows:

A Voronoi diagram divides the drawing into regions around each point that are shaped so that the borders of the regions are equidistant from the two nearest points. It's kind of the vector equivalent of grid mapping where you're trying to show boundaries of influence from point data.

Not sure where that definition originated, but I like it because its short and simple to understand. Manifold http://www.manifold.net/doc/7x/transform_voronoi_operators.htm has the same definition and has some other interesting tidbits on there site.

This Document is available under the GNU Free Documentation License 1.2 http://www.gnu.org/copyleft/fdl.html & for download at the BostonGIS site http://www.bostongis.com

Boston GIS      Copyright 2014      Paragon Corporation