Paragon Corpoation PostGIS Spatial Database Engine OSGeo.org The Open Source Geospatial Foundation UMN Mapserver Boston Geographic Information Systems   
FOSS4G International 2017, August 14th-18th 2017
   PostGreSQL Object Relational Database Management System
Home   About Boston GIS   Consulting Services  Boston GIS Blog  Postgres OnLine Journal  Planet PostGIS  PostGIS Funding

Purpose of BostonGIS

BostonGIS is a testbed for GIS and Web Mapping solutions utilizing open source, freely available and/or open gis technologies. We will be using mostly Boston, Massachusetts data to provide mapping and spatial database examples.

If you have some thoughts or comments on what you would like to see covered on this site, drop us a line on our Feed Back page.


GIS Tutorials on Opensource and OpenGIS technologies Tutorials
GIS Article comments Article and Tutorial Comments
Boston GIS BLog Rss FeedBoston GIS blog

PDF HTML All BostonGIS tutorials packaged together in an E-Book.


Boston GIS Store

Loading


Tutorial and Tip Sites
Desktop GIS
External Data
GIS Events and Groups
GIS SDKs and Frameworks
External Resources
Glossary
GIS Blogs Around Boston
External GIS Blogs
External Papers Articles
GIS Quick Guides and References
OpenStreetMap and OpenLayers Tutorials
PostGIS, pgRouting, and PostgreSQL Tutorials
Part 1: Getting Started With PostGIS: An almost Idiot's Guide (PostGIS 2.2) more ...
pgRouting: Loading OpenStreetMap with Osm2Po and route querying more ...
Part 1: Getting Started With PostGIS: An almost Idiot's Guide (PostGIS 2.0) more ...
OSCON 2009: Tips and Tricks for Writing PostGIS Spatial Queries more ...
PGCon2009: PostGIS 1.4, PostgreSQL 8.4 Spatial Analysis Queries, Building Geometries, Open Jump more ...
PLR Part 3: PL/R and Geospatial Data Abstraction Library (GDAL) RGDAL more ...
PostGIS Nearest Neighbor: A Generic Solution - Much Faster than Previous Solution more ...
Solving the Nearest Neighbor Problem in PostGIS more ...
PLR Part 2: PL/R and PostGIS more ...
PLR Part 1: Up and Running with PL/R (PLR) in PostgreSQL: An almost Idiot's Guide

Printer Friendly

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
Part 2 - PostGIS and SharpMap in ASP.NET 2.0 using VB.NET: Displaying the Maps more ... download
Part 1 - PostGIS and SharpMap in ASP.NET 2.0 using VB.NET: Compiling SharpMap with PostGIS more ...
Part 3: PostGIS Loading Data from Non-Spatial Sources more ...
Part 2: Introduction to Spatial Queries and SFSQL with PostGIS more ...
Miscellaneous Tutorials/Cheatsheets/Examples
SpatiaLite Tutorials
Boston External Map Examples
SQL Server 2008 Tutorials
UMN Mapserver Tutorials
General Commentary
 CommenterComment
6/5/2017 11:45:52 AMMilan HronskyI try to install PL/R extension on Ubuntu 16.04, Intel platform. I installed PostgreSQL and R with apt-get install (both from binary distrib.). Then I followed instructions in http://www.joeconway.com/plr/doc/plr-install.html.
I found the "contrib" directory in "/usr/share/postgresql/9.5/contrib". (I am not sure if this is where the Postgres source code is, as I installed PG from binary distr.?) I unpacked the tar.gz there, changed the locla directory to plr and typed make... Then I got the following error:
Makefile:40: ../../src/Makefile.global: No such file or directory
Makefile:41: /contrib/contrib-global.mk: No such file or directory
make: *** No rule to make target '/contrib/contrib-global.mk'. Stop.
I have doubts, if I shoud have compiled-installed Postgres from its source code? May be it is missing now?
Thank you in advance, Milan Hronsky, Slowakia
3/28/2017 1:10:05 PMJuditI am using postgres vesion 9.6 - can it be the problem?
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
Locations of visitors to BostonGIS
Boston GIS      Copyright 2017      Paragon Corporation