PL/R Part 2: Functions that take arguments and the power of aggregation

In Intro to PL/R and R, we covered how to enable PL/R language in the database and wrote some PL/R functions that rendered plain text reports using the R environment. What makes combining R and PostgreSQL in PL/R most powerful is when you can start writing SQL summary queries that use R functions like any other SQL function.

In this next example, we'll be using PostGIS test runs from tests we autogenerated from the Official PostGIS documentation (Documentation Driven Testing (DDT)) as described in the Garden Test section of the PostGIS Developer wiki. We've also updated some of our logging generator and test patterns so future results may not represent what we demonstrated in the last article.

On a side note: Among the changes in the tests was to introduce more variants of the Empty Geometry now supported by PostGIS 2.0. Our beloved PostGIS 2.0 trunk is at the moment somewhat unstable when working with these new forms of emptiness and stuffing geometries in inappropriate places. At the moment it doesn't survive through the mindless machine gun battery of tests we have mercilessly inflicted. It's been great fun trying to build a better dummy while watching Paul run around patching holes to make the software more dummy proof as the dummy stumbles across questionable but amusing PostGIS use cases not gracefully handled by his new serialization and empty logic.

On yet another side note, it's nice to see that others are doing similar wonderful things with documentation. Check out Euler's comment on catalog tables where he uses the PostgreSQL SGML documentation to autogenerate PostgreSQL catalog table comments using OpenJade's OSX to convert the SGML to XML and then XSL similar to what we did with PostGIS documentation to autogenerate PostGIS function/type comments and as a platform for our test generator.

For our next exercises we'll be using the power of aggregation to push data into R instead of pg.spi.execute. This will make our functions far more reusable and versatile.

A basic PL/R function

For our first example we'll create a tally report similar to what we did in our Part 1, except the tally report data will be completely defined by how we group data in our SQL queries.

CREATE OR REPLACE FUNCTION tally_report(param_row_label text, param_col_label text,
    param_row_data text[], param_col_data text[]) 
  RETURNS text AS
$$
  #create the tally table
  p_break <- table(param_row_data, param_col_data)
 
  #calculate total row
  Total <-  margin.table(p_break,2)
  
  #define new table composed of 
  #our original tally with total row added
  
  p_total <- rbind(p_break,Total)
  
  #rename the headings to be what was passed in
  names(dimnames(p_total)) <- list(param_row_label,param_col_label)
  
  #we use capture.output to capture the string representation 
  #of the data tables as would be shown on screen
  # use ftable to prevent wrapping
  reportoutput <- sprintf("%s",  
        paste(capture.output(ftable(p_total)) , sep ="", collapse="\r")
  )
  return(reportoutput)
$$ language plr;

As you can see above, you can use the named arguments directly in PL/R without any specialness. As a general convention we like to prefix our input arguments with param_. This is just our personal convention and not dictated by PL/R or R. We do this so it doesn't conflict with other variable names we use in the function.

Using our PL/R function in SQL

PL/R seems to really dislike arrays with NULLs as inputs into functions or at least on Windows. Others on Linux have seen no problems with NULL elements in arrays, so the issue might just be limited to PL/R on windows. When we had NULLs in our array_agg, it would crash the PL/R function even when there was nothing in the function. The problem is being investigated.

Now we can use our function by writing an SQL statement:

-- report on only geometries that are not of some variant of empty or null
-- if the test did not complete mark it as F in the timings column
SELECT 
    tally_report(func, 'timings(ms)',
    array_agg(g1), 
    array_agg(
        COALESCE(
            CAST(date_part('epoch', age(log_end,log_start))*1000 As text), 
            'F')
        ) 
    ) As basic_tally 
FROM postgis_garden_log
WHERE func LIKE 'ST_3D%'
AND g1 NOT LIKE '%Empt%' 
    AND g2 NOT LIKE '%Empt%' 
    AND g1 NOT LIKE '%NULL%'AND g2 NOT LIKE '%NULL%'
GROUP BY func
ORDER BY func;

Our example uses the array_agg function introduced in PostgreSQL 8.4 so will not work on lower versions of PostgreSQL. The output of our query looks like:

                     timings(ms)
ST_3DClosestPoint      0 15 16   F
  CIRCULARSTRING       0  0  0  27
  CURVEPOLYGON         0  0  0  27
  GEOMETRYCOLLECTIONM  0  0  0  27
  GEOMETRYCOLLECTIONZ  0  0  0  27
  LINESTRING           6  0  0  21
  LINESTRINGM          5  1  0  21
  LINESTRINGZ          6  0  0  21
  MULTILINESTRING      4  1  1  21
  MULTILINESTRINGM     6  0  0  21
  MULTILINESTRINGZ     3  0  3  21
  MULTIPOINT           8  2  2  15
  MULTIPOINTM         12  0  0  15
  MULTIPOINTZ         11  1  0  15
  MULTIPOLYGON         0  0  0  27
  MULTIPOLYGONM        0  0  0  27
  MULTIPOLYGONZ        0  0  0  27
  MULTISURFACE         0  0  0  27
  POINT               11  0  1  15
  POINTM               8  2  2  15
  POINTZ              10  2  0  15
  POLYGON              0  0  0  27
  POLYGONM             0  0  0  27
  POLYGONZ             0  0  0  27
  POLYGONZM            0  0  0  27
  POLYHEDRALSURFACE    0  0  0  27
  TIN                  0  0  0  27
  TRIANGLE             0  0  0  27
  Total               90  9  9 621
                     timings(ms)
ST_3DDFullyWithin      0 15 16   F
  CIRCULARSTRING       0  0  0  27
  CURVEPOLYGON         0  0  0  27
  GEOMETRYCOLLECTIONM  0  0  0  27
  GEOMETRYCOLLECTIONZ  0  0  0  27
  LINESTRING           6  0  0  21
  LINESTRINGM          5  1  0  21
  LINESTRINGZ          5  1  0  21
  MULTILINESTRING      5  0  1  21
  MULTILINESTRINGM     4  2  0  21
  MULTILINESTRINGZ     6  0  0  21
  MULTIPOINT           9  3  0  15
  MULTIPOINTM         10  2  0  15
  MULTIPOINTZ         12  0  0  15
  MULTIPOLYGON         0  0  0  27
  MULTIPOLYGONM        0  0  0  27
  MULTIPOLYGONZ        0  0  0  27
  MULTISURFACE         0  0  0  27
  POINT               11  1  0  15
  POINTM               9  1  2  15
  POINTZ              10  0  2  15
  POLYGON              0  0  0  27
  POLYGONM             0  0  0  27
  POLYGONZ             0  0  0  27
  POLYGONZM            0  0  0  27
  POLYHEDRALSURFACE    0  0  0  27
  TIN                  0  0  0  27
  TRIANGLE             0  0  0  27
  Total               92 11  5 621
  :
  :

We can reuse this same function to give us a tally of the kinds of geometry companions that are supported by these functions with:

-- report on only geometries that are not of some variant of empty or null
-- if the test completes 
-- mark the geometries. If fails mark first geometry as row and second as F for failure 

SELECT 
    tally_report(func, 'supported',
    array_agg(g1), 
    array_agg(
            CASE WHEN log_end IS NOT NULL THEN g2 ELSE 'F' END
        ) 
    ) As basic_tally 
FROM postgis_garden_log
WHERE func LIKE 'ST_3D%Distance'
AND g1 NOT LIKE '%Empt%' 
    AND g2 NOT LIKE '%Empt%' 
    AND g1 NOT LIKE '%NULL%'AND g2 NOT LIKE '%NULL%'
GROUP BY func
ORDER BY func;

  
                     supported   F LINESTRING LINESTRINGM LINESTRINGZ MULTILINESTRING MULTILINESTRINGM MULTILINESTRINGZ MULTIPOINT MULTIPOINTM MULTIPOINTZ POINT POINTM POINTZ
ST_3DDistance                                                                                                                                                                
CIRCULARSTRING                 27          0           0           0               0                0                0          0           0           0     0      0      0
CURVEPOLYGON                   27          0           0           0               0                0                0          0           0           0     0      0      0
GEOMETRYCOLLECTIONM            27          0           0           0               0                0                0          0           0           0     0      0      0
GEOMETRYCOLLECTIONZ            27          0           0           0               0                0                0          0           0           0     0      0      0
LINESTRING                     21          0           0           0               0                0                0          1           1           1     1      1      1
LINESTRINGM                    21          0           0           0               0                0                0          1           1           1     1      1      1
LINESTRINGZ                    21          0           0           0               0                0                0          1           1           1     1      1      1
MULTILINESTRING                21          0           0           0               0                0                0          1           1           1     1      1      1
MULTILINESTRINGM               21          0           0           0               0                0                0          1           1           1     1      1      1
MULTILINESTRINGZ               21          0           0           0               0                0                0          1           1           1     1      1      1
MULTIPOINT                     15          1           1           1               1                1                1          1           1           1     1      1      1
MULTIPOINTM                    15          1           1           1               1                1                1          1           1           1     1      1      1
MULTIPOINTZ                    15          1           1           1               1                1                1          1           1           1     1      1      1
MULTIPOLYGON                   27          0           0           0               0                0                0          0           0           0     0      0      0
MULTIPOLYGONM                  27          0           0           0               0                0                0          0           0           0     0      0      0
MULTIPOLYGONZ                  27          0           0           0               0                0                0          0           0           0     0      0      0
MULTISURFACE                   27          0           0           0               0                0                0          0           0           0     0      0      0
POINT                          15          1           1           1               1                1                1          1           1           1     1      1      1
POINTM                         15          1           1           1               1                1                1          1           1           1     1      1      1
POINTZ                         15          1           1           1               1                1                1          1           1           1     1      1      1
POLYGON                        27          0           0           0               0                0                0          0           0           0     0      0      0
POLYGONM                       27          0           0           0               0                0                0          0           0           0     0      0      0
POLYGONZ                       27          0           0           0               0                0                0          0           0           0     0      0      0
POLYGONZM                      27          0           0           0               0                0                0          0           0           0     0      0      0
POLYHEDRALSURFACE              27          0           0           0               0                0                0          0           0           0     0      0      0
TIN                            27          0           0           0               0                0                0          0           0           0     0      0      0
TRIANGLE                       27          0           0           0               0                0                0          0           0           0     0      0      0
Total                         621          6           6           6               6                6                6         12          12          12    12     12     12
                    supported   F LINESTRING LINESTRINGM LINESTRINGZ MULTILINESTRING MULTILINESTRINGM MULTILINESTRINGZ MULTIPOINT MULTIPOINTM MULTIPOINTZ POINT POINTM POINTZ
ST_3DMaxDistance                                                                                                                                                             
CIRCULARSTRING                 27          0           0           0               0                0                0          0           0           0     0      0      0
CURVEPOLYGON                   27          0           0           0               0                0                0          0           0           0     0      0      0
GEOMETRYCOLLECTIONM            27          0           0           0               0                0                0          0           0           0     0      0      0
GEOMETRYCOLLECTIONZ            27          0           0           0               0                0                0          0           0           0     0      0      0
LINESTRING                     21          0           0           0               0                0                0          1           1           1     1      1      1
LINESTRINGM                    21          0           0           0               0                0                0          1           1           1     1      1      1
LINESTRINGZ                    21          0           0           0               0                0                0          1           1           1     1      1      1
MULTILINESTRING                21          0           0           0               0                0                0          1           1           1     1      1      1
MULTILINESTRINGM               21          0           0           0               0                0                0          1           1           1     1      1      1
MULTILINESTRINGZ               21          0           0           0               0                0                0          1           1           1     1      1      1
MULTIPOINT                     15          1           1           1               1                1                1          1           1           1     1      1      1
MULTIPOINTM                    15          1           1           1               1                1                1          1           1           1     1      1      1
MULTIPOINTZ                    15          1           1           1               1                1                1          1           1           1     1      1      1
MULTIPOLYGON                   27          0           0           0               0                0                0          0           0           0     0      0      0
MULTIPOLYGONM                  27          0           0           0               0                0                0          0           0           0     0      0      0
MULTIPOLYGONZ                  27          0           0           0               0                0                0          0           0           0     0      0      0
MULTISURFACE                   27          0           0           0               0                0                0          0           0           0     0      0      0
POINT                          15          1           1           1               1                1                1          1           1           1     1      1      1
POINTM                         15          1           1           1               1                1                1          1           1           1     1      1      1
POINTZ                         15          1           1           1               1                1                1          1           1           1     1      1      1
POLYGON                        27          0           0           0               0                0                0          0           0           0     0      0      0
POLYGONM                       27          0           0           0               0                0                0          0           0           0     0      0      0
POLYGONZ                       27          0           0           0               0                0                0          0           0           0     0      0      0
POLYGONZM                      27          0           0           0               0                0                0          0           0           0     0      0      0
POLYHEDRALSURFACE              27          0           0           0               0                0                0          0           0           0     0      0      0
TIN                            27          0           0           0               0                0                0          0           0           0     0      0      0
TRIANGLE                       27          0           0           0               0                0                0          0           0           0     0      0      0
Total                         621          6           6           6               6                6                6         12          12          12    12     12     12